Re: Adding future partition causes deadlock???

On Fri, May 10, 2024 at 10:12 AM Chris Hoover <chrish(at)aweber(dot)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