Hi,
I tested the "not enforced" constraint feature extensively today
especially the cases of partitioned table. Everything seems to be
working fine.
While doing that, I found that foreign_key.sql does not have a test to
make sure that a partition continues to have the constraints in the
same state after detaching. Here's a 0001 patch adding those tests in
the same block as the not enforced constraints. Probably we could add
similar test in other blocks as well, but I haven't done that yet.
Checking if something like this would be acceptable.
0002 fixes a comment in the same block.
--
Best Wishes,
Ashutosh Bapat
From c0305c8572c9c416da45b9a14146be049cf91102 Mon Sep 17 00:00:00 2001
From: Ashutosh Bapat <ashutosh.bapat....@gmail.com>
Date: Thu, 3 Apr 2025 17:29:49 +0530
Subject: [PATCH 1/2] Test detaching a partition with foreign key constraint
A partition of a partitioned table with a foreign key constraint should continue
to have the constraint in the same state even after detaching it from the
partitioned table. Add a test for this case.
Author: Ashutosh Bapat
---
src/test/regress/expected/foreign_key.out | 27 ++++++++++++++++++++++-
src/test/regress/sql/foreign_key.sql | 12 +++++++++-
2 files changed, 37 insertions(+), 2 deletions(-)
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index 53810a0fde9..15782da9c97 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -1810,9 +1810,34 @@ ORDER BY tgrelid, tgtype;
fk_partitioned_fk_a_b_fkey | fk_partitioned_fk_3_1 | RI_ConstraintTrigger_c_N | 17
(14 rows)
+-- Detaching a partition should leave the constraint behind in a good state
+ALTER TABLE fk_partitioned_fk DETACH PARTITION fk_partitioned_fk_1;
+\d fk_partitioned_fk_1
+ Table "public.fk_partitioned_fk_1"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+ b | integer | | |
+Foreign-key constraints:
+ "fk_partitioned_fk_a_b_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b)
+
+SELECT conname, tgrelid::regclass as tgrel, regexp_replace(tgname, '[0-9]+', 'N') as tgname, tgtype
+FROM pg_trigger t JOIN pg_constraint c ON (t.tgconstraint = c.oid)
+WHERE tgrelid = 'fk_partitioned_fk_1'::regclass
+ORDER BY tgrelid, tgtype;
+ conname | tgrel | tgname | tgtype
+----------------------------+---------------------+--------------------------+--------
+ fk_partitioned_fk_a_b_fkey | fk_partitioned_fk_1 | RI_ConstraintTrigger_c_N | 5
+ fk_partitioned_fk_a_b_fkey | fk_partitioned_fk_1 | RI_ConstraintTrigger_c_N | 17
+(2 rows)
+
+INSERT INTO fk_partitioned_fk_1 (a,b) VALUES (600, 601); --fails
+ERROR: insert or update on table "fk_partitioned_fk_1" violates foreign key constraint "fk_partitioned_fk_a_b_fkey"
+DETAIL: Key (a, b)=(600, 601) is not present in table "fk_notpartitioned_pk".
+INSERT INTO fk_partitioned_fk_1 (a,b) VALUES (500, 501);
ALTER TABLE fk_partitioned_fk DROP CONSTRAINT fk_partitioned_fk_a_b_fkey;
-- done.
-DROP TABLE fk_notpartitioned_pk, fk_partitioned_fk;
+DROP TABLE fk_notpartitioned_pk, fk_partitioned_fk, fk_partitioned_fk_1;
-- Altering a type referenced by a foreign key needs to drop/recreate the FK.
-- Ensure that works.
CREATE TABLE fk_notpartitioned_pk (a INT, PRIMARY KEY(a), CHECK (a > 0));
diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql
index 77c0c615630..e68c19d6c4b 100644
--- a/src/test/regress/sql/foreign_key.sql
+++ b/src/test/regress/sql/foreign_key.sql
@@ -1320,9 +1320,19 @@ WHERE tgrelid IN (SELECT relid FROM pg_partition_tree('fk_partitioned_fk'::regcl
UNION ALL SELECT 'fk_notpartitioned_pk'::regclass)
ORDER BY tgrelid, tgtype;
+-- Detaching a partition should leave the constraint behind in a good state
+ALTER TABLE fk_partitioned_fk DETACH PARTITION fk_partitioned_fk_1;
+\d fk_partitioned_fk_1
+SELECT conname, tgrelid::regclass as tgrel, regexp_replace(tgname, '[0-9]+', 'N') as tgname, tgtype
+FROM pg_trigger t JOIN pg_constraint c ON (t.tgconstraint = c.oid)
+WHERE tgrelid = 'fk_partitioned_fk_1'::regclass
+ORDER BY tgrelid, tgtype;
+INSERT INTO fk_partitioned_fk_1 (a,b) VALUES (600, 601); --fails
+INSERT INTO fk_partitioned_fk_1 (a,b) VALUES (500, 501);
+
ALTER TABLE fk_partitioned_fk DROP CONSTRAINT fk_partitioned_fk_a_b_fkey;
-- done.
-DROP TABLE fk_notpartitioned_pk, fk_partitioned_fk;
+DROP TABLE fk_notpartitioned_pk, fk_partitioned_fk, fk_partitioned_fk_1;
-- Altering a type referenced by a foreign key needs to drop/recreate the FK.
-- Ensure that works.
base-commit: b82e7eddb023ade0739002b3ef05939ea6937e57
--
2.34.1
From 2b74fd3ba2e4d14484b81b53efc06f14cdfe640e Mon Sep 17 00:00:00 2001
From: Ashutosh Bapat <ashutosh.bapat....@gmail.com>
Date: Thu, 3 Apr 2025 17:33:13 +0530
Subject: [PATCH 2/2] Fix an inaccurate comment
The comment before SQL statement checking the triggers on the partition
hierarchy mentions "number of triggers" but it is actually checking the triggers
themselves. Fix the comment.
Author: Ashutosh Bapat
---
src/test/regress/expected/foreign_key.out | 2 +-
src/test/regress/sql/foreign_key.sql | 2 +-
2 files changed, 2 insertions(+), 2 deletions(-)
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index 15782da9c97..c6afb1262f0 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -1786,7 +1786,7 @@ ORDER BY tgrelid, tgtype;
-- Changing it back to ENFORCED will recreate the necessary triggers.
ALTER TABLE fk_partitioned_fk ALTER CONSTRAINT fk_partitioned_fk_a_b_fkey ENFORCED;
--- Should be exactly the same number of triggers found as before
+-- Should be exactly the same triggers found as before
SELECT conname, tgrelid::regclass as tgrel, regexp_replace(tgname, '[0-9]+', 'N') as tgname, tgtype
FROM pg_trigger t JOIN pg_constraint c ON (t.tgconstraint = c.oid)
WHERE tgrelid IN (SELECT relid FROM pg_partition_tree('fk_partitioned_fk'::regclass)
diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql
index e68c19d6c4b..2b11cc8775c 100644
--- a/src/test/regress/sql/foreign_key.sql
+++ b/src/test/regress/sql/foreign_key.sql
@@ -1313,7 +1313,7 @@ ORDER BY tgrelid, tgtype;
-- Changing it back to ENFORCED will recreate the necessary triggers.
ALTER TABLE fk_partitioned_fk ALTER CONSTRAINT fk_partitioned_fk_a_b_fkey ENFORCED;
--- Should be exactly the same number of triggers found as before
+-- Should be exactly the same triggers found as before
SELECT conname, tgrelid::regclass as tgrel, regexp_replace(tgname, '[0-9]+', 'N') as tgname, tgtype
FROM pg_trigger t JOIN pg_constraint c ON (t.tgconstraint = c.oid)
WHERE tgrelid IN (SELECT relid FROM pg_partition_tree('fk_partitioned_fk'::regclass)
--
2.34.1
