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

Thank you for an answer!
So reparenting of a constraint required that additional lock.
I made some measurements and can see that even that reparenting (and
additional lock) is required, the time it takes to make the attach is
smaller than when the foreign constraint hasn't been created beforehand.

So, to summarise, there is a tradeoff.
1. Create constraint before attach, but during attach additional tables
will be locked with AccessExculive. The time of an attach will be minimal
(for large tables it is still tens of ms in our db) but there is a higher
chance of deadlocks (as more tables locked with restrictive locks)
2. Just proceed with attach. The constraint will be created because the
parent table has the constraint in its definition. Because no reparenting
is required, no additional exclusive lock is held. But this process will
take more time to finish as a constraint is created from scratch.

Are these the only options?
Basically I want to add partitions dynamically to db while app is running.
I want to minimise the duration of "attach" command but also the amount of
locks held on several tables at once (to avoid deadlocks).

Once again, thanks for an answer. It is now clear to me why such behaviour
occurs.
Regards

On Sun, 10 Nov 2024, 20:07 Adrian Klaver, <adrian(dot)klaver(at)aklaver(dot)com> wrote:

> 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
>
>