Обсуждение: deadlock on declarative partitioned table (11.3)

on linux, pg11.3, i have a (declarative) partitioned table with a 
deadlock that i do not understand. one process does a copy to the 
partitioned table. another process is executing a jdbc batch of commands 
to "atomically" replace one of the table partitions. it has the 
following commands (autocommit is off).

    lock table foo;
    alter table foo detach partition foo_nn;
    alter table foo_nn rename to foo_nn_old;
    alter table new_foo_nn rename to foo_nn
    alter table foo attach partition foo_nn for values  from (...) to (...);
    commit;

the log says the deadlock is on the first alter table command but i 
think that is misleading. i suspect what is happening is that the 
explicit lock command attempts to lock each partition of foo in turn 
rather than locking all partitions immediately. so it acquires some 
locks in some unknown order while the copy acquires locks as needed. so 
they deadlock.

or is something else going on? is there a better way to atomically 
replace a table partition? the table is partitioned by timestamp but i 
don't think that matters.

thanks,

kevin

disregard. problem found. i was locking the wrong table.

On 9/16/2019 11:10 AM, Kevin Wilkinson wrote:
> on linux, pg11.3, i have a (declarative) partitioned table with a 
> deadlock that i do not understand. one process does a copy to the 
> partitioned table. another process is executing a jdbc batch of 
> commands to "atomically" replace one of the table partitions. it has 
> the following commands (autocommit is off).
>
>    lock table foo;
>    alter table foo detach partition foo_nn;
>    alter table foo_nn rename to foo_nn_old;
>    alter table new_foo_nn rename to foo_nn
>    alter table foo attach partition foo_nn for values  from (...) to 
> (...);
>    commit;
>
> the log says the deadlock is on the first alter table command but i 
> think that is misleading. i suspect what is happening is that the 
> explicit lock command attempts to lock each partition of foo in turn 
> rather than locking all partitions immediately. so it acquires some 
> locks in some unknown order while the copy acquires locks as needed. 
> so they deadlock.
>
> or is something else going on? is there a better way to atomically 
> replace a table partition? the table is partitioned by timestamp but i 
> don't think that matters.
>
> thanks,
>
> kevin
>
>
>