From 993a7aa0e4af0922ac86d8914e0e78fc38fb0daf Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Thu, 4 Jun 2026 11:12:58 +0200 Subject: [PATCH] Fix cross-leftover pollution in FOR PORTION OF insert triggers When we insert temporal leftovers after an UPDATE FOR PORTION OF, we must make a new copy of the tuple before each insert. Otherwise, if an insert trigger assigns to attributes of NEW, the second leftover sees those changes. Author: Sergei Patiakin Reviewed-by: Paul A Jungwirth Discussion: https://www.postgresql.org/message-id/flat/CANE55rCqcse_pwXBMWhbj3_7XROb8Dks6%3DOLFmKy3bO3zDsCsg%40mail.gmail.com --- src/backend/executor/nodeModifyTable.c | 12 +++++++ src/test/regress/expected/for_portion_of.out | 38 ++++++++++++++++++++ src/test/regress/sql/for_portion_of.sql | 38 ++++++++++++++++++++ 3 files changed, 88 insertions(+) diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c index 478cb01783c..b796f6e0801 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -1601,6 +1601,18 @@ ExecForPortionOfLeftovers(ModifyTableContext *context, didInit = true; } + else + { + /* + * Re-copy the original row into leftoverSlot because ExecInsert + * might pass leftoverSlot to BEFORE ROW INSERT triggers, which can + * modify the slot contents. + */ + if (map != NULL) + execute_attr_map_slot(map->attrMap, oldtupleSlot, leftoverSlot); + else + ExecForceStoreHeapTuple(oldtuple, leftoverSlot, false); + } leftoverSlot->tts_values[forPortionOf->rangeVar->varattno - 1] = leftover; leftoverSlot->tts_isnull[forPortionOf->rangeVar->varattno - 1] = false; diff --git a/src/test/regress/expected/for_portion_of.out b/src/test/regress/expected/for_portion_of.out index 0c0a205c44b..16b2f998dc0 100644 --- a/src/test/regress/expected/for_portion_of.out +++ b/src/test/regress/expected/for_portion_of.out @@ -1793,6 +1793,44 @@ SELECT * FROM for_portion_of_test WHERE id = '[4,5)' ORDER BY id, valid_at; (3 rows) DROP TRIGGER fpo_after_delete_row ON for_portion_of_test; +-- Test that a tuple-modifying BEFORE INSERT ROW trigger acts +-- consistently on both temporal leftovers. +-- When FOR PORTION OF splits a row into two leftovers, both triggers +-- should get the original row's values. +DROP TABLE for_portion_of_test; +CREATE TABLE for_portion_of_test ( + id int, + valid_at daterange, + name text +); +CREATE FUNCTION fpo_append_name_suffix() +RETURNS TRIGGER LANGUAGE plpgsql AS +$$ +BEGIN + NEW.name := NEW.name || '+insert'; + RETURN NEW; +END; +$$; +CREATE TRIGGER fpo_before_insert_row + BEFORE INSERT ON for_portion_of_test + FOR EACH ROW EXECUTE PROCEDURE fpo_append_name_suffix(); +INSERT INTO for_portion_of_test VALUES (1, '[2020-01-01,2020-12-31)', 'foo'); +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2020-04-01' TO '2020-08-01' + SET name = 'bar' + WHERE id = 1; +-- Both leftovers should have the same name: 'foo+insert+insert'. +SELECT * FROM for_portion_of_test ORDER BY valid_at; + id | valid_at | name +----+-------------------------+------------------- + 1 | [2020-01-01,2020-04-01) | foo+insert+insert + 1 | [2020-04-01,2020-08-01) | bar + 1 | [2020-08-01,2020-12-31) | foo+insert+insert +(3 rows) + +DROP FUNCTION fpo_append_name_suffix CASCADE; +NOTICE: drop cascades to trigger fpo_before_insert_row on table for_portion_of_test +DROP TABLE for_portion_of_test; -- Test with multiranges CREATE TABLE for_portion_of_test2 ( id int4range NOT NULL, diff --git a/src/test/regress/sql/for_portion_of.sql b/src/test/regress/sql/for_portion_of.sql index fd79a9b78e7..63642b1851e 100644 --- a/src/test/regress/sql/for_portion_of.sql +++ b/src/test/regress/sql/for_portion_of.sql @@ -1169,6 +1169,44 @@ SELECT * FROM for_portion_of_test WHERE id = '[4,5)' ORDER BY id, valid_at; DROP TRIGGER fpo_after_delete_row ON for_portion_of_test; +-- Test that a tuple-modifying BEFORE INSERT ROW trigger acts +-- consistently on both temporal leftovers. +-- When FOR PORTION OF splits a row into two leftovers, both triggers +-- should get the original row's values. + +DROP TABLE for_portion_of_test; +CREATE TABLE for_portion_of_test ( + id int, + valid_at daterange, + name text +); + +CREATE FUNCTION fpo_append_name_suffix() +RETURNS TRIGGER LANGUAGE plpgsql AS +$$ +BEGIN + NEW.name := NEW.name || '+insert'; + RETURN NEW; +END; +$$; + +CREATE TRIGGER fpo_before_insert_row + BEFORE INSERT ON for_portion_of_test + FOR EACH ROW EXECUTE PROCEDURE fpo_append_name_suffix(); + +INSERT INTO for_portion_of_test VALUES (1, '[2020-01-01,2020-12-31)', 'foo'); + +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2020-04-01' TO '2020-08-01' + SET name = 'bar' + WHERE id = 1; + +-- Both leftovers should have the same name: 'foo+insert+insert'. +SELECT * FROM for_portion_of_test ORDER BY valid_at; + +DROP FUNCTION fpo_append_name_suffix CASCADE; +DROP TABLE for_portion_of_test; + -- Test with multiranges CREATE TABLE for_portion_of_test2 ( -- 2.47.3