Skip to content
This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository.

Commit 2f30847

Browse files
alvherrealvherre
committedJan 21, 2025
Fix detach of a partition that has a toplevel FK to a partitioned table
In common cases, foreign keys are defined on the toplevel partitioned table; but if instead one is defined on a partition and references a partitioned table, and the referencing partition is detached, we would examine the pg_constraint row on the partition being detached, and fail to realize that the sub-constraints must be left alone. This causes the ALTER TABLE DETACH process to fail with ERROR: could not find ON INSERT check triggers of foreign key constraint NNN This is similar but not quite the same as what was fixed by 53af949. This bug doesn't affect branches earlier than 15, because the detach procedure was different there, so we only backpatch down to 15. Fix by skipping such modifying constraints that are children of other constraints being detached. Author: Amul Sul <sulamul@gmail.com> Diagnosys-by: Sami Imseih <samimseih@gmail.com> Discussion: https://postgr.es/m/CAAJ_b97GuPh6wQPbxQS-Zpy16Oh+0aMv-w64QcGrLhCOZZ6p+g@mail.gmail.com
1 parent e292ba3 commit 2f30847

File tree

3 files changed

+39
-2
lines changed

3 files changed

+39
-2
lines changed
 

‎src/backend/commands/tablecmds.c

Lines changed: 24 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -19258,6 +19258,7 @@ DetachPartitionFinalize(Relation rel, Relation partRel, bool concurrent,
1925819258
HeapTuple tuple,
1925919259
newtuple;
1926019260
Relation trigrel = NULL;
19261+
List *fkoids = NIL;
1926119262

1926219263
if (concurrent)
1926319264
{
@@ -19278,6 +19279,23 @@ DetachPartitionFinalize(Relation rel, Relation partRel, bool concurrent,
1927819279
fks = copyObject(RelationGetFKeyList(partRel));
1927919280
if (fks != NIL)
1928019281
trigrel = table_open(TriggerRelationId, RowExclusiveLock);
19282+
19283+
/*
19284+
* It's possible that the partition being detached has a foreign key that
19285+
* references a partitioned table. When that happens, there are multiple
19286+
* pg_constraint rows for the partition: one points to the partitioned
19287+
* table itself, while the others point to each of its partitions. Only
19288+
* the topmost one is to be considered here; the child constraints must be
19289+
* left alone, because conceptually those aren't coming from our parent
19290+
* partitioned table, but from this partition itself.
19291+
*
19292+
* We implement this by collecting all the constraint OIDs in a first scan
19293+
* of the FK array, and skipping in the loop below those constraints whose
19294+
* parents are listed here.
19295+
*/
19296+
foreach_node(ForeignKeyCacheInfo, fk, fks)
19297+
fkoids = lappend_oid(fkoids, fk->conoid);
19298+
1928119299
foreach(cell, fks)
1928219300
{
1928319301
ForeignKeyCacheInfo *fk = lfirst(cell);
@@ -19291,9 +19309,13 @@ DetachPartitionFinalize(Relation rel, Relation partRel, bool concurrent,
1929119309
elog(ERROR, "cache lookup failed for constraint %u", fk->conoid);
1929219310
conform = (Form_pg_constraint) GETSTRUCT(contup);
1929319311

19294-
/* consider only the inherited foreign keys */
19312+
/*
19313+
* Consider only inherited foreign keys, and only if their parents
19314+
* aren't in the list.
19315+
*/
1929519316
if (conform->contype != CONSTRAINT_FOREIGN ||
19296-
!OidIsValid(conform->conparentid))
19317+
!OidIsValid(conform->conparentid) ||
19318+
list_member_oid(fkoids, conform->conparentid))
1929719319
{
1929819320
ReleaseSysCache(contup);
1929919321
continue;

‎src/test/regress/expected/foreign_key.out

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2339,6 +2339,13 @@ UPDATE pk SET a = 4002 WHERE a = 4000;
23392339
DELETE FROM pk WHERE a = 4002;
23402340
UPDATE pk SET a = 4502 WHERE a = 4500;
23412341
DELETE FROM pk WHERE a = 4502;
2342+
-- Also, detaching a partition that has the FK itself should work
2343+
-- https://postgr.es/m/CAAJ_b97GuPh6wQPbxQS-Zpy16Oh+0aMv-w64QcGrLhCOZZ6p+g@mail.gmail.com
2344+
CREATE TABLE ffk (a int, b int REFERENCES pk) PARTITION BY list (a);
2345+
CREATE TABLE ffk1 PARTITION OF ffk FOR VALUES IN (1);
2346+
ALTER TABLE ffk1 ADD FOREIGN KEY (a) REFERENCES pk;
2347+
ALTER TABLE ffk DETACH PARTITION ffk1;
2348+
DROP TABLE ffk, ffk1;
23422349
CREATE SCHEMA fkpart4;
23432350
SET search_path TO fkpart4;
23442351
-- dropping/detaching PARTITIONs is prevented if that would break

‎src/test/regress/sql/foreign_key.sql

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1672,6 +1672,14 @@ DELETE FROM pk WHERE a = 4002;
16721672
UPDATE pk SET a = 4502 WHERE a = 4500;
16731673
DELETE FROM pk WHERE a = 4502;
16741674

1675+
-- Also, detaching a partition that has the FK itself should work
1676+
-- https://postgr.es/m/CAAJ_b97GuPh6wQPbxQS-Zpy16Oh+0aMv-w64QcGrLhCOZZ6p+g@mail.gmail.com
1677+
CREATE TABLE ffk (a int, b int REFERENCES pk) PARTITION BY list (a);
1678+
CREATE TABLE ffk1 PARTITION OF ffk FOR VALUES IN (1);
1679+
ALTER TABLE ffk1 ADD FOREIGN KEY (a) REFERENCES pk;
1680+
ALTER TABLE ffk DETACH PARTITION ffk1;
1681+
DROP TABLE ffk, ffk1;
1682+
16751683
CREATE SCHEMA fkpart4;
16761684
SET search_path TO fkpart4;
16771685
-- dropping/detaching PARTITIONs is prevented if that would break

0 commit comments

Comments
 (0)
Failed to load comments.