From 74ab96a45ef6259aa6a86a781580edea8488511a Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Wed, 25 Jan 2012 13:15:29 -0300 Subject: [PATCH] Add pg_trigger_depth() function This reports the depth level of triggers currently in execution, or zero if not called from inside a trigger. No catversion bump in this patch, but you have to initdb if you want access to the new function. Author: Kevin Grittner --- doc/src/sgml/func.sgml | 7 ++ src/backend/commands/trigger.c | 21 ++++- src/include/catalog/pg_proc.h | 3 + src/include/commands/trigger.h | 2 + src/test/regress/expected/triggers.out | 117 +++++++++++++++++++++++++ src/test/regress/sql/triggers.sql | 64 ++++++++++++++ 6 files changed, 213 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 48631cc341..219ba9abf3 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -12794,6 +12794,13 @@ postgres=# SELECT * FROM unnest2(ARRAY[[1,2],[3,4]]); server start time + + pg_trigger_depth() + int + current nesting level of PostgreSQL triggers + (0 if not called, directly or indirectly, from inside a trigger) + + session_user name diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c index f5e12e5681..2838b66e40 100644 --- a/src/backend/commands/trigger.c +++ b/src/backend/commands/trigger.c @@ -59,6 +59,8 @@ /* GUC variables */ int SessionReplicationRole = SESSION_REPLICATION_ROLE_ORIGIN; +/* How many levels deep into trigger execution are we? */ +static int MyTriggerDepth = 0; #define GetModifiedColumns(relinfo, estate) \ (rt_fetch((relinfo)->ri_RangeTableIndex, (estate)->es_range_table)->modifiedCols) @@ -1838,7 +1840,18 @@ ExecCallTriggerFunc(TriggerData *trigdata, pgstat_init_function_usage(&fcinfo, &fcusage); - result = FunctionCallInvoke(&fcinfo); + MyTriggerDepth++; + PG_TRY(); + { + result = FunctionCallInvoke(&fcinfo); + } + PG_CATCH(); + { + MyTriggerDepth--; + PG_RE_THROW(); + } + PG_END_TRY(); + MyTriggerDepth--; pgstat_end_function_usage(&fcusage, true); @@ -4632,3 +4645,9 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo, &new_event, &new_shared); } } + +Datum +pg_trigger_depth(PG_FUNCTION_ARGS) +{ + PG_RETURN_INT32(MyTriggerDepth); +} diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index b6ac1959cc..ef5ca3047d 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -2698,6 +2698,9 @@ DESCR("statistics: reset collected statistics for a single table or index in the DATA(insert OID = 3777 ( pg_stat_reset_single_function_counters PGNSP PGUID 12 1 0 0 0 f f f f f v 1 0 2278 "26" _null_ _null_ _null_ _null_ pg_stat_reset_single_function_counters _null_ _null_ _null_ )); DESCR("statistics: reset collected statistics for a single function in the current database"); +DATA(insert OID = 3163 ( pg_trigger_depth PGNSP PGUID 12 1 0 0 0 f f f t f s 0 0 23 "" _null_ _null_ _null_ _null_ pg_trigger_depth _null_ _null_ _null_ )); +DESCR("current trigger depth"); + DATA(insert OID = 3778 ( pg_tablespace_location PGNSP PGUID 12 1 0 0 0 f f f t f s 1 0 25 "26" _null_ _null_ _null_ _null_ pg_tablespace_location _null_ _null_ _null_ )); DESCR("tablespace location"); diff --git a/src/include/commands/trigger.h b/src/include/commands/trigger.h index ba0f32464e..93033414d9 100644 --- a/src/include/commands/trigger.h +++ b/src/include/commands/trigger.h @@ -205,4 +205,6 @@ extern bool RI_Initial_Check(Trigger *trigger, extern int RI_FKey_trigger_type(Oid tgfoid); +extern Datum pg_trigger_depth(PG_FUNCTION_ARGS); + #endif /* TRIGGER_H */ diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out index b4d391974d..d039b6e38b 100644 --- a/src/test/regress/expected/triggers.out +++ b/src/test/regress/expected/triggers.out @@ -1443,3 +1443,120 @@ NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to view city_view drop cascades to view european_city_view DROP TABLE country_table; +-- Test pg_trigger_depth() +create table depth_a (id int not null primary key); +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "depth_a_pkey" for table "depth_a" +create table depth_b (id int not null primary key); +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "depth_b_pkey" for table "depth_b" +create table depth_c (id int not null primary key); +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "depth_c_pkey" for table "depth_c" +create function depth_a_tf() returns trigger + language plpgsql as $$ +begin + raise notice '%: depth = %', tg_name, pg_trigger_depth(); + insert into depth_b values (new.id); + raise notice '%: depth = %', tg_name, pg_trigger_depth(); + return new; +end; +$$; +create trigger depth_a_tr before insert on depth_a + for each row execute procedure depth_a_tf(); +create function depth_b_tf() returns trigger + language plpgsql as $$ +begin + raise notice '%: depth = %', tg_name, pg_trigger_depth(); + begin + execute 'insert into depth_c values (' || new.id::text || ')'; + exception + when sqlstate 'U9999' then + raise notice 'SQLSTATE = U9999: depth = %', pg_trigger_depth(); + end; + raise notice '%: depth = %', tg_name, pg_trigger_depth(); + if new.id = 1 then + execute 'insert into depth_c values (' || new.id::text || ')'; + end if; + return new; +end; +$$; +create trigger depth_b_tr before insert on depth_b + for each row execute procedure depth_b_tf(); +create function depth_c_tf() returns trigger + language plpgsql as $$ +begin + raise notice '%: depth = %', tg_name, pg_trigger_depth(); + if new.id = 1 then + raise exception sqlstate 'U9999'; + end if; + raise notice '%: depth = %', tg_name, pg_trigger_depth(); + return new; +end; +$$; +create trigger depth_c_tr before insert on depth_c + for each row execute procedure depth_c_tf(); +select pg_trigger_depth(); + pg_trigger_depth +------------------ + 0 +(1 row) + +insert into depth_a values (1); +NOTICE: depth_a_tr: depth = 1 +NOTICE: depth_b_tr: depth = 2 +CONTEXT: SQL statement "insert into depth_b values (new.id)" +PL/pgSQL function "depth_a_tf" line 4 at SQL statement +NOTICE: depth_c_tr: depth = 3 +CONTEXT: SQL statement "insert into depth_c values (1)" +PL/pgSQL function "depth_b_tf" line 5 at EXECUTE statement +SQL statement "insert into depth_b values (new.id)" +PL/pgSQL function "depth_a_tf" line 4 at SQL statement +NOTICE: SQLSTATE = U9999: depth = 2 +CONTEXT: SQL statement "insert into depth_b values (new.id)" +PL/pgSQL function "depth_a_tf" line 4 at SQL statement +NOTICE: depth_b_tr: depth = 2 +CONTEXT: SQL statement "insert into depth_b values (new.id)" +PL/pgSQL function "depth_a_tf" line 4 at SQL statement +NOTICE: depth_c_tr: depth = 3 +CONTEXT: SQL statement "insert into depth_c values (1)" +PL/pgSQL function "depth_b_tf" line 12 at EXECUTE statement +SQL statement "insert into depth_b values (new.id)" +PL/pgSQL function "depth_a_tf" line 4 at SQL statement +ERROR: U9999 +CONTEXT: SQL statement "insert into depth_c values (1)" +PL/pgSQL function "depth_b_tf" line 12 at EXECUTE statement +SQL statement "insert into depth_b values (new.id)" +PL/pgSQL function "depth_a_tf" line 4 at SQL statement +select pg_trigger_depth(); + pg_trigger_depth +------------------ + 0 +(1 row) + +insert into depth_a values (2); +NOTICE: depth_a_tr: depth = 1 +NOTICE: depth_b_tr: depth = 2 +CONTEXT: SQL statement "insert into depth_b values (new.id)" +PL/pgSQL function "depth_a_tf" line 4 at SQL statement +NOTICE: depth_c_tr: depth = 3 +CONTEXT: SQL statement "insert into depth_c values (2)" +PL/pgSQL function "depth_b_tf" line 5 at EXECUTE statement +SQL statement "insert into depth_b values (new.id)" +PL/pgSQL function "depth_a_tf" line 4 at SQL statement +NOTICE: depth_c_tr: depth = 3 +CONTEXT: SQL statement "insert into depth_c values (2)" +PL/pgSQL function "depth_b_tf" line 5 at EXECUTE statement +SQL statement "insert into depth_b values (new.id)" +PL/pgSQL function "depth_a_tf" line 4 at SQL statement +NOTICE: depth_b_tr: depth = 2 +CONTEXT: SQL statement "insert into depth_b values (new.id)" +PL/pgSQL function "depth_a_tf" line 4 at SQL statement +NOTICE: depth_a_tr: depth = 1 +select pg_trigger_depth(); + pg_trigger_depth +------------------ + 0 +(1 row) + +drop table depth_a, depth_b, depth_c; +drop function depth_a_tf(); +drop function depth_b_tf(); +drop function depth_c_tf(); diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql index e52131dbba..f88cb81940 100644 --- a/src/test/regress/sql/triggers.sql +++ b/src/test/regress/sql/triggers.sql @@ -961,3 +961,67 @@ SELECT * FROM city_view; DROP TABLE city_table CASCADE; DROP TABLE country_table; + + +-- Test pg_trigger_depth() + +create table depth_a (id int not null primary key); +create table depth_b (id int not null primary key); +create table depth_c (id int not null primary key); + +create function depth_a_tf() returns trigger + language plpgsql as $$ +begin + raise notice '%: depth = %', tg_name, pg_trigger_depth(); + insert into depth_b values (new.id); + raise notice '%: depth = %', tg_name, pg_trigger_depth(); + return new; +end; +$$; +create trigger depth_a_tr before insert on depth_a + for each row execute procedure depth_a_tf(); + +create function depth_b_tf() returns trigger + language plpgsql as $$ +begin + raise notice '%: depth = %', tg_name, pg_trigger_depth(); + begin + execute 'insert into depth_c values (' || new.id::text || ')'; + exception + when sqlstate 'U9999' then + raise notice 'SQLSTATE = U9999: depth = %', pg_trigger_depth(); + end; + raise notice '%: depth = %', tg_name, pg_trigger_depth(); + if new.id = 1 then + execute 'insert into depth_c values (' || new.id::text || ')'; + end if; + return new; +end; +$$; +create trigger depth_b_tr before insert on depth_b + for each row execute procedure depth_b_tf(); + +create function depth_c_tf() returns trigger + language plpgsql as $$ +begin + raise notice '%: depth = %', tg_name, pg_trigger_depth(); + if new.id = 1 then + raise exception sqlstate 'U9999'; + end if; + raise notice '%: depth = %', tg_name, pg_trigger_depth(); + return new; +end; +$$; +create trigger depth_c_tr before insert on depth_c + for each row execute procedure depth_c_tf(); + +select pg_trigger_depth(); +insert into depth_a values (1); +select pg_trigger_depth(); +insert into depth_a values (2); +select pg_trigger_depth(); + +drop table depth_a, depth_b, depth_c; +drop function depth_a_tf(); +drop function depth_b_tf(); +drop function depth_c_tf(); -- 2.40.0