| From: | jian he <jian(dot)universality(at)gmail(dot)com> |
|---|---|
| To: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
| Subject: | UPDATE/DELETE FOR PORTION OF fire FOR EACH STATEMENT more than once |
| Date: | 2026-05-03 04:48:52 |
| Message-ID: | CACJufxF5NZoPufkXLGZsbrZ5fUW9JCrvdeTaDnj6EteJUNbRyg@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
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).
"""
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Henson Choi | 2026-05-03 05:34:47 | Re: Row pattern recognition |
| Previous Message | Jim Jones | 2026-05-02 17:32:30 | Re: Fix bug with accessing to temporary tables of other sessions |