On Sun, 3 May 2026 at 09:24, Thom Brown <[email protected]> wrote: > > On Sun, 3 May 2026, 05:49 jian he, <[email protected]> wrote: >> >> Hi. >> >> CREATE OR REPLACE FUNCTION trigger_info() >> RETURNS TRIGGER LANGUAGE plpgsql AS >> $$ >> BEGIN >> RAISE NOTICE 'trigger name: %: TG_OP: % WHEN: % TG_LEVEL: %', >> TG_NAME, TG_WHEN, TG_OP, TG_LEVEL; >> RAISE NOTICE ' old: %', OLD; >> RAISE NOTICE ' old: %', NEW; >> IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN >> RETURN NEW; >> ELSIF TG_OP = 'DELETE' THEN >> RETURN OLD; >> END IF; >> END; >> $$; >> >> drop table if exists ts; >> create table ts(a int4range, b int); >> insert into ts values('[1,10)', 2), ('[1,10)', 3); >> CREATE TRIGGER ts_trig1 >> BEFORE INSERT OR UPDATE OR DELETE ON ts >> FOR EACH STATEMENT EXECUTE PROCEDURE trigger_info(); >> >> update ts for portion of a from 2 to 4 set b = 12; >> >> The above UPDATE statement is triggering the BEFORE FOR EACH STATEMENT >> action four times. >> This appears to contradict the documentation mentioned below. >> Am I missing something? >> >> https://www.postgresql.org/docs/devel/sql-createtrigger.html >> """ >> In contrast, a trigger that is marked FOR EACH STATEMENT only executes >> once for any given operation, regardless of how many rows it modifies >> (in particular, an operation that modifies zero rows will still result >> in the execution of any applicable FOR EACH STATEMENT triggers). >> """ > > > This appears to be triggering on the internal DML produced by the temporal > tables functionality. > > What if you debug, outputting pg_trigger_depth()? I would expect to see one > at depth 0 and the other 3 to be at depth 1.
Actually, I'm wrong here. It outputs all at depth 1. This would have been a way to implement a barrier into the function so that you can decide whether the trigger only fires for the original statement. So might there be a scenario where a user would wish to distinguish between an explicit INSERT from an INSERT generated by the temporal table machinery? None of the TG_* variables expose anything that would allow one to do this. Sure, the user could pattern match against current_query() and see that it really is an INSERT, but it's somewhat kludgy. Thom
