From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Cc: Simon Riggs <simon@2ndquadrant.com>
Subject: Re: FOR EACH ROW triggers on partitioned tables
Date: Thu, 22 Feb 2018 20:52:12 -0300
Message-ID: <20180222235212.qcdznloyxepn2be3@alvherre.pgsql> (raw)
In-Reply-To: <20180214212624.hm7of76flesodamf@alvherre.pgsql>
Alvaro Herrera wrote:
> Another option is to rethink this feature from the ground up: instead of
> cloning catalog rows for each children, maybe we should have the trigger
> lookup code, when running DML on the child relation (the partition),
> obtain trigger entries not only for the child relation itself but also
> for its parents recursively -- so triggers defined in the parent are
> fired for the partitions, too.
I have written this, and it seems to work fine; it's attached.
Generally speaking, I like this better than my previously proposed
patch: having duplicate pg_trigger rows seems lame, in hindsight.
I haven't measured the performance loss, but we now scan pg_inherits
each time we build a relcache entry and relhastriggers=on. Can't be
good. In general, the pg_inherits stuff looks generally unnatural --
manually doing scans upwards (find parents) and downwards (find
partitions). It's messy and there are no nice abstractions.
Partitioning looks too much bolted-on still.
We could mitigate the performance loss to some extent by adding more to
RelationData. For example, a "is_partition" boolean would help: skip
searching pg_inherits for a relation that is not a partition. The
indexing patch already added some "has_superclass()" calls and they look
somewhat out of place. Also, we could add a syscache to pg_inherits.
Regarding making partitioning feel more natural, we could add some API
"list all ancestors", "list all descendents". Maybe I should have used
find_inheritance_children.
Some cutesy: scanning multiple parents looking for potential triggers
means the order of indexscan results no longer guarantees the correct
ordering. I had to add a qsort() there.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgmlindex 71e20f2740..1c92212dd6 100644--- a/doc/src/sgml/catalogs.sgml+++ b/doc/src/sgml/catalogs.sgml@@ -1873,7 +1873,9 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
<entry></entry>
<entry>
True if table has (or once had) triggers; see
- <link linkend="catalog-pg-trigger"><structname>pg_trigger</structname></link> catalog+ <link linkend="catalog-pg-trigger"><structname>pg_trigger</structname></link> catalog.+ If this is a partition, triggers on its partitioned ancestors are also+ considered
</entry>
</row>
@@ -6991,6 +6993,13 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
</row>
<row>
+ <entry><structfield>tginherits</structfield></entry>+ <entry><type>bool</type></entry>+ <entry></entry>+ <entry>True if trigger applies to children relations too</entry>+ </row>++ <row>
<entry><structfield>tgnargs</structfield></entry>
<entry><type>int2</type></entry>
<entry></entry>
diff --git a/src/backend/bootstrap/bootparse.y b/src/backend/bootstrap/bootparse.yindex ed7a55596f..7ad0126df5 100644--- a/src/backend/bootstrap/bootparse.y+++ b/src/backend/bootstrap/bootparse.y@@ -230,6 +230,7 @@ Boot_CreateStmt:
RELPERSISTENCE_PERMANENT,
shared_relation,
mapped_relation,
+ false,
true);
elog(DEBUG4, "bootstrap relation created");
}
@@ -252,6 +253,7 @@ Boot_CreateStmt:
mapped_relation,
true,
0,
+ false,
ONCOMMIT_NOOP,
(Datum) 0,
false,
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.cindex cf36ce4add..815f371ac2 100644--- a/src/backend/catalog/heap.c+++ b/src/backend/catalog/heap.c@@ -257,6 +257,7 @@ heap_create(const char *relname,
char relpersistence,
bool shared_relation,
bool mapped_relation,
+ bool has_triggers,
bool allow_system_table_mods)
{
bool create_storage;
@@ -351,7 +352,8 @@ heap_create(const char *relname,
shared_relation,
mapped_relation,
relpersistence,
- relkind);+ relkind,+ has_triggers);
/*
* Have the storage manager create the relation's disk file, if needed.
@@ -1005,6 +1007,7 @@ AddNewRelationType(const char *typeName,
* mapped_relation: true if the relation will use the relfilenode map
* oidislocal: true if oid column (if any) should be marked attislocal
* oidinhcount: attinhcount to assign to oid column (if any)
+ * hastriggers: value to set relhastriggers to
* oncommit: ON COMMIT marking (only relevant if it's a temp table)
* reloptions: reloptions in Datum form, or (Datum) 0 if none
* use_user_acl: true if should look for user-defined default permissions;
@@ -1034,6 +1037,7 @@ heap_create_with_catalog(const char *relname,
bool mapped_relation,
bool oidislocal,
int oidinhcount,
+ bool hastriggers,
OnCommitAction oncommit,
Datum reloptions,
bool use_user_acl,
@@ -1173,6 +1177,7 @@ heap_create_with_catalog(const char *relname,
relpersistence,
shared_relation,
mapped_relation,
+ hastriggers,
allow_system_table_mods);
Assert(relid == RelationGetRelid(new_rel_desc));
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.cindex 564f2069cf..6771b8b01d 100644--- a/src/backend/catalog/index.c+++ b/src/backend/catalog/index.c@@ -903,6 +903,7 @@ index_create(Relation heapRelation,
relpersistence,
shared_relation,
mapped_relation,
+ false,
allow_system_table_mods);
Assert(indexRelationId == RelationGetRelid(indexRelation));
diff --git a/src/backend/catalog/toasting.c b/src/backend/catalog/toasting.cindex 8bf2698545..363f39e7fe 100644--- a/src/backend/catalog/toasting.c+++ b/src/backend/catalog/toasting.c@@ -274,6 +274,7 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid,
mapped_relation,
true,
0,
+ false,
ONCOMMIT_NOOP,
reloptions,
false,
diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.cindex 5d481dd50d..cd78f12f19 100644--- a/src/backend/commands/cluster.c+++ b/src/backend/commands/cluster.c@@ -687,6 +687,7 @@ make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, char relpersistence,
RelationIsMapped(OldHeap),
true,
0,
+ OldHeap->rd_rel->relhastriggers, /* XXX why? */
ONCOMMIT_NOOP,
reloptions,
false,
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.cindex 74e020bffc..344eecda7a 100644--- a/src/backend/commands/tablecmds.c+++ b/src/backend/commands/tablecmds.c@@ -303,7 +303,7 @@ struct DropRelationCallbackState
static void truncate_check_rel(Relation rel);
static List *MergeAttributes(List *schema, List *supers, char relpersistence,
bool is_partition, List **supOids, List **supconstr,
- int *supOidCount);+ int *supOidCount, bool *hastriggers);
static bool MergeCheckConstraint(List *constraints, char *name, Node *expr);
static void MergeAttributesIntoExisting(Relation child_rel, Relation parent_rel);
static void MergeConstraintsIntoExisting(Relation child_rel, Relation parent_rel);
@@ -527,8 +527,10 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
List *old_constraints;
bool localHasOids;
int parentOidCount;
+ bool hastriggers;
List *rawDefaults;
List *cookedDefaults;
+ bool is_partition;
Datum reloptions;
ListCell *listptr;
AttrNumber attnum;
@@ -559,6 +561,8 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
relkind = RELKIND_PARTITIONED_TABLE;
}
+ is_partition = stmt->partbound != NULL;+
/*
* Look up the namespace in which we are supposed to create the relation,
* check we have permission to create there, lock it against concurrent
@@ -647,8 +651,9 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
stmt->tableElts =
MergeAttributes(stmt->tableElts, stmt->inhRelations,
stmt->relation->relpersistence,
- stmt->partbound != NULL,- &inheritOids, &old_constraints, &parentOidCount);+ is_partition,+ &inheritOids, &old_constraints, &parentOidCount,+ &hastriggers);
/*
* Create a tuple descriptor from the relation schema. Note that this
@@ -675,7 +680,7 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
* If a partitioned table doesn't have the system OID column, then none of
* its partitions should have it.
*/
- if (stmt->partbound && parentOidCount == 0 && localHasOids)+ if (is_partition && parentOidCount == 0 && localHasOids)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
errmsg("cannot create table with OIDs as partition of table without OIDs")));
@@ -759,6 +764,7 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
false,
localHasOids,
parentOidCount,
+ hastriggers,
stmt->oncommit,
reloptions,
true,
@@ -767,7 +773,7 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
typaddress);
/* Store inheritance information for new rel. */
- StoreCatalogInheritance(relationId, inheritOids, stmt->partbound != NULL);+ StoreCatalogInheritance(relationId, inheritOids, is_partition);
/*
* We must bump the command counter to make the newly-created relation
@@ -784,7 +790,7 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
rel = relation_open(relationId, AccessExclusiveLock);
/* Process and store partition bound, if any. */
- if (stmt->partbound)+ if (is_partition)
{
PartitionBoundSpec *bound;
ParseState *pstate;
@@ -920,7 +926,7 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
* the parent. We can't do it earlier, because DefineIndex wants to know
* the partition key which we just stored.
*/
- if (stmt->partbound)+ if (is_partition)
{
Oid parentId = linitial_oid(inheritOids);
Relation parent;
@@ -1692,6 +1698,8 @@ storage_name(char c)
* 'supconstr' receives a list of constraints belonging to the parents,
* updated as necessary to be valid for the child.
* 'supOidCount' is set to the number of parents that have OID columns.
+ * 'hasTriggers' is set to true if any parent has inheritable triggers,+ * false otherwise.
*
* Return value:
* Completed schema list.
@@ -1738,7 +1746,7 @@ storage_name(char c)
static List *
MergeAttributes(List *schema, List *supers, char relpersistence,
bool is_partition, List **supOids, List **supconstr,
- int *supOidCount)+ int *supOidCount, bool *hasTriggers)
{
ListCell *entry;
List *inhSchema = NIL;
@@ -1750,6 +1758,8 @@ MergeAttributes(List *schema, List *supers, char relpersistence,
static Node bogus_marker = {0}; /* marks conflicting defaults */
List *saved_schema = NIL;
+ *hasTriggers = false;+
/*
* Check for and reject tables with too many columns. We perform this
* check relatively early for two reasons: (a) we don't run the risk of
@@ -2147,6 +2157,23 @@ MergeAttributes(List *schema, List *supers, char relpersistence,
pfree(newattno);
/*
+ * If this parent has triggers, and any of them is marked inheritable,+ * set *hastriggers.+ */+ if (relation->rd_rel->relhastriggers &&+ relation->trigdesc != NULL &&+ !*hasTriggers)+ {+ int trg;++ for (trg = 0; trg < relation->trigdesc->numtriggers; trg++)+ {+ if (relation->trigdesc->triggers[trg].tginherits)+ *hasTriggers = true;+ }+ }++ /*
* Close the parent rel, but keep our lock on it until xact commit.
* That will prevent someone else from deleting or ALTERing the parent
* before the child is committed.
@@ -14248,6 +14275,9 @@ AttachPartitionEnsureIndexes(Relation rel, Relation attachrel)
index_close(idxRel, AccessShareLock);
}
+ /* Make this all visible */+ CommandCounterIncrement();+
/* Clean up. */
for (i = 0; i < list_length(attachRelIdxs); i++)
index_close(attachrelIdxRels[i], AccessShareLock);
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.cindex fffc0095a7..23d669cf26 100644--- a/src/backend/commands/trigger.c+++ b/src/backend/commands/trigger.c@@ -24,6 +24,7 @@
#include "catalog/objectaccess.h"
#include "catalog/pg_constraint.h"
#include "catalog/pg_constraint_fn.h"
+#include "catalog/pg_inherits.h"
#include "catalog/pg_inherits_fn.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_trigger.h"
@@ -100,7 +101,17 @@ static void AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
List *recheckIndexes, Bitmapset *modifiedCols,
TransitionCaptureState *transition_capture);
static void AfterTriggerEnlargeQueryState(void);
+static void SendTriggerRelcacheInval(Relation inheritsRel, Relation rel);
static bool before_stmt_triggers_fired(Oid relid, CmdType cmdType);
+static void recursively_update_relhastriggers(Relation pg_class, Oid relid,+ bool recurse);+static void add_triggers_to_array(Relation tgrel, Relation inhrel,+ Oid tgrelid, bool all_triggers,+ Trigger **triggers, int *numtrigs, int *maxtrigs,+ bool *must_sort);+static void add_trigger_to_array(TupleDesc tgdesc, HeapTuple tgtup,+ Trigger **triggers, int *numtrigs, int *maxtrigs);+static int qsort_trigger_cmp(const void *a, const void *b);
/*
@@ -133,6 +144,9 @@ static bool before_stmt_triggers_fired(Oid relid, CmdType cmdType);
* relation, as well as ACL_EXECUTE on the trigger function. For internal
* triggers the caller must apply any required permission checks.
*
+ * When called on partitioned tables, FOR EACH ROW triggers are marked as+ * applying on partitions too (ie. tginherits), except if isInternal.+ *
* Note: can return InvalidObjectAddress if we decided to not create a trigger
* at all, but a foreign-key constraint. This is a kluge for backwards
* compatibility.
@@ -149,6 +163,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
Node *whenClause;
List *whenRtable;
char *qual;
+ bool tginherits;
Datum values[Natts_pg_trigger];
bool nulls[Natts_pg_trigger];
Relation rel;
@@ -179,8 +194,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
* Triggers must be on tables or views, and there are additional
* relation-type-specific restrictions.
*/
- if (rel->rd_rel->relkind == RELKIND_RELATION ||- rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)+ if (rel->rd_rel->relkind == RELKIND_RELATION)
{
/* Tables can't have INSTEAD OF triggers */
if (stmt->timing != TRIGGER_TYPE_BEFORE &&
@@ -190,13 +204,69 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
errmsg("\"%s\" is a table",
RelationGetRelationName(rel)),
errdetail("Tables cannot have INSTEAD OF triggers.")));
- /* Disallow ROW triggers on partitioned tables */- if (stmt->row && rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)+ }+ else if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)+ {+ /* Partitioned tables can't have INSTEAD OF triggers */+ if (stmt->timing != TRIGGER_TYPE_BEFORE &&+ stmt->timing != TRIGGER_TYPE_AFTER)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
- errmsg("\"%s\" is a partitioned table",+ errmsg("\"%s\" is a table",
RelationGetRelationName(rel)),
- errdetail("Partitioned tables cannot have ROW triggers.")));+ errdetail("Tables cannot have INSTEAD OF triggers.")));+ /*+ * FOR EACH ROW triggers have further restrictions+ */+ if (stmt->row)+ {+ /*+ * Disallow WHEN clauses; I think it's okay, but disallow for now+ * to reduce testing surface.+ */+ if (stmt->whenClause)+ ereport(ERROR,+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),+ errmsg("\"%s\" is a partitioned table",+ RelationGetRelationName(rel)),+ errdetail("Triggers FOR EACH ROW on partitioned table cannot have WHEN clauses.")));++ /*+ * BEFORE triggers FOR EACH ROW are forbidden, because they would+ * allow the user to direct the row to another partition, which+ * isn't implemented in the executor.+ */+ if (stmt->timing != TRIGGER_TYPE_AFTER)+ ereport(ERROR,+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),+ errmsg("\"%s\" is a partitioned table",+ RelationGetRelationName(rel)),+ errdetail("Partitioned tables cannot have BEFORE / FOR EACH ROW triggers.")));++ /*+ * Constraint triggers are not allowed, either.+ */+ if (stmt->isconstraint)+ ereport(ERROR,+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),+ errmsg("\"%s\" is a partitioned table",+ RelationGetRelationName(rel)),+ errdetail("Partitioned tables cannot have CONSTRAINT triggers FOR EACH ROW.")));++ /*+ * Disallow use of transition tables. If this partitioned table+ * has any partitions, the error would occur below; but if it+ * doesn't then we would only hit that code when the first CREATE+ * TABLE ... PARTITION OF is executed, which is too late. Check+ * early to avoid the problem.+ */+ if (stmt->transitionRels != NIL)+ ereport(ERROR,+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),+ errmsg("\"%s\" is a partitioned table",+ RelationGetRelationName(rel)),+ errdetail("Triggers on partitioned tables cannot have transition tables.")));+ }
}
else if (rel->rd_rel->relkind == RELKIND_VIEW)
{
@@ -676,6 +746,12 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
}
/*
+ * FOR EACH ROW triggers in partitioned tables are marked inheritable.+ */+ tginherits = (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE &&+ TRIGGER_FOR_ROW(tgtype));++ /*
* Generate the trigger's OID now, so that we can use it in the name if
* needed.
*/
@@ -748,6 +824,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
values[Anum_pg_trigger_tgconstraint - 1] = ObjectIdGetDatum(constraintOid);
values[Anum_pg_trigger_tgdeferrable - 1] = BoolGetDatum(stmt->deferrable);
values[Anum_pg_trigger_tginitdeferred - 1] = BoolGetDatum(stmt->initdeferred);
+ values[Anum_pg_trigger_tginherits - 1] = BoolGetDatum(tginherits);
if (stmt->args)
{
@@ -872,22 +949,15 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
pfree(DatumGetPointer(values[Anum_pg_trigger_tgnewtable - 1]));
/*
- * Update relation's pg_class entry. Crucial side-effect: other backends+ * Update relation's pg_class entry -- and that of each child relation,+ * if the trigger is inheritable. Crucial side-effect: other backends
* (and this one too!) are sent SI message to make them rebuild relcache
* entries.
*/
pgrel = heap_open(RelationRelationId, RowExclusiveLock);
- tuple = SearchSysCacheCopy1(RELOID,- ObjectIdGetDatum(RelationGetRelid(rel)));- if (!HeapTupleIsValid(tuple))- elog(ERROR, "cache lookup failed for relation %u",- RelationGetRelid(rel));- ((Form_pg_class) GETSTRUCT(tuple))->relhastriggers = true;+ recursively_update_relhastriggers(pgrel, RelationGetRelid(rel), tginherits);- CatalogTupleUpdate(pgrel, &tuple->t_self, tuple);-- heap_freetuple(tuple);
heap_close(pgrel, RowExclusiveLock);
/*
@@ -933,6 +1003,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
referenced.objectId = RelationGetRelid(rel);
referenced.objectSubId = 0;
recordDependencyOn(&myself, &referenced, DEPENDENCY_AUTO);
+
if (OidIsValid(constrrelid))
{
referenced.classId = RelationRelationId;
@@ -988,7 +1059,6 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
return myself;
}
-
/*
* Convert legacy (pre-7.3) CREATE CONSTRAINT TRIGGER commands into
* full-fledged foreign key constraints.
@@ -1337,7 +1407,7 @@ RemoveTriggerById(Oid trigOid)
* There's no great harm in leaving relhastriggers true even if there are
* no triggers left.
*/
- CacheInvalidateRelcache(rel);+ SendTriggerRelcacheInval(NULL, rel);
/* Keep lock on trigger's rel until end of xact */
heap_close(rel, NoLock);
@@ -1535,7 +1605,7 @@ renametrig(RenameStmt *stmt)
* this one too!) are sent SI message to make them rebuild relcache
* entries. (Ideally this should happen automatically...)
*/
- CacheInvalidateRelcache(targetrel);+ SendTriggerRelcacheInval(NULL, targetrel);
}
else
{
@@ -1559,7 +1629,6 @@ renametrig(RenameStmt *stmt)
return address;
}
-
/*
* EnableDisableTrigger()
*
@@ -1665,10 +1734,9 @@ EnableDisableTrigger(Relation rel, const char *tgname,
* Otherwise they will fail to apply the change promptly.
*/
if (changed)
- CacheInvalidateRelcache(rel);+ SendTriggerRelcacheInval(NULL, rel);
}
-
/*
* Build trigger data to attach to the given relcache entry.
*
@@ -1687,11 +1755,10 @@ RelationBuildTriggers(Relation relation)
int maxtrigs;
Trigger *triggers;
Relation tgrel;
- ScanKeyData skey;- SysScanDesc tgscan;- HeapTuple htup;+ Relation inhrel;
MemoryContext oldContext;
int i;
+ bool must_sort = false;
/*
* Allocate a working array to hold the triggers (the array is extended if
@@ -1701,108 +1768,14 @@ RelationBuildTriggers(Relation relation)
triggers = (Trigger *) palloc(maxtrigs * sizeof(Trigger));
numtrigs = 0;
- /*- * Note: since we scan the triggers using TriggerRelidNameIndexId, we will- * be reading the triggers in name order, except possibly during- * emergency-recovery operations (ie, IgnoreSystemIndexes). This in turn- * ensures that triggers will be fired in name order.- */- ScanKeyInit(&skey,- Anum_pg_trigger_tgrelid,- BTEqualStrategyNumber, F_OIDEQ,- ObjectIdGetDatum(RelationGetRelid(relation)));-
tgrel = heap_open(TriggerRelationId, AccessShareLock);
- tgscan = systable_beginscan(tgrel, TriggerRelidNameIndexId, true,- NULL, 1, &skey);+ inhrel = heap_open(InheritsRelationId, AccessShareLock);- while (HeapTupleIsValid(htup = systable_getnext(tgscan)))- {- Form_pg_trigger pg_trigger = (Form_pg_trigger) GETSTRUCT(htup);- Trigger *build;- Datum datum;- bool isnull;+ add_triggers_to_array(tgrel, inhrel, RelationGetRelid(relation),+ true, &triggers, &numtrigs, &maxtrigs, &must_sort);- if (numtrigs >= maxtrigs)- {- maxtrigs *= 2;- triggers = (Trigger *) repalloc(triggers, maxtrigs * sizeof(Trigger));- }- build = &(triggers[numtrigs]);-- build->tgoid = HeapTupleGetOid(htup);- build->tgname = DatumGetCString(DirectFunctionCall1(nameout,- NameGetDatum(&pg_trigger->tgname)));- build->tgfoid = pg_trigger->tgfoid;- build->tgtype = pg_trigger->tgtype;- build->tgenabled = pg_trigger->tgenabled;- build->tgisinternal = pg_trigger->tgisinternal;- build->tgconstrrelid = pg_trigger->tgconstrrelid;- build->tgconstrindid = pg_trigger->tgconstrindid;- build->tgconstraint = pg_trigger->tgconstraint;- build->tgdeferrable = pg_trigger->tgdeferrable;- build->tginitdeferred = pg_trigger->tginitdeferred;- build->tgnargs = pg_trigger->tgnargs;- /* tgattr is first var-width field, so OK to access directly */- build->tgnattr = pg_trigger->tgattr.dim1;- if (build->tgnattr > 0)- {- build->tgattr = (int16 *) palloc(build->tgnattr * sizeof(int16));- memcpy(build->tgattr, &(pg_trigger->tgattr.values),- build->tgnattr * sizeof(int16));- }- else- build->tgattr = NULL;- if (build->tgnargs > 0)- {- bytea *val;- char *p;-- val = DatumGetByteaPP(fastgetattr(htup,- Anum_pg_trigger_tgargs,- tgrel->rd_att, &isnull));- if (isnull)- elog(ERROR, "tgargs is null in trigger for relation \"%s\"",- RelationGetRelationName(relation));- p = (char *) VARDATA_ANY(val);- build->tgargs = (char **) palloc(build->tgnargs * sizeof(char *));- for (i = 0; i < build->tgnargs; i++)- {- build->tgargs[i] = pstrdup(p);- p += strlen(p) + 1;- }- }- else- build->tgargs = NULL;-- datum = fastgetattr(htup, Anum_pg_trigger_tgoldtable,- tgrel->rd_att, &isnull);- if (!isnull)- build->tgoldtable =- DatumGetCString(DirectFunctionCall1(nameout, datum));- else- build->tgoldtable = NULL;-- datum = fastgetattr(htup, Anum_pg_trigger_tgnewtable,- tgrel->rd_att, &isnull);- if (!isnull)- build->tgnewtable =- DatumGetCString(DirectFunctionCall1(nameout, datum));- else- build->tgnewtable = NULL;-- datum = fastgetattr(htup, Anum_pg_trigger_tgqual,- tgrel->rd_att, &isnull);- if (!isnull)- build->tgqual = TextDatumGetCString(datum);- else- build->tgqual = NULL;-- numtrigs++;- }-- systable_endscan(tgscan);
heap_close(tgrel, AccessShareLock);
+ heap_close(inhrel, AccessShareLock);
/* There might not be any triggers */
if (numtrigs == 0)
@@ -1811,6 +1784,10 @@ RelationBuildTriggers(Relation relation)
return;
}
+ /* apply a final sort step, if needed */+ if (must_sort)+ qsort(triggers, numtrigs, sizeof(Trigger), qsort_trigger_cmp);+
/* Build trigdesc */
trigdesc = (TriggerDesc *) palloc0(sizeof(TriggerDesc));
trigdesc->triggers = triggers;
@@ -1827,6 +1804,7 @@ RelationBuildTriggers(Relation relation)
FreeTriggerDesc(trigdesc);
}
+
/*
* Update the TriggerDesc's hint flags to include the specified trigger
*/
@@ -5742,6 +5720,51 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
}
/*
+ * SendTriggerRelcacheInvals+ * Send inval signals for this rel and all its descendants.+ */+static void+SendTriggerRelcacheInval(Relation inheritsRel, Relation rel)+{+ ScanKeyData skey;+ bool opened = false;+ SysScanDesc scan;+ HeapTuple inhtup;++ CacheInvalidateRelcache(rel);++ if (!rel->rd_rel->relhassubclass)+ return;++ if (inheritsRel == NULL)+ {+ inheritsRel = heap_open(InheritsRelationId, AccessShareLock);+ opened = true;+ }++ ScanKeyInit(&skey,+ Anum_pg_inherits_inhparent,+ BTEqualStrategyNumber, F_OIDEQ,+ ObjectIdGetDatum(RelationGetRelid(rel)));+ scan = systable_beginscan(inheritsRel, InheritsParentIndexId, true, NULL,+ 1, &skey);+ while ((inhtup = systable_getnext(scan)) != NULL)+ {+ Oid child = ((Form_pg_inherits) GETSTRUCT(inhtup))->inhrelid;+ Relation childrel;++ childrel = heap_open(child, AccessShareLock);+ SendTriggerRelcacheInval(inheritsRel, childrel);+ heap_close(childrel, AccessShareLock);+ }++ systable_endscan(scan);++ if (opened)+ heap_close(inheritsRel, AccessShareLock);+}++/*
* Detect whether we already queued BEFORE STATEMENT triggers for the given
* relation + operation, and set the flag so the next call will report "true".
*/
@@ -5864,6 +5887,259 @@ done:
}
/*
+ * Update the relhastriggers for the relation with 'relid'; and there are any+ * descendents, recurse to update it on those too, if the 'recurse' flag is+ * true.+ */+static void+recursively_update_relhastriggers(Relation pg_class, Oid relid, bool recurse)+{+ HeapTuple classTup;+ Form_pg_class classForm;++ classTup = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(relid));+ if (!HeapTupleIsValid(classTup))+ elog(ERROR, "cache lookup failed for relation %u", relid);+ classForm = (Form_pg_class) GETSTRUCT(classTup);++ /*+ * Update the relation's relhastriggers flag, if necessary. If we don't+ * do it, make sure to send an inval message anyway.+ */+ if (!classForm->relhastriggers)+ {+ classForm->relhastriggers = true;+ CatalogTupleUpdate(pg_class, &classTup->t_self, classTup);+ }+ else+ CacheInvalidateRelcacheByTuple(classTup);++ /*+ * Recurse to update the children flag, if there are any.+ *+ * You may be tempted to merge this with the above, thinking that if the+ * parent already had relhastriggers then children must be OK too -- but+ * that is wrong if the parent already had a non-inheritable trigger and+ * now has an inheritable one.+ */+ if (recurse && classForm->relhassubclass)+ {+ ScanKeyData key;+ Relation inhRel;+ SysScanDesc scan;+ HeapTuple inhTup;++ inhRel = heap_open(InheritsRelationId, AccessShareLock);+ ScanKeyInit(&key,+ Anum_pg_inherits_inhparent,+ BTEqualStrategyNumber, F_OIDEQ,+ ObjectIdGetDatum(relid));+ scan = systable_beginscan(inhRel, InheritsParentIndexId, true,+ NULL, 1, &key);++ while (HeapTupleIsValid(inhTup = systable_getnext(scan)))+ {+ Form_pg_inherits inhForm = (Form_pg_inherits) GETSTRUCT(inhTup);++ recursively_update_relhastriggers(pg_class, inhForm->inhrelid, recurse);+ }+ systable_endscan(scan);+ heap_close(inhRel, AccessShareLock);+ }++ heap_freetuple(classTup);+}++/*+ * Search for triggers on the relation with oid 'tgrelid', and add any that are+ * found to the array 'triggers' ('numtrigs' is the number of used elements,+ * 'maxtrigs' the number of allocated elements). If 'all_triggers' is false,+ * only consider triggers that have tginherits true; otherwise include all+ * triggers.+ *+ * For each parent of this relation, recurse to do the same, passing+ * 'all_triggers' false.+ *+ * 'tgrel' is the pg_triggers relation.+ *+ * 'must_sort' is set to true if the order of the triggers in the output array+ * is not guaranteed; caller must sort afterwards in that case.+ */+static void+add_triggers_to_array(Relation tgrel, Relation inhrel,+ Oid tgrelid, bool all_triggers,+ Trigger **triggers, int *numtrigs, int *maxtrigs,+ bool *must_sort)+{+ SysScanDesc scan;+ ScanKeyData skey;+ HeapTuple tgtup;+ HeapTuple inhtup;++ /*+ * Note: since we scan the triggers using TriggerRelidNameIndexId, we+ * will be reading the triggers in name order, except possibly during+ * emergency-recovery operations (ie, IgnoreSystemIndexes). This in+ * turn ensures that triggers will be fired in name order. However,+ * when searching for triggers in parent relations, the order is no+ * longer guaranteed (since multiple scans are involved), so we signal+ * our caller to sort afterwards.+ */++ /*+ * Scan pg_trigger for the given relation, appending relevant triggers+ * to our output array.+ */+ ScanKeyInit(&skey,+ Anum_pg_trigger_tgrelid,+ BTEqualStrategyNumber, F_OIDEQ,+ ObjectIdGetDatum(tgrelid));+ scan = systable_beginscan(tgrel, TriggerRelidNameIndexId, true,+ NULL, 1, &skey);+ while (HeapTupleIsValid(tgtup = systable_getnext(scan)))+ {+ Form_pg_trigger trig = (Form_pg_trigger) GETSTRUCT(tgtup);++ /*+ * triggers in ancestor rels are not considered unless they are marked+ * tginherits.+ */+ if (!all_triggers && !trig->tginherits)+ continue;++ add_trigger_to_array(RelationGetDescr(tgrel), tgtup,+ triggers, numtrigs, maxtrigs);+ }+ systable_endscan(scan);++ /*+ * Now scan pg_inherits to find parents of this relation and recurse, in+ * case any inheritable triggers are present there.+ */+ ScanKeyInit(&skey,+ Anum_pg_inherits_inhrelid,+ BTEqualStrategyNumber, F_OIDEQ,+ ObjectIdGetDatum(tgrelid));+ scan = systable_beginscan(inhrel, InheritsRelidSeqnoIndexId, true, NULL,+ 1, &skey);+ while ((inhtup = systable_getnext(scan)) != NULL)+ {+ Oid ancestor = ((Form_pg_inherits) GETSTRUCT(inhtup))->inhparent;+ int local_numtrigs = *numtrigs;++ /* recurse to this parent */+ add_triggers_to_array(tgrel, inhrel, ancestor, false,+ triggers, numtrigs, maxtrigs, must_sort);++ /* if any triggers were added, set the sort flag */+ if (*numtrigs > local_numtrigs)+ *must_sort = true;+ }++ systable_endscan(scan);+}++/*+ * Subroutine for add_triggers_to_array to add a single trigger to the array.+ */+static void+add_trigger_to_array(TupleDesc tgdesc, HeapTuple htup,+ Trigger **triggers, int *numtrigs, int *maxtrigs)+{+ Form_pg_trigger pg_trigger = (Form_pg_trigger) GETSTRUCT(htup);+ Trigger *build;+ Datum datum;+ bool isnull;++ if (*numtrigs >= *maxtrigs)+ {+ *maxtrigs *= 2;+ *triggers = (Trigger *) repalloc(*triggers, *maxtrigs * sizeof(Trigger));+ }+ build = *triggers + *numtrigs;++ build->tgoid = HeapTupleGetOid(htup);+ build->tgname = DatumGetCString(DirectFunctionCall1(nameout,+ NameGetDatum(&pg_trigger->tgname)));+ build->tgfoid = pg_trigger->tgfoid;+ build->tgtype = pg_trigger->tgtype;+ build->tgenabled = pg_trigger->tgenabled;+ build->tgisinternal = pg_trigger->tgisinternal;+ build->tginherits = pg_trigger->tginherits;+ build->tgconstrrelid = pg_trigger->tgconstrrelid;+ build->tgconstrindid = pg_trigger->tgconstrindid;+ build->tgconstraint = pg_trigger->tgconstraint;+ build->tgdeferrable = pg_trigger->tgdeferrable;+ build->tginitdeferred = pg_trigger->tginitdeferred;+ build->tgnargs = pg_trigger->tgnargs;+ /* tgattr is first var-width field, so OK to access directly */+ build->tgnattr = pg_trigger->tgattr.dim1;+ if (build->tgnattr > 0)+ {+ build->tgattr = (int16 *) palloc(build->tgnattr * sizeof(int16));+ memcpy(build->tgattr, &(pg_trigger->tgattr.values),+ build->tgnattr * sizeof(int16));+ }+ else+ build->tgattr = NULL;+ if (build->tgnargs > 0)+ {+ bytea *val;+ char *p;+ int i;++ val = DatumGetByteaPP(fastgetattr(htup,+ Anum_pg_trigger_tgargs,+ tgdesc, &isnull));+ if (isnull)+ elog(ERROR, "tgargs is null in trigger \"%u\"",+ HeapTupleGetOid(htup));+ p = (char *) VARDATA_ANY(val);+ build->tgargs = (char **) palloc(build->tgnargs * sizeof(char *));+ for (i = 0; i < build->tgnargs; i++)+ {+ build->tgargs[i] = pstrdup(p);+ p += strlen(p) + 1;+ }+ }+ else+ build->tgargs = NULL;++ datum = fastgetattr(htup, Anum_pg_trigger_tgoldtable,+ tgdesc, &isnull);+ if (!isnull)+ build->tgoldtable =+ DatumGetCString(DirectFunctionCall1(nameout, datum));+ else+ build->tgoldtable = NULL;++ datum = fastgetattr(htup, Anum_pg_trigger_tgnewtable,+ tgdesc, &isnull);+ if (!isnull)+ build->tgnewtable =+ DatumGetCString(DirectFunctionCall1(nameout, datum));+ else+ build->tgnewtable = NULL;++ datum = fastgetattr(htup, Anum_pg_trigger_tgqual,+ tgdesc, &isnull);+ if (!isnull)+ build->tgqual = TextDatumGetCString(datum);+ else+ build->tgqual = NULL;++ (*numtrigs)++;+}++static int+qsort_trigger_cmp(const void *a, const void *b)+{+ const Trigger *ta = (const Trigger *) a;+ const Trigger *tb = (const Trigger *) b;++ return strcmp(ta->tgname, tb->tgname);+}+/*
* SQL function pg_trigger_depth()
*/
Datum
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.cindex 1ebf9c4ed2..0b11a2d6cf 100644--- a/src/backend/utils/cache/relcache.c+++ b/src/backend/utils/cache/relcache.c@@ -3165,7 +3165,8 @@ RelationBuildLocalRelation(const char *relname,
bool shared_relation,
bool mapped_relation,
char relpersistence,
- char relkind)+ char relkind,+ bool has_triggers)
{
Relation rel;
MemoryContext oldcxt;
@@ -3276,6 +3277,7 @@ RelationBuildLocalRelation(const char *relname,
rel->rd_rel->relhasoids = rel->rd_att->tdhasoid;
rel->rd_rel->relnatts = natts;
rel->rd_rel->reltype = InvalidOid;
+ rel->rd_rel->relhastriggers = has_triggers;
/* needed when bootstrapping: */
rel->rd_rel->relowner = BOOTSTRAP_SUPERUSERID;
diff --git a/src/include/catalog/heap.h b/src/include/catalog/heap.hindex 9bdc63ceb5..8961a557f7 100644--- a/src/include/catalog/heap.h+++ b/src/include/catalog/heap.h@@ -47,6 +47,7 @@ extern Relation heap_create(const char *relname,
TupleDesc tupDesc,
char relkind,
char relpersistence,
+ bool has_triggers,
bool shared_relation,
bool mapped_relation,
bool allow_system_table_mods);
@@ -66,6 +67,7 @@ extern Oid heap_create_with_catalog(const char *relname,
bool mapped_relation,
bool oidislocal,
int oidinhcount,
+ bool hastriggers,
OnCommitAction oncommit,
Datum reloptions,
bool use_user_acl,
diff --git a/src/include/catalog/pg_trigger.h b/src/include/catalog/pg_trigger.hindex c80a3aa54d..1f3d1a8ebe 100644--- a/src/include/catalog/pg_trigger.h+++ b/src/include/catalog/pg_trigger.h@@ -48,6 +48,7 @@ CATALOG(pg_trigger,2620)
Oid tgconstraint; /* associated pg_constraint entry, if any */
bool tgdeferrable; /* constraint trigger is deferrable */
bool tginitdeferred; /* constraint trigger is deferred initially */
+ bool tginherits; /* trigger applies to children relations */
int16 tgnargs; /* # of extra arguments in tgargs */
/*
@@ -75,7 +76,7 @@ typedef FormData_pg_trigger *Form_pg_trigger;
* compiler constants for pg_trigger
* ----------------
*/
-#define Natts_pg_trigger 17+#define Natts_pg_trigger 18
#define Anum_pg_trigger_tgrelid 1
#define Anum_pg_trigger_tgname 2
#define Anum_pg_trigger_tgfoid 3
@@ -87,12 +88,13 @@ typedef FormData_pg_trigger *Form_pg_trigger;
#define Anum_pg_trigger_tgconstraint 9
#define Anum_pg_trigger_tgdeferrable 10
#define Anum_pg_trigger_tginitdeferred 11
-#define Anum_pg_trigger_tgnargs 12-#define Anum_pg_trigger_tgattr 13-#define Anum_pg_trigger_tgargs 14-#define Anum_pg_trigger_tgqual 15-#define Anum_pg_trigger_tgoldtable 16-#define Anum_pg_trigger_tgnewtable 17+#define Anum_pg_trigger_tginherits 12+#define Anum_pg_trigger_tgnargs 13+#define Anum_pg_trigger_tgattr 14+#define Anum_pg_trigger_tgargs 15+#define Anum_pg_trigger_tgqual 16+#define Anum_pg_trigger_tgoldtable 17+#define Anum_pg_trigger_tgnewtable 18
/* Bits within tgtype */
#define TRIGGER_TYPE_ROW (1 << 0)
diff --git a/src/include/utils/relcache.h b/src/include/utils/relcache.hindex 8a546aba28..4ee77bfb3e 100644--- a/src/include/utils/relcache.h+++ b/src/include/utils/relcache.h@@ -103,7 +103,8 @@ extern Relation RelationBuildLocalRelation(const char *relname,
bool shared_relation,
bool mapped_relation,
char relpersistence,
- char relkind);+ char relkind,+ bool has_triggers);
/*
* Routine to manage assignment of new relfilenode to a relation
diff --git a/src/include/utils/reltrigger.h b/src/include/utils/reltrigger.hindex 9b4dc7f810..b1f0354263 100644--- a/src/include/utils/reltrigger.h+++ b/src/include/utils/reltrigger.h@@ -29,6 +29,7 @@ typedef struct Trigger
int16 tgtype;
char tgenabled;
bool tgisinternal;
+ bool tginherits;
Oid tgconstrrelid;
Oid tgconstrindid;
Oid tgconstraint;
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.outindex e7b4b31afc..9bf2dadfd1 100644--- a/src/test/regress/expected/triggers.out+++ b/src/test/regress/expected/triggers.out@@ -1855,7 +1855,66 @@ drop function my_trigger_function();
drop view my_view;
drop table my_table;
--
--- Verify that per-statement triggers are fired for partitioned tables+-- Verify cases that are unsupported with partitioned tables+--+create table parted_trig (a int) partition by list (a);+create function trigger_nothing() returns trigger+ language plpgsql as $$ begin end; $$;+create trigger failed before insert or update or delete on parted_trig+ for each row execute procedure trigger_nothing();+ERROR: "parted_trig" is a partitioned table+DETAIL: Partitioned tables cannot have BEFORE / FOR EACH ROW triggers.+create trigger failed after update on parted_trig+ for each row when (OLD.a <> NEW.a) execute procedure trigger_nothing();+ERROR: "parted_trig" is a partitioned table+DETAIL: Triggers FOR EACH ROW on partitioned table cannot have WHEN clauses.+create trigger failed instead of update on parted_trig+ for each row execute procedure trigger_nothing();+ERROR: "parted_trig" is a table+DETAIL: Tables cannot have INSTEAD OF triggers.+create trigger failed after update on parted_trig+ referencing old table as old_table+ for each statement execute procedure trigger_nothing();+create constraint trigger failed after insert on parted_trig+ for each row execute procedure trigger_nothing();+ERROR: "parted_trig" is a partitioned table+DETAIL: Partitioned tables cannot have CONSTRAINT triggers FOR EACH ROW.+drop table parted_trig;+--+-- Verify trigger creation for partitioned tables, and drop behavior+--+create table trigpart (a int, b int) partition by range (a);+create table trigpart1 partition of trigpart for values from (0) to (1000);+create trigger f after insert on trigpart for each row execute procedure trigger_nothing();+create table trigpart2 partition of trigpart for values from (1000) to (2000);+create table trigpart3 (like trigpart);+alter table trigpart attach partition trigpart3 for values from (2000) to (3000);+select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger+ where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text;+ tgrelid | tgname | tgfoid +----------+--------+-----------------+ trigpart | f | trigger_nothing+(1 row)++drop table trigpart2;+select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger+ where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text;+ tgrelid | tgname | tgfoid +----------+--------+-----------------+ trigpart | f | trigger_nothing+(1 row)++drop trigger f on trigpart; -- ok, all gone+select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger+ where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text;+ tgrelid | tgname | tgfoid +---------+--------+--------+(0 rows)++drop table trigpart;+drop function trigger_nothing();+--+-- Verify that triggers are fired for partitioned tables
--
create table parted_stmt_trig (a int) partition by list (a);
create table parted_stmt_trig1 partition of parted_stmt_trig for values in (1);
@@ -1872,7 +1931,7 @@ create or replace function trigger_notice() returns trigger as $$
return null;
end;
$$ language plpgsql;
--- insert/update/delete statment-level triggers on the parent+-- insert/update/delete statement-level triggers on the parent
create trigger trig_ins_before before insert on parted_stmt_trig
for each statement execute procedure trigger_notice();
create trigger trig_ins_after after insert on parted_stmt_trig
@@ -1885,36 +1944,62 @@ create trigger trig_del_before before delete on parted_stmt_trig
for each statement execute procedure trigger_notice();
create trigger trig_del_after after delete on parted_stmt_trig
for each statement execute procedure trigger_notice();
+-- these cases are disallowed+create trigger trig_ins_before_1 before insert on parted_stmt_trig+ for each row execute procedure trigger_notice();+ERROR: "parted_stmt_trig" is a partitioned table+DETAIL: Partitioned tables cannot have BEFORE / FOR EACH ROW triggers.+create trigger trig_upd_before_1 before update on parted_stmt_trig+ for each row execute procedure trigger_notice();+ERROR: "parted_stmt_trig" is a partitioned table+DETAIL: Partitioned tables cannot have BEFORE / FOR EACH ROW triggers.+create trigger trig_del_before_1 before delete on parted_stmt_trig+ for each row execute procedure trigger_notice();+ERROR: "parted_stmt_trig" is a partitioned table+DETAIL: Partitioned tables cannot have BEFORE / FOR EACH ROW triggers.+-- insert/update/delete row-level triggers on the parent+create trigger trig_ins_after_parent after insert on parted_stmt_trig+ for each row execute procedure trigger_notice();+create trigger trig_upd_after_parent after update on parted_stmt_trig+ for each row execute procedure trigger_notice();+create trigger trig_del_after_parent after delete on parted_stmt_trig+ for each row execute procedure trigger_notice();
-- insert/update/delete row-level triggers on the first partition
-create trigger trig_ins_before before insert on parted_stmt_trig1+create trigger trig_ins_before_child before insert on parted_stmt_trig1
for each row execute procedure trigger_notice();
-create trigger trig_ins_after after insert on parted_stmt_trig1+create trigger trig_ins_after_child after insert on parted_stmt_trig1
for each row execute procedure trigger_notice();
-create trigger trig_upd_before before update on parted_stmt_trig1+create trigger trig_upd_before_child before update on parted_stmt_trig1
for each row execute procedure trigger_notice();
-create trigger trig_upd_after after update on parted_stmt_trig1+create trigger trig_upd_after_child after update on parted_stmt_trig1+ for each row execute procedure trigger_notice();+create trigger trig_del_before_child before delete on parted_stmt_trig1+ for each row execute procedure trigger_notice();+create trigger trig_del_after_child after delete on parted_stmt_trig1
for each row execute procedure trigger_notice();
-- insert/update/delete statement-level triggers on the parent
-create trigger trig_ins_before before insert on parted2_stmt_trig+create trigger trig_ins_before_3 before insert on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_ins_after after insert on parted2_stmt_trig+create trigger trig_ins_after_3 after insert on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_upd_before before update on parted2_stmt_trig+create trigger trig_upd_before_3 before update on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_upd_after after update on parted2_stmt_trig+create trigger trig_upd_after_3 after update on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_del_before before delete on parted2_stmt_trig+create trigger trig_del_before_3 before delete on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_del_after after delete on parted2_stmt_trig+create trigger trig_del_after_3 after delete on parted2_stmt_trig
for each statement execute procedure trigger_notice();
with ins (a) as (
insert into parted2_stmt_trig values (1), (2) returning a
) insert into parted_stmt_trig select a from ins returning tableoid::regclass, a;
NOTICE: trigger trig_ins_before on parted_stmt_trig BEFORE INSERT for STATEMENT
-NOTICE: trigger trig_ins_before on parted2_stmt_trig BEFORE INSERT for STATEMENT-NOTICE: trigger trig_ins_before on parted_stmt_trig1 BEFORE INSERT for ROW-NOTICE: trigger trig_ins_after on parted_stmt_trig1 AFTER INSERT for ROW-NOTICE: trigger trig_ins_after on parted2_stmt_trig AFTER INSERT for STATEMENT+NOTICE: trigger trig_ins_before_3 on parted2_stmt_trig BEFORE INSERT for STATEMENT+NOTICE: trigger trig_ins_before_child on parted_stmt_trig1 BEFORE INSERT for ROW+NOTICE: trigger trig_ins_after_child on parted_stmt_trig1 AFTER INSERT for ROW+NOTICE: trigger trig_ins_after_parent on parted_stmt_trig1 AFTER INSERT for ROW+NOTICE: trigger trig_ins_after_parent on parted_stmt_trig2 AFTER INSERT for ROW+NOTICE: trigger trig_ins_after_3 on parted2_stmt_trig AFTER INSERT for STATEMENT
NOTICE: trigger trig_ins_after on parted_stmt_trig AFTER INSERT for STATEMENT
tableoid | a
-------------------+---
@@ -1926,25 +2011,62 @@ with upd as (
update parted2_stmt_trig set a = a
) update parted_stmt_trig set a = a;
NOTICE: trigger trig_upd_before on parted_stmt_trig BEFORE UPDATE for STATEMENT
-NOTICE: trigger trig_upd_before on parted_stmt_trig1 BEFORE UPDATE for ROW-NOTICE: trigger trig_upd_before on parted2_stmt_trig BEFORE UPDATE for STATEMENT-NOTICE: trigger trig_upd_after on parted_stmt_trig1 AFTER UPDATE for ROW+NOTICE: trigger trig_upd_before_child on parted_stmt_trig1 BEFORE UPDATE for ROW+NOTICE: trigger trig_upd_before_3 on parted2_stmt_trig BEFORE UPDATE for STATEMENT+NOTICE: trigger trig_upd_after_child on parted_stmt_trig1 AFTER UPDATE for ROW+NOTICE: trigger trig_upd_after_parent on parted_stmt_trig1 AFTER UPDATE for ROW+NOTICE: trigger trig_upd_after_parent on parted_stmt_trig2 AFTER UPDATE for ROW
NOTICE: trigger trig_upd_after on parted_stmt_trig AFTER UPDATE for STATEMENT
-NOTICE: trigger trig_upd_after on parted2_stmt_trig AFTER UPDATE for STATEMENT+NOTICE: trigger trig_upd_after_3 on parted2_stmt_trig AFTER UPDATE for STATEMENT
delete from parted_stmt_trig;
NOTICE: trigger trig_del_before on parted_stmt_trig BEFORE DELETE for STATEMENT
+NOTICE: trigger trig_del_before_child on parted_stmt_trig1 BEFORE DELETE for ROW+NOTICE: trigger trig_del_after_parent on parted_stmt_trig2 AFTER DELETE for ROW
NOTICE: trigger trig_del_after on parted_stmt_trig AFTER DELETE for STATEMENT
-- insert via copy on the parent
copy parted_stmt_trig(a) from stdin;
NOTICE: trigger trig_ins_before on parted_stmt_trig BEFORE INSERT for STATEMENT
-NOTICE: trigger trig_ins_before on parted_stmt_trig1 BEFORE INSERT for ROW-NOTICE: trigger trig_ins_after on parted_stmt_trig1 AFTER INSERT for ROW+NOTICE: trigger trig_ins_before_child on parted_stmt_trig1 BEFORE INSERT for ROW+NOTICE: trigger trig_ins_after_child on parted_stmt_trig1 AFTER INSERT for ROW+NOTICE: trigger trig_ins_after_parent on parted_stmt_trig1 AFTER INSERT for ROW+NOTICE: trigger trig_ins_after_parent on parted_stmt_trig2 AFTER INSERT for ROW
NOTICE: trigger trig_ins_after on parted_stmt_trig AFTER INSERT for STATEMENT
-- insert via copy on the first partition
copy parted_stmt_trig1(a) from stdin;
-NOTICE: trigger trig_ins_before on parted_stmt_trig1 BEFORE INSERT for ROW-NOTICE: trigger trig_ins_after on parted_stmt_trig1 AFTER INSERT for ROW+NOTICE: trigger trig_ins_before_child on parted_stmt_trig1 BEFORE INSERT for ROW+NOTICE: trigger trig_ins_after_child on parted_stmt_trig1 AFTER INSERT for ROW+NOTICE: trigger trig_ins_after_parent on parted_stmt_trig1 AFTER INSERT for ROW+-- Disabling a trigger in the parent table should disable children triggers too+alter table parted_stmt_trig disable trigger trig_ins_after_parent;+insert into parted_stmt_trig values (1);+NOTICE: trigger trig_ins_before on parted_stmt_trig BEFORE INSERT for STATEMENT+NOTICE: trigger trig_ins_before_child on parted_stmt_trig1 BEFORE INSERT for ROW+NOTICE: trigger trig_ins_after_child on parted_stmt_trig1 AFTER INSERT for ROW+NOTICE: trigger trig_ins_after on parted_stmt_trig AFTER INSERT for STATEMENT+alter table parted_stmt_trig enable trigger trig_ins_after_parent;+insert into parted_stmt_trig values (1);+NOTICE: trigger trig_ins_before on parted_stmt_trig BEFORE INSERT for STATEMENT+NOTICE: trigger trig_ins_before_child on parted_stmt_trig1 BEFORE INSERT for ROW+NOTICE: trigger trig_ins_after_child on parted_stmt_trig1 AFTER INSERT for ROW+NOTICE: trigger trig_ins_after_parent on parted_stmt_trig1 AFTER INSERT for ROW+NOTICE: trigger trig_ins_after on parted_stmt_trig AFTER INSERT for STATEMENT
drop table parted_stmt_trig, parted2_stmt_trig;
+-- Verify that triggers fire in alphabetical order+create table parted_trig (a int) partition by range (a);+create table parted_trig_1 partition of parted_trig for values from (0) to (1000)+ partition by range (a);+create table parted_trig_1_1 partition of parted_trig_1 for values from (0) to (100);+create trigger zzz after insert on parted_trig for each row execute procedure trigger_notice();+create trigger mmm after insert on parted_trig_1_1 for each row execute procedure trigger_notice();+create trigger aaa after insert on parted_trig_1 for each row execute procedure trigger_notice();+create trigger bbb after insert on parted_trig for each row execute procedure trigger_notice();+create trigger qqq after insert on parted_trig_1_1 for each row execute procedure trigger_notice();+insert into parted_trig values (50);+NOTICE: trigger aaa on parted_trig_1_1 AFTER INSERT for ROW+NOTICE: trigger bbb on parted_trig_1_1 AFTER INSERT for ROW+NOTICE: trigger mmm on parted_trig_1_1 AFTER INSERT for ROW+NOTICE: trigger qqq on parted_trig_1_1 AFTER INSERT for ROW+NOTICE: trigger zzz on parted_trig_1_1 AFTER INSERT for ROW
--
-- Test the interaction between transition tables and both kinds of
-- inheritance. We'll dump the contents of the transition tables in a
diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sqlindex ae8349ccbf..2780918cfe 100644--- a/src/test/regress/sql/triggers.sql+++ b/src/test/regress/sql/triggers.sql@@ -1305,7 +1305,47 @@ drop view my_view;
drop table my_table;
--
--- Verify that per-statement triggers are fired for partitioned tables+-- Verify cases that are unsupported with partitioned tables+--+create table parted_trig (a int) partition by list (a);+create function trigger_nothing() returns trigger+ language plpgsql as $$ begin end; $$;+create trigger failed before insert or update or delete on parted_trig+ for each row execute procedure trigger_nothing();+create trigger failed after update on parted_trig+ for each row when (OLD.a <> NEW.a) execute procedure trigger_nothing();+create trigger failed instead of update on parted_trig+ for each row execute procedure trigger_nothing();+create trigger failed after update on parted_trig+ referencing old table as old_table+ for each statement execute procedure trigger_nothing();+create constraint trigger failed after insert on parted_trig+ for each row execute procedure trigger_nothing();+drop table parted_trig;++--+-- Verify trigger creation for partitioned tables, and drop behavior+--+create table trigpart (a int, b int) partition by range (a);+create table trigpart1 partition of trigpart for values from (0) to (1000);+create trigger f after insert on trigpart for each row execute procedure trigger_nothing();+create table trigpart2 partition of trigpart for values from (1000) to (2000);+create table trigpart3 (like trigpart);+alter table trigpart attach partition trigpart3 for values from (2000) to (3000);+select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger+ where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text;+drop table trigpart2;+select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger+ where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text;+drop trigger f on trigpart; -- ok, all gone+select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger+ where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text;++drop table trigpart;+drop function trigger_nothing();++--+-- Verify that triggers are fired for partitioned tables
--
create table parted_stmt_trig (a int) partition by list (a);
create table parted_stmt_trig1 partition of parted_stmt_trig for values in (1);
@@ -1325,7 +1365,7 @@ create or replace function trigger_notice() returns trigger as $$
end;
$$ language plpgsql;
--- insert/update/delete statment-level triggers on the parent+-- insert/update/delete statement-level triggers on the parent
create trigger trig_ins_before before insert on parted_stmt_trig
for each statement execute procedure trigger_notice();
create trigger trig_ins_after after insert on parted_stmt_trig
@@ -1339,28 +1379,48 @@ create trigger trig_del_before before delete on parted_stmt_trig
create trigger trig_del_after after delete on parted_stmt_trig
for each statement execute procedure trigger_notice();
+-- these cases are disallowed+create trigger trig_ins_before_1 before insert on parted_stmt_trig+ for each row execute procedure trigger_notice();+create trigger trig_upd_before_1 before update on parted_stmt_trig+ for each row execute procedure trigger_notice();+create trigger trig_del_before_1 before delete on parted_stmt_trig+ for each row execute procedure trigger_notice();++-- insert/update/delete row-level triggers on the parent+create trigger trig_ins_after_parent after insert on parted_stmt_trig+ for each row execute procedure trigger_notice();+create trigger trig_upd_after_parent after update on parted_stmt_trig+ for each row execute procedure trigger_notice();+create trigger trig_del_after_parent after delete on parted_stmt_trig+ for each row execute procedure trigger_notice();+
-- insert/update/delete row-level triggers on the first partition
-create trigger trig_ins_before before insert on parted_stmt_trig1+create trigger trig_ins_before_child before insert on parted_stmt_trig1
for each row execute procedure trigger_notice();
-create trigger trig_ins_after after insert on parted_stmt_trig1+create trigger trig_ins_after_child after insert on parted_stmt_trig1
for each row execute procedure trigger_notice();
-create trigger trig_upd_before before update on parted_stmt_trig1+create trigger trig_upd_before_child before update on parted_stmt_trig1
for each row execute procedure trigger_notice();
-create trigger trig_upd_after after update on parted_stmt_trig1+create trigger trig_upd_after_child after update on parted_stmt_trig1+ for each row execute procedure trigger_notice();+create trigger trig_del_before_child before delete on parted_stmt_trig1+ for each row execute procedure trigger_notice();+create trigger trig_del_after_child after delete on parted_stmt_trig1
for each row execute procedure trigger_notice();
-- insert/update/delete statement-level triggers on the parent
-create trigger trig_ins_before before insert on parted2_stmt_trig+create trigger trig_ins_before_3 before insert on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_ins_after after insert on parted2_stmt_trig+create trigger trig_ins_after_3 after insert on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_upd_before before update on parted2_stmt_trig+create trigger trig_upd_before_3 before update on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_upd_after after update on parted2_stmt_trig+create trigger trig_upd_after_3 after update on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_del_before before delete on parted2_stmt_trig+create trigger trig_del_before_3 before delete on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_del_after after delete on parted2_stmt_trig+create trigger trig_del_after_3 after delete on parted2_stmt_trig
for each statement execute procedure trigger_notice();
with ins (a) as (
@@ -1384,8 +1444,26 @@ copy parted_stmt_trig1(a) from stdin;
1
\.
+-- Disabling a trigger in the parent table should disable children triggers too+alter table parted_stmt_trig disable trigger trig_ins_after_parent;+insert into parted_stmt_trig values (1);+alter table parted_stmt_trig enable trigger trig_ins_after_parent;+insert into parted_stmt_trig values (1);+
drop table parted_stmt_trig, parted2_stmt_trig;
+-- Verify that triggers fire in alphabetical order+create table parted_trig (a int) partition by range (a);+create table parted_trig_1 partition of parted_trig for values from (0) to (1000)+ partition by range (a);+create table parted_trig_1_1 partition of parted_trig_1 for values from (0) to (100);+create trigger zzz after insert on parted_trig for each row execute procedure trigger_notice();+create trigger mmm after insert on parted_trig_1_1 for each row execute procedure trigger_notice();+create trigger aaa after insert on parted_trig_1 for each row execute procedure trigger_notice();+create trigger bbb after insert on parted_trig for each row execute procedure trigger_notice();+create trigger qqq after insert on parted_trig_1_1 for each row execute procedure trigger_notice();+insert into parted_trig values (50);+
--
-- Test the interaction between transition tables and both kinds of
-- inheritance. We'll dump the contents of the transition tables in a
Attachments:
[text/plain] v4-0001-Allow-FOR-EACH-ROW-triggers-on-partitioned-tables.patch (57.1K, ../20180222235212.qcdznloyxepn2be3@alvherre.pgsql/2-v4-0001-Allow-FOR-EACH-ROW-triggers-on-partitioned-tables.patch)
download | inline diff:diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgmlindex 71e20f2740..1c92212dd6 100644--- a/doc/src/sgml/catalogs.sgml+++ b/doc/src/sgml/catalogs.sgml@@ -1873,7 +1873,9 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
<entry></entry>
<entry>
True if table has (or once had) triggers; see
- <link linkend="catalog-pg-trigger"><structname>pg_trigger</structname></link> catalog+ <link linkend="catalog-pg-trigger"><structname>pg_trigger</structname></link> catalog.+ If this is a partition, triggers on its partitioned ancestors are also+ considered
</entry>
</row>
@@ -6991,6 +6993,13 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
</row>
<row>
+ <entry><structfield>tginherits</structfield></entry>+ <entry><type>bool</type></entry>+ <entry></entry>+ <entry>True if trigger applies to children relations too</entry>+ </row>++ <row>
<entry><structfield>tgnargs</structfield></entry>
<entry><type>int2</type></entry>
<entry></entry>
diff --git a/src/backend/bootstrap/bootparse.y b/src/backend/bootstrap/bootparse.yindex ed7a55596f..7ad0126df5 100644--- a/src/backend/bootstrap/bootparse.y+++ b/src/backend/bootstrap/bootparse.y@@ -230,6 +230,7 @@ Boot_CreateStmt:
RELPERSISTENCE_PERMANENT,
shared_relation,
mapped_relation,
+ false,
true);
elog(DEBUG4, "bootstrap relation created");
}
@@ -252,6 +253,7 @@ Boot_CreateStmt:
mapped_relation,
true,
0,
+ false,
ONCOMMIT_NOOP,
(Datum) 0,
false,
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.cindex cf36ce4add..815f371ac2 100644--- a/src/backend/catalog/heap.c+++ b/src/backend/catalog/heap.c@@ -257,6 +257,7 @@ heap_create(const char *relname,
char relpersistence,
bool shared_relation,
bool mapped_relation,
+ bool has_triggers,
bool allow_system_table_mods)
{
bool create_storage;
@@ -351,7 +352,8 @@ heap_create(const char *relname,
shared_relation,
mapped_relation,
relpersistence,
- relkind);+ relkind,+ has_triggers);
/*
* Have the storage manager create the relation's disk file, if needed.
@@ -1005,6 +1007,7 @@ AddNewRelationType(const char *typeName,
* mapped_relation: true if the relation will use the relfilenode map
* oidislocal: true if oid column (if any) should be marked attislocal
* oidinhcount: attinhcount to assign to oid column (if any)
+ * hastriggers: value to set relhastriggers to
* oncommit: ON COMMIT marking (only relevant if it's a temp table)
* reloptions: reloptions in Datum form, or (Datum) 0 if none
* use_user_acl: true if should look for user-defined default permissions;
@@ -1034,6 +1037,7 @@ heap_create_with_catalog(const char *relname,
bool mapped_relation,
bool oidislocal,
int oidinhcount,
+ bool hastriggers,
OnCommitAction oncommit,
Datum reloptions,
bool use_user_acl,
@@ -1173,6 +1177,7 @@ heap_create_with_catalog(const char *relname,
relpersistence,
shared_relation,
mapped_relation,
+ hastriggers,
allow_system_table_mods);
Assert(relid == RelationGetRelid(new_rel_desc));
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.cindex 564f2069cf..6771b8b01d 100644--- a/src/backend/catalog/index.c+++ b/src/backend/catalog/index.c@@ -903,6 +903,7 @@ index_create(Relation heapRelation,
relpersistence,
shared_relation,
mapped_relation,
+ false,
allow_system_table_mods);
Assert(indexRelationId == RelationGetRelid(indexRelation));
diff --git a/src/backend/catalog/toasting.c b/src/backend/catalog/toasting.cindex 8bf2698545..363f39e7fe 100644--- a/src/backend/catalog/toasting.c+++ b/src/backend/catalog/toasting.c@@ -274,6 +274,7 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid,
mapped_relation,
true,
0,
+ false,
ONCOMMIT_NOOP,
reloptions,
false,
diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.cindex 5d481dd50d..cd78f12f19 100644--- a/src/backend/commands/cluster.c+++ b/src/backend/commands/cluster.c@@ -687,6 +687,7 @@ make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, char relpersistence,
RelationIsMapped(OldHeap),
true,
0,
+ OldHeap->rd_rel->relhastriggers, /* XXX why? */
ONCOMMIT_NOOP,
reloptions,
false,
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.cindex 74e020bffc..344eecda7a 100644--- a/src/backend/commands/tablecmds.c+++ b/src/backend/commands/tablecmds.c@@ -303,7 +303,7 @@ struct DropRelationCallbackState
static void truncate_check_rel(Relation rel);
static List *MergeAttributes(List *schema, List *supers, char relpersistence,
bool is_partition, List **supOids, List **supconstr,
- int *supOidCount);+ int *supOidCount, bool *hastriggers);
static bool MergeCheckConstraint(List *constraints, char *name, Node *expr);
static void MergeAttributesIntoExisting(Relation child_rel, Relation parent_rel);
static void MergeConstraintsIntoExisting(Relation child_rel, Relation parent_rel);
@@ -527,8 +527,10 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
List *old_constraints;
bool localHasOids;
int parentOidCount;
+ bool hastriggers;
List *rawDefaults;
List *cookedDefaults;
+ bool is_partition;
Datum reloptions;
ListCell *listptr;
AttrNumber attnum;
@@ -559,6 +561,8 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
relkind = RELKIND_PARTITIONED_TABLE;
}
+ is_partition = stmt->partbound != NULL;+
/*
* Look up the namespace in which we are supposed to create the relation,
* check we have permission to create there, lock it against concurrent
@@ -647,8 +651,9 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
stmt->tableElts =
MergeAttributes(stmt->tableElts, stmt->inhRelations,
stmt->relation->relpersistence,
- stmt->partbound != NULL,- &inheritOids, &old_constraints, &parentOidCount);+ is_partition,+ &inheritOids, &old_constraints, &parentOidCount,+ &hastriggers);
/*
* Create a tuple descriptor from the relation schema. Note that this
@@ -675,7 +680,7 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
* If a partitioned table doesn't have the system OID column, then none of
* its partitions should have it.
*/
- if (stmt->partbound && parentOidCount == 0 && localHasOids)+ if (is_partition && parentOidCount == 0 && localHasOids)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
errmsg("cannot create table with OIDs as partition of table without OIDs")));
@@ -759,6 +764,7 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
false,
localHasOids,
parentOidCount,
+ hastriggers,
stmt->oncommit,
reloptions,
true,
@@ -767,7 +773,7 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
typaddress);
/* Store inheritance information for new rel. */
- StoreCatalogInheritance(relationId, inheritOids, stmt->partbound != NULL);+ StoreCatalogInheritance(relationId, inheritOids, is_partition);
/*
* We must bump the command counter to make the newly-created relation
@@ -784,7 +790,7 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
rel = relation_open(relationId, AccessExclusiveLock);
/* Process and store partition bound, if any. */
- if (stmt->partbound)+ if (is_partition)
{
PartitionBoundSpec *bound;
ParseState *pstate;
@@ -920,7 +926,7 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
* the parent. We can't do it earlier, because DefineIndex wants to know
* the partition key which we just stored.
*/
- if (stmt->partbound)+ if (is_partition)
{
Oid parentId = linitial_oid(inheritOids);
Relation parent;
@@ -1692,6 +1698,8 @@ storage_name(char c)
* 'supconstr' receives a list of constraints belonging to the parents,
* updated as necessary to be valid for the child.
* 'supOidCount' is set to the number of parents that have OID columns.
+ * 'hasTriggers' is set to true if any parent has inheritable triggers,+ * false otherwise.
*
* Return value:
* Completed schema list.
@@ -1738,7 +1746,7 @@ storage_name(char c)
static List *
MergeAttributes(List *schema, List *supers, char relpersistence,
bool is_partition, List **supOids, List **supconstr,
- int *supOidCount)+ int *supOidCount, bool *hasTriggers)
{
ListCell *entry;
List *inhSchema = NIL;
@@ -1750,6 +1758,8 @@ MergeAttributes(List *schema, List *supers, char relpersistence,
static Node bogus_marker = {0}; /* marks conflicting defaults */
List *saved_schema = NIL;
+ *hasTriggers = false;+
/*
* Check for and reject tables with too many columns. We perform this
* check relatively early for two reasons: (a) we don't run the risk of
@@ -2147,6 +2157,23 @@ MergeAttributes(List *schema, List *supers, char relpersistence,
pfree(newattno);
/*
+ * If this parent has triggers, and any of them is marked inheritable,+ * set *hastriggers.+ */+ if (relation->rd_rel->relhastriggers &&+ relation->trigdesc != NULL &&+ !*hasTriggers)+ {+ int trg;++ for (trg = 0; trg < relation->trigdesc->numtriggers; trg++)+ {+ if (relation->trigdesc->triggers[trg].tginherits)+ *hasTriggers = true;+ }+ }++ /*
* Close the parent rel, but keep our lock on it until xact commit.
* That will prevent someone else from deleting or ALTERing the parent
* before the child is committed.
@@ -14248,6 +14275,9 @@ AttachPartitionEnsureIndexes(Relation rel, Relation attachrel)
index_close(idxRel, AccessShareLock);
}
+ /* Make this all visible */+ CommandCounterIncrement();+
/* Clean up. */
for (i = 0; i < list_length(attachRelIdxs); i++)
index_close(attachrelIdxRels[i], AccessShareLock);
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.cindex fffc0095a7..23d669cf26 100644--- a/src/backend/commands/trigger.c+++ b/src/backend/commands/trigger.c@@ -24,6 +24,7 @@
#include "catalog/objectaccess.h"
#include "catalog/pg_constraint.h"
#include "catalog/pg_constraint_fn.h"
+#include "catalog/pg_inherits.h"
#include "catalog/pg_inherits_fn.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_trigger.h"
@@ -100,7 +101,17 @@ static void AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
List *recheckIndexes, Bitmapset *modifiedCols,
TransitionCaptureState *transition_capture);
static void AfterTriggerEnlargeQueryState(void);
+static void SendTriggerRelcacheInval(Relation inheritsRel, Relation rel);
static bool before_stmt_triggers_fired(Oid relid, CmdType cmdType);
+static void recursively_update_relhastriggers(Relation pg_class, Oid relid,+ bool recurse);+static void add_triggers_to_array(Relation tgrel, Relation inhrel,+ Oid tgrelid, bool all_triggers,+ Trigger **triggers, int *numtrigs, int *maxtrigs,+ bool *must_sort);+static void add_trigger_to_array(TupleDesc tgdesc, HeapTuple tgtup,+ Trigger **triggers, int *numtrigs, int *maxtrigs);+static int qsort_trigger_cmp(const void *a, const void *b);
/*
@@ -133,6 +144,9 @@ static bool before_stmt_triggers_fired(Oid relid, CmdType cmdType);
* relation, as well as ACL_EXECUTE on the trigger function. For internal
* triggers the caller must apply any required permission checks.
*
+ * When called on partitioned tables, FOR EACH ROW triggers are marked as+ * applying on partitions too (ie. tginherits), except if isInternal.+ *
* Note: can return InvalidObjectAddress if we decided to not create a trigger
* at all, but a foreign-key constraint. This is a kluge for backwards
* compatibility.
@@ -149,6 +163,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
Node *whenClause;
List *whenRtable;
char *qual;
+ bool tginherits;
Datum values[Natts_pg_trigger];
bool nulls[Natts_pg_trigger];
Relation rel;
@@ -179,8 +194,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
* Triggers must be on tables or views, and there are additional
* relation-type-specific restrictions.
*/
- if (rel->rd_rel->relkind == RELKIND_RELATION ||- rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)+ if (rel->rd_rel->relkind == RELKIND_RELATION)
{
/* Tables can't have INSTEAD OF triggers */
if (stmt->timing != TRIGGER_TYPE_BEFORE &&
@@ -190,13 +204,69 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
errmsg("\"%s\" is a table",
RelationGetRelationName(rel)),
errdetail("Tables cannot have INSTEAD OF triggers.")));
- /* Disallow ROW triggers on partitioned tables */- if (stmt->row && rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)+ }+ else if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)+ {+ /* Partitioned tables can't have INSTEAD OF triggers */+ if (stmt->timing != TRIGGER_TYPE_BEFORE &&+ stmt->timing != TRIGGER_TYPE_AFTER)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
- errmsg("\"%s\" is a partitioned table",+ errmsg("\"%s\" is a table",
RelationGetRelationName(rel)),
- errdetail("Partitioned tables cannot have ROW triggers.")));+ errdetail("Tables cannot have INSTEAD OF triggers.")));+ /*+ * FOR EACH ROW triggers have further restrictions+ */+ if (stmt->row)+ {+ /*+ * Disallow WHEN clauses; I think it's okay, but disallow for now+ * to reduce testing surface.+ */+ if (stmt->whenClause)+ ereport(ERROR,+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),+ errmsg("\"%s\" is a partitioned table",+ RelationGetRelationName(rel)),+ errdetail("Triggers FOR EACH ROW on partitioned table cannot have WHEN clauses.")));++ /*+ * BEFORE triggers FOR EACH ROW are forbidden, because they would+ * allow the user to direct the row to another partition, which+ * isn't implemented in the executor.+ */+ if (stmt->timing != TRIGGER_TYPE_AFTER)+ ereport(ERROR,+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),+ errmsg("\"%s\" is a partitioned table",+ RelationGetRelationName(rel)),+ errdetail("Partitioned tables cannot have BEFORE / FOR EACH ROW triggers.")));++ /*+ * Constraint triggers are not allowed, either.+ */+ if (stmt->isconstraint)+ ereport(ERROR,+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),+ errmsg("\"%s\" is a partitioned table",+ RelationGetRelationName(rel)),+ errdetail("Partitioned tables cannot have CONSTRAINT triggers FOR EACH ROW.")));++ /*+ * Disallow use of transition tables. If this partitioned table+ * has any partitions, the error would occur below; but if it+ * doesn't then we would only hit that code when the first CREATE+ * TABLE ... PARTITION OF is executed, which is too late. Check+ * early to avoid the problem.+ */+ if (stmt->transitionRels != NIL)+ ereport(ERROR,+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),+ errmsg("\"%s\" is a partitioned table",+ RelationGetRelationName(rel)),+ errdetail("Triggers on partitioned tables cannot have transition tables.")));+ }
}
else if (rel->rd_rel->relkind == RELKIND_VIEW)
{
@@ -676,6 +746,12 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
}
/*
+ * FOR EACH ROW triggers in partitioned tables are marked inheritable.+ */+ tginherits = (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE &&+ TRIGGER_FOR_ROW(tgtype));++ /*
* Generate the trigger's OID now, so that we can use it in the name if
* needed.
*/
@@ -748,6 +824,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
values[Anum_pg_trigger_tgconstraint - 1] = ObjectIdGetDatum(constraintOid);
values[Anum_pg_trigger_tgdeferrable - 1] = BoolGetDatum(stmt->deferrable);
values[Anum_pg_trigger_tginitdeferred - 1] = BoolGetDatum(stmt->initdeferred);
+ values[Anum_pg_trigger_tginherits - 1] = BoolGetDatum(tginherits);
if (stmt->args)
{
@@ -872,22 +949,15 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
pfree(DatumGetPointer(values[Anum_pg_trigger_tgnewtable - 1]));
/*
- * Update relation's pg_class entry. Crucial side-effect: other backends+ * Update relation's pg_class entry -- and that of each child relation,+ * if the trigger is inheritable. Crucial side-effect: other backends
* (and this one too!) are sent SI message to make them rebuild relcache
* entries.
*/
pgrel = heap_open(RelationRelationId, RowExclusiveLock);
- tuple = SearchSysCacheCopy1(RELOID,- ObjectIdGetDatum(RelationGetRelid(rel)));- if (!HeapTupleIsValid(tuple))- elog(ERROR, "cache lookup failed for relation %u",- RelationGetRelid(rel));- ((Form_pg_class) GETSTRUCT(tuple))->relhastriggers = true;+ recursively_update_relhastriggers(pgrel, RelationGetRelid(rel), tginherits);- CatalogTupleUpdate(pgrel, &tuple->t_self, tuple);-- heap_freetuple(tuple);
heap_close(pgrel, RowExclusiveLock);
/*
@@ -933,6 +1003,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
referenced.objectId = RelationGetRelid(rel);
referenced.objectSubId = 0;
recordDependencyOn(&myself, &referenced, DEPENDENCY_AUTO);
+
if (OidIsValid(constrrelid))
{
referenced.classId = RelationRelationId;
@@ -988,7 +1059,6 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
return myself;
}
-
/*
* Convert legacy (pre-7.3) CREATE CONSTRAINT TRIGGER commands into
* full-fledged foreign key constraints.
@@ -1337,7 +1407,7 @@ RemoveTriggerById(Oid trigOid)
* There's no great harm in leaving relhastriggers true even if there are
* no triggers left.
*/
- CacheInvalidateRelcache(rel);+ SendTriggerRelcacheInval(NULL, rel);
/* Keep lock on trigger's rel until end of xact */
heap_close(rel, NoLock);
@@ -1535,7 +1605,7 @@ renametrig(RenameStmt *stmt)
* this one too!) are sent SI message to make them rebuild relcache
* entries. (Ideally this should happen automatically...)
*/
- CacheInvalidateRelcache(targetrel);+ SendTriggerRelcacheInval(NULL, targetrel);
}
else
{
@@ -1559,7 +1629,6 @@ renametrig(RenameStmt *stmt)
return address;
}
-
/*
* EnableDisableTrigger()
*
@@ -1665,10 +1734,9 @@ EnableDisableTrigger(Relation rel, const char *tgname,
* Otherwise they will fail to apply the change promptly.
*/
if (changed)
- CacheInvalidateRelcache(rel);+ SendTriggerRelcacheInval(NULL, rel);
}
-
/*
* Build trigger data to attach to the given relcache entry.
*
@@ -1687,11 +1755,10 @@ RelationBuildTriggers(Relation relation)
int maxtrigs;
Trigger *triggers;
Relation tgrel;
- ScanKeyData skey;- SysScanDesc tgscan;- HeapTuple htup;+ Relation inhrel;
MemoryContext oldContext;
int i;
+ bool must_sort = false;
/*
* Allocate a working array to hold the triggers (the array is extended if
@@ -1701,108 +1768,14 @@ RelationBuildTriggers(Relation relation)
triggers = (Trigger *) palloc(maxtrigs * sizeof(Trigger));
numtrigs = 0;
- /*- * Note: since we scan the triggers using TriggerRelidNameIndexId, we will- * be reading the triggers in name order, except possibly during- * emergency-recovery operations (ie, IgnoreSystemIndexes). This in turn- * ensures that triggers will be fired in name order.- */- ScanKeyInit(&skey,- Anum_pg_trigger_tgrelid,- BTEqualStrategyNumber, F_OIDEQ,- ObjectIdGetDatum(RelationGetRelid(relation)));-
tgrel = heap_open(TriggerRelationId, AccessShareLock);
- tgscan = systable_beginscan(tgrel, TriggerRelidNameIndexId, true,- NULL, 1, &skey);+ inhrel = heap_open(InheritsRelationId, AccessShareLock);- while (HeapTupleIsValid(htup = systable_getnext(tgscan)))- {- Form_pg_trigger pg_trigger = (Form_pg_trigger) GETSTRUCT(htup);- Trigger *build;- Datum datum;- bool isnull;+ add_triggers_to_array(tgrel, inhrel, RelationGetRelid(relation),+ true, &triggers, &numtrigs, &maxtrigs, &must_sort);- if (numtrigs >= maxtrigs)- {- maxtrigs *= 2;- triggers = (Trigger *) repalloc(triggers, maxtrigs * sizeof(Trigger));- }- build = &(triggers[numtrigs]);-- build->tgoid = HeapTupleGetOid(htup);- build->tgname = DatumGetCString(DirectFunctionCall1(nameout,- NameGetDatum(&pg_trigger->tgname)));- build->tgfoid = pg_trigger->tgfoid;- build->tgtype = pg_trigger->tgtype;- build->tgenabled = pg_trigger->tgenabled;- build->tgisinternal = pg_trigger->tgisinternal;- build->tgconstrrelid = pg_trigger->tgconstrrelid;- build->tgconstrindid = pg_trigger->tgconstrindid;- build->tgconstraint = pg_trigger->tgconstraint;- build->tgdeferrable = pg_trigger->tgdeferrable;- build->tginitdeferred = pg_trigger->tginitdeferred;- build->tgnargs = pg_trigger->tgnargs;- /* tgattr is first var-width field, so OK to access directly */- build->tgnattr = pg_trigger->tgattr.dim1;- if (build->tgnattr > 0)- {- build->tgattr = (int16 *) palloc(build->tgnattr * sizeof(int16));- memcpy(build->tgattr, &(pg_trigger->tgattr.values),- build->tgnattr * sizeof(int16));- }- else- build->tgattr = NULL;- if (build->tgnargs > 0)- {- bytea *val;- char *p;-- val = DatumGetByteaPP(fastgetattr(htup,- Anum_pg_trigger_tgargs,- tgrel->rd_att, &isnull));- if (isnull)- elog(ERROR, "tgargs is null in trigger for relation \"%s\"",- RelationGetRelationName(relation));- p = (char *) VARDATA_ANY(val);- build->tgargs = (char **) palloc(build->tgnargs * sizeof(char *));- for (i = 0; i < build->tgnargs; i++)- {- build->tgargs[i] = pstrdup(p);- p += strlen(p) + 1;- }- }- else- build->tgargs = NULL;-- datum = fastgetattr(htup, Anum_pg_trigger_tgoldtable,- tgrel->rd_att, &isnull);- if (!isnull)- build->tgoldtable =- DatumGetCString(DirectFunctionCall1(nameout, datum));- else- build->tgoldtable = NULL;-- datum = fastgetattr(htup, Anum_pg_trigger_tgnewtable,- tgrel->rd_att, &isnull);- if (!isnull)- build->tgnewtable =- DatumGetCString(DirectFunctionCall1(nameout, datum));- else- build->tgnewtable = NULL;-- datum = fastgetattr(htup, Anum_pg_trigger_tgqual,- tgrel->rd_att, &isnull);- if (!isnull)- build->tgqual = TextDatumGetCString(datum);- else- build->tgqual = NULL;-- numtrigs++;- }-- systable_endscan(tgscan);
heap_close(tgrel, AccessShareLock);
+ heap_close(inhrel, AccessShareLock);
/* There might not be any triggers */
if (numtrigs == 0)
@@ -1811,6 +1784,10 @@ RelationBuildTriggers(Relation relation)
return;
}
+ /* apply a final sort step, if needed */+ if (must_sort)+ qsort(triggers, numtrigs, sizeof(Trigger), qsort_trigger_cmp);+
/* Build trigdesc */
trigdesc = (TriggerDesc *) palloc0(sizeof(TriggerDesc));
trigdesc->triggers = triggers;
@@ -1827,6 +1804,7 @@ RelationBuildTriggers(Relation relation)
FreeTriggerDesc(trigdesc);
}
+
/*
* Update the TriggerDesc's hint flags to include the specified trigger
*/
@@ -5742,6 +5720,51 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
}
/*
+ * SendTriggerRelcacheInvals+ * Send inval signals for this rel and all its descendants.+ */+static void+SendTriggerRelcacheInval(Relation inheritsRel, Relation rel)+{+ ScanKeyData skey;+ bool opened = false;+ SysScanDesc scan;+ HeapTuple inhtup;++ CacheInvalidateRelcache(rel);++ if (!rel->rd_rel->relhassubclass)+ return;++ if (inheritsRel == NULL)+ {+ inheritsRel = heap_open(InheritsRelationId, AccessShareLock);+ opened = true;+ }++ ScanKeyInit(&skey,+ Anum_pg_inherits_inhparent,+ BTEqualStrategyNumber, F_OIDEQ,+ ObjectIdGetDatum(RelationGetRelid(rel)));+ scan = systable_beginscan(inheritsRel, InheritsParentIndexId, true, NULL,+ 1, &skey);+ while ((inhtup = systable_getnext(scan)) != NULL)+ {+ Oid child = ((Form_pg_inherits) GETSTRUCT(inhtup))->inhrelid;+ Relation childrel;++ childrel = heap_open(child, AccessShareLock);+ SendTriggerRelcacheInval(inheritsRel, childrel);+ heap_close(childrel, AccessShareLock);+ }++ systable_endscan(scan);++ if (opened)+ heap_close(inheritsRel, AccessShareLock);+}++/*
* Detect whether we already queued BEFORE STATEMENT triggers for the given
* relation + operation, and set the flag so the next call will report "true".
*/
@@ -5864,6 +5887,259 @@ done:
}
/*
+ * Update the relhastriggers for the relation with 'relid'; and there are any+ * descendents, recurse to update it on those too, if the 'recurse' flag is+ * true.+ */+static void+recursively_update_relhastriggers(Relation pg_class, Oid relid, bool recurse)+{+ HeapTuple classTup;+ Form_pg_class classForm;++ classTup = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(relid));+ if (!HeapTupleIsValid(classTup))+ elog(ERROR, "cache lookup failed for relation %u", relid);+ classForm = (Form_pg_class) GETSTRUCT(classTup);++ /*+ * Update the relation's relhastriggers flag, if necessary. If we don't+ * do it, make sure to send an inval message anyway.+ */+ if (!classForm->relhastriggers)+ {+ classForm->relhastriggers = true;+ CatalogTupleUpdate(pg_class, &classTup->t_self, classTup);+ }+ else+ CacheInvalidateRelcacheByTuple(classTup);++ /*+ * Recurse to update the children flag, if there are any.+ *+ * You may be tempted to merge this with the above, thinking that if the+ * parent already had relhastriggers then children must be OK too -- but+ * that is wrong if the parent already had a non-inheritable trigger and+ * now has an inheritable one.+ */+ if (recurse && classForm->relhassubclass)+ {+ ScanKeyData key;+ Relation inhRel;+ SysScanDesc scan;+ HeapTuple inhTup;++ inhRel = heap_open(InheritsRelationId, AccessShareLock);+ ScanKeyInit(&key,+ Anum_pg_inherits_inhparent,+ BTEqualStrategyNumber, F_OIDEQ,+ ObjectIdGetDatum(relid));+ scan = systable_beginscan(inhRel, InheritsParentIndexId, true,+ NULL, 1, &key);++ while (HeapTupleIsValid(inhTup = systable_getnext(scan)))+ {+ Form_pg_inherits inhForm = (Form_pg_inherits) GETSTRUCT(inhTup);++ recursively_update_relhastriggers(pg_class, inhForm->inhrelid, recurse);+ }+ systable_endscan(scan);+ heap_close(inhRel, AccessShareLock);+ }++ heap_freetuple(classTup);+}++/*+ * Search for triggers on the relation with oid 'tgrelid', and add any that are+ * found to the array 'triggers' ('numtrigs' is the number of used elements,+ * 'maxtrigs' the number of allocated elements). If 'all_triggers' is false,+ * only consider triggers that have tginherits true; otherwise include all+ * triggers.+ *+ * For each parent of this relation, recurse to do the same, passing+ * 'all_triggers' false.+ *+ * 'tgrel' is the pg_triggers relation.+ *+ * 'must_sort' is set to true if the order of the triggers in the output array+ * is not guaranteed; caller must sort afterwards in that case.+ */+static void+add_triggers_to_array(Relation tgrel, Relation inhrel,+ Oid tgrelid, bool all_triggers,+ Trigger **triggers, int *numtrigs, int *maxtrigs,+ bool *must_sort)+{+ SysScanDesc scan;+ ScanKeyData skey;+ HeapTuple tgtup;+ HeapTuple inhtup;++ /*+ * Note: since we scan the triggers using TriggerRelidNameIndexId, we+ * will be reading the triggers in name order, except possibly during+ * emergency-recovery operations (ie, IgnoreSystemIndexes). This in+ * turn ensures that triggers will be fired in name order. However,+ * when searching for triggers in parent relations, the order is no+ * longer guaranteed (since multiple scans are involved), so we signal+ * our caller to sort afterwards.+ */++ /*+ * Scan pg_trigger for the given relation, appending relevant triggers+ * to our output array.+ */+ ScanKeyInit(&skey,+ Anum_pg_trigger_tgrelid,+ BTEqualStrategyNumber, F_OIDEQ,+ ObjectIdGetDatum(tgrelid));+ scan = systable_beginscan(tgrel, TriggerRelidNameIndexId, true,+ NULL, 1, &skey);+ while (HeapTupleIsValid(tgtup = systable_getnext(scan)))+ {+ Form_pg_trigger trig = (Form_pg_trigger) GETSTRUCT(tgtup);++ /*+ * triggers in ancestor rels are not considered unless they are marked+ * tginherits.+ */+ if (!all_triggers && !trig->tginherits)+ continue;++ add_trigger_to_array(RelationGetDescr(tgrel), tgtup,+ triggers, numtrigs, maxtrigs);+ }+ systable_endscan(scan);++ /*+ * Now scan pg_inherits to find parents of this relation and recurse, in+ * case any inheritable triggers are present there.+ */+ ScanKeyInit(&skey,+ Anum_pg_inherits_inhrelid,+ BTEqualStrategyNumber, F_OIDEQ,+ ObjectIdGetDatum(tgrelid));+ scan = systable_beginscan(inhrel, InheritsRelidSeqnoIndexId, true, NULL,+ 1, &skey);+ while ((inhtup = systable_getnext(scan)) != NULL)+ {+ Oid ancestor = ((Form_pg_inherits) GETSTRUCT(inhtup))->inhparent;+ int local_numtrigs = *numtrigs;++ /* recurse to this parent */+ add_triggers_to_array(tgrel, inhrel, ancestor, false,+ triggers, numtrigs, maxtrigs, must_sort);++ /* if any triggers were added, set the sort flag */+ if (*numtrigs > local_numtrigs)+ *must_sort = true;+ }++ systable_endscan(scan);+}++/*+ * Subroutine for add_triggers_to_array to add a single trigger to the array.+ */+static void+add_trigger_to_array(TupleDesc tgdesc, HeapTuple htup,+ Trigger **triggers, int *numtrigs, int *maxtrigs)+{+ Form_pg_trigger pg_trigger = (Form_pg_trigger) GETSTRUCT(htup);+ Trigger *build;+ Datum datum;+ bool isnull;++ if (*numtrigs >= *maxtrigs)+ {+ *maxtrigs *= 2;+ *triggers = (Trigger *) repalloc(*triggers, *maxtrigs * sizeof(Trigger));+ }+ build = *triggers + *numtrigs;++ build->tgoid = HeapTupleGetOid(htup);+ build->tgname = DatumGetCString(DirectFunctionCall1(nameout,+ NameGetDatum(&pg_trigger->tgname)));+ build->tgfoid = pg_trigger->tgfoid;+ build->tgtype = pg_trigger->tgtype;+ build->tgenabled = pg_trigger->tgenabled;+ build->tgisinternal = pg_trigger->tgisinternal;+ build->tginherits = pg_trigger->tginherits;+ build->tgconstrrelid = pg_trigger->tgconstrrelid;+ build->tgconstrindid = pg_trigger->tgconstrindid;+ build->tgconstraint = pg_trigger->tgconstraint;+ build->tgdeferrable = pg_trigger->tgdeferrable;+ build->tginitdeferred = pg_trigger->tginitdeferred;+ build->tgnargs = pg_trigger->tgnargs;+ /* tgattr is first var-width field, so OK to access directly */+ build->tgnattr = pg_trigger->tgattr.dim1;+ if (build->tgnattr > 0)+ {+ build->tgattr = (int16 *) palloc(build->tgnattr * sizeof(int16));+ memcpy(build->tgattr, &(pg_trigger->tgattr.values),+ build->tgnattr * sizeof(int16));+ }+ else+ build->tgattr = NULL;+ if (build->tgnargs > 0)+ {+ bytea *val;+ char *p;+ int i;++ val = DatumGetByteaPP(fastgetattr(htup,+ Anum_pg_trigger_tgargs,+ tgdesc, &isnull));+ if (isnull)+ elog(ERROR, "tgargs is null in trigger \"%u\"",+ HeapTupleGetOid(htup));+ p = (char *) VARDATA_ANY(val);+ build->tgargs = (char **) palloc(build->tgnargs * sizeof(char *));+ for (i = 0; i < build->tgnargs; i++)+ {+ build->tgargs[i] = pstrdup(p);+ p += strlen(p) + 1;+ }+ }+ else+ build->tgargs = NULL;++ datum = fastgetattr(htup, Anum_pg_trigger_tgoldtable,+ tgdesc, &isnull);+ if (!isnull)+ build->tgoldtable =+ DatumGetCString(DirectFunctionCall1(nameout, datum));+ else+ build->tgoldtable = NULL;++ datum = fastgetattr(htup, Anum_pg_trigger_tgnewtable,+ tgdesc, &isnull);+ if (!isnull)+ build->tgnewtable =+ DatumGetCString(DirectFunctionCall1(nameout, datum));+ else+ build->tgnewtable = NULL;++ datum = fastgetattr(htup, Anum_pg_trigger_tgqual,+ tgdesc, &isnull);+ if (!isnull)+ build->tgqual = TextDatumGetCString(datum);+ else+ build->tgqual = NULL;++ (*numtrigs)++;+}++static int+qsort_trigger_cmp(const void *a, const void *b)+{+ const Trigger *ta = (const Trigger *) a;+ const Trigger *tb = (const Trigger *) b;++ return strcmp(ta->tgname, tb->tgname);+}+/*
* SQL function pg_trigger_depth()
*/
Datum
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.cindex 1ebf9c4ed2..0b11a2d6cf 100644--- a/src/backend/utils/cache/relcache.c+++ b/src/backend/utils/cache/relcache.c@@ -3165,7 +3165,8 @@ RelationBuildLocalRelation(const char *relname,
bool shared_relation,
bool mapped_relation,
char relpersistence,
- char relkind)+ char relkind,+ bool has_triggers)
{
Relation rel;
MemoryContext oldcxt;
@@ -3276,6 +3277,7 @@ RelationBuildLocalRelation(const char *relname,
rel->rd_rel->relhasoids = rel->rd_att->tdhasoid;
rel->rd_rel->relnatts = natts;
rel->rd_rel->reltype = InvalidOid;
+ rel->rd_rel->relhastriggers = has_triggers;
/* needed when bootstrapping: */
rel->rd_rel->relowner = BOOTSTRAP_SUPERUSERID;
diff --git a/src/include/catalog/heap.h b/src/include/catalog/heap.hindex 9bdc63ceb5..8961a557f7 100644--- a/src/include/catalog/heap.h+++ b/src/include/catalog/heap.h@@ -47,6 +47,7 @@ extern Relation heap_create(const char *relname,
TupleDesc tupDesc,
char relkind,
char relpersistence,
+ bool has_triggers,
bool shared_relation,
bool mapped_relation,
bool allow_system_table_mods);
@@ -66,6 +67,7 @@ extern Oid heap_create_with_catalog(const char *relname,
bool mapped_relation,
bool oidislocal,
int oidinhcount,
+ bool hastriggers,
OnCommitAction oncommit,
Datum reloptions,
bool use_user_acl,
diff --git a/src/include/catalog/pg_trigger.h b/src/include/catalog/pg_trigger.hindex c80a3aa54d..1f3d1a8ebe 100644--- a/src/include/catalog/pg_trigger.h+++ b/src/include/catalog/pg_trigger.h@@ -48,6 +48,7 @@ CATALOG(pg_trigger,2620)
Oid tgconstraint; /* associated pg_constraint entry, if any */
bool tgdeferrable; /* constraint trigger is deferrable */
bool tginitdeferred; /* constraint trigger is deferred initially */
+ bool tginherits; /* trigger applies to children relations */
int16 tgnargs; /* # of extra arguments in tgargs */
/*
@@ -75,7 +76,7 @@ typedef FormData_pg_trigger *Form_pg_trigger;
* compiler constants for pg_trigger
* ----------------
*/
-#define Natts_pg_trigger 17+#define Natts_pg_trigger 18
#define Anum_pg_trigger_tgrelid 1
#define Anum_pg_trigger_tgname 2
#define Anum_pg_trigger_tgfoid 3
@@ -87,12 +88,13 @@ typedef FormData_pg_trigger *Form_pg_trigger;
#define Anum_pg_trigger_tgconstraint 9
#define Anum_pg_trigger_tgdeferrable 10
#define Anum_pg_trigger_tginitdeferred 11
-#define Anum_pg_trigger_tgnargs 12-#define Anum_pg_trigger_tgattr 13-#define Anum_pg_trigger_tgargs 14-#define Anum_pg_trigger_tgqual 15-#define Anum_pg_trigger_tgoldtable 16-#define Anum_pg_trigger_tgnewtable 17+#define Anum_pg_trigger_tginherits 12+#define Anum_pg_trigger_tgnargs 13+#define Anum_pg_trigger_tgattr 14+#define Anum_pg_trigger_tgargs 15+#define Anum_pg_trigger_tgqual 16+#define Anum_pg_trigger_tgoldtable 17+#define Anum_pg_trigger_tgnewtable 18
/* Bits within tgtype */
#define TRIGGER_TYPE_ROW (1 << 0)
diff --git a/src/include/utils/relcache.h b/src/include/utils/relcache.hindex 8a546aba28..4ee77bfb3e 100644--- a/src/include/utils/relcache.h+++ b/src/include/utils/relcache.h@@ -103,7 +103,8 @@ extern Relation RelationBuildLocalRelation(const char *relname,
bool shared_relation,
bool mapped_relation,
char relpersistence,
- char relkind);+ char relkind,+ bool has_triggers);
/*
* Routine to manage assignment of new relfilenode to a relation
diff --git a/src/include/utils/reltrigger.h b/src/include/utils/reltrigger.hindex 9b4dc7f810..b1f0354263 100644--- a/src/include/utils/reltrigger.h+++ b/src/include/utils/reltrigger.h@@ -29,6 +29,7 @@ typedef struct Trigger
int16 tgtype;
char tgenabled;
bool tgisinternal;
+ bool tginherits;
Oid tgconstrrelid;
Oid tgconstrindid;
Oid tgconstraint;
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.outindex e7b4b31afc..9bf2dadfd1 100644--- a/src/test/regress/expected/triggers.out+++ b/src/test/regress/expected/triggers.out@@ -1855,7 +1855,66 @@ drop function my_trigger_function();
drop view my_view;
drop table my_table;
--
--- Verify that per-statement triggers are fired for partitioned tables+-- Verify cases that are unsupported with partitioned tables+--+create table parted_trig (a int) partition by list (a);+create function trigger_nothing() returns trigger+ language plpgsql as $$ begin end; $$;+create trigger failed before insert or update or delete on parted_trig+ for each row execute procedure trigger_nothing();+ERROR: "parted_trig" is a partitioned table+DETAIL: Partitioned tables cannot have BEFORE / FOR EACH ROW triggers.+create trigger failed after update on parted_trig+ for each row when (OLD.a <> NEW.a) execute procedure trigger_nothing();+ERROR: "parted_trig" is a partitioned table+DETAIL: Triggers FOR EACH ROW on partitioned table cannot have WHEN clauses.+create trigger failed instead of update on parted_trig+ for each row execute procedure trigger_nothing();+ERROR: "parted_trig" is a table+DETAIL: Tables cannot have INSTEAD OF triggers.+create trigger failed after update on parted_trig+ referencing old table as old_table+ for each statement execute procedure trigger_nothing();+create constraint trigger failed after insert on parted_trig+ for each row execute procedure trigger_nothing();+ERROR: "parted_trig" is a partitioned table+DETAIL: Partitioned tables cannot have CONSTRAINT triggers FOR EACH ROW.+drop table parted_trig;+--+-- Verify trigger creation for partitioned tables, and drop behavior+--+create table trigpart (a int, b int) partition by range (a);+create table trigpart1 partition of trigpart for values from (0) to (1000);+create trigger f after insert on trigpart for each row execute procedure trigger_nothing();+create table trigpart2 partition of trigpart for values from (1000) to (2000);+create table trigpart3 (like trigpart);+alter table trigpart attach partition trigpart3 for values from (2000) to (3000);+select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger+ where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text;+ tgrelid | tgname | tgfoid +----------+--------+-----------------+ trigpart | f | trigger_nothing+(1 row)++drop table trigpart2;+select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger+ where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text;+ tgrelid | tgname | tgfoid +----------+--------+-----------------+ trigpart | f | trigger_nothing+(1 row)++drop trigger f on trigpart; -- ok, all gone+select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger+ where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text;+ tgrelid | tgname | tgfoid +---------+--------+--------+(0 rows)++drop table trigpart;+drop function trigger_nothing();+--+-- Verify that triggers are fired for partitioned tables
--
create table parted_stmt_trig (a int) partition by list (a);
create table parted_stmt_trig1 partition of parted_stmt_trig for values in (1);
@@ -1872,7 +1931,7 @@ create or replace function trigger_notice() returns trigger as $$
return null;
end;
$$ language plpgsql;
--- insert/update/delete statment-level triggers on the parent+-- insert/update/delete statement-level triggers on the parent
create trigger trig_ins_before before insert on parted_stmt_trig
for each statement execute procedure trigger_notice();
create trigger trig_ins_after after insert on parted_stmt_trig
@@ -1885,36 +1944,62 @@ create trigger trig_del_before before delete on parted_stmt_trig
for each statement execute procedure trigger_notice();
create trigger trig_del_after after delete on parted_stmt_trig
for each statement execute procedure trigger_notice();
+-- these cases are disallowed+create trigger trig_ins_before_1 before insert on parted_stmt_trig+ for each row execute procedure trigger_notice();+ERROR: "parted_stmt_trig" is a partitioned table+DETAIL: Partitioned tables cannot have BEFORE / FOR EACH ROW triggers.+create trigger trig_upd_before_1 before update on parted_stmt_trig+ for each row execute procedure trigger_notice();+ERROR: "parted_stmt_trig" is a partitioned table+DETAIL: Partitioned tables cannot have BEFORE / FOR EACH ROW triggers.+create trigger trig_del_before_1 before delete on parted_stmt_trig+ for each row execute procedure trigger_notice();+ERROR: "parted_stmt_trig" is a partitioned table+DETAIL: Partitioned tables cannot have BEFORE / FOR EACH ROW triggers.+-- insert/update/delete row-level triggers on the parent+create trigger trig_ins_after_parent after insert on parted_stmt_trig+ for each row execute procedure trigger_notice();+create trigger trig_upd_after_parent after update on parted_stmt_trig+ for each row execute procedure trigger_notice();+create trigger trig_del_after_parent after delete on parted_stmt_trig+ for each row execute procedure trigger_notice();
-- insert/update/delete row-level triggers on the first partition
-create trigger trig_ins_before before insert on parted_stmt_trig1+create trigger trig_ins_before_child before insert on parted_stmt_trig1
for each row execute procedure trigger_notice();
-create trigger trig_ins_after after insert on parted_stmt_trig1+create trigger trig_ins_after_child after insert on parted_stmt_trig1
for each row execute procedure trigger_notice();
-create trigger trig_upd_before before update on parted_stmt_trig1+create trigger trig_upd_before_child before update on parted_stmt_trig1
for each row execute procedure trigger_notice();
-create trigger trig_upd_after after update on parted_stmt_trig1+create trigger trig_upd_after_child after update on parted_stmt_trig1+ for each row execute procedure trigger_notice();+create trigger trig_del_before_child before delete on parted_stmt_trig1+ for each row execute procedure trigger_notice();+create trigger trig_del_after_child after delete on parted_stmt_trig1
for each row execute procedure trigger_notice();
-- insert/update/delete statement-level triggers on the parent
-create trigger trig_ins_before before insert on parted2_stmt_trig+create trigger trig_ins_before_3 before insert on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_ins_after after insert on parted2_stmt_trig+create trigger trig_ins_after_3 after insert on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_upd_before before update on parted2_stmt_trig+create trigger trig_upd_before_3 before update on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_upd_after after update on parted2_stmt_trig+create trigger trig_upd_after_3 after update on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_del_before before delete on parted2_stmt_trig+create trigger trig_del_before_3 before delete on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_del_after after delete on parted2_stmt_trig+create trigger trig_del_after_3 after delete on parted2_stmt_trig
for each statement execute procedure trigger_notice();
with ins (a) as (
insert into parted2_stmt_trig values (1), (2) returning a
) insert into parted_stmt_trig select a from ins returning tableoid::regclass, a;
NOTICE: trigger trig_ins_before on parted_stmt_trig BEFORE INSERT for STATEMENT
-NOTICE: trigger trig_ins_before on parted2_stmt_trig BEFORE INSERT for STATEMENT-NOTICE: trigger trig_ins_before on parted_stmt_trig1 BEFORE INSERT for ROW-NOTICE: trigger trig_ins_after on parted_stmt_trig1 AFTER INSERT for ROW-NOTICE: trigger trig_ins_after on parted2_stmt_trig AFTER INSERT for STATEMENT+NOTICE: trigger trig_ins_before_3 on parted2_stmt_trig BEFORE INSERT for STATEMENT+NOTICE: trigger trig_ins_before_child on parted_stmt_trig1 BEFORE INSERT for ROW+NOTICE: trigger trig_ins_after_child on parted_stmt_trig1 AFTER INSERT for ROW+NOTICE: trigger trig_ins_after_parent on parted_stmt_trig1 AFTER INSERT for ROW+NOTICE: trigger trig_ins_after_parent on parted_stmt_trig2 AFTER INSERT for ROW+NOTICE: trigger trig_ins_after_3 on parted2_stmt_trig AFTER INSERT for STATEMENT
NOTICE: trigger trig_ins_after on parted_stmt_trig AFTER INSERT for STATEMENT
tableoid | a
-------------------+---
@@ -1926,25 +2011,62 @@ with upd as (
update parted2_stmt_trig set a = a
) update parted_stmt_trig set a = a;
NOTICE: trigger trig_upd_before on parted_stmt_trig BEFORE UPDATE for STATEMENT
-NOTICE: trigger trig_upd_before on parted_stmt_trig1 BEFORE UPDATE for ROW-NOTICE: trigger trig_upd_before on parted2_stmt_trig BEFORE UPDATE for STATEMENT-NOTICE: trigger trig_upd_after on parted_stmt_trig1 AFTER UPDATE for ROW+NOTICE: trigger trig_upd_before_child on parted_stmt_trig1 BEFORE UPDATE for ROW+NOTICE: trigger trig_upd_before_3 on parted2_stmt_trig BEFORE UPDATE for STATEMENT+NOTICE: trigger trig_upd_after_child on parted_stmt_trig1 AFTER UPDATE for ROW+NOTICE: trigger trig_upd_after_parent on parted_stmt_trig1 AFTER UPDATE for ROW+NOTICE: trigger trig_upd_after_parent on parted_stmt_trig2 AFTER UPDATE for ROW
NOTICE: trigger trig_upd_after on parted_stmt_trig AFTER UPDATE for STATEMENT
-NOTICE: trigger trig_upd_after on parted2_stmt_trig AFTER UPDATE for STATEMENT+NOTICE: trigger trig_upd_after_3 on parted2_stmt_trig AFTER UPDATE for STATEMENT
delete from parted_stmt_trig;
NOTICE: trigger trig_del_before on parted_stmt_trig BEFORE DELETE for STATEMENT
+NOTICE: trigger trig_del_before_child on parted_stmt_trig1 BEFORE DELETE for ROW+NOTICE: trigger trig_del_after_parent on parted_stmt_trig2 AFTER DELETE for ROW
NOTICE: trigger trig_del_after on parted_stmt_trig AFTER DELETE for STATEMENT
-- insert via copy on the parent
copy parted_stmt_trig(a) from stdin;
NOTICE: trigger trig_ins_before on parted_stmt_trig BEFORE INSERT for STATEMENT
-NOTICE: trigger trig_ins_before on parted_stmt_trig1 BEFORE INSERT for ROW-NOTICE: trigger trig_ins_after on parted_stmt_trig1 AFTER INSERT for ROW+NOTICE: trigger trig_ins_before_child on parted_stmt_trig1 BEFORE INSERT for ROW+NOTICE: trigger trig_ins_after_child on parted_stmt_trig1 AFTER INSERT for ROW+NOTICE: trigger trig_ins_after_parent on parted_stmt_trig1 AFTER INSERT for ROW+NOTICE: trigger trig_ins_after_parent on parted_stmt_trig2 AFTER INSERT for ROW
NOTICE: trigger trig_ins_after on parted_stmt_trig AFTER INSERT for STATEMENT
-- insert via copy on the first partition
copy parted_stmt_trig1(a) from stdin;
-NOTICE: trigger trig_ins_before on parted_stmt_trig1 BEFORE INSERT for ROW-NOTICE: trigger trig_ins_after on parted_stmt_trig1 AFTER INSERT for ROW+NOTICE: trigger trig_ins_before_child on parted_stmt_trig1 BEFORE INSERT for ROW+NOTICE: trigger trig_ins_after_child on parted_stmt_trig1 AFTER INSERT for ROW+NOTICE: trigger trig_ins_after_parent on parted_stmt_trig1 AFTER INSERT for ROW+-- Disabling a trigger in the parent table should disable children triggers too+alter table parted_stmt_trig disable trigger trig_ins_after_parent;+insert into parted_stmt_trig values (1);+NOTICE: trigger trig_ins_before on parted_stmt_trig BEFORE INSERT for STATEMENT+NOTICE: trigger trig_ins_before_child on parted_stmt_trig1 BEFORE INSERT for ROW+NOTICE: trigger trig_ins_after_child on parted_stmt_trig1 AFTER INSERT for ROW+NOTICE: trigger trig_ins_after on parted_stmt_trig AFTER INSERT for STATEMENT+alter table parted_stmt_trig enable trigger trig_ins_after_parent;+insert into parted_stmt_trig values (1);+NOTICE: trigger trig_ins_before on parted_stmt_trig BEFORE INSERT for STATEMENT+NOTICE: trigger trig_ins_before_child on parted_stmt_trig1 BEFORE INSERT for ROW+NOTICE: trigger trig_ins_after_child on parted_stmt_trig1 AFTER INSERT for ROW+NOTICE: trigger trig_ins_after_parent on parted_stmt_trig1 AFTER INSERT for ROW+NOTICE: trigger trig_ins_after on parted_stmt_trig AFTER INSERT for STATEMENT
drop table parted_stmt_trig, parted2_stmt_trig;
+-- Verify that triggers fire in alphabetical order+create table parted_trig (a int) partition by range (a);+create table parted_trig_1 partition of parted_trig for values from (0) to (1000)+ partition by range (a);+create table parted_trig_1_1 partition of parted_trig_1 for values from (0) to (100);+create trigger zzz after insert on parted_trig for each row execute procedure trigger_notice();+create trigger mmm after insert on parted_trig_1_1 for each row execute procedure trigger_notice();+create trigger aaa after insert on parted_trig_1 for each row execute procedure trigger_notice();+create trigger bbb after insert on parted_trig for each row execute procedure trigger_notice();+create trigger qqq after insert on parted_trig_1_1 for each row execute procedure trigger_notice();+insert into parted_trig values (50);+NOTICE: trigger aaa on parted_trig_1_1 AFTER INSERT for ROW+NOTICE: trigger bbb on parted_trig_1_1 AFTER INSERT for ROW+NOTICE: trigger mmm on parted_trig_1_1 AFTER INSERT for ROW+NOTICE: trigger qqq on parted_trig_1_1 AFTER INSERT for ROW+NOTICE: trigger zzz on parted_trig_1_1 AFTER INSERT for ROW
--
-- Test the interaction between transition tables and both kinds of
-- inheritance. We'll dump the contents of the transition tables in a
diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sqlindex ae8349ccbf..2780918cfe 100644--- a/src/test/regress/sql/triggers.sql+++ b/src/test/regress/sql/triggers.sql@@ -1305,7 +1305,47 @@ drop view my_view;
drop table my_table;
--
--- Verify that per-statement triggers are fired for partitioned tables+-- Verify cases that are unsupported with partitioned tables+--+create table parted_trig (a int) partition by list (a);+create function trigger_nothing() returns trigger+ language plpgsql as $$ begin end; $$;+create trigger failed before insert or update or delete on parted_trig+ for each row execute procedure trigger_nothing();+create trigger failed after update on parted_trig+ for each row when (OLD.a <> NEW.a) execute procedure trigger_nothing();+create trigger failed instead of update on parted_trig+ for each row execute procedure trigger_nothing();+create trigger failed after update on parted_trig+ referencing old table as old_table+ for each statement execute procedure trigger_nothing();+create constraint trigger failed after insert on parted_trig+ for each row execute procedure trigger_nothing();+drop table parted_trig;++--+-- Verify trigger creation for partitioned tables, and drop behavior+--+create table trigpart (a int, b int) partition by range (a);+create table trigpart1 partition of trigpart for values from (0) to (1000);+create trigger f after insert on trigpart for each row execute procedure trigger_nothing();+create table trigpart2 partition of trigpart for values from (1000) to (2000);+create table trigpart3 (like trigpart);+alter table trigpart attach partition trigpart3 for values from (2000) to (3000);+select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger+ where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text;+drop table trigpart2;+select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger+ where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text;+drop trigger f on trigpart; -- ok, all gone+select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger+ where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text;++drop table trigpart;+drop function trigger_nothing();++--+-- Verify that triggers are fired for partitioned tables
--
create table parted_stmt_trig (a int) partition by list (a);
create table parted_stmt_trig1 partition of parted_stmt_trig for values in (1);
@@ -1325,7 +1365,7 @@ create or replace function trigger_notice() returns trigger as $$
end;
$$ language plpgsql;
--- insert/update/delete statment-level triggers on the parent+-- insert/update/delete statement-level triggers on the parent
create trigger trig_ins_before before insert on parted_stmt_trig
for each statement execute procedure trigger_notice();
create trigger trig_ins_after after insert on parted_stmt_trig
@@ -1339,28 +1379,48 @@ create trigger trig_del_before before delete on parted_stmt_trig
create trigger trig_del_after after delete on parted_stmt_trig
for each statement execute procedure trigger_notice();
+-- these cases are disallowed+create trigger trig_ins_before_1 before insert on parted_stmt_trig+ for each row execute procedure trigger_notice();+create trigger trig_upd_before_1 before update on parted_stmt_trig+ for each row execute procedure trigger_notice();+create trigger trig_del_before_1 before delete on parted_stmt_trig+ for each row execute procedure trigger_notice();++-- insert/update/delete row-level triggers on the parent+create trigger trig_ins_after_parent after insert on parted_stmt_trig+ for each row execute procedure trigger_notice();+create trigger trig_upd_after_parent after update on parted_stmt_trig+ for each row execute procedure trigger_notice();+create trigger trig_del_after_parent after delete on parted_stmt_trig+ for each row execute procedure trigger_notice();+
-- insert/update/delete row-level triggers on the first partition
-create trigger trig_ins_before before insert on parted_stmt_trig1+create trigger trig_ins_before_child before insert on parted_stmt_trig1
for each row execute procedure trigger_notice();
-create trigger trig_ins_after after insert on parted_stmt_trig1+create trigger trig_ins_after_child after insert on parted_stmt_trig1
for each row execute procedure trigger_notice();
-create trigger trig_upd_before before update on parted_stmt_trig1+create trigger trig_upd_before_child before update on parted_stmt_trig1
for each row execute procedure trigger_notice();
-create trigger trig_upd_after after update on parted_stmt_trig1+create trigger trig_upd_after_child after update on parted_stmt_trig1+ for each row execute procedure trigger_notice();+create trigger trig_del_before_child before delete on parted_stmt_trig1+ for each row execute procedure trigger_notice();+create trigger trig_del_after_child after delete on parted_stmt_trig1
for each row execute procedure trigger_notice();
-- insert/update/delete statement-level triggers on the parent
-create trigger trig_ins_before before insert on parted2_stmt_trig+create trigger trig_ins_before_3 before insert on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_ins_after after insert on parted2_stmt_trig+create trigger trig_ins_after_3 after insert on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_upd_before before update on parted2_stmt_trig+create trigger trig_upd_before_3 before update on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_upd_after after update on parted2_stmt_trig+create trigger trig_upd_after_3 after update on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_del_before before delete on parted2_stmt_trig+create trigger trig_del_before_3 before delete on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_del_after after delete on parted2_stmt_trig+create trigger trig_del_after_3 after delete on parted2_stmt_trig
for each statement execute procedure trigger_notice();
with ins (a) as (
@@ -1384,8 +1444,26 @@ copy parted_stmt_trig1(a) from stdin;
1
\.
+-- Disabling a trigger in the parent table should disable children triggers too+alter table parted_stmt_trig disable trigger trig_ins_after_parent;+insert into parted_stmt_trig values (1);+alter table parted_stmt_trig enable trigger trig_ins_after_parent;+insert into parted_stmt_trig values (1);+
drop table parted_stmt_trig, parted2_stmt_trig;
+-- Verify that triggers fire in alphabetical order+create table parted_trig (a int) partition by range (a);+create table parted_trig_1 partition of parted_trig for values from (0) to (1000)+ partition by range (a);+create table parted_trig_1_1 partition of parted_trig_1 for values from (0) to (100);+create trigger zzz after insert on parted_trig for each row execute procedure trigger_notice();+create trigger mmm after insert on parted_trig_1_1 for each row execute procedure trigger_notice();+create trigger aaa after insert on parted_trig_1 for each row execute procedure trigger_notice();+create trigger bbb after insert on parted_trig for each row execute procedure trigger_notice();+create trigger qqq after insert on parted_trig_1_1 for each row execute procedure trigger_notice();+insert into parted_trig values (50);+
--
-- Test the interaction between transition tables and both kinds of
-- inheritance. We'll dump the contents of the transition tables in a
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: alvherre@alvh.no-ip.org, robertmhaas@gmail.com, peter.eisentraut@2ndquadrant.com, simon@2ndquadrant.com
Subject: Re: FOR EACH ROW triggers on partitioned tables
In-Reply-To: <20180222235212.qcdznloyxepn2be3@alvherre.pgsql>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox