v3-0001-Test-what-BEFORE-UPDATE-triggers-do-to-FOR-PORTIO.patch

From 6b88d02d1834b073f9a690e981ee32fc24bcc451 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <pj@illuminatedcomputing.com>
Date: Thu, 2 Jul 2026 14:34:44 -0700
Subject: [PATCH v3] Test what BEFORE UPDATE triggers do to FOR PORTION OF

If a BEFORE trigger changes NEW.valid_at, what is the interaction with FOR
PORTION OF? This commit gives a test to capture our current behavior: the
trigger's change replaces the value we computed automatically, but it does not
change the bounds of the temporal leftovers.

This matches the behavior of MariaDB. On the other hand DB2 rejects changing the
start/end columns of a PERIOD. Since we don't have PERIODs, we can't reject the
change at trigger definition time as DB2 does, but we could reject it at runtime
by comparing the values before and after running triggers.
---
 src/test/regress/expected/for_portion_of.out | 47 ++++++++++++++++++++
 src/test/regress/sql/for_portion_of.sql      | 47 ++++++++++++++++++++
 2 files changed, 94 insertions(+)

diff --git a/src/test/regress/expected/for_portion_of.out b/src/test/regress/expected/for_portion_of.out
index 207e370627e..23c7128e859 100644
--- a/src/test/regress/expected/for_portion_of.out
+++ b/src/test/regress/expected/for_portion_of.out
@@ -1843,6 +1843,53 @@ SELECT * FROM for_portion_of_test ORDER BY valid_at;
 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;
+-- A BEFORE UPDATE trigger that changes the application-time column is allowed,
+-- even if the results are senseless.
+-- Note this is likely to cause a primary key violation.
+CREATE TABLE for_portion_of_test (
+  id int4range,
+  valid_at daterange,
+  name text
+);
+CREATE FUNCTION trg_fpo_change_valid_at()
+RETURNS TRIGGER LANGUAGE plpgsql AS
+$$
+BEGIN
+  NEW.valid_at = daterange('2018-01-01', '2019-01-01');
+  RETURN NEW;
+END;
+$$;
+CREATE TRIGGER fpo_before_update_row
+  BEFORE UPDATE ON for_portion_of_test
+  FOR EACH ROW EXECUTE PROCEDURE trg_fpo_change_valid_at();
+INSERT INTO for_portion_of_test VALUES ('[1,2)', '[2010-01-01,2020-01-01)', 'foo');
+UPDATE for_portion_of_test
+  FOR PORTION OF valid_at FROM '2018-05-01' TO '2018-06-01'
+  SET name = CONCAT(name, '!')
+  WHERE id = '[1,2)';
+SELECT * FROM for_portion_of_test ORDER BY id, valid_at;
+  id   |        valid_at         | name 
+-------+-------------------------+------
+ [1,2) | [2010-01-01,2018-05-01) | foo
+ [1,2) | [2018-01-01,2019-01-01) | foo!
+ [1,2) | [2018-06-01,2020-01-01) | foo
+(3 rows)
+
+-- A primary key should reject anything invalid:
+TRUNCATE for_portion_of_test;
+ALTER TABLE for_portion_of_test
+  ADD CONSTRAINT for_portion_of_test_key
+  PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
+INSERT INTO for_portion_of_test VALUES ('[1,2)', '[2010-01-01,2020-01-01)', 'foo');
+UPDATE for_portion_of_test
+  FOR PORTION OF valid_at FROM '2018-05-01' TO '2018-06-01'
+  SET name = CONCAT(name, '!')
+  WHERE id = '[1,2)';
+ERROR:  conflicting key value violates exclusion constraint "for_portion_of_test_key"
+DETAIL:  Key (id, valid_at)=([1,2), [2010-01-01,2018-05-01)) conflicts with existing key (id, valid_at)=([1,2), [2018-01-01,2019-01-01)).
+DROP TRIGGER fpo_before_update_row ON for_portion_of_test;
+DROP FUNCTION trg_fpo_change_valid_at();
+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 a3c41abf7b7..21402eda00d 100644
--- a/src/test/regress/sql/for_portion_of.sql
+++ b/src/test/regress/sql/for_portion_of.sql
@@ -1215,6 +1215,53 @@ SELECT * FROM for_portion_of_test ORDER BY valid_at;
 DROP FUNCTION fpo_append_name_suffix CASCADE;
 DROP TABLE for_portion_of_test;
 
+-- A BEFORE UPDATE trigger that changes the application-time column is allowed,
+-- even if the results are senseless.
+-- Note this is likely to cause a primary key violation.
+
+CREATE TABLE for_portion_of_test (
+  id int4range,
+  valid_at daterange,
+  name text
+);
+
+CREATE FUNCTION trg_fpo_change_valid_at()
+RETURNS TRIGGER LANGUAGE plpgsql AS
+$$
+BEGIN
+  NEW.valid_at = daterange('2018-01-01', '2019-01-01');
+  RETURN NEW;
+END;
+$$;
+
+CREATE TRIGGER fpo_before_update_row
+  BEFORE UPDATE ON for_portion_of_test
+  FOR EACH ROW EXECUTE PROCEDURE trg_fpo_change_valid_at();
+
+INSERT INTO for_portion_of_test VALUES ('[1,2)', '[2010-01-01,2020-01-01)', 'foo');
+
+UPDATE for_portion_of_test
+  FOR PORTION OF valid_at FROM '2018-05-01' TO '2018-06-01'
+  SET name = CONCAT(name, '!')
+  WHERE id = '[1,2)';
+
+SELECT * FROM for_portion_of_test ORDER BY id, valid_at;
+
+-- A primary key should reject anything invalid:
+TRUNCATE for_portion_of_test;
+ALTER TABLE for_portion_of_test
+  ADD CONSTRAINT for_portion_of_test_key
+  PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
+INSERT INTO for_portion_of_test VALUES ('[1,2)', '[2010-01-01,2020-01-01)', 'foo');
+UPDATE for_portion_of_test
+  FOR PORTION OF valid_at FROM '2018-05-01' TO '2018-06-01'
+  SET name = CONCAT(name, '!')
+  WHERE id = '[1,2)';
+
+DROP TRIGGER fpo_before_update_row ON for_portion_of_test;
+DROP FUNCTION trg_fpo_change_valid_at();
+DROP TABLE for_portion_of_test;
+
 -- Test with multiranges
 
 CREATE TABLE for_portion_of_test2 (
-- 
2.45.0