RE: Deadlock risk while inserting directly into partition?

On Wednesday, June 23, 2021 5:07 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> I noticed that while inserting directly into a partition table we compute the
> PartitionCheckExpr by traversing all the parent partitions via
> ExecPartitionCheck()->RelationGetPartitionQual()->generate_partition_qual().
> We take AccessShareLock on parent tables while generating qual.
>
> Now, on the other hand, while dropping constraint on a partitioned table, we
> take the lock from parent to all the child tables.
>
> I think taking locks in opposite directions can lead to deadlock in these
> operations.
>
> I have tried with the below example on HEAD.
>
> Setup
> =======
> create or replace function func_dummy(price integer) returns integer as $$
> begin
> raise notice 'hello from func_dummy';
> return price;
> end;
> $$ language plpgsql immutable parallel unsafe;
>
>
> CREATE TABLE pt_test (a int, c char(1000)) PARTITION BY range (a); CREATE
> TABLE pt_test1 PARTITION OF pt_test FOR VALUES FROM (0) TO (100000);
> CREATE TABLE pt_test2 PARTITION OF pt_test FOR VALUES FROM (100000) TO
> (400000);
>
> ALTER TABLE pt_test ADD CONSTRAINT check_cons CHECK(func_dummy(a)
> == a);
>
> Actual test
> =============
> Session-1
> --------------
> Add breakpoint in generate_partition_qual(). Perform below statement.
> insert into pt_test2 values(100001, 'aaaa');
>
> Now, stop in the debugger just before taking AccessShareLock on the parent
> table.
>
> Session-2
> =========
> ALTER TABLE pt_test DROP CONSTRAINT check_cons;
>
> You will see that session-2 is waiting to get a lock on pt_test2.
> Then, continue debugging in session-1 which will lead to a deadlock.

I can reproduce this dead lock issue with the above steps.
And I can see the following error message.

postgres=# insert into pt_test2 values(100001, 'aaaa');
NOTICE: hello from func_dummy
ERROR: deadlock detected
DETAIL: Process 3068763 waits for AccessShareLock on relation 16385 of database 13027; blocked by process 3068966.
Process 3068966 waits for AccessExclusiveLock on relation 16393 of database 13027; blocked by process 3068763.
HINT: See server log for query details.

Best regards,
houzj