I have a strange situation, seen from the log:

Process 37278 waits for ExclusiveLock on advisory lock [16421,999999,12864385,2]; blocked by process 53807. 
Process 53807 waits for ExclusiveLock on advisory lock [16421,999999,12864395,2]; blocked by process 37278. 
Process 37278: SELECT * FROM zel_api.insert_event ($1 ,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24) 
Process 53807: SELECT * FROM zel_api.insert_event ($1 ,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24)",
"See server log for query details.",,,"SQL statement ""SELECT pg_advisory_xact_lock(999999, format('zel_event.%I', p_table_name)::regclass::oid::integer)""

This is already strange in itself, as it looks like two processes block the same advisory lock but neither can actually grab it.

The function which tries to acquire the lock is the following:

CREATE OR REPLACE FUNCTION zel_event.create_new_partition(
    p_table_name text
    _schema text;
    IF NOT EXISTS (table from catalog)
        PERFORM pg_advisory_xact_lock(999999, format('zel_event.%I', p_table_name)::regclass::oid::integer);

        IF NOT EXISTS (table from catalog)
            EXECUTE format('
                CREATE TABLE %1$I.%2$I (
                    LIKE zel_event.%2$I INCLUDING ALL
                ) INHERITS (zel_event.%2$I)', _schema, p_table_name);
        END IF;
    END IF;

LANGUAGE plpgsql

To my eyes none of the 'classic' deadlock causes stands here as there is only one logic...
The idea behind it is to create the new table on demand where the demand can come at a really high frequency from several connections. It is called when an insertion function does the insert aimed at the parent table, being diverted by a dispatcher trigger to the appropriate child table.

This is how the transaction looks like:

INSERT INTO parent (zel_api.insert_event())
        +----> child exists? ---no---> CREATE TABLE child
                      |                         |
                     yes                        |
                      |                         |
                      V                         V
              INSERT INTO child (zel_event.create_new_partition())   

The PostgreSQL version is 9.2

It would be very interesting to have some insights how this can happen (and mitigated, of course).

  • The error message mentions zel_api.insert_event(...), but you show the function definiton for zel_event.create_new_partition(.). Can you clarify? Nov 8, 2013 at 16:30
  • I updated my highly sophisticated flowchart with the function names - the advisory lock is exclusively taken in zel_event.create_new_partition(). Thanks for pointing it out!
    – dezso
    Nov 8, 2013 at 16:37
  • It would be nice to reproduce your issue but the code does not compile. "IF NOT EXISTS (table from catalog)" - not correct. format() function - unknown. is it a standard Pg thing?
    – filiprem
    Nov 12, 2013 at 14:17
  • format() is there since 9.1, the other one is pseudocode. We think we have the solution though, it's under testing.
    – dezso
    Nov 12, 2013 at 17:48

1 Answer 1


It's not the same advisory lock. Your log mentions a different table oid (off by 10) for the first process as the second. This looks like a classic deadlock situation where two processes are seeking the same locks just in a different order. The third number in the first is 12864385 while the third number in the second is 12864395.

So my suspicion is that both processes are trying to create the same partitions, just in a different order and so they deadlock.

One thing I have found helpful in puzzling situations like this is to go through and compare one digit at a time all the way through the series of numbers because with advisory locks it is way too easy to miss one digit subtly off like this.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.