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.
Show quoted text
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