Re: Adding future partition causes deadlock???

On Fri, May 10, 2024 at 11:34 AM Chris Hoover <chrish(at)aweber(dot)com> wrote:

> Keith,
>
> 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);
> CREATE TABLE
> <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' );
> ALTER TABLE
> ```
>
> Thanks,
>
>
> Chris Hoover
> Senior DBA
> AWeber.com
> Cell: (803) 528-2269
> Email: chrish(at)aweber(dot)com
>
>
>
> On May 10, 2024, at 11:05 AM, Keith Fiske <keith(dot)fiske(at)crunchydata(dot)com>
> wrote:
>
>
>
> 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
>
>
>
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.

https://www.postgresql.org/docs/current/explicit-locking.html#ADVISORY-LOCKS

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.

https://github.com/pgpartman/pg_partman

--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com