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

On 10/21/24 1:40 AM, user wrote:
> ** 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.

1) Case 1

test=# \d films
Partitioned table "public.films"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
id | integer | | |
code | character(5) | | |
title | character varying(40) | | not null |
did | integer | | not null |
Partition key: LIST (code)
Foreign-key constraints:
"films_did_fkey" FOREIGN KEY (did) REFERENCES refs(id)
Number of partitions: 0

create table films_partition (LIKE films INCLUDING ALL);
CREATE TABLE

test=# \d+ films_partition
Table "public.films_partition"
Column | Type | Collation | Nullable | Default |
Storage | Compression | Stats target | Description
--------+-----------------------+-----------+----------+---------+----------+-------------+--------------+-------------
id | integer | | | |
plain | | |
code | character(5) | | | |
extended | | |
title | character varying(40) | | not null | |
extended | | |
did | integer | | not null | |
plain | | |
Access method: heap

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

test=# \d+ films
Partitioned table "public.films"
Column | Type | Collation | Nullable | Default |
Storage | Compression | Stats target | Description
--------+-----------------------+-----------+----------+---------+----------+-------------+--------------+-------------
id | integer | | | |
plain | | |
code | character(5) | | | |
extended | | |
title | character varying(40) | | not null | |
extended | | |
did | integer | | not null | |
plain | | |
Partition key: LIST (code)
Foreign-key constraints:
"films_did_fkey" FOREIGN KEY (did) REFERENCES refs(id)
Partitions: films_partition FOR VALUES IN ('dr ')

test=# \d+ films_partition
Table "public.films_partition"
Column | Type | Collation | Nullable | Default |
Storage | Compression | Stats target | Description
--------+-----------------------+-----------+----------+---------+----------+-------------+--------------+-------------
id | integer | | | |
plain | | |
code | character(5) | | | |
extended | | |
title | character varying(40) | | not null | |
extended | | |
did | integer | | not null | |
plain | | |
Partition of: films FOR VALUES IN ('dr ')
Partition constraint: ((code IS NOT NULL) AND (code = 'dr
'::character(5)))
Check constraints:
"check_code" CHECK (code = 'dr'::bpchar)
Foreign-key constraints:
TABLE "films" CONSTRAINT "films_did_fkey" FOREIGN KEY (did)
REFERENCES refs(id)
Access method: heap

2) Case 2

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

test=# \d films_partition
Table "public.films_partition"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
id | integer | | |
code | character(5) | | |
title | character varying(40) | | not null |
did | integer | | not null |
Check constraints:
"check_code" CHECK (code = 'dr'::bpchar)
Foreign-key constraints:
"fk_did" FOREIGN KEY (did) REFERENCES refs(id)

****Note the FK definition***

test=# ALTER TABLE films ATTACH PARTITION films_partition for values in
('dr');

test=# \d films_partition
Table "public.films_partition"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
id | integer | | |
code | character(5) | | |
title | character varying(40) | | not null |
did | integer | | not null |
Partition of: films FOR VALUES IN ('dr ')
Check constraints:
"check_code" CHECK (code = 'dr'::bpchar)
Foreign-key constraints:
TABLE "films" CONSTRAINT "films_did_fkey" FOREIGN KEY (did)
REFERENCES refs(id)

****Note the FK definition***

What you are seeing is the locking for

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

At this point films_partition is a stand alone table that you are
creating a FK back to refs. The ALTER TABLE films_partition ADD
CONSTRAINT command has no knowledge of the target table you are going to
attach films_partition to. When you do the ATTACH then a new FK is
created just the same as in Case 1.

>
>
> On Sun, 20 Oct 2024, 18:23 Adrian Klaver, <adrian(dot)klaver(at)aklaver(dot)com
> <mailto: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>
> > <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
> <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
> <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 <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>

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