Thread

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

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

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.

Thom

>