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

-- 
Adrian Klaver
adrian.klaver@aklaver.com