diff options
| -rw-r--r-- | src/backend/commands/tablecmds.c | 26 | ||||
| -rw-r--r-- | src/test/regress/expected/foreign_key.out | 7 | ||||
| -rw-r--r-- | src/test/regress/sql/foreign_key.sql | 8 |
3 files changed, 39 insertions, 2 deletions
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 4117a0ab1a6..d8f0a99ad93 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -19987,6 +19987,7 @@ DetachPartitionFinalize(Relation rel, Relation partRel, bool concurrent, HeapTuple tuple, newtuple; Relation trigrel = NULL; + List *fkoids = NIL; if (concurrent) { @@ -20007,6 +20008,23 @@ DetachPartitionFinalize(Relation rel, Relation partRel, bool concurrent, fks = copyObject(RelationGetFKeyList(partRel)); if (fks != NIL) trigrel = table_open(TriggerRelationId, RowExclusiveLock); + + /* + * It's possible that the partition being detached has a foreign key that + * references a partitioned table. When that happens, there are multiple + * pg_constraint rows for the partition: one points to the partitioned + * table itself, while the others point to each of its partitions. Only + * the topmost one is to be considered here; the child constraints must be + * left alone, because conceptually those aren't coming from our parent + * partitioned table, but from this partition itself. + * + * We implement this by collecting all the constraint OIDs in a first scan + * of the FK array, and skipping in the loop below those constraints whose + * parents are listed here. + */ + foreach_node(ForeignKeyCacheInfo, fk, fks) + fkoids = lappend_oid(fkoids, fk->conoid); + foreach(cell, fks) { ForeignKeyCacheInfo *fk = lfirst(cell); @@ -20020,9 +20038,13 @@ DetachPartitionFinalize(Relation rel, Relation partRel, bool concurrent, elog(ERROR, "cache lookup failed for constraint %u", fk->conoid); conform = (Form_pg_constraint) GETSTRUCT(contup); - /* consider only the inherited foreign keys */ + /* + * Consider only inherited foreign keys, and only if their parents + * aren't in the list. + */ if (conform->contype != CONSTRAINT_FOREIGN || - !OidIsValid(conform->conparentid)) + !OidIsValid(conform->conparentid) || + list_member_oid(fkoids, conform->conparentid)) { ReleaseSysCache(contup); continue; diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out index 3f459f70ac1..ee4cf85fda9 100644 --- a/src/test/regress/expected/foreign_key.out +++ b/src/test/regress/expected/foreign_key.out @@ -2351,6 +2351,13 @@ UPDATE pk SET a = 4002 WHERE a = 4000; DELETE FROM pk WHERE a = 4002; UPDATE pk SET a = 4502 WHERE a = 4500; DELETE FROM pk WHERE a = 4502; +-- Also, detaching a partition that has the FK itself should work +-- https://postgr.es/m/CAAJ_b97GuPh6wQPbxQS-Zpy16Oh+0aMv-w64QcGrLhCOZZ6p+g@mail.gmail.com +CREATE TABLE ffk (a int, b int REFERENCES pk) PARTITION BY list (a); +CREATE TABLE ffk1 PARTITION OF ffk FOR VALUES IN (1); +ALTER TABLE ffk1 ADD FOREIGN KEY (a) REFERENCES pk; +ALTER TABLE ffk DETACH PARTITION ffk1; +DROP TABLE ffk, ffk1; CREATE SCHEMA fkpart4; SET search_path TO fkpart4; -- dropping/detaching PARTITIONs is prevented if that would break diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql index 2e710e419c2..8c4e4c7c833 100644 --- a/src/test/regress/sql/foreign_key.sql +++ b/src/test/regress/sql/foreign_key.sql @@ -1672,6 +1672,14 @@ DELETE FROM pk WHERE a = 4002; UPDATE pk SET a = 4502 WHERE a = 4500; DELETE FROM pk WHERE a = 4502; +-- Also, detaching a partition that has the FK itself should work +-- https://postgr.es/m/CAAJ_b97GuPh6wQPbxQS-Zpy16Oh+0aMv-w64QcGrLhCOZZ6p+g@mail.gmail.com +CREATE TABLE ffk (a int, b int REFERENCES pk) PARTITION BY list (a); +CREATE TABLE ffk1 PARTITION OF ffk FOR VALUES IN (1); +ALTER TABLE ffk1 ADD FOREIGN KEY (a) REFERENCES pk; +ALTER TABLE ffk DETACH PARTITION ffk1; +DROP TABLE ffk, ffk1; + CREATE SCHEMA fkpart4; SET search_path TO fkpart4; -- dropping/detaching PARTITIONs is prevented if that would break |