Re: Adding future partition causes deadlock???

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


On Fri, May 10, 2024 at 11:34 AM Chris Hoover <chrish@xxxxxxxxxx> wrote:

The create and then attach appears to work.  However, I agree with you, I really want to know why the create table as partition fails as a deadlock since that does not make any sense to me.

<server>:5432 <user>@<db>=> create table if not exists <partition_schema>.”<table>_2024-05-15" (LIKE <parent_schema>.<parent_table> INCLUDING All);
<server>:5432 <user>@<db>=> alter table <parent_schema>.<parent_table> attach partition <partition_schema>.”<table>_2024-05-15" for values from ( '2024-05-15 00:00:00-04' ) to ( '2024-05-16 00:00:00-04' );


Chris Hoover
Senior DBA
Cell: (803) 528-2269

On May 10, 2024, at 11:05 AM, Keith Fiske <keith.fiske@xxxxxxxxxxxxxxx> wrote:

On Fri, May 10, 2024 at 10:12 AM Chris Hoover <chrish@xxxxxxxxxx> wrote:

I've been trying to find out why adding a future partition to a moderately heavy insert/update table would be stopped by deadlocks.  I've searched in Google and seen others have the same issue, but no real resolution given.

Our table is partitioned by day and we try to stay 6 partitions ahead.  I have gone as far as to lock the parent table and active partition in "access exclusive mode" and still get the deadlock.  The application is only dealing with current time data.  It does not get future data, so no activity would be happening in the partitions for future dates.

Any idea why this is happening and how to resolve it?  (BTW, sometimes the partition create goes through fine, but seems like the majority fail.)

Here is what we are doing:
<server>:5432 <user>@<db>=> select maintain_partitions.maintain_partitions();
NOTICE:  v_is_active: [t], v_default_schema: [<partition_schema>]
NOTICE:  relation "<table>_2024-05-10" already exists, skipping
NOTICE:  relation "<table>_2024-05-11" already exists, skipping
NOTICE:  relation "<table>_2024-05-12" already exists, skipping
NOTICE:  relation "<table>_2024-05-13" already exists, skipping
NOTICE:  relation "<table>_2024-05-14" already exists, skipping
ERROR:  deadlock detected
DETAIL:  Process 32724 waits for ShareRowExclusiveLock on relation 23478 of database 16404; blocked by process 1015.
Process 1015 waits for ShareLock on transaction 3387140539; blocked by process 1055.
Process 1055 waits for RowExclusiveLock on relation 49192 of database 16404; blocked by process 32724.
HINT:  See server log for query details.
CONTEXT:  SQL statement "create table if not exists <partition_schema>."<table>_2024-05-15" partition of <parent_schema>.<table> for values from ( '2024-05-15 00:00:00-04' ) to ( '2024-05-16 00:00:00-04' )"
PL/pgSQL function maintain_partitions.maintain_partitions_create_tables(text,text,text,text,integer,text) line 57 at EXECUTE
SQL statement "SELECT maintain_partitions.maintain_partitions_create_tables(
                       v_parent_table.schemaname, v_parent_table.tablename,
                       v_table_format, v_partition_by, v_loop_num, v_partition_schema
PL/pgSQL function maintain_partitions.maintain_partitions(integer) line 51 at PERFORM
<server>:5432 <user>@<db>=>

Maintain_partitions is an extension I wrote that dynamically builds the create table sql for us and then executes it.  It takes into account various requirements we have as a business.  But you can see the sql it built and ran is just a normal create table statement.  We run it daily on multiple databases and this specific db is the only one showing this issue.


Chris Hoover
Senior DBA @ AWeber
Cell: 803-528-2269

Just curious, have you tried creating the table first without immediately attaching it as a child table then doing an ALTER TABLE to attach it? I ask this because that is how I did it in pg_partman and I have yet to have anyone report this bug. Might just be lucky so far tho.

Even if that does fix it, would be good to know why this causes a deadlock. Not sure myself at the moment.

Keith Fiske
Senior Database Engineer
Crunchy Data -

Another thought... is it possible for your extension maintenance process to have something try to run it in parallel? Your logic for handling that may be causing the deadlock on tables that are actively getting a lot of updates. I ask that because you use CREATE IF NOT EXISTS and your log is showing it's trying to create tables that already exist. I would want to avoid that situation if at all possible for regular maintenance. You certainly want to handle if the tables already exist, but it should be a rare occurrence that it's finding that that many children already exist. If it's not parallel runs causing it to try and make already existing tables, I'd look at revising your logic so it doesn't have to try and figure that out every time by trial and error.

Perhaps you can try to do an advisory lock to prevent any actual CREATE statements from being run at all if something else is trying to create them.

I know you said you've already got your own partition extension going, but if you have a chance to look at pg_partman, I'd be curious if there's any situations it's not handling for your business use-case. I use the advisory lock system myself to prevent parallel runs.

Keith Fiske
Senior Database Engineer
Crunchy Data -

[Index of Archives]     [Postgresql Home]     [Postgresql General]     [Postgresql Performance]     [Postgresql PHP]     [Postgresql Jobs]     [PHP Users]     [PHP Databases]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Yosemite Forum]

  Powered by Linux