Re: pg_trigger_depth() v3 (was: TG_DEPTH)
Started by Kevin Grittneralmost 14 years ago2 messages
"Kevin Grittner" wrote:
Florian Pflug wrote:
The trigger depth is incremented before calling the trigger
function in ExecCallTriggerFunc() and decremented right
afterwards, which seems fine - apart from the fact that the
decrement is skipped in case of an error. The patch handles that
by saving respectively restoring the value of pg_depth in
PushTransaction() respectively {Commit,Abort}SubTransaction().While I can't come up with a failure case for that approach, it
seems rather fragile - who's to say that nested trigger
invocations correspond that tightly to sub-transaction?
I believe the same effect could be achieved more cleanly by
a TRY/CATCH block in ExecCallTriggerFunc().
Done that way in this version.
* Other in-core PLs
As it stands, the patch only export tg_depth to plpgsql functions,
not to functions written in one of the other in-core PLs. It'd be
good to change that, I believe - otherwise the other PLs become
second-class citizens in the long run.Are you suggesting that this be implemented as a special trigger
variable in every PL, or that it simply be a regular function that
returns zero when not in a trigger and some positive value when
called from a trigger? The latter seems like a pretty good idea to
me. If that is done, is there any point to *also* having a TG_DEPTH
trigger variable in plpgsql? (I don't think there is.)
I dropped the TG_DEPTH name and the patch just supports a
pg_trigger_depth() function now. Useful values are probably:
0: No trigger active on the connection.
1: Top level trigger. Useful to restrict certain DML to be allowed
only from triggers.
1: OK to do trigger-restricted DML.
greater than expected maximum depth: warn before hard crash
[questions about code coverage in regression tests]
I altered the tests to improve code coverage. In addition, since
this is no longer just a plpgsql feature, I move the tests to the
triggers.sql file.
-Kevin
Attachments:
tg_depth.v3.patchapplication/octet-stream; name=tg_depth.v3.patchDownload
*** 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 ----
</row>
<row>
+ <entry><literal><function>pg_trigger_depth()</function></literal></entry>
+ <entry><type>int</type></entry>
+ <entry>current nesting level of <productname>PostgreSQL</> triggers
+ (0 if not called, directly or indirectly, from inside a trigger)</entry>
+ </row>
+
+ <row>
<entry><literal><function>session_user</function></literal></entry>
<entry><type>name</type></entry>
<entry>session user name</entry>
*** 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();
