deadlock on declarative partitioned table (11.3)

From: Kevin Wilkinson <w(dot)kevin(dot)wilkinson(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: deadlock on declarative partitioned table (11.3)
Date: 2019-09-16 18:10:22
Message-ID: 80032bef-7c43-ae4c-a7cc-abb81e2ccadc@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message stan 2019-09-16 18:52:08 Re: FW: Re: FW: Re: Shouldn;t this trigger be called?
Previous Message George Neuner 2019-09-16 16:24:25 Re: Cascade Trigger Not Firing