Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit74ab96a

Browse files
committed
Add pg_trigger_depth() function
This reports the depth level of triggers currently in execution, or zeroif not called from inside a trigger.No catversion bump in this patch, but you have to initdb if you wantaccess to the new function.Author: Kevin Grittner
1 parent6d5aae7 commit74ab96a

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
intSessionReplicationRole=SESSION_REPLICATION_ROLE_ORIGIN;
6161

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

6365
#defineGetModifiedColumns(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(insertOID=3777 (pg_stat_reset_single_function_countersPGNSPPGUID121000fffffv102278"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(insertOID=3163 (pg_trigger_depthPGNSPPGUID121000ffftfs0023""_null__null__null__null_pg_trigger_depth_null__null__null_ ));
2702+
DESCR("current trigger depth");
2703+
27012704
DATA(insertOID=3778 (pg_tablespace_locationPGNSPPGUID121000ffftfs1025"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
externintRI_FKey_trigger_type(Oidtgfoid);
207207

208+
externDatumpg_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
DROPTABLE city_table CASCADE;
963963
DROPTABLE country_table;
964+
965+
966+
-- Test pg_trigger_depth()
967+
968+
createtabledepth_a (idintnot nullprimary key);
969+
createtabledepth_b (idintnot nullprimary key);
970+
createtabledepth_c (idintnot nullprimary key);
971+
972+
createfunctiondepth_a_tf() returns trigger
973+
language plpgsqlas $$
974+
begin
975+
raise notice'%: depth = %', tg_name, pg_trigger_depth();
976+
insert into depth_bvalues (new.id);
977+
raise notice'%: depth = %', tg_name, pg_trigger_depth();
978+
return new;
979+
end;
980+
$$;
981+
createtriggerdepth_a_tr before inserton depth_a
982+
for each row execute procedure depth_a_tf();
983+
984+
createfunctiondepth_b_tf() returns trigger
985+
language plpgsqlas $$
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+
ifnew.id=1 then
996+
execute'insert into depth_c values ('||new.id::text||')';
997+
end if;
998+
return new;
999+
end;
1000+
$$;
1001+
createtriggerdepth_b_tr before inserton depth_b
1002+
for each row execute procedure depth_b_tf();
1003+
1004+
createfunctiondepth_c_tf() returns trigger
1005+
language plpgsqlas $$
1006+
begin
1007+
raise notice'%: depth = %', tg_name, pg_trigger_depth();
1008+
ifnew.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+
createtriggerdepth_c_tr before inserton depth_c
1016+
for each row execute procedure depth_c_tf();
1017+
1018+
select pg_trigger_depth();
1019+
insert into depth_avalues (1);
1020+
select pg_trigger_depth();
1021+
insert into depth_avalues (2);
1022+
select pg_trigger_depth();
1023+
1024+
droptable depth_a, depth_b, depth_c;
1025+
dropfunction depth_a_tf();
1026+
dropfunction depth_b_tf();
1027+
dropfunction depth_c_tf();

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp