Deadlock risk while inserting directly into partition?

From: Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Deadlock risk while inserting directly into partition?
Date: 2021-06-23 09:07:24
Message-ID: CAA4eK1LsFpjK5gL+0HEvoqB2DJVOi19vGAWbZBEx8fACOi5+_A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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.

Is this expected, if so why?

--
With Regards,
Amit Kapila.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Antonin Houska 2021-06-23 09:26:32 Re: decoupling table and index vacuum
Previous Message Peter Eisentraut 2021-06-23 08:55:41 Re: improvements in Unicode tables generation code