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

Commit2f30847

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 parente292ba3 commit2f30847

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
HeapTupletuple,
1925919259
newtuple;
1926019260
Relationtrigrel = 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 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