*** a/doc/src/sgml/func.sgml --- b/doc/src/sgml/func.sgml *************** *** 12793,12798 **** postgres=# SELECT * FROM unnest2(ARRAY[[1,2],[3,4]]); --- 12793,12805 ---- + pg_trigger_depth() + int + current nesting level of PostgreSQL triggers + (0 if not called, directly or indirectly, from inside a trigger) + + + session_user name session user name *** a/src/backend/commands/trigger.c --- b/src/backend/commands/trigger.c *************** *** 60,65 **** --- 60,68 ---- 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,1844 **** ExecCallTriggerFunc(TriggerData *trigdata, pgstat_init_function_usage(&fcinfo, &fcusage); ! result = FunctionCallInvoke(&fcinfo); pgstat_end_function_usage(&fcusage, true); --- 1841,1858 ---- pgstat_init_function_usage(&fcinfo, &fcusage); ! MyTriggerDepth++; ! PG_TRY(); ! { ! result = FunctionCallInvoke(&fcinfo); ! } ! PG_CATCH(); ! { ! MyTriggerDepth--; ! PG_RE_THROW(); ! } ! PG_END_TRY(); ! MyTriggerDepth--; pgstat_end_function_usage(&fcusage, true); *************** *** 4632,4634 **** AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo, --- 4646,4655 ---- &new_event, &new_shared); } } + + + Datum + pg_trigger_depth(PG_FUNCTION_ARGS) + { + PG_RETURN_INT32(MyTriggerDepth); + } *** a/src/include/catalog/pg_proc.h --- b/src/include/catalog/pg_proc.h *************** *** 2695,2700 **** DESCR("statistics: reset collected statistics for a single table or index in the --- 2695,2703 ---- 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 = 2040 ( 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("statistics: 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"); *** a/src/include/commands/trigger.h --- b/src/include/commands/trigger.h *************** *** 205,208 **** extern bool RI_Initial_Check(Trigger *trigger, --- 205,210 ---- extern int RI_FKey_trigger_type(Oid tgfoid); + extern Datum pg_trigger_depth(PG_FUNCTION_ARGS); + #endif /* TRIGGER_H */ *** a/src/test/regress/expected/triggers.out --- b/src/test/regress/expected/triggers.out *************** *** 1443,1445 **** NOTICE: drop cascades to 2 other objects --- 1443,1562 ---- 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(); *** a/src/test/regress/sql/triggers.sql --- b/src/test/regress/sql/triggers.sql *************** *** 961,963 **** SELECT * FROM city_view; --- 961,1027 ---- 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();