Search Postgresql Archives

Re: deadlock on declarative partitioned table (11.3)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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








[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux