Hello
I just realized that the current code to assign constraint names to
partitions is going against the SQL standard's idea that constraint
names must be unique within a schema. When a partition is created, the
foreign key gets exactly the same name as the constraint in the parent
table.
Now maybe you could argue that these constraints should simply be hidden
from view, because they are implementation artifacts; and then their
names don't matter. But we already expose the partitions themselves as
individual tables, so I don't buy this argument.
One way to fix this would be to use ChooseConstraintName() for the FK in
the partition, as in the attached patch. One caveat with this is that
there is no visual clue (in \d <partition>) that distinguishes FKs
inherited from the parent rel from ones that have been created in the
partition directly. I'm not sure that that's an important issue,
though. Another point, maybe more visible, is that if you give an
explicit name to the constraint in the parent table, this is completely
lost in the partitions -- again with any visual clue to link the two.
I'm +0.2 on applying this patch to pg11, but I'd like to hear others'
opinions.
Thanks
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c
index f4057a9f15..9e83853dfe 100644
--- a/src/backend/catalog/pg_constraint.c
+++ b/src/backend/catalog/pg_constraint.c
@@ -490,6 +490,8 @@ clone_fk_constraints(Relation pg_constraint, Relation parentRel,
ArrayType *arr;
Datum datum;
bool isnull;
+ char *conname;
+ char *colname;
tuple = SearchSysCache1(CONSTROID, parentConstrOid);
if (!tuple)
@@ -677,8 +679,17 @@ clone_fk_constraints(Relation pg_constraint, Relation parentRel,
continue;
}
+ colname = get_attname(RelationGetRelid(partRel),
+ mapped_conkey[0],
+ false);
+ conname = ChooseConstraintName(RelationGetRelationName(partRel),
+ colname,
+ "fkey",
+ RelationGetNamespace(partRel),
+ NIL);
+
constrOid =
- CreateConstraintEntry(NameStr(constrForm->conname),
+ CreateConstraintEntry(conname,
constrForm->connamespace,
CONSTRAINT_FOREIGN,
constrForm->condeferrable,
@@ -741,6 +752,8 @@ clone_fk_constraints(Relation pg_constraint, Relation parentRel,
}
ReleaseSysCache(tuple);
+ pfree(colname);
+ pfree(conname);
}
pfree(attmap);
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index 52164e89d2..aef5cc3ed0 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -1485,22 +1485,22 @@ INSERT INTO fk_partitioned_fk_1 (a,b) VALUES (500, 501);
ERROR: insert or update on table "fk_partitioned_fk_1" violates foreign key constraint "fk_partitioned_fk_a_fkey"
DETAIL: Key (a, b)=(500, 501) is not present in table "fk_notpartitioned_pk".
INSERT INTO fk_partitioned_fk (a,b) VALUES (1500, 1501);
-ERROR: insert or update on table "fk_partitioned_fk_2" violates foreign key constraint "fk_partitioned_fk_a_fkey"
+ERROR: insert or update on table "fk_partitioned_fk_2" violates foreign key constraint "fk_partitioned_fk_2_a_fkey"
DETAIL: Key (a, b)=(1500, 1501) is not present in table "fk_notpartitioned_pk".
INSERT INTO fk_partitioned_fk_2 (a,b) VALUES (1500, 1501);
-ERROR: insert or update on table "fk_partitioned_fk_2" violates foreign key constraint "fk_partitioned_fk_a_fkey"
+ERROR: insert or update on table "fk_partitioned_fk_2" violates foreign key constraint "fk_partitioned_fk_2_a_fkey"
DETAIL: Key (a, b)=(1500, 1501) is not present in table "fk_notpartitioned_pk".
INSERT INTO fk_partitioned_fk (a,b) VALUES (2500, 2502);
-ERROR: insert or update on table "fk_partitioned_fk_3_1" violates foreign key constraint "fk_partitioned_fk_a_fkey"
+ERROR: insert or update on table "fk_partitioned_fk_3_1" violates foreign key constraint "fk_partitioned_fk_3_1_a_fkey"
DETAIL: Key (a, b)=(2500, 2502) is not present in table "fk_notpartitioned_pk".
INSERT INTO fk_partitioned_fk_3 (a,b) VALUES (2500, 2502);
-ERROR: insert or update on table "fk_partitioned_fk_3_1" violates foreign key constraint "fk_partitioned_fk_a_fkey"
+ERROR: insert or update on table "fk_partitioned_fk_3_1" violates foreign key constraint "fk_partitioned_fk_3_1_a_fkey"
DETAIL: Key (a, b)=(2500, 2502) is not present in table "fk_notpartitioned_pk".
INSERT INTO fk_partitioned_fk (a,b) VALUES (2501, 2503);
-ERROR: insert or update on table "fk_partitioned_fk_3_0" violates foreign key constraint "fk_partitioned_fk_a_fkey"
+ERROR: insert or update on table "fk_partitioned_fk_3_0" violates foreign key constraint "fk_partitioned_fk_3_0_a_fkey"
DETAIL: Key (a, b)=(2501, 2503) is not present in table "fk_notpartitioned_pk".
INSERT INTO fk_partitioned_fk_3 (a,b) VALUES (2501, 2503);
-ERROR: insert or update on table "fk_partitioned_fk_3_0" violates foreign key constraint "fk_partitioned_fk_a_fkey"
+ERROR: insert or update on table "fk_partitioned_fk_3_0" violates foreign key constraint "fk_partitioned_fk_3_0_a_fkey"
DETAIL: Key (a, b)=(2501, 2503) is not present in table "fk_notpartitioned_pk".
-- but if we insert the values that make them valid, then they work
INSERT INTO fk_notpartitioned_pk VALUES (500, 501), (1500, 1501),
@@ -1511,7 +1511,7 @@ INSERT INTO fk_partitioned_fk (a,b) VALUES (2500, 2502);
INSERT INTO fk_partitioned_fk (a,b) VALUES (2501, 2503);
-- this update fails because there is no referenced row
UPDATE fk_partitioned_fk SET a = a + 1 WHERE a = 2501;
-ERROR: insert or update on table "fk_partitioned_fk_3_1" violates foreign key constraint "fk_partitioned_fk_a_fkey"
+ERROR: insert or update on table "fk_partitioned_fk_3_1" violates foreign key constraint "fk_partitioned_fk_3_1_a_fkey"
DETAIL: Key (a, b)=(2502, 2503) is not present in table "fk_notpartitioned_pk".
-- but we can fix it thusly:
INSERT INTO fk_notpartitioned_pk (a,b) VALUES (2502, 2503);
@@ -1554,10 +1554,10 @@ CREATE TABLE fk_partitioned_fk_3 (a int, b int);
ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_3 FOR VALUES IN (2500,2501,2502,2503);
-- this insert fails
INSERT INTO fk_partitioned_fk (a, b) VALUES (2502, 2503);
-ERROR: insert or update on table "fk_partitioned_fk_3" violates foreign key constraint "fk_partitioned_fk_a_fkey"
+ERROR: insert or update on table "fk_partitioned_fk_3" violates foreign key constraint "fk_partitioned_fk_3_a_fkey"
DETAIL: Key (a, b)=(2502, 2503) is not present in table "fk_notpartitioned_pk".
INSERT INTO fk_partitioned_fk_3 (a, b) VALUES (2502, 2503);
-ERROR: insert or update on table "fk_partitioned_fk_3" violates foreign key constraint "fk_partitioned_fk_a_fkey"
+ERROR: insert or update on table "fk_partitioned_fk_3" violates foreign key constraint "fk_partitioned_fk_3_a_fkey"
DETAIL: Key (a, b)=(2502, 2503) is not present in table "fk_notpartitioned_pk".
-- but since the FK is MATCH SIMPLE, this one doesn't
INSERT INTO fk_partitioned_fk_3 (a, b) VALUES (2502, NULL);
@@ -1662,7 +1662,7 @@ DROP TABLE fk_partitioned_fk;
a | integer | | | 2501
b | integer | | | 142857
Foreign-key constraints:
- "fk_partitioned_fk_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
+ "fk_partitioned_fk_2_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
ROLLBACK;
ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES IN (1500,1502);
@@ -1711,7 +1711,7 @@ Number of partitions: 2 (Use \d+ to list them.)
b | integer | | |
Partition of: fk_partitioned_fk_4 FOR VALUES FROM (1, 1) TO (100, 100)
Foreign-key constraints:
- "fk_partitioned_fk_4_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
+ "fk_partitioned_fk_4_1_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
-- this one has an FK with mismatched properties
\d fk_partitioned_fk_4_2
@@ -1723,7 +1723,7 @@ Foreign-key constraints:
Partition of: fk_partitioned_fk_4 FOR VALUES FROM (100, 100) TO (1000, 1000)
Foreign-key constraints:
"fk_partitioned_fk_4_2_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE SET NULL
- "fk_partitioned_fk_4_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
+ "fk_partitioned_fk_4_2_a_fkey1" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
CREATE TABLE fk_partitioned_fk_5 (a int, b int,
FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE,
@@ -1747,7 +1747,7 @@ Partition key: RANGE (a)
Foreign-key constraints:
"fk_partitioned_fk_5_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE
"fk_partitioned_fk_5_a_fkey1" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE
- "fk_partitioned_fk_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
+ "fk_partitioned_fk_5_a_fkey2" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
Number of partitions: 1 (Use \d+ to list them.)
-- verify that it works to reattaching a child with multiple candidate
@@ -1763,9 +1763,9 @@ ALTER TABLE fk_partitioned_fk_5 ATTACH PARTITION fk_partitioned_fk_5_1 FOR VALUE
Partition of: fk_partitioned_fk_5 FOR VALUES FROM (0) TO (10)
Foreign-key constraints:
"fk_partitioned_fk_5_1_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b)
- "fk_partitioned_fk_5_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE
- "fk_partitioned_fk_5_a_fkey1" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE
- "fk_partitioned_fk_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
+ "fk_partitioned_fk_5_1_a_fkey1" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE
+ "fk_partitioned_fk_5_1_a_fkey2" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE
+ "fk_partitioned_fk_5_1_a_fkey3" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
-- verify that attaching a table checks that the existing data satisfies the
-- constraint
@@ -1775,7 +1775,7 @@ CREATE TABLE fk_partitioned_fk_2_2 PARTITION OF fk_partitioned_fk_2 FOR VALUES F
INSERT INTO fk_partitioned_fk_2 VALUES (1600, 601), (1600, 1601);
ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2
FOR VALUES IN (1600);
-ERROR: insert or update on table "fk_partitioned_fk_2" violates foreign key constraint "fk_partitioned_fk_a_fkey"
+ERROR: insert or update on table "fk_partitioned_fk_2" violates foreign key constraint "fk_partitioned_fk_2_a_fkey"
DETAIL: Key (a, b)=(1600, 601) is not present in table "fk_notpartitioned_pk".
INSERT INTO fk_notpartitioned_pk VALUES (1600, 601), (1600, 1601);
ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2
