Thread

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

Paul A Jungwirth <pj@illuminatedcomputing.com> — 2026-05-03T15:57:46Z

On Sat, May 2, 2026 at 9:49 PM jian he <jian.universality@gmail.com> wrote:
> 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?

In fact it fires once for the update, and four times for the inserts.
This behavior is correct and intended. The standard says that the
inserts for temporal leftovers are "statements", so we decided to fire
statement triggers on them. We talked about this a lot at the last
PGConf and in some emails afterwards. Here is a mailing list thread
with lots of details:
https://www.postgresql.org/message-id/ec498c3d-5f2b-48ec-b989-5561c8aa2024%40illuminatedcomputing.com

Yours,

-- 
Paul              ~{:-)
pj@illuminatedcomputing.com