From e180c8aa8caf5c55a273d4a8e6092e77ff3cff10 Mon Sep 17 00:00:00 2001 From: Robert Haas Date: Mon, 1 May 2017 08:23:01 -0400 Subject: [PATCH] Fire per-statement triggers on partitioned tables. Even though no actual tuples are ever inserted into a partitioned table (the actual tuples are in the partitions, not the partitioned table itself), we still need to have a ResultRelInfo for the partitioned table, or per-statement triggers won't get fired. Amit Langote, per a report from Rajkumar Raghuwanshi. Reviewed by me. Discussion: http://postgr.es/m/CAKcux6%3DwYospCRY2J4XEFuVy0L41S%3Dfic7rmkbsU-GXhhSbmBg%40mail.gmail.com --- doc/src/sgml/trigger.sgml | 19 +++--- src/backend/executor/execMain.c | 55 +++++++++++++++-- src/backend/executor/nodeModifyTable.c | 42 ++++++++++--- 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, 296 insertions(+), 27 deletions(-) diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml index 2a718d7f47..6f8416dda7 100644 --- a/doc/src/sgml/trigger.sgml +++ b/doc/src/sgml/trigger.sgml @@ -33,7 +33,8 @@ 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. @@ -111,14 +112,14 @@ Statement-level BEFORE triggers naturally fire before the statement starts to do anything, while statement-level AFTER triggers fire at the very end of the statement. These types of - triggers may be defined on tables or views. Row-level BEFORE - triggers fire immediately before a particular row is operated on, - while row-level AFTER triggers fire at the end of the - statement (but before any statement-level AFTER triggers). - These types of triggers may only be defined on tables and foreign tables. - Row-level 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 + BEFORE triggers fire immediately before a particular row is + operated on, while row-level AFTER triggers fire at the end of + the statement (but before any statement-level AFTER triggers). + These types of triggers may only be defined on non-partitioned tables and + foreign tables. Row-level 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. 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..652cd97599 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -1328,19 +1328,29 @@ ExecOnConflictUpdate(ModifyTableState *mtstate, static void fireBSTriggers(ModifyTableState *node) { + ResultRelInfo *resultRelInfo = node->resultRelInfo; + + /* + * If the node modifies a partitioned table, we must fire its triggers. + * Note that in that case, node->resultRelInfo points to the first leaf + * partition, not the root table. + */ + if (node->rootResultRelInfo != NULL) + resultRelInfo = node->rootResultRelInfo; + 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); + 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 +1364,29 @@ fireBSTriggers(ModifyTableState *node) static void fireASTriggers(ModifyTableState *node) { + ResultRelInfo *resultRelInfo = node->resultRelInfo; + + /* + * If the node modifies a partitioned table, we must fire its triggers. + * Note that in that case, node->resultRelInfo points to the first leaf + * partition, not the root table. + */ + if (node->rootResultRelInfo != NULL) + resultRelInfo = node->rootResultRelInfo; + switch (node->operation) { case CMD_INSERT: if (node->mt_onconflict == ONCONFLICT_UPDATE) ExecASUpdateTriggers(node->ps.state, - node->resultRelInfo); - ExecASInsertTriggers(node->ps.state, node->resultRelInfo); + 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 +1672,12 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) mtstate->mt_plans = (PlanState **) palloc0(sizeof(PlanState *) * nplans); mtstate->resultRelInfo = estate->es_result_relations + node->resultRelIndex; + + /* If modifying a partitioned table, initialize the root table info */ + if (node->rootResultRelIndex >= 0) + 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 8fb872d288..35a237a000 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 05a78b32b7..98f67681a7 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..c192dc4f70 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 the executor initialization. */ - root->glob->nonleafResultRelations = - list_concat(root->glob->nonleafResultRelations, - list_copy(splan->partitioned_rels)); + if (splan->partitioned_rels != NIL) + { + root->glob->nonleafResultRelations = + list_concat(root->glob->nonleafResultRelations, + list_copy(splan->partitioned_rels)); + /* Remember where this root will be in the global list. */ + splan->rootResultRelIndex = + list_length(root->glob->rootResultRelations); + 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.40.0