Postgres attach partition: AccessExclusive lock set on different tables depending on how attaching is performed

From: user <user(at)pidu(dot)dev>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Postgres attach partition: AccessExclusive lock set on different tables depending on how attaching is performed
Date: 2024-10-20 11:31:23
Message-ID: CAPDhG9aVAt6ed7Lji=G3XhcGRFz0FxHkGiuxmRu=hZgWW9D0OA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,
I was reading all the tips that could make the attach partition operation
seamless. https://www.postgresql.org/docs/current/ddl-partitioning.html
There is a mention about check constraint that could be places before the
attach process. But to minimise the time when AccessExclusive lock is held
on my table, I wanted to push it further and also add indexes and foreign
keys BEFORE the attach command is invoked.
And here is a problem. When I run the attach command without foreign keys
being present beforehand on a table, there is only AccessExclusive lock on
a table I attach partition to.
BUT if my table to-be-attached has a foreign key constraint already, then
the referenced table will get the ExclusiveLock! I do not understand why is
it needed, the constraint already exists...

The reproduction: ( Postgres Version 14 )

CREATE TABLE refs (
id integer primary key,
did integer
);
CREATE TABLE films (
id integer,
code char(5) ,
title varchar(40) NOT NULL,
did integer NOT NULL references refs(id)
)
partition by list (code);

insert into refs values (5, 5)
create table films_partition (LIKE films INCLUDING ALL)
case 1: films_partition does not have a foreign key added before the attach
BEGIN;
insert into films_partition values (1, 'dr', 'musician',5)
alter table films_partition add constraint check_code check (code = 'dr');
ALTER TABLE films ATTACH PARTITION films_partition for values in ('dr')
keep the transaction running...

check the locks:

select relname, mode
from pg_locks l
join pg_class c on (relation = c.oid)
join pg_namespace nsp on (c.relnamespace = nsp.oid);
films relname, ShareUpdateExclusiveLock mode
films_partition relname, AccessShareLock mode
films_partition relname, RowExclusiveLock mode
films_partition relname, ShareRowExclusiveLock mode
films_partition relname, AccessExclusiveLock mode
refs relname, AccessShareLock mode
refs relname, RowShareLock mode
refs relname, ShareRowExclusiveLock mode

No AccessExclusive lock on "refs" table!

case 2: films_partition does have the foreign key contrain
BEGIN;
insert into films_partition values (1, 'dr', 'musician',5)
alter table films_partition add constraint check_code check (code = 'dr');
alter table films_partition ADD CONSTRAINT fk_did FOREIGN KEY (did)
REFERENCES refs (id);
ALTER TABLE films ATTACH PARTITION films_partition for values in ('dr')
keep the transaction running...

check the locks:

films relname, ShareUpdateExclusiveLock mode
films_partition relname, AccessShareLock mode
films_partition relname, RowExclusiveLock mode
films_partition relname, ShareRowExclusiveLock mode
films_partition relname, AccessExclusiveLock mode
refs relname, AccessShareLock mode
refs relname, RowShareLock mode
refs relname, ShareRowExclusiveLock mode
refs relname, AccessExclusiveLock mode

There is AccessExclusiveLock on "refs" table!

Conclusion
I really don't want the "attach partition" to take too much time, so I want
to have all the constraints added before it is run. And indeed, the time is
reduced. But this additional lock now increases the chance of deadlocks, as
AccessExclusive locks are grabbed on many tables referenced by foreing
keys. Is there anything I can do better? Whi is it that attach_partition
adds a foreign key without additional AccessExclusive lock, but this lock
is required when the constrint already exists?

Regards!

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2024-10-20 16:23:19 Re: Postgres attach partition: AccessExclusive lock set on different tables depending on how attaching is performed
Previous Message Vijaykumar Jain 2024-10-19 18:31:07 Re: explain vs auto_explain