Skip to content

Commit 74ab96a

Browse files
alvherrealvherre
committed
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
1 parent 6d5aae7 commit 74ab96a

File tree

6 files changed

+213
-1
lines changed

6 files changed

+213
-1
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -12794,6 +12794,13 @@ postgres=# SELECT * FROM unnest2(ARRAY[[1,2],[3,4]]);
1279412794
<entry>server start time</entry>
1279512795
</row>
1279612796

12797+
<row>
12798+
<entry><literal><function>pg_trigger_depth()</function></literal></entry>
12799+
<entry><type>int</type></entry>
12800+
<entry>current nesting level of <productname>PostgreSQL</> triggers
12801+
(0 if not called, directly or indirectly, from inside a trigger)</entry>
12802+
</row>
12803+
1279712804
<row>
1279812805
<entry><literal><function>session_user</function></literal></entry>
1279912806
<entry><type>name</type></entry>

‎src/backend/commands/trigger.c

Lines changed: 20 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -59,6 +59,8 @@
5959
/* GUC variables */
6060
int SessionReplicationRole = SESSION_REPLICATION_ROLE_ORIGIN;
6161

62+
/* How many levels deep into trigger execution are we? */
63+
static int MyTriggerDepth = 0;
6264

6365
#define GetModifiedColumns(relinfo, estate) \
6466
(rt_fetch((relinfo)->ri_RangeTableIndex, (estate)->es_range_table)->modifiedCols)
@@ -1838,7 +1840,18 @@ ExecCallTriggerFunc(TriggerData *trigdata,
18381840

18391841
pgstat_init_function_usage(&fcinfo, &fcusage);
18401842

1841-
result = FunctionCallInvoke(&fcinfo);
1843+
MyTriggerDepth++;
1844+
PG_TRY();
1845+
{
1846+
result = FunctionCallInvoke(&fcinfo);
1847+
}
1848+
PG_CATCH();
1849+
{
1850+
MyTriggerDepth--;
1851+
PG_RE_THROW();
1852+
}
1853+
PG_END_TRY();
1854+
MyTriggerDepth--;
18421855

18431856
pgstat_end_function_usage(&fcusage, true);
18441857

@@ -4632,3 +4645,9 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
46324645
&new_event, &new_shared);
46334646
}
46344647
}
4648+
4649+
Datum
4650+
pg_trigger_depth(PG_FUNCTION_ARGS)
4651+
{
4652+
PG_RETURN_INT32(MyTriggerDepth);
4653+
}

‎src/include/catalog/pg_proc.h

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2698,6 +2698,9 @@ DESCR("statistics: reset collected statistics for a single table or index in the
26982698
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_ ));
26992699
DESCR("statistics: reset collected statistics for a single function in the current database");
27002700

2701+
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_ ));
2702+
DESCR("current trigger depth");
2703+
27012704
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_ ));
27022705
DESCR("tablespace location");
27032706

