Обсуждение: Race condition while creating a new partition

We use this code in order to automatically create new partitions for a partitioned table (Postgres 10.6):

begin
  insert into <partitioned_table>;
exception when undefined_table then
  begin
    <create_unexistent_partition>
    -- A concurrent txn has created the new partition
    exception when others then end;
    -- Insert data into the new partition
    insert into <partitioned_table>;
  end;
end;

As far as I understand we should always have a new partition created either in current or in concurrent transaction but
todaywe faced the problem when we failed to insert data due to a partition nonexistence for a small period of time.
HaveI missed something? 

Thank you.

—

With best regards, Andrei Zhidenkov.

I think that I’ve got a deadlock (which is handled by `exception when others` statements). But the problem is it occurs
toofast. Is it possible to get a deadlock faster than deadlock_timeout? It’s set to 1s (default value) but it looks
likeI get it immidiately. Error message I’m getting after removing the second exception handling is the following: 

psycopg2.errors.DeadlockDetected: deadlock detected
DETAIL:  Process 33 waits for AccessExclusiveLock on relation 16453 of database 16384; blocked by process 37.
Process 37 waits for AccessExclusiveLock on relation 16453 of database 16384; blocked by process 33.
HINT:  See server log for query details.
CONTEXT:  SQL statement "
            CREATE TABLE IF NOT EXISTS prov_level_1.log_*__2019_12_16_10_25_46 PARTITION OF prov_level_1.log_*
                FOR VALUES FROM ('2019-12-16 10:25:46+00') TO ('2019-12-16 10:25:47+00');
        "

> On 15. Nov 2019, at 11:49, Andrei Zhidenkov <andrei.zhidenkov@n26.com> wrote:
>
> We use this code in order to automatically create new partitions for a partitioned table (Postgres 10.6):
>
> begin
>  insert into <partitioned_table>;
> exception when undefined_table then
>  begin
>    <create_unexistent_partition>
>    -- A concurrent txn has created the new partition
>    exception when others then end;
>    -- Insert data into the new partition
>    insert into <partitioned_table>;
>  end;
> end;
>
> As far as I understand we should always have a new partition created either in current or in concurrent transaction
buttoday we faced the problem when we failed to insert data due to a partition nonexistence for a small period of time.
HaveI missed something? 
>
> Thank you.
>
> —
>
> With best regards, Andrei Zhidenkov.

I’m creating a new partition for every second deliberately in order to faster reproduce a bug I have on the live
environment.In the live environment a new partitions are being created every one day. More to that, we create new
partitionsin advance and this procedure is only a backup mechanisum in case of something goes wrong. Now I think that
theonly way to avoid errors like this is to use explicit locking, I tried advisory locks and it worked well.  

> On 17. Dec 2019, at 00:06, Michael Lewis <mlewis@entrata.com> wrote:
>
> It looks like you are creating a partition for each minute of the day (when logs get inserted for a given minute at
least).Would it be at all reasonable to have an hourly or daily job which creates the partitions ahead of when they are
actuallyneeded? If partitions went unused in the recent past, you could also drop those if appropriate to your
workflow.I just wonder about doing the work just-in-time via 2+ connections vs doing it from a single process to avoid
thechance of deadlock completely.