Re: Race condition while creating a new partition

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(dot)zhidenkov(at)n26(dot)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(dot)zhidenkov(at)n26(dot)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.
>
>
>
>