Inconsistent trigger behavior between two temporal leftovers

Sergei Patiakin <sergei.patiakin@enterprisedb.com>

From: Sergei Patiakin <sergei.patiakin@enterprisedb.com>
To: pgsql-hackers@lists.postgresql.org
Date: 2026-04-07T12:49:27Z
Lists: pgsql-hackers
Hi hackers,

The FOR PORTION OF thread [1] noted the challenges in defining the
interaction between FPO and insert triggers.

Where an UPDATE FOR PORTION OF produces two temporal leftovers, I feel
trigger behavior should be
symmetrical between the two leftovers, whatever that behavior may be.

Currently a tuple-modifying BEFORE INSERT ROW trigger will fire for
both leftovers, but the second leftover's trigger
will see tuple modifications from the first leftover's trigger. I feel
this produces a surprising asymmetry:

```
CREATE TABLE products (id int, valid_at daterange, name text, revision int);

CREATE FUNCTION increment_product_revision() RETURNS trigger LANGUAGE
plpgsql AS $$
BEGIN NEW.revision := NEW.revision + 1; RETURN NEW; END; $$;

CREATE TRIGGER products_insert_trigger BEFORE INSERT ON products
  FOR EACH ROW EXECUTE FUNCTION increment_product_revision();

INSERT INTO products VALUES (1, '[2020-01-01, 2020-12-31)', 'widget', 0);

-- Update producing two leftovers
UPDATE products FOR PORTION OF valid_at FROM '2020-04-01' TO '2020-08-01'
  SET name = 'gadget' WHERE id = 1;

SELECT * FROM products ORDER BY valid_at;
-- id |        valid_at         |  name  | revision
-- ---+-------------------------+--------+---------
--  1 | [2020-01-01,2020-04-01) | widget |        2
--  1 | [2020-04-01,2020-08-01) | gadget |        1
--  1 | [2020-08-01,2020-12-31) | widget |        3
-- first leftover has revision=2 - ok
-- second leftover has revision=3 - surprising?
```

[1] https://www.postgresql.org/message-id/ec498c3d-5f2b-48ec-b989-5561c8aa2024%40illuminatedcomputing.com

Best regards,
Sergei



Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →
  1. Fix cross-leftover pollution in FOR PORTION OF insert triggers