Tag Info

Hot answers tagged

9

I posted this to pgsql-bugs and the reply there from Tom Lane indicates this is a lock escalation issue, disguised by the mechanics of the way SQL language functions are processed. Essentially, the lock generated by the insert is obtained before the exclusive lock on the table: I believe the issue with this is that a SQL function will do parsing (and ...


5

Imagine you are out with a group of friends and the conversation turns to movies. Someone asks, "What did you think of 'The Three Musketeers'?" You respond, "Which one?" What additional information would you need to be absolutely certain you are both thinking of the same movie? The director's name? The production studio? The year it was released? One of the ...


5

14.04 shipped with PostgreSQL 9.3, so that's what you're going to get if you use Ubuntu's repositories. If you want PostgreSQL 9.4 or newer releases you need to add a 3rd party package repository, like the official PostgreSQL repositories from http://apt.postgresql.org/ . See the instructions there for details. (You should also probably read ...


4

Your table definition looks reasonable all over now. With all columns NOT NULL the UNIQUE constraint will work as expected - except for typos and minor differences in spelling, which may be rather common I am afraid. Consider @a_horse's comment. Alternative with functional unique index The other option would be a functional unique index (similar to what ...


4

My locale settings were not properly configured when PostgreSQL was installed. Purging and reinstalling didn't help. I followed the instructions here and that did the trick for me. Essential parts of the linked information reproduced below: The problem showed itself in the following manner: warning: Please check that your locale settings: LANGUAGE = ...


3

Basic answers Since you select a couple of big columns (info in comment) an index-only scan are probably not a viable option. This code works (if no NULL values in data!) Add NULLS LAST to make it work in any case, even with NULL values. The added clause won't hurt either way. Ideally, use the clause in the accompanying index as well: SELECT <some ...


3

I would use select distinct on instead of window function, then just join the days. select distinct on (date, client_id) date, id from orders inner join generate_series('2015-07-18'::date, '2015-07-19'::date, '1 day') date on start_date <= date and (end_date is null or date <= end_date) order by date, client_id, order_type desc ...


3

What you see in the logs is a prepared statement execution. Using prepared statements is the usual way to interact with the database from an application layer. For example, using Hibernate, one could write something like this (hopefully, the code snippet below is valid, did not test it): String sql = "SELECT first_name, last_name FROM customer WHERE email ...


3

Erwin describes a method of creating an index that violates PostgreSQL's assumptions about the index, producing results that are likely incorrect and do not match the underlying table. That's one kind of corruption. Another kind of corruption is where an index has blocks that are simply invalid - zeroed out, replaced with random values, etc. Most likely ...


3

Erwin's answer does a good job of answering the question as originally stated, however you added the comment: I want to check proactively if there is any corrupted index in the database, using the below query SELECT index_name, status from user_indexes where status='INVALID' Assuming you are talking about the indisvalid column in pg_index, or some ...


3

I think @Craig's comments are more important, addressing the intentions behind your question. In any case, to answer the question asked: One simple way (among many others) would be to fake an IMMUTABLE function and base an index on it like outlined in this answer from yesterday: CREATE OR REPLACE FUNCTION f_fake_immutable_ts() RETURNS timestamp LANGUAGE ...


3

The ID column has no advantage at all when it comes to the uniqueness you want/need to enforce. Uniqueness of whatever combination of attributes is never going to be enforced by adding a meaningless ID. Its "advantage" only shows when you ever get to the point where you'd need a new table that needs a foreign key to this one. In that case, and IF you have ...


3

The documentation about ALTER DEFAULT PRIVILEGES tells you a possible reason - it is not that clearly described, though. Let's see, what is said: You can change default privileges only for objects that will be created by yourself or by roles that you are a member of. This means that the default privileges defined by this statement applies only to ...


3

Assuming you run another statements before calling new_customer, and those acquire a lock that conflicts with EXCLUSIVE (basically, any data modification in the customer table), the explanation is very simple. One can reproduce the problem with a simple example (not even including a function): CREATE TABLE test(id INTEGER); 1st session: BEGIN; INSERT ...


2

You have two options: you change the empty strings in the original data to NULLs and then change the type you handle them while changing the type The first one would be a simple UPDATE table SET col=NULL where col=''; but this naturally takes time and is a bit unnecessary since you're changing the type after that. Second way is to handle the empty ...


2

You can see if a dump is running from within PostgreSQL by checking for pg_dump or pg_dumpall as the application_name in pg_stat_activity. It's not perfect though, since the client has to connect to each DB in turn, so there might be a period with no visible connection. I haven't verified whether pg_dumpall maintains its connection throughout, while running ...


2

There are two ways to run a replica. You can use either, or both together: Streaming replication, where the replica makes a PostgreSQL protocol connection to the standby as configured with primary_conninfo in recovery.conf; or WAL shipping, where the replica runs a restore_command (set in recovery.conf that fetches WAL archives to replay. See ...


2

From your description, it sounds like you need something like Bucardo to try and merge data from multiple sources into one. Though you'll probably have to write some custom conflict handlers to deal with data that conflicts across B, B1, and B2. There are examples out there like this for custom conflict resolution: ...


2

As with most things, it depends. First of all, if you're not 100% certain that the data will always be valid JSON, use a normal text type instead. If the data is indeed always JSON, I would use a JSON type if for no other reason that as documentation that the column contains JSON data. If there will ever be any chance of requiring indexing of data, go for ...


1

There was a bug with commit_delay in PostgreSQL 9.4.X. It was just fixed, and the fix will appear in version 9.4.5. See the thread on the PostgreSQL mailing list: http://www.postgresql.org/message-id/CACKh8C-tDjQUceMQeG3BUSHS5AA35SjvCD6HgRm-Vj7x5rXmeQ@mail.gmail.com


1

I'm assuming based on the post that the PostgreSQL database server and the process restoring the dump are on the same machine. If so: Is it possible to pause the import of the database? Not really. You could SIGSTOP the pg_restore or psql process and/or the corresponding postgres backend, but I wouldn't consider that my first-choice option. If not, ...


1

what will be the effect of this on fields of type "timestamp with timezone"? They're converted from their internal UTC representation to UTC-5 at the moment. They'll be converted using UTC+4 after. So applications will see a 9-hour time shift if they're ignoring the time zone. If they respect the time zone reported in the timestamp then there will ...


1

It looks like you changed the context of the /mnt/db/pg_data directory, but didn't apply the changes to everything underneath. You can verify this by running ls -Z on the files in question. If you don't see 'postgresql_db_t', then the changes weren't applied. restorecon -Rv /mnt/db/pg_data/ should set the SELinux contexts correctly. Forcing a relabel of ...


1

lat and lng are obviously numbers, so you should store them as appropriate numeric data type, not as text. That makes for smaller storage and disallows invalid input, it simplifies your query syntax and is also a bit faster overall. It's not going to do much for your query, though. For instance: lat | numeric lng | numeric So ...


1

You can easily find the subsequence when you cast the arrays to strings and replace the curly brackets with commas: translate(cast(sequence as varchar(10000)), '{}',',,') {1,3,17,25,377,424,242,1234} -> ',1,3,17,25,377,424,242,1234,' Do the same for the array you're searching for and add a leading and trailing %: '%' || translate(cast(searchedarray ...



Only top voted, non community-wiki answers of a minimum length are eligible