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
