Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t6sk7-00GDDV-85 for pgsql-general@arkaria.postgresql.org; Fri, 01 Nov 2024 14:35:16 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1t6sk4-00EPrO-EF for pgsql-general@arkaria.postgresql.org; Fri, 01 Nov 2024 14:35:12 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t6sk3-00EPrF-I9 for pgsql-general@lists.postgresql.org; Fri, 01 Nov 2024 14:35:12 +0000 Received: from eu-shark1.inbox.eu ([195.216.236.81]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t6sjy-003yWH-Fc for pgsql-general@lists.postgresql.org; Fri, 01 Nov 2024 14:35:10 +0000 Received: from eu-shark1.inbox.eu (localhost [127.0.0.1]) by eu-shark1-out.inbox.eu (Postfix) with ESMTP id 4Xg3Kz0d6fzJjjBp for ; Fri, 1 Nov 2024 16:35:03 +0200 (EET) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=signed-inbox.eu; s=p20220330; t=1730471703; x=1730473503; bh=VqzBq10ky8gQQyJo80ihM53O3scWv9OO5Gw/IuWGBA4=; h=References:In-Reply-To:Reply-To:From:Date:Message-ID:Subject:To: Cc:Content-Type:X-ESPOL:From:Date:To:Cc:Message-ID:Subject: Reply-To; b=rlUkP5I+WryIeJen/CuhMK1bmlHoKjQNWD2gAAoGGMyQU8n4lXHL34ingRuE6+E2z PuhrPQOP1mrCLRIiL73H6jZxTsRaa5FuHwhSkcmyOh7BpBwTeFbtSwLsM6ENa+raPZ 9SVHYeFZnnAGD2hV+Fw4R9m+RuzNwJDGrh+8mvOA= Received: from eu-shark1.inbox.eu (localhost [127.0.0.1]) by eu-shark1-in.inbox.eu (Postfix) with ESMTP id 4Xg3Ky71lwzJjjBQ for ; Fri, 1 Nov 2024 16:35:02 +0200 (EET) Received: from eu-shark1.inbox.eu ([127.0.0.1]) by localhost (eu-shark1.inbox.eu [127.0.0.1]) (spamfilter, port 35) with ESMTP id uI7l55okppT2 for ; Fri, 1 Nov 2024 16:35:02 +0200 (EET) Received: from mail.inbox.eu (eu-pop2 [127.0.0.1]) by eu-shark1-in.inbox.eu (Postfix) with ESMTP id 4Xg3Ky0QNSzJjj6N for ; Fri, 1 Nov 2024 16:35:02 +0200 (EET) Received: by mail-ed1-f49.google.com with SMTP id 4fb4d7f45d1cf-5c935d99dc5so2744988a12.1 for ; Fri, 01 Nov 2024 07:35:01 -0700 (PDT) X-Gm-Message-State: AOJu0Yx2p8u49OBTYv/z9Oueg1OHPlvJnNHdeFGeTdXGQHARGDLiU3TY pYTVVLum70DBgiRrmtr4JPIXiwUNsT1YfrFdjaAUNMFgf5boYlE71shNTDZGBOeO8uLqDKxljg4 P+oO/FokaO8yUc5W4SaP2kqyDaE4= X-Google-Smtp-Source: AGHT+IHLp6hb2bhdBHoon4PohZXeZaB+EPHKuZmVV6w8fyWGnNOH2P16mZm5HcharHopMEdnOwN31y4CuGpEICL2FRg= X-Received: by 2002:a05:6402:2350:b0:5c9:8705:ea9c with SMTP id 4fb4d7f45d1cf-5cbbf8e176cmr17785121a12.19.1730471699222; Fri, 01 Nov 2024 07:34:59 -0700 (PDT) MIME-Version: 1.0 References: <254c31c2-ff15-4720-a4fc-f0969d677d72@aklaver.com> <4ebbbcf5-9c35-4711-bd7c-06a56a31aeff@aklaver.com> In-Reply-To: <4ebbbcf5-9c35-4711-bd7c-06a56a31aeff@aklaver.com> Reply-To: user@pidu.dev From: user Date: Fri, 1 Nov 2024 15:35:12 +0100 X-Gmail-Original-Message-ID: Message-ID: Subject: Re: Fwd: Postgres attach partition: AccessExclusive lock set on different tables depending on how attaching is performed To: adrian.klaver@aklaver.com Cc: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000ed1d0d0625dad6db" X-Virus-Scanned: OK X-ESPOL: 6N1mlZY9ZDPxz0PhHDvcGXVEok8qQJSF9uW62QNRmWX4MSaaekER List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000ed1d0d0625dad6db Content-Type: text/plain; charset="UTF-8" Hello Adrian, My apology for answering so late. Indeed I have not splitted my transactions correctly in my first example. BUT, if you change the syntaxt so that attach is performed in its own transaction, result is the same. Try it out! Also you have told me that I am seeing ExclusiveLock on refs table because it is from foreign key constraint. Well this is incorrect, foreign key does not take the ExclusiveLock in any situation. https://pglocks.org/?pgcommand=ALTER%20TABLE%20ADD%20FOREIGN%20KEY%20(CHILD) Could you try again with only attach being in its own transaction? You should reproduce it. Regards On Mon, 21 Oct 2024 at 20:31, Adrian Klaver wrote: > > > 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, > > 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 > > > > > > > > > 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.klaver@aklaver.com > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > --000000000000ed1d0d0625dad6db Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hello Adrian,
My apology for answering so l= ate.

Indeed I have not splitted my transactions co= rrectly in my first example. BUT, if you change the syntaxt so that attach = is performed in its own transaction, result is the same. Try it out!
<= div>
Also you have told me that I am seeing ExclusiveLock on = refs table because it is from foreign key constraint. Well this is incorrec= t, foreign key does not take the ExclusiveLock in any situation.
https://pglocks.org/?pgcommand=3DALTER%20TABLE%20ADD%20FOREIGN%20= KEY%20(CHILD)

Could you try again with only attach b= eing in its own transaction? You should reproduce it.
Regard= s

On Mon, 21 Oct 2024 at 20:31, Adrian Klaver <adrian.klaver@aklaver.com> wrote:


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 <= br> > 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=3D# \d films
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Partitioned t= able "public.films"
=C2=A0 Column |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Type=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
=C2=A0 id=C2=A0 =C2=A0 =C2=A0| integer=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0|=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 |
=C2=A0 code=C2=A0 =C2=A0| character(5)=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 |
=C2=A0 title=C2=A0 | character varying(40) |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0| not null |
=C2=A0 did=C2=A0 =C2=A0 | integer=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0|=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| not null |
Partition key: LIST (code)
Foreign-key constraints:
=C2=A0 =C2=A0 =C2=A0"films_did_fkey" FOREIGN KEY (did) REFERENCES= refs(id)
Number of partitions: 0

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

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


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

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

test=3D# \d+ films_partition
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0Table "public.films_partition"
=C2=A0 Column |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Type=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 | Collation | Nullable | Default |
Storage=C2=A0 | Compression | Stats target | Description
--------+-----------------------+-----------+----------+---------+---------= -+-------------+--------------+-------------
=C2=A0 id=C2=A0 =C2=A0 =C2=A0| integer=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0|=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|
plain=C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|=C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |
=C2=A0 code=C2=A0 =C2=A0| character(5)=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|
extended |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |
=C2=A0 title=C2=A0 | character varying(40) |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0| not null |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|
extended |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |
=C2=A0 did=C2=A0 =C2=A0 | integer=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0|=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| not null |=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0|
plain=C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|=C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |
Partition of: films FOR VALUES IN ('dr=C2=A0 =C2=A0')
Partition constraint: ((code IS NOT NULL) AND (code =3D 'dr
'::character(5)))
Check constraints:
=C2=A0 =C2=A0 =C2=A0"check_code" CHECK (code =3D 'dr'::bp= char)
Foreign-key constraints:
=C2=A0 =C2=A0 =C2=A0TABLE "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 =3D '= dr');

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

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

****Note the FK definition***

test=3D# ALTER TABLE films ATTACH PARTITION films_partition for values in <= br> ('dr');

test=3D# \d films_partition
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Table "= public.films_partition"
=C2=A0 Column |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Type=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
=C2=A0 id=C2=A0 =C2=A0 =C2=A0| integer=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0|=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 |
=C2=A0 code=C2=A0 =C2=A0| character(5)=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 |
=C2=A0 title=C2=A0 | character varying(40) |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0| not null |
=C2=A0 did=C2=A0 =C2=A0 | integer=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0|=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| not null |
Partition of: films FOR VALUES IN ('dr=C2=A0 =C2=A0')
Check constraints:
=C2=A0 =C2=A0 =C2=A0"check_code" CHECK (code =3D 'dr'::bp= char)
Foreign-key constraints:
=C2=A0 =C2=A0 =C2=A0TABLE "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:
>
>=C2=A0 =C2=A0 =C2=A0On 10/20/24 04:31, user wrote:
>=C2=A0 =C2=A0 =C2=A0 > Hello,
>=C2=A0 =C2=A0 =C2=A0 > I was reading all the tips that could make th= e attach partition
>=C2=A0 =C2=A0 =C2=A0 > operation seamless.
>=C2=A0 =C2=A0 =C2=A0 > https://w= ww.postgresql.org/docs/current/ddl-partitioning.html
>=C2=A0 =C2=A0 =C2=A0<https://www= .postgresql.org/docs/current/ddl-partitioning.html>
>=C2=A0 =C2=A0 =C2=A0 > <https= ://www.postgresql.org/docs/current/ddl-partitioning.html
>=C2=A0 =C2=A0 =C2=A0<https://www= .postgresql.org/docs/current/ddl-partitioning.html>>
>=C2=A0 =C2=A0 =C2=A0There is
>=C2=A0 =C2=A0 =C2=A0 > a mention about check constraint that could b= e places before the
>=C2=A0 =C2=A0 =C2=A0attach
>=C2=A0 =C2=A0 =C2=A0 > process. But to minimise the time when Access= Exclusive lock is
>=C2=A0 =C2=A0 =C2=A0held on
>=C2=A0 =C2=A0 =C2=A0 > my table, I wanted to push it further and als= o add indexes and
>=C2=A0 =C2=A0 =C2=A0foreign
>=C2=A0 =C2=A0 =C2=A0 > keys BEFORE the attach command is invoked. >=C2=A0 =C2=A0 =C2=A0 > And here is a problem. When I run the attach = command without foreign
>=C2=A0 =C2=A0 =C2=A0 > keys being present beforehand on a table, the= re is only
>=C2=A0 =C2=A0 =C2=A0AccessExclusive
>=C2=A0 =C2=A0 =C2=A0 > lock on a table I attach partition to.
>=C2=A0 =C2=A0 =C2=A0 > BUT if my table to-be-attached has a foreign = key constraint already,
>=C2=A0 =C2=A0 =C2=A0 > then the referenced table will get the Exclus= iveLock! I do not
>=C2=A0 =C2=A0 =C2=A0 > understand why is it needed, the constraint a= lready exists...
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > The reproduction: ( Postgres Version 14 )
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > CREATE TABLE refs (
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 id integer primary key,
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 did integer
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 );
>=C2=A0 =C2=A0 =C2=A0 > CREATE TABLE films (
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 id integer,
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 code char(5) ,
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 title varchar(40) NOT NULL,
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 did integer NOT NULL references refs(id) >=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 )
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 partition by list (code);
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > insert into refs values (5, 5)
>=C2=A0 =C2=A0 =C2=A0 > create table films_partition (LIKE films INCL= UDING ALL)
>=C2=A0 =C2=A0 =C2=A0 > case 1: films_partition does not have a forei= gn key added before
>=C2=A0 =C2=A0 =C2=A0the attach
>=C2=A0 =C2=A0 =C2=A0 > BEGIN;
>=C2=A0 =C2=A0 =C2=A0 > insert into films_partition values (1, 'd= r', 'musician',5)
>=C2=A0 =C2=A0 =C2=A0 > alter table films_partition add constraint ch= eck_code check (code
>=C2=A0 =C2=A0 =C2=A0=3D 'dr');
>=C2=A0 =C2=A0 =C2=A0 > ALTER TABLE films ATTACH PARTITION films_part= ition for values in
>=C2=A0 =C2=A0 =C2=A0('dr')
>=C2=A0 =C2=A0 =C2=A0 > keep the transaction running...
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > check the locks:
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > select relname, mode
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 from pg_locks l
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 join pg_class c on (relation =3D c.oid)
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 join pg_namespace nsp on (c.relnamespace = =3D
>=C2=A0 =C2=A0 =C2=A0nsp.oid);
>=C2=A0 =C2=A0 =C2=A0 > films relname, ShareUpdateExclusiveLock mode<= br> >=C2=A0 =C2=A0 =C2=A0 > films_partition relname, AccessShareLock mode=
>=C2=A0 =C2=A0 =C2=A0 > films_partition relname, RowExclusiveLock mod= e
>=C2=A0 =C2=A0 =C2=A0 > films_partition relname, ShareRowExclusiveLoc= k mode
>=C2=A0 =C2=A0 =C2=A0 > films_partition relname, AccessExclusiveLock = mode
>=C2=A0 =C2=A0 =C2=A0 > refs relname, AccessShareLock mode
>=C2=A0 =C2=A0 =C2=A0 > refs relname, RowShareLock mode
>=C2=A0 =C2=A0 =C2=A0 > refs relname, ShareRowExclusiveLock mode
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > No AccessExclusive lock on "refs" t= able!
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > case 2: films_partition does have the foreign= key contrain
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0BEGIN;
>=C2=A0 =C2=A0 =C2=A0 > insert into films_partition values (1, 'd= r', 'musician',5)
>=C2=A0 =C2=A0 =C2=A0 > alter table films_partition add constraint ch= eck_code check (code
>=C2=A0 =C2=A0 =C2=A0=3D 'dr');
>=C2=A0 =C2=A0 =C2=A0 > alter table films_partition ADD CONSTRAINT fk= _did FOREIGN KEY (did)
>=C2=A0 =C2=A0 =C2=A0 > REFERENCES refs (id);
>=C2=A0 =C2=A0 =C2=A0 > ALTER TABLE films ATTACH PARTITION films_part= ition for values in
>=C2=A0 =C2=A0 =C2=A0('dr')
>=C2=A0 =C2=A0 =C2=A0 > keep the transaction running...
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > check the locks:
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > films relname, ShareUpdateExclusiveLock mode<= br> >=C2=A0 =C2=A0 =C2=A0 > films_partition relname, AccessShareLock mode=
>=C2=A0 =C2=A0 =C2=A0 > films_partition relname, RowExclusiveLock mod= e
>=C2=A0 =C2=A0 =C2=A0 > films_partition relname, ShareRowExclusiveLoc= k mode
>=C2=A0 =C2=A0 =C2=A0 > films_partition relname, AccessExclusiveLock = mode
>=C2=A0 =C2=A0 =C2=A0 > refs relname, AccessShareLock mode
>=C2=A0 =C2=A0 =C2=A0 > refs relname, RowShareLock mode
>=C2=A0 =C2=A0 =C2=A0 > refs relname, ShareRowExclusiveLock mode
>=C2=A0 =C2=A0 =C2=A0 > refs relname, AccessExclusiveLock mode
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > There is AccessExclusiveLock on "refs&qu= ot; table!
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > Conclusion
>=C2=A0 =C2=A0 =C2=A0 > I really don't want the "attach part= ition" to take too much time,
>=C2=A0 =C2=A0 =C2=A0so I
>=C2=A0 =C2=A0 =C2=A0 > want to have all the constraints added before= it is run. And
>=C2=A0 =C2=A0 =C2=A0indeed, the
>=C2=A0 =C2=A0 =C2=A0 > time is reduced. But this additional lock now= increases the
>=C2=A0 =C2=A0 =C2=A0chance of
>=C2=A0 =C2=A0 =C2=A0 > deadlocks, as AccessExclusive locks are grabb= ed on many tables
>=C2=A0 =C2=A0 =C2=A0 > referenced by foreing keys. Is there anything= I can do better?
>=C2=A0 =C2=A0 =C2=A0Whi is it
>=C2=A0 =C2=A0 =C2=A0 > that attach_partition adds a foreign key with= out additional
>=C2=A0 =C2=A0 =C2=A0 > AccessExclusive lock,
>
>=C2=A0 =C2=A0 =C2=A0https://www.postg= resql.org/docs/current/sql-altertable.html
>=C2=A0 =C2=A0 =C2=A0<https://www.p= ostgresql.org/docs/current/sql-altertable.html>
>
>=C2=A0 =C2=A0 =C2=A0ATTACH PARTITION
>
>=C2=A0 =C2=A0 =C2=A0[...]
>
>=C2=A0 =C2=A0 =C2=A0"Currently FOREIGN KEY constraints are not con= sidered. "
>
>
>
>
>
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 but this lock is required when the constrin= t
>=C2=A0 =C2=A0 =C2=A0 > already exists?
>
>
>=C2=A0 =C2=A0 =C2=A0Because I am pretty sure it is due to this statemen= t:
>
>=C2=A0 =C2=A0 =C2=A0alter table films_partition ADD CONSTRAINT fk_did F= OREIGN KEY (did)
>=C2=A0 =C2=A0 =C2=A0REFERENCES refs (id);
>
>=C2=A0 =C2=A0 =C2=A0Try:
>
>=C2=A0 =C2=A0 =C2=A0BEGIN;
>=C2=A0 =C2=A0 =C2=A0insert into films_partition values (1, 'dr'= , 'musician',5)
>=C2=A0 =C2=A0 =C2=A0alter table films_partition add constraint check_co= de check (code =3D
>=C2=A0 =C2=A0 =C2=A0'dr');
>=C2=A0 =C2=A0 =C2=A0alter table films_partition ADD CONSTRAINT fk_did F= OREIGN KEY (did)
>=C2=A0 =C2=A0 =C2=A0REFERENCES refs (id);
>=C2=A0 =C2=A0 =C2=A0COMMIT;
>
>=C2=A0 =C2=A0 =C2=A0Then:
>
>=C2=A0 =C2=A0 =C2=A0ALTER TABLE films ATTACH PARTITION films_partition = for values in ('dr')
>
>
>=C2=A0 =C2=A0 =C2=A0Also from
>
>=C2=A0 =C2=A0 =C2=A0https://www.postg= resql.org/docs/current/sql-altertable.html
>=C2=A0 =C2=A0 =C2=A0<https://www.p= ostgresql.org/docs/current/sql-altertable.html>
>
>=C2=A0 =C2=A0 =C2=A0Take a look at:
>
>=C2=A0 =C2=A0 =C2=A0ADD table_constraint [ NOT VALID ]
>
>
>=C2=A0 =C2=A0 =C2=A0...
>
>=C2=A0 =C2=A0 =C2=A0"Normally, this form will cause a scan of the = table to verify that all
>=C2=A0 =C2=A0 =C2=A0existing rows in the table satisfy the new constrai= nt. But if the NOT
>=C2=A0 =C2=A0 =C2=A0VALID option is used, this potentially-lengthy scan= is skipped. The
>=C2=A0 =C2=A0 =C2=A0constraint will still be enforced against subsequen= t inserts or updates
>=C2=A0 =C2=A0 =C2=A0(that is, they'll fail unless there is a matchi= ng row in the referenced
>=C2=A0 =C2=A0 =C2=A0table, in the case of foreign keys, or they'll = fail unless the new row
>=C2=A0 =C2=A0 =C2=A0matches the specified check condition). But the dat= abase will not
>=C2=A0 =C2=A0 =C2=A0assume
>=C2=A0 =C2=A0 =C2=A0that the constraint holds for all rows in the table= , until it is
>=C2=A0 =C2=A0 =C2=A0validated by using the VALIDATE CONSTRAINT option. = See Notes below for
>=C2=A0 =C2=A0 =C2=A0more information about using the NOT VALID option.&= quot;
>
>
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > Regards!
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >
>
>=C2=A0 =C2=A0 =C2=A0--
>=C2=A0 =C2=A0 =C2=A0Adrian Klaver
>=C2=A0 =C2=A0 =C2=A0adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com><= br> >

--
Adrian Klaver
adrian.klave= r@aklaver.com
--000000000000ed1d0d0625dad6db--