Re: Adding future partition causes deadlock???

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

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

Hello,

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.

--

Thanks,

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 - http://crunchydata.com