Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit9b21f20

Browse files
committed
Fix detach of a partition that has a toplevel FK to a partitioned table
In common cases, foreign keys are defined on the toplevel partitionedtable; but if instead one is defined on a partition and references apartitioned table, and the referencing partition is detached, we wouldexamine the pg_constraint row on the partition being detached, and failto realize that the sub-constraints must be left alone. This causes theALTER TABLE DETACH process to fail with ERROR: could not find ON INSERT check triggers of foreign key constraint NNNThis is similar but not quite the same as what was fixed by53af949. This bug doesn't affect branches earlier than 15, becausethe detach procedure was different there, so we only backpatch down to15.Fix by skipping such modifying constraints that are children of otherconstraints 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 parent1772d55 commit9b21f20

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
@@ -19987,6 +19987,7 @@ DetachPartitionFinalize(Relation rel, Relation partRel, bool concurrent,
1998719987
HeapTupletuple,
1998819988
newtuple;
1998919989
Relationtrigrel = NULL;
19990+
List *fkoids = NIL;
1999019991

1999119992
if (concurrent)
1999219993
{
@@ -20007,6 +20008,23 @@ DetachPartitionFinalize(Relation rel, Relation partRel, bool concurrent,
2000720008
fks = copyObject(RelationGetFKeyList(partRel));
2000820009
if (fks != NIL)
2000920010
trigrel = table_open(TriggerRelationId, RowExclusiveLock);
20011+
20012+
/*
20013+
* It's possible that the partition being detached has a foreign key that
20014+
* references a partitioned table. When that happens, there are multiple
20015+
* pg_constraint rows for the partition: one points to the partitioned
20016+
* table itself, while the others point to each of its partitions. Only
20017+
* the topmost one is to be considered here; the child constraints must be
20018+
* left alone, because conceptually those aren't coming from our parent
20019+
* partitioned table, but from this partition itself.
20020+
*
20021+
* We implement this by collecting all the constraint OIDs in a first scan
20022+
* of the FK array, and skipping in the loop below those constraints whose
20023+
* parents are listed here.
20024+
*/
20025+
foreach_node(ForeignKeyCacheInfo, fk, fks)
20026+
fkoids = lappend_oid(fkoids, fk->conoid);
20027+
2001020028
foreach(cell, fks)
2001120029
{
2001220030
ForeignKeyCacheInfo *fk = lfirst(cell);
@@ -20020,9 +20038,13 @@ DetachPartitionFinalize(Relation rel, Relation partRel, bool concurrent,
2002020038
elog(ERROR, "cache lookup failed for constraint %u", fk->conoid);
2002120039
conform = (Form_pg_constraint) GETSTRUCT(contup);
2002220040

20023-
/* consider only the inherited foreign keys */
20041+
/*
20042+
* Consider only inherited foreign keys, and only if their parents
20043+
* aren't in the list.
20044+
*/
2002420045
if (conform->contype != CONSTRAINT_FOREIGN ||
20025-
!OidIsValid(conform->conparentid))
20046+
!OidIsValid(conform->conparentid) ||
20047+
list_member_oid(fkoids, conform->conparentid))
2002620048
{
2002720049
ReleaseSysCache(contup);
2002820050
continue;

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

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2351,6 +2351,13 @@ UPDATE pk SET a = 4002 WHERE a = 4000;
23512351
DELETE FROM pk WHERE a = 4002;
23522352
UPDATE pk SET a = 4502 WHERE a = 4500;
23532353
DELETE FROM pk WHERE a = 4502;
2354+
-- Also, detaching a partition that has the FK itself should work
2355+
-- https://postgr.es/m/CAAJ_b97GuPh6wQPbxQS-Zpy16Oh+0aMv-w64QcGrLhCOZZ6p+g@mail.gmail.com
2356+
CREATE TABLE ffk (a int, b int REFERENCES pk) PARTITION BY list (a);
2357+
CREATE TABLE ffk1 PARTITION OF ffk FOR VALUES IN (1);
2358+
ALTER TABLE ffk1 ADD FOREIGN KEY (a) REFERENCES pk;
2359+
ALTER TABLE ffk DETACH PARTITION ffk1;
2360+
DROP TABLE ffk, ffk1;
23542361
CREATE SCHEMA fkpart4;
23552362
SET search_path TO fkpart4;
23562363
-- 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 pkSET a=4502WHERE a=4500;
16731673
DELETEFROM pkWHERE 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+
CREATETABLEffk (aint, bintREFERENCES pk) PARTITION BY list (a);
1678+
CREATETABLEffk1 PARTITION OF ffk FORVALUESIN (1);
1679+
ALTERTABLE ffk1 ADDFOREIGN KEY (a)REFERENCES pk;
1680+
ALTERTABLE ffk DETACH PARTITION ffk1;
1681+
DROPTABLE ffk, ffk1;
1682+
16751683
CREATESCHEMAfkpart4;
16761684
SET search_path TO fkpart4;
16771685
-- dropping/detaching PARTITIONs is prevented if that would break

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp