From f69acdb660e391302caf9cfe4086a4ae7f4122b2 Mon Sep 17 00:00:00 2001 From: amit Date: Mon, 24 Apr 2017 13:57:50 +0900 Subject: [PATCH 2/2] Fire per-statement triggers of inheritance child tables There has been a long-standing decision to not fire the child table's per-statement triggers even though update/delete on inheritance hierarchies (including the partitioning case) *do* affect the child tables along with the parent table mentioned in the statement. --- src/backend/executor/nodeModifyTable.c | 32 ++++++++++++++++------------- src/test/regress/expected/triggers.out | 37 ++++++++++++++++++++++++++++++++++ src/test/regress/sql/triggers.sql | 29 ++++++++++++++++++++++++++ 3 files changed, 84 insertions(+), 14 deletions(-) diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c index 6cab15646f..cbaf402e62 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -1330,19 +1330,21 @@ ExecOnConflictUpdate(ModifyTableState *mtstate, static void fireBSTriggers(ModifyTableState *node) { - /* Fire for the root partitioned table, if any, and return. */ + int i; + + /* Fire for the non-leaf targets, including the root partitioned table */ if (node->nonleafResultRelInfo) { - fireBSTriggersFor(node, node->nonleafResultRelInfo); - return; + for (i = 0; i < node->mt_numnonleafrels; i++) + fireBSTriggersFor(node, node->nonleafResultRelInfo + i); } /* - * 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. + * Fire for the leaf targets. In the non-partitioned inheritance case, + * this includes all the tables in the hierarchy. */ - fireBSTriggersFor(node, node->resultRelInfo); + for (i = 0; i < node->mt_nplans; i++) + fireBSTriggersFor(node, node->resultRelInfo + i); } /* @@ -1376,19 +1378,21 @@ fireBSTriggersFor(ModifyTableState *node, ResultRelInfo *resultRelInfo) static void fireASTriggers(ModifyTableState *node) { - /* Fire for the root partitioned table, if any, and return. */ + int i; + + /* Fire for the non-leaf targets, including the root partitioned table */ if (node->nonleafResultRelInfo) { - fireASTriggersFor(node, node->nonleafResultRelInfo); - return; + for (i = 0; i < node->mt_numnonleafrels; i++) + fireASTriggersFor(node, node->nonleafResultRelInfo + i); } /* - * 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. + * Fire for the leaf targets. In the non-partitioned inheritance case, + * this includes all the tables in the hierarchy. */ - fireASTriggersFor(node, node->resultRelInfo); + for (i = 0; i < node->mt_nplans; i++) + fireASTriggersFor(node, node->resultRelInfo + i); } /* diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out index ec0e0c2782..df2726c497 100644 --- a/src/test/regress/expected/triggers.out +++ b/src/test/regress/expected/triggers.out @@ -1793,6 +1793,8 @@ drop table my_table; 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 regular_stmt_trig (a int); +create table regular_stmt_trig_child () inherits (regular_stmt_trig); create or replace function trigger_notice() returns trigger as $$ begin raise notice 'trigger on % % %', TG_TABLE_NAME, TG_WHEN, TG_OP; return null; end; $$ language plpgsql; @@ -1822,6 +1824,24 @@ create trigger trig_del_before before delete on parted_stmt_trig1 for each statement execute procedure trigger_notice(); create trigger trig_del_after after delete on parted_stmt_trig1 for each statement execute procedure trigger_notice(); +-- update/delete triggers on the regular inheritance parent +create trigger trig_upd_before before update on regular_stmt_trig + for each statement execute procedure trigger_notice(); +create trigger trig_upd_after after update on regular_stmt_trig + for each statement execute procedure trigger_notice(); +create trigger trig_del_before before delete on regular_stmt_trig + for each statement execute procedure trigger_notice(); +create trigger trig_del_after after delete on regular_stmt_trig + for each statement execute procedure trigger_notice(); +-- update/delete triggers on the regular inheritance child +create trigger trig_upd_before before update on regular_stmt_trig_child + for each statement execute procedure trigger_notice(); +create trigger trig_upd_after after update on regular_stmt_trig_child + for each statement execute procedure trigger_notice(); +create trigger trig_del_before before delete on regular_stmt_trig_child + for each statement execute procedure trigger_notice(); +create trigger trig_del_after after delete on regular_stmt_trig_child + for each statement execute procedure trigger_notice(); with ins (partname, a) as ( insert into parted_stmt_trig values (1) returning tableoid::regclass, a ) insert into parted_stmt_trig select a+1 from ins returning tableoid::regclass, a; @@ -1836,8 +1856,25 @@ NOTICE: trigger on parted_stmt_trig AFTER INSERT update parted_stmt_trig set a = a; NOTICE: trigger on parted_stmt_trig BEFORE UPDATE +NOTICE: trigger on parted_stmt_trig1 BEFORE UPDATE NOTICE: trigger on parted_stmt_trig AFTER UPDATE +NOTICE: trigger on parted_stmt_trig1 AFTER UPDATE delete from parted_stmt_trig; NOTICE: trigger on parted_stmt_trig BEFORE DELETE +NOTICE: trigger on parted_stmt_trig1 BEFORE DELETE NOTICE: trigger on parted_stmt_trig AFTER DELETE +NOTICE: trigger on parted_stmt_trig1 AFTER DELETE +insert into regular_stmt_trig values (1); +insert into regular_stmt_trig_child values (1); +update regular_stmt_trig set a = a; +NOTICE: trigger on regular_stmt_trig BEFORE UPDATE +NOTICE: trigger on regular_stmt_trig_child BEFORE UPDATE +NOTICE: trigger on regular_stmt_trig AFTER UPDATE +NOTICE: trigger on regular_stmt_trig_child AFTER UPDATE +delete from regular_stmt_trig; +NOTICE: trigger on regular_stmt_trig BEFORE DELETE +NOTICE: trigger on regular_stmt_trig_child BEFORE DELETE +NOTICE: trigger on regular_stmt_trig AFTER DELETE +NOTICE: trigger on regular_stmt_trig_child AFTER DELETE drop table parted_stmt_trig; +drop table regular_stmt_trig, regular_stmt_trig_child; diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql index a767098465..2784fe1077 100644 --- a/src/test/regress/sql/triggers.sql +++ b/src/test/regress/sql/triggers.sql @@ -1271,6 +1271,9 @@ 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 regular_stmt_trig (a int); +create table regular_stmt_trig_child () inherits (regular_stmt_trig); + create or replace function trigger_notice() returns trigger as $$ begin raise notice 'trigger on % % %', TG_TABLE_NAME, TG_WHEN, TG_OP; return null; end; $$ language plpgsql; @@ -1303,6 +1306,26 @@ create trigger trig_del_before before delete on parted_stmt_trig1 create trigger trig_del_after after delete on parted_stmt_trig1 for each statement execute procedure trigger_notice(); +-- update/delete triggers on the regular inheritance parent +create trigger trig_upd_before before update on regular_stmt_trig + for each statement execute procedure trigger_notice(); +create trigger trig_upd_after after update on regular_stmt_trig + for each statement execute procedure trigger_notice(); +create trigger trig_del_before before delete on regular_stmt_trig + for each statement execute procedure trigger_notice(); +create trigger trig_del_after after delete on regular_stmt_trig + for each statement execute procedure trigger_notice(); + +-- update/delete triggers on the regular inheritance child +create trigger trig_upd_before before update on regular_stmt_trig_child + for each statement execute procedure trigger_notice(); +create trigger trig_upd_after after update on regular_stmt_trig_child + for each statement execute procedure trigger_notice(); +create trigger trig_del_before before delete on regular_stmt_trig_child + for each statement execute procedure trigger_notice(); +create trigger trig_del_after after delete on regular_stmt_trig_child + for each statement execute procedure trigger_notice(); + with ins (partname, a) as ( insert into parted_stmt_trig values (1) returning tableoid::regclass, a ) insert into parted_stmt_trig select a+1 from ins returning tableoid::regclass, a; @@ -1310,4 +1333,10 @@ with ins (partname, a) as ( update parted_stmt_trig set a = a; delete from parted_stmt_trig; +insert into regular_stmt_trig values (1); +insert into regular_stmt_trig_child values (1); +update regular_stmt_trig set a = a; +delete from regular_stmt_trig; + drop table parted_stmt_trig; +drop table regular_stmt_trig, regular_stmt_trig_child; -- 2.11.0