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
