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

On 11/10/24 05:18, user wrote:
> Hello,
> Sorry for nagging, but I would really like to find some answers.
> So, to reiterate. Experiment done as follows:
> """""""""""""""""""""'
> 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)
> 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);
> """""""""""""""""""""""""
> Then, when we open a transaction and try to attach:
> """""""""""""""""""""""""
>  BEGIN;
> ALTER TABLE films ATTACH PARTITION films_partition for values in ('dr')
> keep the transaction running..
> """"""""""""""""""""""""
> Once we check a locks, we will see that there is AccessExclusiveLock on
> table refs.
> """"""
> select relname, mode
>                 from pg_locks l
>                     join pg_class c on (relation = c.oid)
>                     join pg_namespace nsp on (c.relnamespace = nsp.oid);
> """""
> My questions are:
> 1. Why is postgres adding again a constraint? Can't it detect that
> foreign key already exists? I want to avoid locking partitioned table
> for too long.

I see, I missed it my previous post:

alter table films_partition ADD CONSTRAINT fk_did FOREIGN KEY (did)
REFERENCES refs (id);

\d films_partition

Foreign-key constraints:
"fk_did" FOREIGN KEY (did) REFERENCES refs(id)

ALTER TABLE films ATTACH PARTITION films_partition for values in ('dr')

\d films_partition

Foreign-key constraints:
TABLE "films" CONSTRAINT "films_did_fkey" FOREIGN KEY (did)
REFERENCES refs(id)

The FK constraint changes from being

films_partition <--> refs

to

films <--> refs

> 2. Even when attach is adding a foreign key again, why is there
> AccessExclusiveLock on refs table? foreign key constraint addition does
> not require it.
> https://pglocks.org/?pgcommand=ALTER%20TABLE%20ADD%20FOREIGN%20KEY%20(CHILD) <https://pglocks.org/?pgcommand=ALTER%20TABLE%20ADD%20FOREIGN%20KEY%20(CHILD)>
> 3. If I repeat the steps listed above, but do not add foreign key
> manually, then attach partition does not hold AccessExclusive lock on
> table refs. It still needs to add a foreign key, as "films" table has
> that constraint. Why is the AccessExclusive lock missing from "refs"
> table now?

Best guess because the FK is changing referencing table and in:

~//src/backend/commands/tablecmds.c

"CloneFkReferencing

For each FK constraint of the parent relation in the given list, find an
equivalent constraint in its partition relation that can be reparented;
if one cannot be found, create a new constraint in the partition as its
child."

[...]

addFkRecurseReferencing(wqueue,
fkconstraint,
partRel,
pkrel,
indexOid,
constrOid,
numfks,
confkey,
mapped_conkey,
conpfeqop,
conppeqop,
conffeqop,
numfkdelsetcols,
confdelsetcols,
false, /* no old check exists */
AccessExclusiveLock,
insertTriggerOid,
updateTriggerOid);

>
> Regards!

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com