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

Commit86f5759

Browse files
committed
Allow FOR EACH ROW triggers on partitioned tables
Previously, FOR EACH ROW triggers were not allowed in partitionedtables. Now we allow AFTER triggers on them, and on trigger creation wecascade to create an identical trigger in each partition. We also clonethe triggers to each partition that is created or attached later.This means that deferred unique keys are allowed on partitioned tables,too.Author: Álvaro HerreraReviewed-by: Peter Eisentraut, Simon Riggs, Amit Langote, Robert Haas,Thomas MunroDiscussion:https://postgr.es/m/20171229225319.ajltgss2ojkfd3kp@alvherre.pgsql
1 parent5700aa1 commit86f5759

File tree

20 files changed

+1078
-93
lines changed

20 files changed

+1078
-93
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2254,6 +2254,14 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
22542254
key, foreign key, or exclusion constraint; else 0</entry>
22552255
</row>
22562256

2257+
<row>
2258+
<entry><structfield>conparentid</structfield></entry>
2259+
<entry><type>oid</type></entry>
2260+
<entry><literal><link linkend="catalog-pg-constraint"><structname>pg_constraint</structname></link>.oid</literal></entry>
2261+
<entry>The corresponding constraint in the parent partitioned table,
2262+
if this is a constraint in a partition; else 0</entry>
2263+
</row>
2264+
22572265
<row>
22582266
<entry><structfield>confrelid</structfield></entry>
22592267
<entry><type>oid</type></entry>

