| From: | Sergei Patiakin <sergei(dot)patiakin(at)enterprisedb(dot)com> |
|---|---|
| To: | pgsql-hackers(at)lists(dot)postgresql(dot)org |
| Subject: | Inconsistent trigger behavior between two temporal leftovers |
| Date: | 2026-04-07 12:49:27 |
| Message-ID: | CANE55rCqcse_pwXBMWhbj3_7XROb8Dks6=OLFmKy3bO3zDsCsg@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| 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?
```
Best regards,
Sergei
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Alexander Korotkov | 2026-04-07 12:52:03 | Re: Implement waiting for wal lsn replay: reloaded |
| Previous Message | Jelte Fennema-Nio | 2026-04-07 12:36:02 | Re: pg_get__*_ddl consolidation |