Re: deadlock on declarative partitioned table (11.3)

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