‎doc/src/sgml/ref/create_trigger.sgml

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -512,6 +512,13 @@ UPDATE OF <replaceable>column_name1</replaceable> [, <replaceable>column_name2</
512512
the ones that are fired.
513513
</para>
514514

515+
<para>
516+
Creating a row-level trigger on a partitioned table will cause identical
517+
triggers to be created in all its existing partitions; and any partitions
518+
created or attached later will contain an identical trigger, too.
519+
Triggers on partitioned tables may only be <literal>AFTER</literal>.
520+
</para>
521+
515522
<para>
516523
Modifying a partitioned table or a table with inheritance children fires
517524
statement-level triggers attached to the explicitly named table, but not

‎src/backend/catalog/heap.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2121,6 +2121,7 @@ StoreRelCheck(Relation rel, const char *ccname, Node *expr,
21212121
false,/* Is Deferrable */
21222122
false,/* Is Deferred */
21232123
is_validated,
2124+
InvalidOid,/* no parent constraint */
21242125
RelationGetRelid(rel),/* relation */
21252126
attNos,/* attrs in the constraint */
21262127
keycount,/* # attrs in the constraint */

‎src/backend/catalog/index.c

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1282,6 +1282,7 @@ index_constraint_create(Relation heapRelation,
12821282
deferrable,
12831283
initdeferred,
12841284
true,
1285+
parentConstraintId,
12851286
RelationGetRelid(heapRelation),
12861287
indexInfo->ii_KeyAttrNumbers,
12871288
indexInfo->ii_NumIndexAttrs,
@@ -1360,7 +1361,8 @@ index_constraint_create(Relation heapRelation,
13601361
trigger->constrrel=NULL;
13611362

13621363
(void)CreateTrigger(trigger,NULL,RelationGetRelid(heapRelation),
1363-
InvalidOid,conOid,indexRelationId, true);
1364+
InvalidOid,conOid,indexRelationId,InvalidOid,
1365+
InvalidOid,NULL, true, false);
13641366
}
13651367

13661368
/*

‎src/backend/catalog/pg_constraint.c

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -52,6 +52,7 @@ CreateConstraintEntry(const char *constraintName,
5252
boolisDeferrable,
5353
boolisDeferred,
5454
boolisValidated,
55+
OidparentConstrId,
5556
OidrelId,
5657
constint16*constraintKey,
5758
intconstraintNKeys,
@@ -170,6 +171,7 @@ CreateConstraintEntry(const char *constraintName,
170171
values[Anum_pg_constraint_conrelid-1]=ObjectIdGetDatum(relId);
171172
values[Anum_pg_constraint_contypid-1]=ObjectIdGetDatum(domainId);
172173
values[Anum_pg_constraint_conindid-1]=ObjectIdGetDatum(indexRelId);
174+
values[Anum_pg_constraint_conparentid-1]=ObjectIdGetDatum(parentConstrId);
173175
values[Anum_pg_constraint_confrelid-1]=ObjectIdGetDatum(foreignRelId);
174176
values[Anum_pg_constraint_confupdtype-1]=CharGetDatum(foreignUpdateType);
175177
values[Anum_pg_constraint_confdeltype-1]=CharGetDatum(foreignDeleteType);
@@ -772,6 +774,7 @@ ConstraintSetParentConstraint(Oid childConstrId, Oid parentConstrId)
772774
constrForm= (Form_pg_constraint)GETSTRUCT(newtup);
773775
constrForm->conislocal= false;
774776
constrForm->coninhcount++;
777+
constrForm->conparentid=parentConstrId;
775778
CatalogTupleUpdate(constrRel,&tuple->t_self,newtup);
776779
ReleaseSysCache(tuple);
777780

‎src/backend/commands/tablecmds.c

Lines changed: 143 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -487,6 +487,7 @@ static void ValidatePartitionConstraints(List **wqueue, Relation scanrel,
487487
List*scanrel_children,
488488
List*partConstraint,
489489
boolvalidate_default);
490+
staticvoidCloneRowTriggersToPartition(Relationparent,Relationpartition);
490491
staticObjectAddressATExecDetachPartition(Relationrel,RangeVar*name);
491492
staticObjectAddressATExecAttachPartitionIdx(List**wqueue,Relationrel,
492493
RangeVar*name);
@@ -906,9 +907,11 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
906907
}
907908

908909
/*
909-
* If we're creating a partition, create now all the indexes defined in
910-
* the parent. We can't do it earlier, because DefineIndex wants to know
911-
* the partition key which we just stored.
910+
* If we're creating a partition, create now all the indexes and triggers
911+
* defined in the parent.
912+
*
913+
* We can't do it earlier, because DefineIndex wants to know the partition
914+
* key which we just stored.
912915
*/
913916
if (stmt->partbound)
914917
{
@@ -949,6 +952,14 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
949952
}
950953

951954
list_free(idxlist);
955+
956+
/*
957+
* If there are any row-level triggers, clone them to the new
958+
* partition.
959+
*/
960+
if (parent->trigdesc!=NULL)
961+
CloneRowTriggersToPartition(parent,rel);
962+
952963
heap_close(parent,NoLock);
953964
}
954965

@@ -7491,6 +7502,7 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
74917502
fkconstraint->deferrable,
74927503
fkconstraint->initdeferred,
74937504
fkconstraint->initially_valid,
7505+
InvalidOid,/* no parent constraint */
74947506
RelationGetRelid(rel),
74957507
fkattnum,
74967508
numfks,
@@ -8445,7 +8457,7 @@ CreateFKCheckTrigger(Oid myRelOid, Oid refRelOid, Constraint *fkconstraint,
84458457
fk_trigger->args=NIL;
84468458

84478459
(void)CreateTrigger(fk_trigger,NULL,myRelOid,refRelOid,constraintOid,
8448-
indexOid, true);
8460+
indexOid,InvalidOid,InvalidOid,NULL,true, false);
84498461

84508462
/* Make changes-so-far visible */
84518463
CommandCounterIncrement();
@@ -8519,7 +8531,7 @@ createForeignKeyTriggers(Relation rel, Oid refRelOid, Constraint *fkconstraint,
85198531
fk_trigger->args=NIL;
85208532

85218533
(void)CreateTrigger(fk_trigger,NULL,refRelOid,myRelOid,constraintOid,
8522-
indexOid, true);
8534+
indexOid,InvalidOid,InvalidOid,NULL,true, false);
85238535

85248536
/* Make changes-so-far visible */
85258537
CommandCounterIncrement();
@@ -8574,7 +8586,7 @@ createForeignKeyTriggers(Relation rel, Oid refRelOid, Constraint *fkconstraint,
85748586
fk_trigger->args=NIL;
85758587

85768588
(void)CreateTrigger(fk_trigger,NULL,refRelOid,myRelOid,constraintOid,
8577-
indexOid, true);
8589+
indexOid,InvalidOid,InvalidOid,NULL,true, false);
85788590

85798591
/* Make changes-so-far visible */
85808592
CommandCounterIncrement();
@@ -11114,7 +11126,7 @@ static void
1111411126
ATExecEnableDisableTrigger(Relationrel,constchar*trigname,
1111511127
charfires_when,boolskip_system,LOCKMODElockmode)
1111611128
{
11117-
EnableDisableTrigger(rel,trigname,fires_when,skip_system);
11129+
EnableDisableTrigger(rel,trigname,fires_when,skip_system,lockmode);
1111811130
}
1111911131

1112011132
/*
@@ -14031,6 +14043,9 @@ ATExecAttachPartition(List **wqueue, Relation rel, PartitionCmd *cmd)
1403114043
/* Ensure there exists a correct set of indexes in the partition. */
1403214044
AttachPartitionEnsureIndexes(rel,attachrel);
1403314045

14046+
/* and triggers */
14047+
CloneRowTriggersToPartition(rel,attachrel);
14048+
1403414049
/*
1403514050
* Generate partition constraint from the partition bound specification.
1403614051
* If the parent itself is a partition, make sure to include its
@@ -14254,6 +14269,127 @@ AttachPartitionEnsureIndexes(Relation rel, Relation attachrel)
1425414269
MemoryContextDelete(cxt);
1425514270
}
1425614271

14272+
/*
14273+
* CloneRowTriggersToPartition
14274+
*subroutine for ATExecAttachPartition/DefineRelation to create row
14275+
*triggers on partitions
14276+
*/
14277+
staticvoid
14278+
CloneRowTriggersToPartition(Relationparent,Relationpartition)
14279+
{
14280+
Relationpg_trigger;
14281+
ScanKeyDatakey;
14282+
SysScanDescscan;
14283+
HeapTupletuple;
14284+
MemoryContextoldcxt,
14285+
perTupCxt;
14286+
14287+
ScanKeyInit(&key,Anum_pg_trigger_tgrelid,BTEqualStrategyNumber,
14288+
F_OIDEQ,ObjectIdGetDatum(RelationGetRelid(parent)));
14289+
pg_trigger=heap_open(TriggerRelationId,RowExclusiveLock);
14290+
scan=systable_beginscan(pg_trigger,TriggerRelidNameIndexId,
14291+
true,NULL,1,&key);
14292+
14293+
perTupCxt=AllocSetContextCreate(CurrentMemoryContext,
14294+
"clone trig",ALLOCSET_SMALL_SIZES);
14295+
oldcxt=MemoryContextSwitchTo(perTupCxt);
14296+
14297+
while (HeapTupleIsValid(tuple=systable_getnext(scan)))
14298+
{
14299+
Form_pg_triggertrigForm;
14300+
CreateTrigStmt*trigStmt;
14301+
Node*qual=NULL;
14302+
Datumvalue;
14303+
boolisnull;
14304+
List*cols=NIL;
14305+
14306+
trigForm= (Form_pg_trigger)GETSTRUCT(tuple);
14307+
14308+
/*
14309+
* Ignore statement-level triggers; those are not cloned.
14310+
*/
14311+
if (!TRIGGER_FOR_ROW(trigForm->tgtype))
14312+
continue;
14313+
14314+
/*
14315+
* Complain if we find an unexpected trigger type.
14316+
*/
14317+
if (!TRIGGER_FOR_AFTER(trigForm->tgtype))
14318+
elog(ERROR,"unexpected trigger \"%s\" found",
14319+
NameStr(trigForm->tgname));
14320+
14321+
/*
14322+
* If there is a WHEN clause, generate a 'cooked' version of it that's
14323+
* appropriate for the partition.
14324+
*/
14325+
value=heap_getattr(tuple,Anum_pg_trigger_tgqual,
14326+
RelationGetDescr(pg_trigger),&isnull);
14327+
if (!isnull)
14328+
{
14329+
boolfound_whole_row;
14330+
14331+
qual=stringToNode(TextDatumGetCString(value));
14332+
qual= (Node*)map_partition_varattnos((List*)qual,PRS2_OLD_VARNO,
14333+
partition,parent,
14334+
&found_whole_row);
14335+
if (found_whole_row)
14336+
elog(ERROR,"unexpected whole-row reference found in trigger WHEN clause");
14337+
qual= (Node*)map_partition_varattnos((List*)qual,PRS2_NEW_VARNO,
14338+
partition,parent,
14339+
&found_whole_row);
14340+
if (found_whole_row)
14341+
elog(ERROR,"unexpected whole-row reference found in trigger WHEN clause");
14342+
}
14343+
14344+
/*
14345+
* If there is a column list, transform it to a list of column names.
14346+
* Note we don't need to map this list in any way ...
14347+
*/
14348+
if (trigForm->tgattr.dim1>0)
14349+
{
14350+
inti;
14351+
14352+
for (i=0;i<trigForm->tgattr.dim1;i++)
14353+
{
14354+
Form_pg_attributecol;
14355+
14356+
col=TupleDescAttr(parent->rd_att,
14357+
trigForm->tgattr.values[i]-1);
14358+
cols=lappend(cols,makeString(NameStr(col->attname)));
14359+
}
14360+
}
14361+
14362+
trigStmt=makeNode(CreateTrigStmt);
14363+
trigStmt->trigname=NameStr(trigForm->tgname);
14364+
trigStmt->relation=NULL;
14365+
trigStmt->funcname=NULL;/* passed separately */
14366+
trigStmt->args=NULL;/* passed separately */
14367+
trigStmt->row= true;
14368+
trigStmt->timing=trigForm->tgtype&TRIGGER_TYPE_TIMING_MASK;
14369+
trigStmt->events=trigForm->tgtype&TRIGGER_TYPE_EVENT_MASK;
14370+
trigStmt->columns=cols;
14371+
trigStmt->whenClause=NULL;/* passed separately */
14372+
trigStmt->isconstraint=OidIsValid(trigForm->tgconstraint);
14373+
trigStmt->transitionRels=NIL;/* not supported at present */
14374+
trigStmt->deferrable=trigForm->tgdeferrable;
14375+
trigStmt->initdeferred=trigForm->tginitdeferred;
14376+
trigStmt->constrrel=NULL;/* passed separately */
14377+
14378+
CreateTrigger(trigStmt,NULL,RelationGetRelid(partition),
14379+
trigForm->tgconstrrelid,InvalidOid,InvalidOid,
14380+
trigForm->tgfoid,HeapTupleGetOid(tuple),qual,
14381+
false, true);
14382+
14383+
MemoryContextReset(perTupCxt);
14384+
}
14385+
14386+
MemoryContextSwitchTo(oldcxt);
14387+
MemoryContextDelete(perTupCxt);
14388+
14389+
systable_endscan(scan);
14390+
heap_close(pg_trigger,RowExclusiveLock);
14391+
}
14392+
1425714393
/*
1425814394
* ALTER TABLE DETACH PARTITION
1425914395
*

0 commit comments

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