Basically, when we are inserting into a leaf relation (or lower level relation of the partitioned relation), we acquire the lock on the leaf relation during parsing time itself whereas parent lock is acquire during generate_partition_qual(). Now concurrently if we try to drop the partition root then it will acquire the lock in reverse order, i.e. parent first and then child so this will create a deadlock. Below example reproduce this case.
Setup: -------- CREATE TABLE test(a int, b int) partition by range(a); CREATE TABLE test1 partition of test for values from (1) to (100000); Test: ------ --Session1: INSERT INTO test1 VALUES (1, 4); -- let session is lock the relation test1 and make it wait before it locks test (put breakpoint in ExecInitModifyTable) --Session2: -- try to drop the top table which will try to take AccessExclusive lock on all partitions DROP TABLE test; --session3 -- see PG_LOCKS -- we can see that session1 has locked locked root table test(16384) waiting on test1(16387) as session1 is holding that lock locktype | database | relation | pid | mode | granted ---------------+----------+---------------+-------+---------------------+------------ relation | 5 | 16387 | 30368 | RowExclusiveLock | t relation | 5 | 16387 | 30410 | AccessExclusiveLock | f relation | 5 | 16384 | 30410 | AccessExclusiveLock | t (11 rows) --Session1, now as soon as you continue in gdb in session 1 it will hit the deadlock ERROR: 40P01: deadlock detected DETAIL: Process 30368 waits for AccessShareLock on relation 16384 of database 5; blocked by process 30410. Process 30410 waits for AccessExclusiveLock on relation 16387 of database 5; blocked by process 30368. HINT: See server log for query details. LOCATION: DeadLockReport, deadlock.c:1135 -- Regards, Dilip Kumar EnterpriseDB: http://www.enterprisedb.com
