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

On Sun, 3 May 2026, 05:49 jian he, <jian(dot)universality(at)gmail(dot)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.

Thom

>