Subject: Deadlock due to locking order violation while inserting into a leaf relation

From: Dilip Kumar <[email-masked]>
Date: Sun, 8 Sep 2024 15:50:27 +0530

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