Thread

Re: UPDATE/DELETE FOR PORTION OF fire FOR EACH STATEMENT more than once

Thom Brown <thom@linux.com> — 2026-05-03T22:50:13Z

On Sun, 3 May 2026 at 09:24, Thom Brown <thom@linux.com> wrote:
>
> On Sun, 3 May 2026, 05:49 jian he, <jian.universality@gmail.com> 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