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

Commit46a8c27

Browse files
alvherreioguixtenderwg
committed
Restructure foreign key handling code for ATTACH/DETACH
... to fix bugs when the referenced table is partitioned.The catalog representation we chose for foreign keys connectingpartitioned tables (in commitf56f8f8) is inconvenient, in thesense that a standalone table has a different way to represent theconstraint when referencing a partitioned table, than when the sametable becomes a partition (and vice versa). Because of this, we need tocreate additional catalog rows on detach (pg_constraint and pg_trigger),and remove them on attach. We were doing some of those things, but notall of them, leading to missing catalog rows in certain cases.The worst problem seems to be that we are missing action triggers afterdetaching a partition, which means that you could update/delete rowsfrom the referenced partitioned table that still had referencing rows onthat table, the server failing to throw the required errors.!!!Note that this means existing databases with FKs that referencepartitioned tables might have rows that break relational integrity, ontables that were once partitions on the referencing side of the FK.Another possible problem is that trying to reattach a tablethat had been detached would fail indicating that internal triggerscannot be found, which from the user's point of view is nonsensical.In branches 15 and above, we fix this by creating a new helper functionaddFkConstraint() which is in charge of creating a standalonepg_constraint row, and repurposing addFkRecurseReferencing() andaddFkRecurseReferenced() so that they're only the recursive routine foreach side of the FK, and they call addFkConstraint() to createpg_constraint at each partitioning level and add the necessary triggers.These new routines can be used during partition creation, partitionattach and detach, and foreign key creation. This reduces redundantcode and simplifies the flow.In branches 14 and 13, we have a much simpler fix that consists onsimply removing the constraint on detach. The reason is that thosebranches are missing commitf456634, which reworked the way thisworks in a way that we didn't consider back-patchable at the time.We opted to leave branch 12 alone, because it's different from branch 13enough that the fix doesn't apply; and because it is going in EOL modevery soon, patching it now might be worse since there's no way to undothe damage if it goes wrong.Existing databases might need to be repaired.In the future we might want to rethink the catalog representation toavoid this problem, but for now the code seems to do what's required tomake the constraints operate correctly.Co-authored-by: Jehan-Guillaume de Rorthais <jgdr@dalibo.com>Co-authored-by: Tender Wang <tndrwang@gmail.com>Co-authored-by: Alvaro Herrera <alvherre@alvh.no-ip.org>Reported-by: Guillaume Lelarge <guillaume@lelarge.info>Reported-by: Jehan-Guillaume de Rorthais <jgdr@dalibo.com>Reported-by: Thomas Baehler (SBB CFF FFS) <thomas.baehler2@sbb.ch>Discussion:https://postgr.es/m/20230420144344.40744130@karstDiscussion:https://postgr.es/m/20230705233028.2f554f73@karstDiscussion:https://postgr.es/m/GVAP278MB02787E7134FD691861635A8BC9032@GVAP278MB0278.CHEP278.PROD.OUTLOOK.COMDiscussion:https://postgr.es/m/18541-628a61bc267cd2d3@postgresql.org
1 parent5e94f61 commit46a8c27

File tree

3 files changed

+281
-26
lines changed

3 files changed

+281
-26
lines changed

‎src/backend/commands/tablecmds.c

