Deadlock on same table when creating partition in PostgreSQL

Question

PostgreSQL 14.1. I have a partitioned table liked this:

create table dataset_session
(
 dataset_id integer not null references dataset (id)
 session_id integer not null,
 external_id uuid not null references session (external_id)
) partition by list (dataset_id);

When multiple clients simultaneously try to create a partition of it this often fails with a deadlock, like this:

 Detail: Process 21929 waits for AccessExclusiveLock on relation 173796 (dataset_session) of database 173691; blocked by process 26983.
Process 26983 waits for AccessExclusiveLock on relation 173796 (dataset_session) of database 173691; blocked by process 21929.
 Where: SQL statement "create table dataset_session_50 partition of dataset_session (
 constraint dataset_session_50_pkey primary key (session_id),
 constraint dataset_session_50_external_id_uq unique (external_id)
 ) for values in (50);

Note that both processes apparently have an exclusive lock on the same table (dataset_session, the parent table) - how is this possible? And how do I fix this?

My understanding was always that you need multiple statements in a transaction to cause a deadlock, but I'm only running that one statement in the transaction! (It's done by a procedure which calls "commit;" before and after.)

Edit: Interestingly, the deadlock does not happen with 2 other partitioned tables that seem very similar to this one.

Answer 1

While I never figured out the cause of this, I figured out the workaround: create the two constraints on the partition separately from the partition itself:

 execute format('create table dataset_session_%1$s partition of dataset_session
 /*(
 constraint dataset_session_%1$s_pkey primary key (session_id), -- deadlocks!
 constraint dataset_session_%1$s_external_id_uq unique (external_id) -- deadlocks!
 )*/
 for values in (%1$s);
 alter table dataset_session_%1$s add constraint dataset_session_%1$s_pkey primary key (session_id); -- does not deadlock!
 alter table dataset_session_%1$s add constraint dataset_session_%1$s_external_id_uq unique (external_id); -- does not deadlock!',
 dataset_internal_id);

Note that these still all run in the same execute statement and therefore in the same transaction, so I wouldn't have thought it would make a difference, but it does.

Answer 2

I've been having a similar issue the past couple days. In my case, adding a partition conflicts with inserting data into a partition via constraints, or selecting data from multiple partitions. I have been able to solve it by acquiring an explicit table lock on the base table before attempting to add a partition. Note that I initially tried an EXCLUSIVE lock, and still got occasional deadlocks. Here is the lock that works:

LOCK TABLE "<base_table>" IN ACCESS EXCLUSIVE MODE;

This acquires a lock on the base table, and all existing partition tables. It seems pretty intrusive, but it's done in a function that, in production, is only called about once per day. There is no need for explicit locking in "regular" queries that select/insert from/into.