Tag Info

Hot answers tagged

7

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 ...


4

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 ...


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 ...


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 ...


1

Basic answers This code works (if no NULL values in data!) Just 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: SELECT FROM my_table_ ORDER BY when_ DESC NULLS LAST LIMIT 1; Since you are selecting a couple of big columns (updated info ...



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