Re: Fwd: Fwd: Postgres attach partition: AccessExclusive lock set on different tables depending on how attaching is performed - Mailing list pgsql-general

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.klaver@aklaver.com