Lines changed: 134 additions & 26 deletions
Original file line numberDiff line numberDiff line change
@@ -10389,6 +10389,82 @@ tryAttachPartitionForeignKey(ForeignKeyCacheInfo *fk,
1038910389
table_close(trigrel, RowExclusiveLock);
1039010390

1039110391
ConstraintSetParentConstraint(fk->conoid, parentConstrOid, partRelid);
10392+
10393+
/*
10394+
* If the referenced table is partitioned, then the partition we're
10395+
* attaching now has extra pg_constraint rows and action triggers that are
10396+
* no longer needed. Remove those.
10397+
*/
10398+
if (get_rel_relkind(fk->confrelid) == RELKIND_PARTITIONED_TABLE)
10399+
{
10400+
Relation pg_constraint = table_open(ConstraintRelationId, RowShareLock);
10401+
ObjectAddresses *objs;
10402+
HeapTuple consttup;
10403+
10404+
ScanKeyInit(&key,
10405+
Anum_pg_constraint_conrelid,
10406+
BTEqualStrategyNumber, F_OIDEQ,
10407+
ObjectIdGetDatum(fk->conrelid));
10408+
10409+
scan = systable_beginscan(pg_constraint,
10410+
ConstraintRelidTypidNameIndexId,
10411+
true, NULL, 1, &key);
10412+
objs = new_object_addresses();
10413+
while ((consttup = systable_getnext(scan)) != NULL)
10414+
{
10415+
Form_pg_constraint conform = (Form_pg_constraint) GETSTRUCT(consttup);
10416+
10417+
if (conform->conparentid != fk->conoid)
10418+
continue;
10419+
else
10420+
{
10421+
ObjectAddress addr;
10422+
SysScanDesc scan2;
10423+
ScanKeyData key2;
10424+
int n PG_USED_FOR_ASSERTS_ONLY;
10425+
10426+
ObjectAddressSet(addr, ConstraintRelationId, conform->oid);
10427+
add_exact_object_address(&addr, objs);
10428+
10429+
/*
10430+
* First we must delete the dependency records that bind
10431+
* the constraint records together.
10432+
*/
10433+
n = deleteDependencyRecordsForSpecific(ConstraintRelationId,
10434+
conform->oid,
10435+
DEPENDENCY_INTERNAL,
10436+
ConstraintRelationId,
10437+
fk->conoid);
10438+
Assert(n == 1); /* actually only one is expected */
10439+
10440+
/*
10441+
* Now search for the triggers for this constraint and set
10442+
* them up for deletion too
10443+
*/
10444+
ScanKeyInit(&key2,
10445+
Anum_pg_trigger_tgconstraint,
10446+
BTEqualStrategyNumber, F_OIDEQ,
10447+
ObjectIdGetDatum(conform->oid));
10448+
scan2 = systable_beginscan(trigrel, TriggerConstraintIndexId,
10449+
true, NULL, 1, &key2);
10450+
while ((trigtup = systable_getnext(scan2)) != NULL)
10451+
{
10452+
ObjectAddressSet(addr, TriggerRelationId,
10453+
((Form_pg_trigger) GETSTRUCT(trigtup))->oid);
10454+
add_exact_object_address(&addr, objs);
10455+
}
10456+
systable_endscan(scan2);
10457+
}
10458+
}
10459+
/* make the dependency deletions visible */
10460+
CommandCounterIncrement();
10461+
performMultipleDeletions(objs, DROP_RESTRICT,
10462+
PERFORM_DELETION_INTERNAL);
10463+
systable_endscan(scan);
10464+
10465+
table_close(pg_constraint, RowShareLock);
10466+
}
10467+
1039210468
CommandCounterIncrement();
1039310469
return true;
1039410470
}
@@ -18154,6 +18230,7 @@ DetachPartitionFinalize(Relation rel, Relation partRel, bool concurrent,
1815418230
new_repl[Natts_pg_class];
1815518231
HeapTupletuple,
1815618232
newtuple;
18233+
ObjectAddresses *dropobjs = NULL;
1815718234

1815818235
if (concurrent)
1815918236
{
@@ -18168,16 +18245,15 @@ DetachPartitionFinalize(Relation rel, Relation partRel, bool concurrent,
1816818245
DropClonedTriggersFromPartition(RelationGetRelid(partRel));
1816918246

1817018247
/*
18171-
* Detach any foreign keys that are inherited. This includes creating
18172-
*additional action triggers.
18248+
* Detach any foreign keys that are inherited -- or, if they reference
18249+
*partitioned tables, drop them.
1817318250
*/
1817418251
fks = copyObject(RelationGetFKeyList(partRel));
1817518252
foreach(cell, fks)
1817618253
{
1817718254
ForeignKeyCacheInfo *fk = lfirst(cell);
1817818255
HeapTuplecontup;
1817918256
Form_pg_constraint conform;
18180-
Constraint *fkconstraint;
1818118257

1818218258
contup = SearchSysCache1(CONSTROID, ObjectIdGetDatum(fk->conoid));
1818318259
if (!HeapTupleIsValid(contup))
@@ -18192,39 +18268,71 @@ DetachPartitionFinalize(Relation rel, Relation partRel, bool concurrent,
1819218268
continue;
1819318269
}
1819418270

18195-
/*unset conparentid and adjust conislocal, coninhcount, etc. */
18271+
/*Mark the constraint as independent */
1819618272
ConstraintSetParentConstraint(fk->conoid, InvalidOid, InvalidOid);
1819718273

1819818274
/*
18199-
* Make the action triggers on the referenced relation. When this was
18200-
* a partition the action triggers pointed to the parent rel (they
18201-
* still do), but now we need separate ones of our own.
18275+
* If the constraint references a partitioned table, just drop the
18276+
* constraint, because it's more work to preserve the constraint
18277+
* correctly.
18278+
*
18279+
* If it references a plain table, then we can create the action
18280+
* triggers and it'll be okay.
1820218281
*/
18203-
fkconstraint = makeNode(Constraint);
18204-
fkconstraint->contype = CONSTRAINT_FOREIGN;
18205-
fkconstraint->conname = pstrdup(NameStr(conform->conname));
18206-
fkconstraint->deferrable = conform->condeferrable;
18207-
fkconstraint->initdeferred = conform->condeferred;
18208-
fkconstraint->location = -1;
18209-
fkconstraint->pktable = NULL;
18210-
fkconstraint->fk_attrs = NIL;
18211-
fkconstraint->pk_attrs = NIL;
18212-
fkconstraint->fk_matchtype = conform->confmatchtype;
18213-
fkconstraint->fk_upd_action = conform->confupdtype;
18214-
fkconstraint->fk_del_action = conform->confdeltype;
18215-
fkconstraint->old_conpfeqop = NIL;
18216-
fkconstraint->old_pktable_oid = InvalidOid;
18217-
fkconstraint->skip_validation = false;
18218-
fkconstraint->initially_valid = true;
18282+
if (get_rel_relkind(fk->confrelid) == RELKIND_PARTITIONED_TABLE)
18283+
{
18284+
ObjectAddress constraddr;
1821918285

18220-
createForeignKeyActionTriggers(partRel, conform->confrelid,
18221-
fkconstraint, fk->conoid,
18222-
conform->conindid);
18286+
/* make the dependency deletions above visible */
18287+
CommandCounterIncrement();
18288+
18289+
/*
18290+
* Remember the constraint and its triggers for later deletion.
18291+
*/
18292+
if (dropobjs == NULL)
18293+
dropobjs = new_object_addresses();
18294+
ObjectAddressSet(constraddr, ConstraintRelationId, fk->conoid);
18295+
add_exact_object_address(&constraddr, dropobjs);
18296+
}
18297+
else
18298+
{
18299+
Constraint *fkconstraint;
18300+
18301+
/*
18302+
* Make the action triggers on the referenced relation. When this
18303+
* was a partition the action triggers pointed to the parent rel
18304+
* (they still do), but now we need separate ones of our own.
18305+
*/
18306+
fkconstraint = makeNode(Constraint);
18307+
fkconstraint->contype = CONSTRAINT_FOREIGN;
18308+
fkconstraint->conname = pstrdup(NameStr(conform->conname));
18309+
fkconstraint->deferrable = conform->condeferrable;
18310+
fkconstraint->initdeferred = conform->condeferred;
18311+
fkconstraint->location = -1;
18312+
fkconstraint->pktable = NULL;
18313+
fkconstraint->fk_attrs = NIL;
18314+
fkconstraint->pk_attrs = NIL;
18315+
fkconstraint->fk_matchtype = conform->confmatchtype;
18316+
fkconstraint->fk_upd_action = conform->confupdtype;
18317+
fkconstraint->fk_del_action = conform->confdeltype;
18318+
fkconstraint->old_conpfeqop = NIL;
18319+
fkconstraint->old_pktable_oid = InvalidOid;
18320+
fkconstraint->skip_validation = false;
18321+
fkconstraint->initially_valid = true;
18322+
18323+
createForeignKeyActionTriggers(partRel, conform->confrelid,
18324+
fkconstraint, fk->conoid,
18325+
conform->conindid);
18326+
}
1822318327

1822418328
ReleaseSysCache(contup);
1822518329
}
1822618330
list_free_deep(fks);
1822718331

18332+
/* If we collected any constraints for deletion, do so now. */
18333+
if (dropobjs != NULL)
18334+
performMultipleDeletions(dropobjs, DROP_CASCADE, 0);
18335+
1822818336
/*
1822918337
* Any sub-constraints that are in the referenced-side of a larger
1823018338
* constraint have to be removed. This partition is no longer part of the

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

Lines changed: 91 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2665,3 +2665,94 @@ DROP SCHEMA fkpart10 CASCADE;
26652665
NOTICE: drop cascades to 2 other objects
26662666
DETAIL: drop cascades to table fkpart10.tbl1
26672667
drop cascades to table fkpart10.tbl2
2668+
-- When a table is attached as partition to a partitioned table that has
2669+
-- a foreign key to another partitioned table, it acquires a clone of the
2670+
-- FK. Upon detach, Postgres 14 and earlier remove the foreign key (newer
2671+
-- versions make it a standalone constraint.)
2672+
CREATE SCHEMA fkpart12
2673+
CREATE TABLE fk_p ( id int, jd int, PRIMARY KEY(id, jd)) PARTITION BY list (id)
2674+
CREATE TABLE fk_p_1 PARTITION OF fk_p FOR VALUES IN (1) PARTITION BY list (jd)
2675+
CREATE TABLE fk_p_1_1 PARTITION OF fk_p_1 FOR VALUES IN (1)
2676+
CREATE TABLE fk_p_1_2 PARTITION OF fk_p_1 FOR VALUES IN (2)
2677+
CREATE TABLE fk_p_2 PARTITION OF fk_p FOR VALUES IN (2) PARTITION BY list (jd)
2678+
CREATE TABLE fk_p_2_1 PARTITION OF fk_p_2 FOR VALUES IN (1)
2679+
CREATE TABLE fk_p_2_2 PARTITION OF fk_p_2 FOR VALUES IN (2)
2680+
CREATE TABLE fk_r_1 ( id int PRIMARY KEY, p_id int NOT NULL, p_jd int NOT NULL)
2681+
CREATE TABLE fk_r_2 ( id int PRIMARY KEY, p_id int NOT NULL, p_jd int NOT NULL) PARTITION BY list (id)
2682+
CREATE TABLE fk_r_2_1 PARTITION OF fk_r_2 FOR VALUES IN (2, 1)
2683+
CREATE TABLE fk_r ( id int PRIMARY KEY, p_id int NOT NULL, p_jd int NOT NULL,
2684+
FOREIGN KEY (p_id, p_jd) REFERENCES fk_p (id, jd)
2685+
) PARTITION BY list (id);
2686+
SET search_path TO fkpart12;
2687+
INSERT INTO fk_p VALUES (1, 1);
2688+
ALTER TABLE fk_r ATTACH PARTITION fk_r_1 FOR VALUES IN (1);
2689+
ALTER TABLE fk_r ATTACH PARTITION fk_r_2 FOR VALUES IN (2);
2690+
\d fk_r_2
2691+
Partitioned table "fkpart12.fk_r_2"
2692+
Column | Type | Collation | Nullable | Default
2693+
--------+---------+-----------+----------+---------
2694+
id | integer | | not null |
2695+
p_id | integer | | not null |
2696+
p_jd | integer | | not null |
2697+
Partition of: fk_r FOR VALUES IN (2)
2698+
Partition key: LIST (id)
2699+
Indexes:
2700+
"fk_r_2_pkey" PRIMARY KEY, btree (id)
2701+
Foreign-key constraints:
2702+
TABLE "fk_r" CONSTRAINT "fk_r_p_id_p_jd_fkey" FOREIGN KEY (p_id, p_jd) REFERENCES fk_p(id, jd)
2703+
Number of partitions: 1 (Use \d+ to list them.)
2704+
2705+
INSERT INTO fk_r VALUES (1, 1, 1);
2706+
INSERT INTO fk_r VALUES (2, 2, 1);-- fails
2707+
ERROR: insert or update on table "fk_r_2_1" violates foreign key constraint "fk_r_p_id_p_jd_fkey"
2708+
DETAIL: Key (p_id, p_jd)=(2, 1) is not present in table "fk_p".
2709+
ALTER TABLE fk_r DETACH PARTITION fk_r_1;
2710+
ALTER TABLE fk_r DETACH PARTITION fk_r_2;
2711+
\d fk_r_2
2712+
Partitioned table "fkpart12.fk_r_2"
2713+
Column | Type | Collation | Nullable | Default
2714+
--------+---------+-----------+----------+---------
2715+
id | integer | | not null |
2716+
p_id | integer | | not null |
2717+
p_jd | integer | | not null |
2718+
Partition key: LIST (id)
2719+
Indexes:
2720+
"fk_r_2_pkey" PRIMARY KEY, btree (id)
2721+
Number of partitions: 1 (Use \d+ to list them.)
2722+
2723+
INSERT INTO fk_r_1 VALUES (2, 1, 2); -- works: there's no FK anymore
2724+
DELETE FROM fk_p;-- works
2725+
ALTER TABLE fk_r ATTACH PARTITION fk_r_1 FOR VALUES IN (1);-- fails
2726+
ERROR: partition constraint of relation "fk_r_1" is violated by some row
2727+
INSERT INTO fk_r_2 VALUES (2, 2, 2);
2728+
INSERT INTO fk_p VALUES (2, 2);
2729+
ALTER TABLE fk_r ATTACH PARTITION fk_r_2 FOR VALUES IN (2);
2730+
\d fk_r_2
2731+
Partitioned table "fkpart12.fk_r_2"
2732+
Column | Type | Collation | Nullable | Default
2733+
--------+---------+-----------+----------+---------
2734+
id | integer | | not null |
2735+
p_id | integer | | not null |
2736+
p_jd | integer | | not null |
2737+
Partition of: fk_r FOR VALUES IN (2)
2738+
Partition key: LIST (id)
2739+
Indexes:
2740+
"fk_r_2_pkey" PRIMARY KEY, btree (id)
2741+
Foreign-key constraints:
2742+
TABLE "fk_r" CONSTRAINT "fk_r_p_id_p_jd_fkey" FOREIGN KEY (p_id, p_jd) REFERENCES fk_p(id, jd)
2743+
Number of partitions: 1 (Use \d+ to list them.)
2744+
2745+
DELETE FROM fk_p;-- fails
2746+
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"
2747+
DETAIL: Key (id, jd)=(2, 2) is still referenced from table "fk_r".
2748+
-- these should all fail
2749+
ALTER TABLE fk_r_1 DROP CONSTRAINT fk_r_p_id_p_jd_fkey;
2750+
ERROR: constraint "fk_r_p_id_p_jd_fkey" of relation "fk_r_1" does not exist
2751+
ALTER TABLE fk_r DROP CONSTRAINT fk_r_p_id_p_jd_fkey1;
2752+
ERROR: cannot drop inherited constraint "fk_r_p_id_p_jd_fkey1" of relation "fk_r"
2753+
ALTER TABLE fk_r_2 DROP CONSTRAINT fk_r_p_id_p_jd_fkey;
2754+
ERROR: cannot drop inherited constraint "fk_r_p_id_p_jd_fkey" of relation "fk_r_2"
2755+
SET client_min_messages TO warning;
2756+
DROP SCHEMA fkpart12 CASCADE;
2757+
RESET client_min_messages;
2758+
RESET search_path;

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

Lines changed: 56 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1902,3 +1902,59 @@ UPDATE fkpart10.tbl1 SET f1 = 2 WHERE f1 = 1;
19021902
INSERT INTOfkpart10.tbl1VALUES (0), (1);
19031903
COMMIT;
19041904
DROPSCHEMA fkpart10 CASCADE;
1905+
1906+
-- When a table is attached as partition to a partitioned table that has
1907+
-- a foreign key to another partitioned table, it acquires a clone of the
1908+
-- FK. Upon detach, Postgres 14 and earlier remove the foreign key (newer
1909+
-- versions make it a standalone constraint.)
1910+
CREATESCHEMAfkpart12
1911+
CREATETABLEfk_p ( idint, jdint,PRIMARY KEY(id, jd)) PARTITION BY list (id)
1912+
CREATETABLEfk_p_1 PARTITION OF fk_p FORVALUESIN (1) PARTITION BY list (jd)
1913+
CREATETABLEfk_p_1_1 PARTITION OF fk_p_1 FORVALUESIN (1)
1914+
CREATETABLEfk_p_1_2 PARTITION OF fk_p_1 FORVALUESIN (2)
1915+
CREATETABLEfk_p_2 PARTITION OF fk_p FORVALUESIN (2) PARTITION BY list (jd)
1916+
CREATETABLEfk_p_2_1 PARTITION OF fk_p_2 FORVALUESIN (1)
1917+
CREATETABLEfk_p_2_2 PARTITION OF fk_p_2 FORVALUESIN (2)
1918+
CREATETABLEfk_r_1 ( idintPRIMARY KEY, p_idintNOT NULL, p_jdintNOT NULL)
1919+
CREATETABLEfk_r_2 ( idintPRIMARY KEY, p_idintNOT NULL, p_jdintNOT NULL) PARTITION BY list (id)
1920+
CREATETABLEfk_r_2_1 PARTITION OF fk_r_2 FORVALUESIN (2,1)
1921+
CREATETABLEfk_r ( idintPRIMARY KEY, p_idintNOT NULL, p_jdintNOT NULL,
1922+
FOREIGN KEY (p_id, p_jd)REFERENCES fk_p (id, jd)
1923+
) PARTITION BY list (id);
1924+
SET search_path TO fkpart12;
1925+
1926+
INSERT INTO fk_pVALUES (1,1);
1927+
1928+
ALTERTABLE fk_r ATTACH PARTITION fk_r_1 FORVALUESIN (1);
1929+
ALTERTABLE fk_r ATTACH PARTITION fk_r_2 FORVALUESIN (2);
1930+
1931+
\d fk_r_2
1932+
1933+
INSERT INTO fk_rVALUES (1,1,1);
1934+
INSERT INTO fk_rVALUES (2,2,1);-- fails
1935+
1936+
ALTERTABLE fk_r DETACH PARTITION fk_r_1;
1937+
ALTERTABLE fk_r DETACH PARTITION fk_r_2;
1938+
1939+
\d fk_r_2
1940+
1941+
INSERT INTO fk_r_1VALUES (2,1,2);-- works: there's no FK anymore
1942+
DELETEFROM fk_p;-- works
1943+
1944+
ALTERTABLE fk_r ATTACH PARTITION fk_r_1 FORVALUESIN (1);-- fails
1945+
1946+
INSERT INTO fk_r_2VALUES (2,2,2);
1947+
INSERT INTO fk_pVALUES (2,2);
1948+
ALTERTABLE fk_r ATTACH PARTITION fk_r_2 FORVALUESIN (2);
1949+
\d fk_r_2
1950+
DELETEFROM fk_p;-- fails
1951+
1952+
-- these should all fail
1953+
ALTERTABLE fk_r_1 DROPCONSTRAINT fk_r_p_id_p_jd_fkey;
1954+
ALTERTABLE fk_r DROPCONSTRAINT fk_r_p_id_p_jd_fkey1;
1955+
ALTERTABLE fk_r_2 DROPCONSTRAINT fk_r_p_id_p_jd_fkey;
1956+
1957+
SET client_min_messages TO warning;
1958+
DROPSCHEMA fkpart12 CASCADE;
1959+
RESET client_min_messages;
1960+
RESET search_path;

0 commit comments

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