‎src/include/commands/trigger.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -205,4 +205,6 @@ extern bool RI_Initial_Check(Trigger *trigger,
205205

206206
extern int RI_FKey_trigger_type(Oid tgfoid);
207207

208+
extern Datum pg_trigger_depth(PG_FUNCTION_ARGS);
209+
208210
#endif /* TRIGGER_H */

‎src/test/regress/expected/triggers.out

Lines changed: 117 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1443,3 +1443,120 @@ NOTICE: drop cascades to 2 other objects
14431443
DETAIL: drop cascades to view city_view
14441444
drop cascades to view european_city_view
14451445
DROP TABLE country_table;
1446+
-- Test pg_trigger_depth()
1447+
create table depth_a (id int not null primary key);
1448+
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "depth_a_pkey" for table "depth_a"
1449+
create table depth_b (id int not null primary key);
1450+
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "depth_b_pkey" for table "depth_b"
1451+
create table depth_c (id int not null primary key);
1452+
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "depth_c_pkey" for table "depth_c"
1453+
create function depth_a_tf() returns trigger
1454+
language plpgsql as $$
1455+
begin
1456+
raise notice '%: depth = %', tg_name, pg_trigger_depth();
1457+
insert into depth_b values (new.id);
1458+
raise notice '%: depth = %', tg_name, pg_trigger_depth();
1459+
return new;
1460+
end;
1461+
$$;
1462+
create trigger depth_a_tr before insert on depth_a
1463+
for each row execute procedure depth_a_tf();
1464+
create function depth_b_tf() returns trigger
1465+
language plpgsql as $$
1466+
begin
1467+
raise notice '%: depth = %', tg_name, pg_trigger_depth();
1468+
begin
1469+
execute 'insert into depth_c values (' || new.id::text || ')';
1470+
exception
1471+
when sqlstate 'U9999' then
1472+
raise notice 'SQLSTATE = U9999: depth = %', pg_trigger_depth();
1473+
end;
1474+
raise notice '%: depth = %', tg_name, pg_trigger_depth();
1475+
if new.id = 1 then
1476+
execute 'insert into depth_c values (' || new.id::text || ')';
1477+
end if;
1478+
return new;
1479+
end;
1480+
$$;
1481+
create trigger depth_b_tr before insert on depth_b
1482+
for each row execute procedure depth_b_tf();
1483+
create function depth_c_tf() returns trigger
1484+
language plpgsql as $$
1485+
begin
1486+
raise notice '%: depth = %', tg_name, pg_trigger_depth();
1487+
if new.id = 1 then
1488+
raise exception sqlstate 'U9999';
1489+
end if;
1490+
raise notice '%: depth = %', tg_name, pg_trigger_depth();
1491+
return new;
1492+
end;
1493+
$$;
1494+
create trigger depth_c_tr before insert on depth_c
1495+
for each row execute procedure depth_c_tf();
1496+
select pg_trigger_depth();
1497+
pg_trigger_depth
1498+
------------------
1499+
0
1500+
(1 row)
1501+
1502+
insert into depth_a values (1);
1503+
NOTICE: depth_a_tr: depth = 1
1504+
NOTICE: depth_b_tr: depth = 2
1505+
CONTEXT: SQL statement "insert into depth_b values (new.id)"
1506+
PL/pgSQL function "depth_a_tf" line 4 at SQL statement
1507+
NOTICE: depth_c_tr: depth = 3
1508+
CONTEXT: SQL statement "insert into depth_c values (1)"
1509+
PL/pgSQL function "depth_b_tf" line 5 at EXECUTE statement
1510+
SQL statement "insert into depth_b values (new.id)"
1511+
PL/pgSQL function "depth_a_tf" line 4 at SQL statement
1512+
NOTICE: SQLSTATE = U9999: depth = 2
1513+
CONTEXT: SQL statement "insert into depth_b values (new.id)"
1514+
PL/pgSQL function "depth_a_tf" line 4 at SQL statement
1515+
NOTICE: depth_b_tr: depth = 2
1516+
CONTEXT: SQL statement "insert into depth_b values (new.id)"
1517+
PL/pgSQL function "depth_a_tf" line 4 at SQL statement
1518+
NOTICE: depth_c_tr: depth = 3
1519+
CONTEXT: SQL statement "insert into depth_c values (1)"
1520+
PL/pgSQL function "depth_b_tf" line 12 at EXECUTE statement
1521+
SQL statement "insert into depth_b values (new.id)"
1522+
PL/pgSQL function "depth_a_tf" line 4 at SQL statement
1523+
ERROR: U9999
1524+
CONTEXT: SQL statement "insert into depth_c values (1)"
1525+
PL/pgSQL function "depth_b_tf" line 12 at EXECUTE statement
1526+
SQL statement "insert into depth_b values (new.id)"
1527+
PL/pgSQL function "depth_a_tf" line 4 at SQL statement
1528+
select pg_trigger_depth();
1529+
pg_trigger_depth
1530+
------------------
1531+
0
1532+
(1 row)
1533+
1534+
insert into depth_a values (2);
1535+
NOTICE: depth_a_tr: depth = 1
1536+
NOTICE: depth_b_tr: depth = 2
1537+
CONTEXT: SQL statement "insert into depth_b values (new.id)"
1538+
PL/pgSQL function "depth_a_tf" line 4 at SQL statement
1539+
NOTICE: depth_c_tr: depth = 3
1540+
CONTEXT: SQL statement "insert into depth_c values (2)"
1541+
PL/pgSQL function "depth_b_tf" line 5 at EXECUTE statement
1542+
SQL statement "insert into depth_b values (new.id)"
1543+
PL/pgSQL function "depth_a_tf" line 4 at SQL statement
1544+
NOTICE: depth_c_tr: depth = 3
1545+
CONTEXT: SQL statement "insert into depth_c values (2)"
1546+
PL/pgSQL function "depth_b_tf" line 5 at EXECUTE statement
1547+
SQL statement "insert into depth_b values (new.id)"
1548+
PL/pgSQL function "depth_a_tf" line 4 at SQL statement
1549+
NOTICE: depth_b_tr: depth = 2
1550+
CONTEXT: SQL statement "insert into depth_b values (new.id)"
1551+
PL/pgSQL function "depth_a_tf" line 4 at SQL statement
1552+
NOTICE: depth_a_tr: depth = 1
1553+
select pg_trigger_depth();
1554+
pg_trigger_depth
1555+
------------------
1556+
0
1557+
(1 row)
1558+
1559+
drop table depth_a, depth_b, depth_c;
1560+
drop function depth_a_tf();
1561+
drop function depth_b_tf();
1562+
drop function depth_c_tf();

‎src/test/regress/sql/triggers.sql

Lines changed: 64 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -961,3 +961,67 @@ SELECT * FROM city_view;
961961

962962
DROP TABLE city_table CASCADE;
963963
DROP TABLE country_table;
964+
965+
966+
-- Test pg_trigger_depth()
967+
968+
create table depth_a (id int not null primary key);
969+
create table depth_b (id int not null primary key);
970+
create table depth_c (id int not null primary key);
971+
972+
create function depth_a_tf() returns trigger
973+
language plpgsql as $$
974+
begin
975+
raise notice '%: depth = %', tg_name, pg_trigger_depth();
976+
insert into depth_b values (new.id);
977+
raise notice '%: depth = %', tg_name, pg_trigger_depth();
978+
return new;
979+
end;
980+
$$;
981+
create trigger depth_a_tr before insert on depth_a
982+
for each row execute procedure depth_a_tf();
983+
984+
create function depth_b_tf() returns trigger
985+
language plpgsql as $$
986+
begin
987+
raise notice '%: depth = %', tg_name, pg_trigger_depth();
988+
begin
989+
execute 'insert into depth_c values (' || new.id::text || ')';
990+
exception
991+
when sqlstate 'U9999' then
992+
raise notice 'SQLSTATE = U9999: depth = %', pg_trigger_depth();
993+
end;
994+
raise notice '%: depth = %', tg_name, pg_trigger_depth();
995+
if new.id = 1 then
996+
execute 'insert into depth_c values (' || new.id::text || ')';
997+
end if;
998+
return new;
999+
end;
1000+
$$;
1001+
create trigger depth_b_tr before insert on depth_b
1002+
for each row execute procedure depth_b_tf();
1003+
1004+
create function depth_c_tf() returns trigger
1005+
language plpgsql as $$
1006+
begin
1007+
raise notice '%: depth = %', tg_name, pg_trigger_depth();
1008+
if new.id = 1 then
1009+
raise exception sqlstate 'U9999';
1010+
end if;
1011+
raise notice '%: depth = %', tg_name, pg_trigger_depth();
1012+
return new;
1013+
end;
1014+
$$;
1015+
create trigger depth_c_tr before insert on depth_c
1016+
for each row execute procedure depth_c_tf();
1017+
1018+
select pg_trigger_depth();
1019+
insert into depth_a values (1);
1020+
select pg_trigger_depth();
1021+
insert into depth_a values (2);
1022+
select pg_trigger_depth();
1023+
1024+
drop table depth_a, depth_b, depth_c;
1025+
drop function depth_a_tf();
1026+
drop function depth_b_tf();
1027+
drop function depth_c_tf();

0 commit comments

Comments
 (0)