Re: Race condition while creating a new partition

On 16. Dec 2019, at 16:03, Justin <zzzzz.graf@gmail.com> wrote:

Hi Andrei, 

My gut reactions is Yes this is a deadlock caused by a race condition,  the error from psycopg2 tells us  that.  Question becomes what is causing these two process to collide, are both processes 33 and 37 python code,  As both are trying to access the same resource 16453  i would assume both sending the same command Create Table Partition.  Are these two connections from different computers or the same computer using multi threading??

What does Postgresql Log show what is going on??

On Mon, Dec 16, 2019 at 5:32 AM Andrei Zhidenkov <andrei.zhidenkov@n26.com> wrote:

I think that I’ve got a deadlock (which is handled by `exception when others` statements). But the problem is it occurs too fast. Is it possible to get a deadlock faster than deadlock_timeout? It’s set to 1s (default value) but it looks like I 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 but today we faced the problem when we failed to insert data due to a partition nonexistence for a small period of time. Have I missed something?
>
> Thank you.
>
> —
>
> With best regards, Andrei Zhidenkov.