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

Commit0464fdf

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 parent1755440 commit0464fdf

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
@@ -7851,6 +7851,10 @@ CloneFkReferencing(Relation pg_constraint, Relation parentRel,
78517851
ForeignKeyCacheInfo*fk=lfirst_node(ForeignKeyCacheInfo,cell);
78527852
Form_pg_constraintpartConstr;
78537853
HeapTuplepartcontup;
7854+
Relationtrigrel;
7855+
HeapTupletrigtup;
7856+
SysScanDescscan;
7857+
ScanKeyDatakey;
78547858

78557859
attach_it= true;
78567860

@@ -7902,7 +7906,39 @@ CloneFkReferencing(Relation pg_constraint, Relation parentRel,
79027906

79037907
ReleaseSysCache(partcontup);
79047908

7905-
/* looks good! Attach this constraint */
7909+
/*
7910+
* Looks good! Attach this constraint. Note that the action
7911+
* triggers are no longer needed, so remove them. We identify
7912+
* them because they have our constraint OID, as well as being
7913+
* on the referenced rel.
7914+
*/
7915+
trigrel=heap_open(TriggerRelationId,RowExclusiveLock);
7916+
ScanKeyInit(&key,
7917+
Anum_pg_trigger_tgconstraint,
7918+
BTEqualStrategyNumber,F_OIDEQ,
7919+
ObjectIdGetDatum(fk->conoid));
7920+
7921+
scan=systable_beginscan(trigrel,TriggerConstraintIndexId, true,
7922+
NULL,1,&key);
7923+
while ((trigtup=systable_getnext(scan))!=NULL)
7924+
{
7925+
Form_pg_triggertrgform= (Form_pg_trigger)GETSTRUCT(trigtup);
7926+
7927+
if (trgform->tgconstrrelid!=fk->conrelid)
7928+
continue;
7929+
if (trgform->tgrelid!=fk->confrelid)
7930+
continue;
7931+
7932+
deleteDependencyRecordsForClass(TriggerRelationId,
7933+
trgform->oid,
7934+
ConstraintRelationId,
7935+
DEPENDENCY_INTERNAL);
7936+
CatalogTupleDelete(trigrel,&trigtup->t_self);
7937+
}
7938+
7939+
systable_endscan(scan);
7940+
heap_close(trigrel,RowExclusiveLock);
7941+
79067942
ConstraintSetParentConstraint(fk->conoid,parentConstrOid);
79077943
CommandCounterIncrement();
79087944
attach_it= true;
@@ -15073,19 +15109,50 @@ ATExecDetachPartition(Relation rel, RangeVar *name)
1507315109
}
1507415110
table_close(classRel,RowExclusiveLock);
1507515111

15076-
/* Detach foreign keys */
15112+
/*
15113+
* Detach any foreign keys that are inherited. This includes creating
15114+
* additional action triggers.
15115+
*/
1507715116
fks=copyObject(RelationGetFKeyList(partRel));
1507815117
foreach(cell,fks)
1507915118
{
1508015119
ForeignKeyCacheInfo*fk=lfirst(cell);
1508115120
HeapTuplecontup;
15121+
Form_pg_constraintconform;
15122+
Constraint*fkconstraint;
1508215123

1508315124
contup=SearchSysCache1(CONSTROID,ObjectIdGetDatum(fk->conoid));
1508415125
if (!contup)
1508515126
elog(ERROR,"cache lookup failed for constraint %u",fk->conoid);
15127+
conform= (Form_pg_constraint)GETSTRUCT(contup);
15128+
15129+
/* consider only the inherited foreign keys */
15130+
if (conform->contype!=CONSTRAINT_FOREIGN||
15131+
!OidIsValid(conform->conparentid))
15132+
{
15133+
ReleaseSysCache(contup);
15134+
continue;
15135+
}
1508615136

15137+
/* unset conparentid and adjust conislocal, coninhcount, etc. */
1508715138
ConstraintSetParentConstraint(fk->conoid,InvalidOid);
1508815139

15140+
/*
15141+
* Make the action triggers on the referenced relation. When this was
15142+
* a partition the action triggers pointed to the parent rel (they
15143+
* still do), but now we need separate ones of our own.
15144+
*/
15145+
fkconstraint=makeNode(Constraint);
15146+
fkconstraint->conname=pstrdup(NameStr(conform->conname));
15147+
fkconstraint->fk_upd_action=conform->confupdtype;
15148+
fkconstraint->fk_del_action=conform->confdeltype;
15149+
fkconstraint->deferrable=conform->condeferrable;
15150+
fkconstraint->initdeferred=conform->condeferred;
15151+
15152+
createForeignKeyActionTriggers(partRel,conform->confrelid,
15153+
fkconstraint,fk->conoid,
15154+
conform->conindid);
15155+
1508915156
ReleaseSysCache(contup);
1509015157
}
1509115158
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
@@ -1921,7 +1921,31 @@ alter table fkpart0.fk_part_56 drop constraint fk_part_a_fkey;
19211921
ERROR: cannot drop inherited constraint "fk_part_a_fkey" of relation "fk_part_56"
19221922
alter table fkpart0.fk_part_56_5 drop constraint fk_part_a_fkey;
19231923
ERROR: cannot drop inherited constraint "fk_part_a_fkey" of relation "fk_part_56_5"
1924+
-- verify that attaching and detaching partitions maintains the right set of
1925+
-- triggers
1926+
create schema fkpart1
1927+
create table pkey (a int primary key)
1928+
create table fk_part (a int) partition by list (a)
1929+
create table fk_part_1 partition of fk_part for values in (1) partition by list (a)
1930+
create table fk_part_1_1 partition of fk_part_1 for values in (1);
1931+
alter table fkpart1.fk_part add foreign key (a) references fkpart1.pkey;
1932+
insert into fkpart1.fk_part values (1);-- should fail
1933+
ERROR: insert or update on table "fk_part_1_1" violates foreign key constraint "fk_part_a_fkey"
1934+
DETAIL: Key (a)=(1) is not present in table "pkey".
1935+
insert into fkpart1.pkey values (1);
1936+
insert into fkpart1.fk_part values (1);
1937+
delete from fkpart1.pkey where a = 1;-- should fail
1938+
ERROR: update or delete on table "pkey" violates foreign key constraint "fk_part_a_fkey" on table "fk_part"
1939+
DETAIL: Key (a)=(1) is still referenced from table "fk_part".
1940+
alter table fkpart1.fk_part detach partition fkpart1.fk_part_1;
1941+
create table fkpart1.fk_part_1_2 partition of fkpart1.fk_part_1 for values in (2);
1942+
insert into fkpart1.fk_part_1 values (2);-- should fail
1943+
ERROR: insert or update on table "fk_part_1_2" violates foreign key constraint "fk_part_a_fkey"
1944+
DETAIL: Key (a)=(2) is not present in table "pkey".
1945+
delete from fkpart1.pkey where a = 1;
1946+
ERROR: update or delete on table "pkey" violates foreign key constraint "fk_part_a_fkey" on table "fk_part_1"
1947+
DETAIL: Key (a)=(1) is still referenced from table "fk_part_1".
19241948
\set VERBOSITY terse\\ -- suppress cascade details
1925-
drop schema fkpart0 cascade;
1926-
NOTICE: drop cascades to2 other objects
1949+
drop schema fkpart0, fkpart1 cascade;
1950+
NOTICE: drop cascades to5 other objects
19271951
\set VERBOSITY default

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

Lines changed: 18 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1375,6 +1375,23 @@ create table fkpart0.fk_part_56_5 partition of fkpart0.fk_part_56
13751375
altertablefkpart0.fk_part_56 dropconstraint fk_part_a_fkey;
13761376
altertablefkpart0.fk_part_56_5 dropconstraint fk_part_a_fkey;
13771377

1378+
-- verify that attaching and detaching partitions maintains the right set of
1379+
-- triggers
1380+
createschemafkpart1
1381+
createtablepkey (aintprimary key)
1382+
createtablefk_part (aint) partition by list (a)
1383+
createtablefk_part_1 partition of fk_part forvaluesin (1) partition by list (a)
1384+
createtablefk_part_1_1 partition of fk_part_1 forvaluesin (1);
1385+
altertablefkpart1.fk_part addforeign key (a)referencesfkpart1.pkey;
1386+
insert intofkpart1.fk_partvalues (1);-- should fail
1387+
insert intofkpart1.pkeyvalues (1);
1388+
insert intofkpart1.fk_partvalues (1);
1389+
deletefromfkpart1.pkeywhere a=1;-- should fail
1390+
altertablefkpart1.fk_part detach partitionfkpart1.fk_part_1;
1391+
createtablefkpart1.fk_part_1_2 partition offkpart1.fk_part_1 forvaluesin (2);
1392+
insert intofkpart1.fk_part_1values (2);-- should fail
1393+
deletefromfkpart1.pkeywhere a=1;
1394+
13781395
\set VERBOSITY terse\\-- suppress cascade details
1379-
dropschema fkpart0 cascade;
1396+
dropschema fkpart0, fkpart1 cascade;
13801397
\set VERBOSITY default

0 commit comments

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