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

Commit123cc69

Browse files
committed
Create action triggers when partitions are detached
Detaching a partition from a partitioned table that's constrained byforeign keys requires additional action triggers on the referenced side;otherwise, DELETE/UPDATE actions there fail to notice rows in the tablethat was partition, and so are incorrectly allowed through. With thiscommit, those triggers are now created. Conversely, when a table thathas a foreign key is attached as a partition to a table that also hasthe same foreign key, those action triggers are no longer needed, so weremove them.Add a minimal test case verifying (part of) this.Authors: Amit Langote, Álvaro HerreraDiscussion:https://postgr.es/m/f2b8ead5-4131-d5a8-8016-2ea0a31250af@lab.ntt.co.jp
1 parenta747430 commit123cc69

File tree

3 files changed

+113
-5
lines changed

3 files changed

+113
-5
lines changed

‎src/backend/commands/tablecmds.c

Lines changed: 69 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -7945,6 +7945,10 @@ CloneFkReferencing(Relation pg_constraint, Relation parentRel,
79457945
ForeignKeyCacheInfo*fk=lfirst_node(ForeignKeyCacheInfo,cell);
79467946
Form_pg_constraintpartConstr;
79477947
HeapTuplepartcontup;
7948+
Relationtrigrel;
7949+
HeapTupletrigtup;
7950+
SysScanDescscan;
7951+
ScanKeyDatakey;
79487952

79497953
attach_it= true;
79507954

@@ -7996,7 +8000,39 @@ CloneFkReferencing(Relation pg_constraint, Relation parentRel,
79968000

79978001
ReleaseSysCache(partcontup);
79988002

7999-
/* looks good! Attach this constraint */
8003+
/*
8004+
* Looks good! Attach this constraint. Note that the action
8005+
* triggers are no longer needed, so remove them. We identify
8006+
* them because they have our constraint OID, as well as being
8007+
* on the referenced rel.
8008+
*/
8009+
trigrel=heap_open(TriggerRelationId,RowExclusiveLock);
8010+
ScanKeyInit(&key,
8011+
Anum_pg_trigger_tgconstraint,
8012+
BTEqualStrategyNumber,F_OIDEQ,
8013+
ObjectIdGetDatum(fk->conoid));
8014+
8015+
scan=systable_beginscan(trigrel,TriggerConstraintIndexId, true,
8016+
NULL,1,&key);
8017+
while ((trigtup=systable_getnext(scan))!=NULL)
8018+
{
8019+
Form_pg_triggertrgform= (Form_pg_trigger)GETSTRUCT(trigtup);
8020+
8021+
if (trgform->tgconstrrelid!=fk->conrelid)
8022+
continue;
8023+
if (trgform->tgrelid!=fk->confrelid)
8024+
continue;
8025+
8026+
deleteDependencyRecordsForClass(TriggerRelationId,
8027+
HeapTupleGetOid(trigtup),
8028+
ConstraintRelationId,
8029+
DEPENDENCY_INTERNAL);
8030+
CatalogTupleDelete(trigrel,&trigtup->t_self);
8031+
}
8032+
8033+
systable_endscan(scan);
8034+
heap_close(trigrel,RowExclusiveLock);
8035+
80008036
ConstraintSetParentConstraint(fk->conoid,parentConstrOid);
80018037
CommandCounterIncrement();
80028038
attach_it= true;
@@ -15211,19 +15247,50 @@ ATExecDetachPartition(Relation rel, RangeVar *name)
1521115247
}
1521215248
heap_close(classRel,RowExclusiveLock);
1521315249

15214-
/* Detach foreign keys */
15250+
/*
15251+
* Detach any foreign keys that are inherited. This includes creating
15252+
* additional action triggers.
15253+
*/
1521515254
fks=copyObject(RelationGetFKeyList(partRel));
1521615255
foreach(cell,fks)
1521715256
{
1521815257
ForeignKeyCacheInfo*fk=lfirst(cell);
1521915258
HeapTuplecontup;
15259+
Form_pg_constraintconform;
15260+
Constraint*fkconstraint;
1522015261

1522115262
contup=SearchSysCache1(CONSTROID,ObjectIdGetDatum(fk->conoid));
1522215263
if (!contup)
1522315264
elog(ERROR,"cache lookup failed for constraint %u",fk->conoid);
15265+
conform= (Form_pg_constraint)GETSTRUCT(contup);
15266+
15267+
/* consider only the inherited foreign keys */
15268+
if (conform->contype!=CONSTRAINT_FOREIGN||
15269+
!OidIsValid(conform->conparentid))
15270+
{
15271+
ReleaseSysCache(contup);
15272+
continue;
15273+
}
1522415274

15275+
/* unset conparentid and adjust conislocal, coninhcount, etc. */
1522515276
ConstraintSetParentConstraint(fk->conoid,InvalidOid);
1522615277

15278+
/*
15279+
* Make the action triggers on the referenced relation. When this was
15280+
* a partition the action triggers pointed to the parent rel (they
15281+
* still do), but now we need separate ones of our own.
15282+
*/
15283+
fkconstraint=makeNode(Constraint);
15284+
fkconstraint->conname=pstrdup(NameStr(conform->conname));
15285+
fkconstraint->fk_upd_action=conform->confupdtype;
15286+
fkconstraint->fk_del_action=conform->confdeltype;
15287+
fkconstraint->deferrable=conform->condeferrable;
15288+
fkconstraint->initdeferred=conform->condeferred;
15289+
15290+
createForeignKeyActionTriggers(partRel,conform->confrelid,
15291+
fkconstraint,fk->conoid,
15292+
conform->conindid);
15293+
1522715294
ReleaseSysCache(contup);
1522815295
}
1522915296
list_free_deep(fks);

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

Lines changed: 26 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1860,7 +1860,31 @@ alter table fkpart0.fk_part_56 drop constraint fk_part_a_fkey;
18601860
ERROR: cannot drop inherited constraint "fk_part_a_fkey" of relation "fk_part_56"
18611861
alter table fkpart0.fk_part_56_5 drop constraint fk_part_a_fkey;
18621862
ERROR: cannot drop inherited constraint "fk_part_a_fkey" of relation "fk_part_56_5"
1863+
-- verify that attaching and detaching partitions maintains the right set of
1864+
-- triggers
1865+
create schema fkpart1
1866+
create table pkey (a int primary key)
1867+
create table fk_part (a int) partition by list (a)
1868+
create table fk_part_1 partition of fk_part for values in (1) partition by list (a)
1869+
create table fk_part_1_1 partition of fk_part_1 for values in (1);
1870+
alter table fkpart1.fk_part add foreign key (a) references fkpart1.pkey;
1871+
insert into fkpart1.fk_part values (1);-- should fail
1872+
ERROR: insert or update on table "fk_part_1_1" violates foreign key constraint "fk_part_a_fkey"
1873+
DETAIL: Key (a)=(1) is not present in table "pkey".
1874+
insert into fkpart1.pkey values (1);
1875+
insert into fkpart1.fk_part values (1);
1876+
delete from fkpart1.pkey where a = 1;-- should fail
1877+
ERROR: update or delete on table "pkey" violates foreign key constraint "fk_part_a_fkey" on table "fk_part"
1878+
DETAIL: Key (a)=(1) is still referenced from table "fk_part".
1879+
alter table fkpart1.fk_part detach partition fkpart1.fk_part_1;
1880+
create table fkpart1.fk_part_1_2 partition of fkpart1.fk_part_1 for values in (2);
1881+
insert into fkpart1.fk_part_1 values (2);-- should fail
1882+
ERROR: insert or update on table "fk_part_1_2" violates foreign key constraint "fk_part_a_fkey"
1883+
DETAIL: Key (a)=(2) is not present in table "pkey".
1884+
delete from fkpart1.pkey where a = 1;
1885+
ERROR: update or delete on table "pkey" violates foreign key constraint "fk_part_a_fkey" on table "fk_part_1"
1886+
DETAIL: Key (a)=(1) is still referenced from table "fk_part_1".
18631887
\set VERBOSITY terse\\ -- suppress cascade details
1864-
drop schema fkpart0 cascade;
1865-
NOTICE: drop cascades to2 other objects
1888+
drop schema fkpart0, fkpart1 cascade;
1889+
NOTICE: drop cascades to5 other objects
18661890
\set VERBOSITY default

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

Lines changed: 18 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1324,6 +1324,23 @@ create table fkpart0.fk_part_56_5 partition of fkpart0.fk_part_56
13241324
altertablefkpart0.fk_part_56 dropconstraint fk_part_a_fkey;
13251325
altertablefkpart0.fk_part_56_5 dropconstraint fk_part_a_fkey;
13261326

1327+
-- verify that attaching and detaching partitions maintains the right set of
1328+
-- triggers
1329+
createschemafkpart1
1330+
createtablepkey (aintprimary key)
1331+
createtablefk_part (aint) partition by list (a)
1332+
createtablefk_part_1 partition of fk_part forvaluesin (1) partition by list (a)
1333+
createtablefk_part_1_1 partition of fk_part_1 forvaluesin (1);
1334+
altertablefkpart1.fk_part addforeign key (a)referencesfkpart1.pkey;
1335+
insert intofkpart1.fk_partvalues (1);-- should fail
1336+
insert intofkpart1.pkeyvalues (1);
1337+
insert intofkpart1.fk_partvalues (1);
1338+
deletefromfkpart1.pkeywhere a=1;-- should fail
1339+
altertablefkpart1.fk_part detach partitionfkpart1.fk_part_1;
1340+
createtablefkpart1.fk_part_1_2 partition offkpart1.fk_part_1 forvaluesin (2);
1341+
insert intofkpart1.fk_part_1values (2);-- should fail
1342+
deletefromfkpart1.pkeywhere a=1;
1343+
13271344
\set VERBOSITY terse\\-- suppress cascade details
1328-
dropschema fkpart0 cascade;
1345+
dropschema fkpart0, fkpart1 cascade;
13291346
\set VERBOSITY default

0 commit comments

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