Thanks for taking a look. On 2017/04/28 5:24, Robert Haas wrote: > On Wed, Apr 26, 2017 at 9:30 PM, Amit Langote > <langote_amit...@lab.ntt.co.jp> wrote: >>> Do we need to update the documentation? >> >> Yes, I think we should. How about as in the attached? > > Looks reasonable, but I was thinking you might also update the section > which contrasts inheritance-based partitioning with declarative > partitioning.
It seems to me that there is no difference in behavior between inheritance-based and declarative partitioning as far as statement-level triggers are concerned (at least currently). In both the cases, we fire statement-level triggers only for the table specified in the command. Maybe, we will fix things someday so that statement-level triggers will be fired for all the tables in a inheritance hierarchy when the root parent is updated or deleted, but that's currently not true. We may never implement that behavior for declarative partitioned tables though, so there will be a difference if and when we implement the former. Am I missing something? >> By the way, code changes I made in the attached are such that a subsequent >> patch could implement firing statement-level triggers of all the tables in >> a partition hierarchy, which it seems we don't want to do. Should then >> the code be changed to not create ResultRelInfos of all the tables but >> only the root table (the one mentioned in the command)? You will see that >> the patch adds fields named es_nonleaf_result_relations and >> es_num_nonleaf_result_relations, whereas just es_root_result_relation >> would perhaps do, for example. > > It seems better not to create any ResultRelInfos that we don't > actually need, so +1 for such a revision to the patch. OK, done. It took a bit more work than I thought. Updated patch attached. Thanks, Amit
>From a8b584f09bc02b6c16c33e816fc12c7e443dd65c Mon Sep 17 00:00:00 2001 From: amit <amitlangot...@gmail.com> Date: Mon, 24 Apr 2017 14:55:08 +0900 Subject: [PATCH] Fire per-statement triggers of partitioned tables Current implementation completely misses them, because it assumes that no ResultRelInfos need to be created for partitioned tables, whereas they *are* needed to fire the per-statment triggers, which we *do* allow to be defined on the partitioned tables. Reported By: Rajkumar Raghuwanshi Patch by: Amit Langote Report: https://www.postgresql.org/message-id/CAKcux6%3DwYospCRY2J4XEFuVy0L41S%3Dfic7rmkbsU-GXhhSbmBg%40mail.gmail.com --- doc/src/sgml/trigger.sgml | 26 +++++++---- src/backend/executor/execMain.c | 55 ++++++++++++++++++++-- src/backend/executor/nodeModifyTable.c | 64 ++++++++++++++++++++++---- src/backend/nodes/copyfuncs.c | 2 + src/backend/nodes/outfuncs.c | 3 ++ src/backend/nodes/readfuncs.c | 2 + src/backend/optimizer/plan/createplan.c | 1 + src/backend/optimizer/plan/planner.c | 3 ++ src/backend/optimizer/plan/setrefs.c | 19 ++++++-- src/include/nodes/execnodes.h | 12 +++++ src/include/nodes/plannodes.h | 13 +++++- src/include/nodes/relation.h | 1 + src/test/regress/expected/triggers.out | 81 +++++++++++++++++++++++++++++++++ src/test/regress/sql/triggers.sql | 70 ++++++++++++++++++++++++++++ 14 files changed, 323 insertions(+), 29 deletions(-) diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml index 2a718d7f47..5771bd5fdc 100644 --- a/doc/src/sgml/trigger.sgml +++ b/doc/src/sgml/trigger.sgml @@ -33,7 +33,8 @@ <para> A trigger is a specification that the database should automatically execute a particular function whenever a certain type of operation is - performed. Triggers can be attached to tables, views, and foreign tables. + performed. Triggers can be attached to tables (partitioned or not), + views, and foreign tables. </para> <para> @@ -111,14 +112,21 @@ Statement-level <literal>BEFORE</> triggers naturally fire before the statement starts to do anything, while statement-level <literal>AFTER</> triggers fire at the very end of the statement. These types of - triggers may be defined on tables or views. Row-level <literal>BEFORE</> - triggers fire immediately before a particular row is operated on, - while row-level <literal>AFTER</> triggers fire at the end of the - statement (but before any statement-level <literal>AFTER</> triggers). - These types of triggers may only be defined on tables and foreign tables. - Row-level <literal>INSTEAD OF</> triggers may only be defined on views, - and fire immediately as each row in the view is identified as needing to - be operated on. + triggers may be defined on tables, views, or foreign tables. Row-level + <literal>BEFORE</> triggers fire immediately before a particular row is + operated on, while row-level <literal>AFTER</> triggers fire at the end of + the statement (but before any statement-level <literal>AFTER</> triggers). + These types of triggers may only be defined on non-partitioned tables and + foreign tables. Row-level <literal>INSTEAD OF</> triggers may only be + defined on views, and fire immediately as each row in the view is + identified as needing to be operated on. + </para> + + <para> + A statement-level trigger defined on partitioned tables is fired only + once for the table itself, not once for every table in the partitioning + hierarchy. However, row-level triggers of any affected leaf partitions + will be fired. </para> <para> diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c index 5c12fb457d..cdb1a6a5f5 100644 --- a/src/backend/executor/execMain.c +++ b/src/backend/executor/execMain.c @@ -861,17 +861,52 @@ InitPlan(QueryDesc *queryDesc, int eflags) /* * In the partitioned result relation case, lock the non-leaf result - * relations too. We don't however need ResultRelInfos for them. + * relations too. A subset of these are the roots of respective + * partitioned tables, for which we also allocate ResulRelInfos. */ + estate->es_root_result_relations = NULL; + estate->es_num_root_result_relations = 0; if (plannedstmt->nonleafResultRelations) { + int num_roots = list_length(plannedstmt->rootResultRelations); + + /* + * Firstly, build ResultRelInfos for all the partitioned table + * roots, because we will need them to fire the statement-level + * triggers, if any. + */ + resultRelInfos = (ResultRelInfo *) + palloc(num_roots * sizeof(ResultRelInfo)); + resultRelInfo = resultRelInfos; + foreach(l, plannedstmt->rootResultRelations) + { + Index resultRelIndex = lfirst_int(l); + Oid resultRelOid; + Relation resultRelDesc; + + resultRelOid = getrelid(resultRelIndex, rangeTable); + resultRelDesc = heap_open(resultRelOid, RowExclusiveLock); + InitResultRelInfo(resultRelInfo, + resultRelDesc, + lfirst_int(l), + NULL, + estate->es_instrument); + resultRelInfo++; + } + + estate->es_root_result_relations = resultRelInfos; + estate->es_num_root_result_relations = num_roots; + + /* Simply lock the rest of them. */ foreach(l, plannedstmt->nonleafResultRelations) { - Index resultRelationIndex = lfirst_int(l); - Oid resultRelationOid; + Index resultRelIndex = lfirst_int(l); - resultRelationOid = getrelid(resultRelationIndex, rangeTable); - LockRelationOid(resultRelationOid, RowExclusiveLock); + /* We locked the roots above. */ + if (!list_member_int(plannedstmt->rootResultRelations, + resultRelIndex)) + LockRelationOid(getrelid(resultRelIndex, rangeTable), + RowExclusiveLock); } } } @@ -883,6 +918,8 @@ InitPlan(QueryDesc *queryDesc, int eflags) estate->es_result_relations = NULL; estate->es_num_result_relations = 0; estate->es_result_relation_info = NULL; + estate->es_root_result_relations = NULL; + estate->es_num_root_result_relations = 0; } /* @@ -1565,6 +1602,14 @@ ExecEndPlan(PlanState *planstate, EState *estate) resultRelInfo++; } + /* Close the root target relation(s). */ + resultRelInfo = estate->es_root_result_relations; + for (i = estate->es_num_root_result_relations; i > 0; i--) + { + heap_close(resultRelInfo->ri_RelationDesc, NoLock); + resultRelInfo++; + } + /* * likewise close any trigger target relations */ diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c index 71e3b8ec2d..654e777c67 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -62,6 +62,8 @@ static bool ExecOnConflictUpdate(ModifyTableState *mtstate, EState *estate, bool canSetTag, TupleTableSlot **returning); +static void fireBSTriggersFor(ModifyTableState *node, ResultRelInfo *resultRelInfo); +static void fireASTriggersFor(ModifyTableState *node, ResultRelInfo *resultRelInfo); /* * Verify that the tuples to be produced by INSERT or UPDATE match the @@ -1328,19 +1330,39 @@ ExecOnConflictUpdate(ModifyTableState *mtstate, static void fireBSTriggers(ModifyTableState *node) { + /* Fire for the root partitioned table, if any, and return. */ + if (node->rootResultRelInfo) + { + fireBSTriggersFor(node, node->rootResultRelInfo); + return; + } + + /* + * Fire for the main result relation. In the partitioned table case, + * the following happens to be the first leaf partition, which we don't + * need to fire triggers for. + */ + fireBSTriggersFor(node, node->resultRelInfo); +} + +/* + * Process BEFORE EACH STATEMENT triggers for one result relation + */ +static void +fireBSTriggersFor(ModifyTableState *node, ResultRelInfo *resultRelInfo) +{ switch (node->operation) { case CMD_INSERT: - ExecBSInsertTriggers(node->ps.state, node->resultRelInfo); + ExecBSInsertTriggers(node->ps.state, resultRelInfo); if (node->mt_onconflict == ONCONFLICT_UPDATE) - ExecBSUpdateTriggers(node->ps.state, - node->resultRelInfo); + ExecBSUpdateTriggers(node->ps.state, resultRelInfo); break; case CMD_UPDATE: - ExecBSUpdateTriggers(node->ps.state, node->resultRelInfo); + ExecBSUpdateTriggers(node->ps.state, resultRelInfo); break; case CMD_DELETE: - ExecBSDeleteTriggers(node->ps.state, node->resultRelInfo); + ExecBSDeleteTriggers(node->ps.state, resultRelInfo); break; default: elog(ERROR, "unknown operation"); @@ -1354,19 +1376,39 @@ fireBSTriggers(ModifyTableState *node) static void fireASTriggers(ModifyTableState *node) { + /* Fire for the root partitioned table, if any, and return. */ + if (node->rootResultRelInfo) + { + fireASTriggersFor(node, node->rootResultRelInfo); + return; + } + + /* + * Fire for the main result relation. In the partitioned table case, + * the following happens to be the first leaf partition, which we don't + * need to fire triggers for. + */ + fireASTriggersFor(node, node->resultRelInfo); +} + +/* + * Process AFTER EACH STATEMENT triggers for one result relation + */ +static void +fireASTriggersFor(ModifyTableState *node, ResultRelInfo *resultRelInfo) +{ switch (node->operation) { case CMD_INSERT: if (node->mt_onconflict == ONCONFLICT_UPDATE) - ExecASUpdateTriggers(node->ps.state, - node->resultRelInfo); - ExecASInsertTriggers(node->ps.state, node->resultRelInfo); + ExecASUpdateTriggers(node->ps.state, resultRelInfo); + ExecASInsertTriggers(node->ps.state, resultRelInfo); break; case CMD_UPDATE: - ExecASUpdateTriggers(node->ps.state, node->resultRelInfo); + ExecASUpdateTriggers(node->ps.state, resultRelInfo); break; case CMD_DELETE: - ExecASDeleteTriggers(node->ps.state, node->resultRelInfo); + ExecASDeleteTriggers(node->ps.state, resultRelInfo); break; default: elog(ERROR, "unknown operation"); @@ -1652,6 +1694,8 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) mtstate->mt_plans = (PlanState **) palloc0(sizeof(PlanState *) * nplans); mtstate->resultRelInfo = estate->es_result_relations + node->resultRelIndex; + mtstate->rootResultRelInfo = estate->es_root_result_relations + + node->rootResultRelIndex; mtstate->mt_arowmarks = (List **) palloc0(sizeof(List *) * nplans); mtstate->mt_nplans = nplans; mtstate->mt_onconflict = node->onConflictAction; diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 00a0fed23d..b58ea83065 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -91,6 +91,7 @@ _copyPlannedStmt(const PlannedStmt *from) COPY_NODE_FIELD(rtable); COPY_NODE_FIELD(resultRelations); COPY_NODE_FIELD(nonleafResultRelations); + COPY_NODE_FIELD(rootResultRelations); COPY_NODE_FIELD(subplans); COPY_BITMAPSET_FIELD(rewindPlanIDs); COPY_NODE_FIELD(rowMarks); @@ -205,6 +206,7 @@ _copyModifyTable(const ModifyTable *from) COPY_NODE_FIELD(partitioned_rels); COPY_NODE_FIELD(resultRelations); COPY_SCALAR_FIELD(resultRelIndex); + COPY_SCALAR_FIELD(rootResultRelIndex); COPY_NODE_FIELD(plans); COPY_NODE_FIELD(withCheckOptionLists); COPY_NODE_FIELD(returningLists); diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index 28cef85579..01cc4f3f47 100644 --- a/src/backend/nodes/outfuncs.c +++ b/src/backend/nodes/outfuncs.c @@ -253,6 +253,7 @@ _outPlannedStmt(StringInfo str, const PlannedStmt *node) WRITE_NODE_FIELD(rtable); WRITE_NODE_FIELD(resultRelations); WRITE_NODE_FIELD(nonleafResultRelations); + WRITE_NODE_FIELD(rootResultRelations); WRITE_NODE_FIELD(subplans); WRITE_BITMAPSET_FIELD(rewindPlanIDs); WRITE_NODE_FIELD(rowMarks); @@ -350,6 +351,7 @@ _outModifyTable(StringInfo str, const ModifyTable *node) WRITE_NODE_FIELD(partitioned_rels); WRITE_NODE_FIELD(resultRelations); WRITE_INT_FIELD(resultRelIndex); + WRITE_INT_FIELD(rootResultRelIndex); WRITE_NODE_FIELD(plans); WRITE_NODE_FIELD(withCheckOptionLists); WRITE_NODE_FIELD(returningLists); @@ -2145,6 +2147,7 @@ _outPlannerGlobal(StringInfo str, const PlannerGlobal *node) WRITE_NODE_FIELD(finalrowmarks); WRITE_NODE_FIELD(resultRelations); WRITE_NODE_FIELD(nonleafResultRelations); + WRITE_NODE_FIELD(rootResultRelations); WRITE_NODE_FIELD(relationOids); WRITE_NODE_FIELD(invalItems); WRITE_INT_FIELD(nParamExec); diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c index a883220a49..f9a227e237 100644 --- a/src/backend/nodes/readfuncs.c +++ b/src/backend/nodes/readfuncs.c @@ -1453,6 +1453,7 @@ _readPlannedStmt(void) READ_NODE_FIELD(rtable); READ_NODE_FIELD(resultRelations); READ_NODE_FIELD(nonleafResultRelations); + READ_NODE_FIELD(rootResultRelations); READ_NODE_FIELD(subplans); READ_BITMAPSET_FIELD(rewindPlanIDs); READ_NODE_FIELD(rowMarks); @@ -1548,6 +1549,7 @@ _readModifyTable(void) READ_NODE_FIELD(partitioned_rels); READ_NODE_FIELD(resultRelations); READ_INT_FIELD(resultRelIndex); + READ_INT_FIELD(rootResultRelIndex); READ_NODE_FIELD(plans); READ_NODE_FIELD(withCheckOptionLists); READ_NODE_FIELD(returningLists); diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index 95e6eb7d28..52daf43c81 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -6437,6 +6437,7 @@ make_modifytable(PlannerInfo *root, node->partitioned_rels = partitioned_rels; node->resultRelations = resultRelations; node->resultRelIndex = -1; /* will be set correctly in setrefs.c */ + node->rootResultRelIndex = -1; /* will be set correctly in setrefs.c */ node->plans = subplans; if (!onconflict) { diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c index 649a233e11..c4a5651abd 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -240,6 +240,7 @@ standard_planner(Query *parse, int cursorOptions, ParamListInfo boundParams) glob->finalrowmarks = NIL; glob->resultRelations = NIL; glob->nonleafResultRelations = NIL; + glob->rootResultRelations = NIL; glob->relationOids = NIL; glob->invalItems = NIL; glob->nParamExec = 0; @@ -408,6 +409,7 @@ standard_planner(Query *parse, int cursorOptions, ParamListInfo boundParams) Assert(glob->finalrowmarks == NIL); Assert(glob->resultRelations == NIL); Assert(glob->nonleafResultRelations == NIL); + Assert(glob->rootResultRelations == NIL); top_plan = set_plan_references(root, top_plan); /* ... and the subplans (both regular subplans and initplans) */ Assert(list_length(glob->subplans) == list_length(glob->subroots)); @@ -434,6 +436,7 @@ standard_planner(Query *parse, int cursorOptions, ParamListInfo boundParams) result->rtable = glob->finalrtable; result->resultRelations = glob->resultRelations; result->nonleafResultRelations = glob->nonleafResultRelations; + result->rootResultRelations = glob->rootResultRelations; result->subplans = glob->subplans; result->rewindPlanIDs = glob->rewindPlanIDs; result->rowMarks = glob->finalrowmarks; diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c index 1278371b65..edf00a6408 100644 --- a/src/backend/optimizer/plan/setrefs.c +++ b/src/backend/optimizer/plan/setrefs.c @@ -882,11 +882,22 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset) /* * If the main target relation is a partitioned table, the * following list contains the RT indexes of partitioned child - * relations, which are not included in the above list. + * relations including the root, which are not included in the + * above list. We also keep RT indexes of the roots separately + * to be identitied as such during executor initialization. + * First, remember where this root will be in the global list. */ - root->glob->nonleafResultRelations = - list_concat(root->glob->nonleafResultRelations, - list_copy(splan->partitioned_rels)); + splan->rootResultRelIndex = + list_length(root->glob->rootResultRelations); + if (splan->partitioned_rels != NIL) + { + root->glob->nonleafResultRelations = + list_concat(root->glob->nonleafResultRelations, + list_copy(splan->partitioned_rels)); + root->glob->rootResultRelations = + lappend_int(root->glob->rootResultRelations, + linitial_int(splan->partitioned_rels)); + } } break; case T_Append: diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index 4330a851c3..f289f3c3c2 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -422,6 +422,16 @@ typedef struct EState int es_num_result_relations; /* length of array */ ResultRelInfo *es_result_relation_info; /* currently active array elt */ + /* + * Info about the target partitioned target table root(s) for + * update/delete queries. They required only to fire any per-statement + * triggers defined on the table. It exists separately from + * es_result_relations, because partitioned tables don't appear in the + * plan tree for the update/delete cases. + */ + ResultRelInfo *es_root_result_relations; /* array of ResultRelInfos */ + int es_num_root_result_relations; /* length of the array */ + /* Stuff used for firing triggers: */ List *es_trig_target_relations; /* trigger-only ResultRelInfos */ TupleTableSlot *es_trig_tuple_slot; /* for trigger output tuples */ @@ -914,6 +924,8 @@ typedef struct ModifyTableState int mt_nplans; /* number of plans in the array */ int mt_whichplan; /* which one is being executed (0..n-1) */ ResultRelInfo *resultRelInfo; /* per-subplan target relations */ + ResultRelInfo *rootResultRelInfo; /* root target relation (partitioned + * table root) */ List **mt_arowmarks; /* per-subplan ExecAuxRowMark lists */ EPQState mt_epqstate; /* for evaluating EvalPlanQual rechecks */ bool fireBSTriggers; /* do we need to fire stmt triggers? */ diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h index cba915572e..164105a3a9 100644 --- a/src/include/nodes/plannodes.h +++ b/src/include/nodes/plannodes.h @@ -65,9 +65,19 @@ typedef struct PlannedStmt /* rtable indexes of target relations for INSERT/UPDATE/DELETE */ List *resultRelations; /* integer list of RT indexes, or NIL */ - /* rtable indexes of non-leaf target relations for INSERT/UPDATE/DELETE */ + /* + * rtable indexes of non-leaf target relations for UPDATE/DELETE on + * all the partitioned table mentioned in the query. + */ List *nonleafResultRelations; + /* + * rtable indexes of root target relations for UPDATE/DELETE; this list + * maintains a subset of the RT indexes in nonleafResultRelations, + * indicating the roots of the respective partition hierarchies. + */ + List *rootResultRelations; + List *subplans; /* Plan trees for SubPlan expressions; note * that some could be NULL */ @@ -211,6 +221,7 @@ typedef struct ModifyTable List *partitioned_rels; List *resultRelations; /* integer list of RT indexes */ int resultRelIndex; /* index of first resultRel in plan's list */ + int rootResultRelIndex; /* index of the partitioned table root */ List *plans; /* plan(s) producing source data */ List *withCheckOptionLists; /* per-target-table WCO lists */ List *returningLists; /* per-target-table RETURNING tlists */ diff --git a/src/include/nodes/relation.h b/src/include/nodes/relation.h index 7a8e2fd2b8..adbd3dd556 100644 --- a/src/include/nodes/relation.h +++ b/src/include/nodes/relation.h @@ -108,6 +108,7 @@ typedef struct PlannerGlobal List *resultRelations; /* "flat" list of integer RT indexes */ List *nonleafResultRelations; /* "flat" list of integer RT indexes */ + List *rootResultRelations; /* "flat" list of integer RT indexes */ List *relationOids; /* OIDs of relations the plan depends on */ diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out index 4b0b3b7c42..10a301310b 100644 --- a/src/test/regress/expected/triggers.out +++ b/src/test/regress/expected/triggers.out @@ -1787,3 +1787,84 @@ create trigger my_trigger after update on my_table_42 referencing old table as o drop trigger my_trigger on my_table_42; drop table my_table_42; drop table my_table; +-- +-- Verify that per-statement 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); +create table parted_stmt_trig2 partition of parted_stmt_trig for values in (2); +create table parted2_stmt_trig (a int) partition by list (a); +create table parted2_stmt_trig1 partition of parted2_stmt_trig for values in (1); +create table parted2_stmt_trig2 partition of parted2_stmt_trig for values in (2); +create or replace function trigger_notice() returns trigger as $$ + begin + raise notice 'trigger on % % % for %', TG_TABLE_NAME, TG_WHEN, TG_OP, TG_LEVEL; + if TG_LEVEL = 'ROW' then + return NEW; + end if; + return null; + end; + $$ language plpgsql; +-- insert/update/delete statment-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 + for each statement execute procedure trigger_notice(); +create trigger trig_upd_before before update on parted_stmt_trig + for each statement execute procedure trigger_notice(); +create trigger trig_upd_after after update on parted_stmt_trig + for each statement execute procedure trigger_notice(); +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(); +-- insert/update/delete row-level triggers on the first partition +create trigger trig_ins_before before insert on parted_stmt_trig1 + for each row execute procedure trigger_notice(); +create trigger trig_ins_after after insert on parted_stmt_trig1 + for each row execute procedure trigger_notice(); +create trigger trig_upd_before before update on parted_stmt_trig1 + for each row execute procedure trigger_notice(); +create trigger trig_upd_after after update 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 + for each statement execute procedure trigger_notice(); +create trigger trig_ins_after after insert on parted2_stmt_trig + for each statement execute procedure trigger_notice(); +create trigger trig_upd_before before update on parted2_stmt_trig + for each statement execute procedure trigger_notice(); +create trigger trig_upd_after after update on parted2_stmt_trig + for each statement execute procedure trigger_notice(); +create trigger trig_del_before before delete on parted2_stmt_trig + for each statement execute procedure trigger_notice(); +create trigger trig_del_after 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 on parted_stmt_trig BEFORE INSERT for STATEMENT +NOTICE: trigger on parted2_stmt_trig BEFORE INSERT for STATEMENT +NOTICE: trigger on parted_stmt_trig1 BEFORE INSERT for ROW +NOTICE: trigger on parted_stmt_trig1 AFTER INSERT for ROW +NOTICE: trigger on parted2_stmt_trig AFTER INSERT for STATEMENT +NOTICE: trigger on parted_stmt_trig AFTER INSERT for STATEMENT + tableoid | a +-------------------+--- + parted_stmt_trig1 | 1 + parted_stmt_trig2 | 2 +(2 rows) + +with upd as ( + update parted2_stmt_trig set a = a +) update parted_stmt_trig set a = a; +NOTICE: trigger on parted_stmt_trig BEFORE UPDATE for STATEMENT +NOTICE: trigger on parted_stmt_trig1 BEFORE UPDATE for ROW +NOTICE: trigger on parted2_stmt_trig BEFORE UPDATE for STATEMENT +NOTICE: trigger on parted_stmt_trig1 AFTER UPDATE for ROW +NOTICE: trigger on parted_stmt_trig AFTER UPDATE for STATEMENT +NOTICE: trigger on parted2_stmt_trig AFTER UPDATE for STATEMENT +delete from parted_stmt_trig; +NOTICE: trigger on parted_stmt_trig BEFORE DELETE for STATEMENT +NOTICE: trigger on parted_stmt_trig AFTER DELETE for STATEMENT +drop table parted_stmt_trig, parted2_stmt_trig; diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql index 4473ce0518..84b5ada554 100644 --- a/src/test/regress/sql/triggers.sql +++ b/src/test/regress/sql/triggers.sql @@ -1263,3 +1263,73 @@ create trigger my_trigger after update on my_table_42 referencing old table as o drop trigger my_trigger on my_table_42; drop table my_table_42; drop table my_table; + +-- +-- Verify that per-statement 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); +create table parted_stmt_trig2 partition of parted_stmt_trig for values in (2); + +create table parted2_stmt_trig (a int) partition by list (a); +create table parted2_stmt_trig1 partition of parted2_stmt_trig for values in (1); +create table parted2_stmt_trig2 partition of parted2_stmt_trig for values in (2); + +create or replace function trigger_notice() returns trigger as $$ + begin + raise notice 'trigger on % % % for %', TG_TABLE_NAME, TG_WHEN, TG_OP, TG_LEVEL; + if TG_LEVEL = 'ROW' then + return NEW; + end if; + return null; + end; + $$ language plpgsql; + +-- insert/update/delete statment-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 + for each statement execute procedure trigger_notice(); +create trigger trig_upd_before before update on parted_stmt_trig + for each statement execute procedure trigger_notice(); +create trigger trig_upd_after after update on parted_stmt_trig + for each statement execute procedure trigger_notice(); +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(); + +-- insert/update/delete row-level triggers on the first partition +create trigger trig_ins_before before insert on parted_stmt_trig1 + for each row execute procedure trigger_notice(); +create trigger trig_ins_after after insert on parted_stmt_trig1 + for each row execute procedure trigger_notice(); +create trigger trig_upd_before before update on parted_stmt_trig1 + for each row execute procedure trigger_notice(); +create trigger trig_upd_after after update 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 + for each statement execute procedure trigger_notice(); +create trigger trig_ins_after after insert on parted2_stmt_trig + for each statement execute procedure trigger_notice(); +create trigger trig_upd_before before update on parted2_stmt_trig + for each statement execute procedure trigger_notice(); +create trigger trig_upd_after after update on parted2_stmt_trig + for each statement execute procedure trigger_notice(); +create trigger trig_del_before before delete on parted2_stmt_trig + for each statement execute procedure trigger_notice(); +create trigger trig_del_after 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; + +with upd as ( + update parted2_stmt_trig set a = a +) update parted_stmt_trig set a = a; + +delete from parted_stmt_trig; +drop table parted_stmt_trig, parted2_stmt_trig; -- 2.11.0
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
