On Wednesday, June 23, 2021 5:07 PM Amit Kapila <amit.kapil...@gmail.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
