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

** forwarding to mailing list, forgot to add header

Thanks for answering.
I think one misunderstanding happened.
The parent table has the foreign key constraint.
So attach partition will add this constraint for table being attached. (How
this compares to foreign keys not being considered, not sure).

Why is it that attach_partition does not require exclusive lock when
creating a constraint automatically?

What is more, you have provided a quote that states the lock is needed
because the table needs to be checked that all entries comply with the NEW
constraint.

Well it is not new when I manually create it before I attach.
It is new when I run attach command without previous manual constraint
creation, but then the lock is not created.

On Sun, 20 Oct 2024, 18:23 Adrian Klaver, <adrian(dot)klaver(at)aklaver(dot)com> wrote:

> On 10/20/24 04:31, user wrote:
> > Hello,
> > I was reading all the tips that could make the attach partition
> > operation seamless.
> > https://www.postgresql.org/docs/current/ddl-partitioning.html
> > <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,
>
> https://www.postgresql.org/docs/current/sql-altertable.html
>
> ATTACH PARTITION
>
> [...]
>
> "Currently FOREIGN KEY constraints are not considered. "
>
>
>
>
>
> but this lock is required when the constrint
> > already exists?
>
>
> Because I am pretty sure it is due to this statement:
>
> alter table films_partition ADD CONSTRAINT fk_did FOREIGN KEY (did)
> REFERENCES refs (id);
>
> Try:
>
> 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);
> COMMIT;
>
> Then:
>
> ALTER TABLE films ATTACH PARTITION films_partition for values in ('dr')
>
>
> Also from
>
> https://www.postgresql.org/docs/current/sql-altertable.html
>
> Take a look at:
>
> ADD table_constraint [ NOT VALID ]
>
>
> ...
>
> "Normally, this form will cause a scan of the table to verify that all
> existing rows in the table satisfy the new constraint. But if the NOT
> VALID option is used, this potentially-lengthy scan is skipped. The
> constraint will still be enforced against subsequent inserts or updates
> (that is, they'll fail unless there is a matching row in the referenced
> table, in the case of foreign keys, or they'll fail unless the new row
> matches the specified check condition). But the database will not assume
> that the constraint holds for all rows in the table, until it is
> validated by using the VALIDATE CONSTRAINT option. See Notes below for
> more information about using the NOT VALID option."
>
>
> >
> > Regards!
> >
> >
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>
>