summaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/backend/commands/tablecmds.c160
-rw-r--r--src/test/regress/expected/foreign_key.out91
-rw-r--r--src/test/regress/sql/foreign_key.sql56
3 files changed, 281 insertions, 26 deletions
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 38cc7f6b854..6dd4e88d48e 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -10389,6 +10389,82 @@ tryAttachPartitionForeignKey(ForeignKeyCacheInfo *fk,
table_close(trigrel, RowExclusiveLock);
ConstraintSetParentConstraint(fk->conoid, parentConstrOid, partRelid);
+
+ /*
+ * If the referenced table is partitioned, then the partition we're
+ * attaching now has extra pg_constraint rows and action triggers that are
+ * no longer needed. Remove those.
+ */
+ if (get_rel_relkind(fk->confrelid) == RELKIND_PARTITIONED_TABLE)
+ {
+ Relation pg_constraint = table_open(ConstraintRelationId, RowShareLock);
+ ObjectAddresses *objs;
+ HeapTuple consttup;
+
+ ScanKeyInit(&key,
+ Anum_pg_constraint_conrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(fk->conrelid));
+
+ scan = systable_beginscan(pg_constraint,
+ ConstraintRelidTypidNameIndexId,
+ true, NULL, 1, &key);
+ objs = new_object_addresses();
+ while ((consttup = systable_getnext(scan)) != NULL)
+ {
+ Form_pg_constraint conform = (Form_pg_constraint) GETSTRUCT(consttup);
+
+ if (conform->conparentid != fk->conoid)
+ continue;
+ else
+ {
+ ObjectAddress addr;
+ SysScanDesc scan2;
+ ScanKeyData key2;
+ int n PG_USED_FOR_ASSERTS_ONLY;
+
+ ObjectAddressSet(addr, ConstraintRelationId, conform->oid);
+ add_exact_object_address(&addr, objs);
+
+ /*
+ * First we must delete the dependency records that bind
+ * the constraint records together.
+ */
+ n = deleteDependencyRecordsForSpecific(ConstraintRelationId,
+ conform->oid,
+ DEPENDENCY_INTERNAL,
+ ConstraintRelationId,
+ fk->conoid);
+ Assert(n == 1); /* actually only one is expected */
+
+ /*
+ * Now search for the triggers for this constraint and set
+ * them up for deletion too
+ */
+ ScanKeyInit(&key2,
+ Anum_pg_trigger_tgconstraint,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(conform->oid));
+ scan2 = systable_beginscan(trigrel, TriggerConstraintIndexId,
+ true, NULL, 1, &key2);
+ while ((trigtup = systable_getnext(scan2)) != NULL)
+ {
+ ObjectAddressSet(addr, TriggerRelationId,
+ ((Form_pg_trigger) GETSTRUCT(trigtup))->oid);
+ add_exact_object_address(&addr, objs);
+ }
+ systable_endscan(scan2);
+ }
+ }
+ /* make the dependency deletions visible */
+ CommandCounterIncrement();
+ performMultipleDeletions(objs, DROP_RESTRICT,
+ PERFORM_DELETION_INTERNAL);
+ systable_endscan(scan);
+
+ table_close(pg_constraint, RowShareLock);
+ }
+
CommandCounterIncrement();
return true;
}
@@ -18154,6 +18230,7 @@ DetachPartitionFinalize(Relation rel, Relation partRel, bool concurrent,
new_repl[Natts_pg_class];
HeapTuple tuple,
newtuple;
+ ObjectAddresses *dropobjs = NULL;
if (concurrent)
{
@@ -18168,8 +18245,8 @@ DetachPartitionFinalize(Relation rel, Relation partRel, bool concurrent,
DropClonedTriggersFromPartition(RelationGetRelid(partRel));
/*
- * Detach any foreign keys that are inherited. This includes creating
- * additional action triggers.
+ * Detach any foreign keys that are inherited -- or, if they reference
+ * partitioned tables, drop them.
*/
fks = copyObject(RelationGetFKeyList(partRel));
foreach(cell, fks)
@@ -18177,7 +18254,6 @@ DetachPartitionFinalize(Relation rel, Relation partRel, bool concurrent,
ForeignKeyCacheInfo *fk = lfirst(cell);
HeapTuple contup;
Form_pg_constraint conform;
- Constraint *fkconstraint;
contup = SearchSysCache1(CONSTROID, ObjectIdGetDatum(fk->conoid));
if (!HeapTupleIsValid(contup))
@@ -18192,39 +18268,71 @@ DetachPartitionFinalize(Relation rel, Relation partRel, bool concurrent,
continue;
}
- /* unset conparentid and adjust conislocal, coninhcount, etc. */
+ /* Mark the constraint as independent */
ConstraintSetParentConstraint(fk->conoid, InvalidOid, InvalidOid);
/*
- * Make the action triggers on the referenced relation. When this was
- * a partition the action triggers pointed to the parent rel (they
- * still do), but now we need separate ones of our own.
+ * If the constraint references a partitioned table, just drop the
+ * constraint, because it's more work to preserve the constraint
+ * correctly.
+ *
+ * If it references a plain table, then we can create the action
+ * triggers and it'll be okay.
*/
- fkconstraint = makeNode(Constraint);
- fkconstraint->contype = CONSTRAINT_FOREIGN;
- fkconstraint->conname = pstrdup(NameStr(conform->conname));
- fkconstraint->deferrable = conform->condeferrable;
- fkconstraint->initdeferred = conform->condeferred;
- fkconstraint->location = -1;
- fkconstraint->pktable = NULL;
- fkconstraint->fk_attrs = NIL;
- fkconstraint->pk_attrs = NIL;
- fkconstraint->fk_matchtype = conform->confmatchtype;
- fkconstraint->fk_upd_action = conform->confupdtype;
- fkconstraint->fk_del_action = conform->confdeltype;
- fkconstraint->old_conpfeqop = NIL;
- fkconstraint->old_pktable_oid = InvalidOid;
- fkconstraint->skip_validation = false;
- fkconstraint->initially_valid = true;
+ if (get_rel_relkind(fk->confrelid) == RELKIND_PARTITIONED_TABLE)
+ {
+ ObjectAddress constraddr;
- createForeignKeyActionTriggers(partRel, conform->confrelid,
- fkconstraint, fk->conoid,
- conform->conindid);
+ /* make the dependency deletions above visible */
+ CommandCounterIncrement();
+
+ /*
+ * Remember the constraint and its triggers for later deletion.
+ */
+ if (dropobjs == NULL)
+ dropobjs = new_object_addresses();
+ ObjectAddressSet(constraddr, ConstraintRelationId, fk->conoid);
+ add_exact_object_address(&constraddr, dropobjs);
+ }
+ else
+ {
+ Constraint *fkconstraint;
+
+ /*
+ * Make the action triggers on the referenced relation. When this
+ * was a partition the action triggers pointed to the parent rel
+ * (they still do), but now we need separate ones of our own.
+ */
+ fkconstraint = makeNode(Constraint);
+ fkconstraint->contype = CONSTRAINT_FOREIGN;
+ fkconstraint->conname = pstrdup(NameStr(conform->conname));
+ fkconstraint->deferrable = conform->condeferrable;
+ fkconstraint->initdeferred = conform->condeferred;
+ fkconstraint->location = -1;
+ fkconstraint->pktable = NULL;
+ fkconstraint->fk_attrs = NIL;
+ fkconstraint->pk_attrs = NIL;
+ fkconstraint->fk_matchtype = conform->confmatchtype;
+ fkconstraint->fk_upd_action = conform->confupdtype;
+ fkconstraint->fk_del_action = conform->confdeltype;
+ fkconstraint->old_conpfeqop = NIL;
+ fkconstraint->old_pktable_oid = InvalidOid;
+ fkconstraint->skip_validation = false;
+ fkconstraint->initially_valid = true;
+
+ createForeignKeyActionTriggers(partRel, conform->confrelid,
+ fkconstraint, fk->conoid,
+ conform->conindid);
+ }
ReleaseSysCache(contup);
}
list_free_deep(fks);
+ /* If we collected any constraints for deletion, do so now. */
+ if (dropobjs != NULL)
+ performMultipleDeletions(dropobjs, DROP_CASCADE, 0);
+
/*
* Any sub-constraints that are in the referenced-side of a larger
* constraint have to be removed. This partition is no longer part of the
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index a95fe17d1a7..e17b95021cf 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -2665,3 +2665,94 @@ DROP SCHEMA fkpart10 CASCADE;
NOTICE: drop cascades to 2 other objects
DETAIL: drop cascades to table fkpart10.tbl1
drop cascades to table fkpart10.tbl2
+-- When a table is attached as partition to a partitioned table that has
+-- a foreign key to another partitioned table, it acquires a clone of the
+-- FK. Upon detach, Postgres 14 and earlier remove the foreign key (newer
+-- versions make it a standalone constraint.)
+CREATE SCHEMA fkpart12
+ CREATE TABLE fk_p ( id int, jd int, PRIMARY KEY(id, jd)) PARTITION BY list (id)
+ CREATE TABLE fk_p_1 PARTITION OF fk_p FOR VALUES IN (1) PARTITION BY list (jd)
+ CREATE TABLE fk_p_1_1 PARTITION OF fk_p_1 FOR VALUES IN (1)
+ CREATE TABLE fk_p_1_2 PARTITION OF fk_p_1 FOR VALUES IN (2)
+ CREATE TABLE fk_p_2 PARTITION OF fk_p FOR VALUES IN (2) PARTITION BY list (jd)
+ CREATE TABLE fk_p_2_1 PARTITION OF fk_p_2 FOR VALUES IN (1)
+ CREATE TABLE fk_p_2_2 PARTITION OF fk_p_2 FOR VALUES IN (2)
+ CREATE TABLE fk_r_1 ( id int PRIMARY KEY, p_id int NOT NULL, p_jd int NOT NULL)
+ CREATE TABLE fk_r_2 ( id int PRIMARY KEY, p_id int NOT NULL, p_jd int NOT NULL) PARTITION BY list (id)
+ CREATE TABLE fk_r_2_1 PARTITION OF fk_r_2 FOR VALUES IN (2, 1)
+ CREATE TABLE fk_r ( id int PRIMARY KEY, p_id int NOT NULL, p_jd int NOT NULL,
+ FOREIGN KEY (p_id, p_jd) REFERENCES fk_p (id, jd)
+ ) PARTITION BY list (id);
+SET search_path TO fkpart12;
+INSERT INTO fk_p VALUES (1, 1);
+ALTER TABLE fk_r ATTACH PARTITION fk_r_1 FOR VALUES IN (1);
+ALTER TABLE fk_r ATTACH PARTITION fk_r_2 FOR VALUES IN (2);
+\d fk_r_2
+ Partitioned table "fkpart12.fk_r_2"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ id | integer | | not null |
+ p_id | integer | | not null |
+ p_jd | integer | | not null |
+Partition of: fk_r FOR VALUES IN (2)
+Partition key: LIST (id)
+Indexes:
+ "fk_r_2_pkey" PRIMARY KEY, btree (id)
+Foreign-key constraints:
+ TABLE "fk_r" CONSTRAINT "fk_r_p_id_p_jd_fkey" FOREIGN KEY (p_id, p_jd) REFERENCES fk_p(id, jd)
+Number of partitions: 1 (Use \d+ to list them.)
+
+INSERT INTO fk_r VALUES (1, 1, 1);
+INSERT INTO fk_r VALUES (2, 2, 1); -- fails
+ERROR: insert or update on table "fk_r_2_1" violates foreign key constraint "fk_r_p_id_p_jd_fkey"
+DETAIL: Key (p_id, p_jd)=(2, 1) is not present in table "fk_p".
+ALTER TABLE fk_r DETACH PARTITION fk_r_1;
+ALTER TABLE fk_r DETACH PARTITION fk_r_2;
+\d fk_r_2
+ Partitioned table "fkpart12.fk_r_2"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ id | integer | | not null |
+ p_id | integer | | not null |
+ p_jd | integer | | not null |
+Partition key: LIST (id)
+Indexes:
+ "fk_r_2_pkey" PRIMARY KEY, btree (id)
+Number of partitions: 1 (Use \d+ to list them.)
+
+INSERT INTO fk_r_1 VALUES (2, 1, 2); -- works: there's no FK anymore
+DELETE FROM fk_p; -- works
+ALTER TABLE fk_r ATTACH PARTITION fk_r_1 FOR VALUES IN (1); -- fails
+ERROR: partition constraint of relation "fk_r_1" is violated by some row
+INSERT INTO fk_r_2 VALUES (2, 2, 2);
+INSERT INTO fk_p VALUES (2, 2);
+ALTER TABLE fk_r ATTACH PARTITION fk_r_2 FOR VALUES IN (2);
+\d fk_r_2
+ Partitioned table "fkpart12.fk_r_2"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ id | integer | | not null |
+ p_id | integer | | not null |
+ p_jd | integer | | not null |
+Partition of: fk_r FOR VALUES IN (2)
+Partition key: LIST (id)
+Indexes:
+ "fk_r_2_pkey" PRIMARY KEY, btree (id)
+Foreign-key constraints:
+ TABLE "fk_r" CONSTRAINT "fk_r_p_id_p_jd_fkey" FOREIGN KEY (p_id, p_jd) REFERENCES fk_p(id, jd)
+Number of partitions: 1 (Use \d+ to list them.)
+
+DELETE FROM fk_p; -- fails
+ERROR: update or delete on table "fk_p_2_2" violates foreign key constraint "fk_r_p_id_p_jd_fkey6" on table "fk_r"
+DETAIL: Key (id, jd)=(2, 2) is still referenced from table "fk_r".
+-- these should all fail
+ALTER TABLE fk_r_1 DROP CONSTRAINT fk_r_p_id_p_jd_fkey;
+ERROR: constraint "fk_r_p_id_p_jd_fkey" of relation "fk_r_1" does not exist
+ALTER TABLE fk_r DROP CONSTRAINT fk_r_p_id_p_jd_fkey1;
+ERROR: cannot drop inherited constraint "fk_r_p_id_p_jd_fkey1" of relation "fk_r"
+ALTER TABLE fk_r_2 DROP CONSTRAINT fk_r_p_id_p_jd_fkey;
+ERROR: cannot drop inherited constraint "fk_r_p_id_p_jd_fkey" of relation "fk_r_2"
+SET client_min_messages TO warning;
+DROP SCHEMA fkpart12 CASCADE;
+RESET client_min_messages;
+RESET search_path;
diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql
index 2ae74ebb148..7af7d562c7b 100644
--- a/src/test/regress/sql/foreign_key.sql
+++ b/src/test/regress/sql/foreign_key.sql
@@ -1902,3 +1902,59 @@ UPDATE fkpart10.tbl1 SET f1 = 2 WHERE f1 = 1;
INSERT INTO fkpart10.tbl1 VALUES (0), (1);
COMMIT;
DROP SCHEMA fkpart10 CASCADE;
+
+-- When a table is attached as partition to a partitioned table that has
+-- a foreign key to another partitioned table, it acquires a clone of the
+-- FK. Upon detach, Postgres 14 and earlier remove the foreign key (newer
+-- versions make it a standalone constraint.)
+CREATE SCHEMA fkpart12
+ CREATE TABLE fk_p ( id int, jd int, PRIMARY KEY(id, jd)) PARTITION BY list (id)
+ CREATE TABLE fk_p_1 PARTITION OF fk_p FOR VALUES IN (1) PARTITION BY list (jd)
+ CREATE TABLE fk_p_1_1 PARTITION OF fk_p_1 FOR VALUES IN (1)
+ CREATE TABLE fk_p_1_2 PARTITION OF fk_p_1 FOR VALUES IN (2)
+ CREATE TABLE fk_p_2 PARTITION OF fk_p FOR VALUES IN (2) PARTITION BY list (jd)
+ CREATE TABLE fk_p_2_1 PARTITION OF fk_p_2 FOR VALUES IN (1)
+ CREATE TABLE fk_p_2_2 PARTITION OF fk_p_2 FOR VALUES IN (2)
+ CREATE TABLE fk_r_1 ( id int PRIMARY KEY, p_id int NOT NULL, p_jd int NOT NULL)
+ CREATE TABLE fk_r_2 ( id int PRIMARY KEY, p_id int NOT NULL, p_jd int NOT NULL) PARTITION BY list (id)
+ CREATE TABLE fk_r_2_1 PARTITION OF fk_r_2 FOR VALUES IN (2, 1)
+ CREATE TABLE fk_r ( id int PRIMARY KEY, p_id int NOT NULL, p_jd int NOT NULL,
+ FOREIGN KEY (p_id, p_jd) REFERENCES fk_p (id, jd)
+ ) PARTITION BY list (id);
+SET search_path TO fkpart12;
+
+INSERT INTO fk_p VALUES (1, 1);
+
+ALTER TABLE fk_r ATTACH PARTITION fk_r_1 FOR VALUES IN (1);
+ALTER TABLE fk_r ATTACH PARTITION fk_r_2 FOR VALUES IN (2);
+
+\d fk_r_2
+
+INSERT INTO fk_r VALUES (1, 1, 1);
+INSERT INTO fk_r VALUES (2, 2, 1); -- fails
+
+ALTER TABLE fk_r DETACH PARTITION fk_r_1;
+ALTER TABLE fk_r DETACH PARTITION fk_r_2;
+
+\d fk_r_2
+
+INSERT INTO fk_r_1 VALUES (2, 1, 2); -- works: there's no FK anymore
+DELETE FROM fk_p; -- works
+
+ALTER TABLE fk_r ATTACH PARTITION fk_r_1 FOR VALUES IN (1); -- fails
+
+INSERT INTO fk_r_2 VALUES (2, 2, 2);
+INSERT INTO fk_p VALUES (2, 2);
+ALTER TABLE fk_r ATTACH PARTITION fk_r_2 FOR VALUES IN (2);
+\d fk_r_2
+DELETE FROM fk_p; -- fails
+
+-- these should all fail
+ALTER TABLE fk_r_1 DROP CONSTRAINT fk_r_p_id_p_jd_fkey;
+ALTER TABLE fk_r DROP CONSTRAINT fk_r_p_id_p_jd_fkey1;
+ALTER TABLE fk_r_2 DROP CONSTRAINT fk_r_p_id_p_jd_fkey;
+
+SET client_min_messages TO warning;
+DROP SCHEMA fkpart12 CASCADE;
+RESET client_min_messages;
+RESET search_path;
generated by cgit v1.2.3 (git 2.46.0) at 2025-09-18 20:30:12 +0000