v8-0002-Add-tests-for-FOR-PORTION-OF-reaching-INSTEAD-OF-.patch

From 5d41626a1c2fe5f7cd99e2b973552e9aae109549 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <pj@illuminatedcomputing.com>
Date: Wed, 8 Jul 2026 08:43:10 -0700
Subject: [PATCH v8 2/4] Add tests for FOR PORTION OF reaching INSTEAD OF
 triggers via stored queries

These tests crash if we forbid FOR PORTION OF with INSTEAD OF triggers
at parse time. So they show why checking at rewrite or later is
necessary.

Anything that stores the parsed query risks a TOCTOU vulnerability. The
tests show two ways: a rewrite RULE and a BEGIN ATOMIC function.

Discussion: https://www.postgresql.org/message-id/1894792.1783476457%40sss.pgh.pa.us
---
 src/test/regress/expected/updatable_views.out | 41 +++++++++++++++++++
 src/test/regress/sql/updatable_views.sql      | 39 ++++++++++++++++++
 2 files changed, 80 insertions(+)

diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out
index acab165ae4c..e6232a5f8c0 100644
--- a/src/test/regress/expected/updatable_views.out
+++ b/src/test/regress/expected/updatable_views.out
@@ -4203,3 +4203,44 @@ LINE 2:   for portion of valid_at from '2017-01-01' to '2022-01-01'
                          ^
 drop view uv_fpo_instead_view;
 drop function uv_fpo_instead_trig();
+-- Forbid INSTEAD OF triggers with FOR PORTION OF even if the FOR PORTION OF
+-- statement is parsed before the trigger exists.
+-- This can happen in at least a couple ways: a rewrite rule or a BEGIN ATOMIC function.
+create function uv_fpo_instead_trig() returns trigger language plpgsql as
+$$ begin return new; end $$;
+-- via a rewrite rule
+create table uv_fpo_rule_tab (id int4range, valid_at tsrange, b float);
+insert into uv_fpo_rule_tab values ('[1,1]', '[2000-01-01, 2030-01-01)', 0);
+create view uv_fpo_rule_view as select id, valid_at, b from uv_fpo_rule_tab;
+create rule uv_fpo_rule as on insert to uv_fpo_rule_tab do also
+  update uv_fpo_rule_view
+    for portion of valid_at from '2010-01-01' to '2020-01-01'
+    set b = 99 where id = '[1,1]';
+create trigger uv_fpo_rule_instead
+  instead of update on uv_fpo_rule_view
+  for each row execute function uv_fpo_instead_trig();
+-- Would crash if we checked at parse-time:
+insert into uv_fpo_rule_tab values ('[2,2]', '[2000-01-01,2030-01-01)', 0);
+ERROR:  views with INSTEAD OF triggers do not support FOR PORTION OF
+drop table uv_fpo_rule_tab cascade;
+NOTICE:  drop cascades to view uv_fpo_rule_view
+-- via a BEGIN ATOMIC function body
+create table uv_fpo_atomic_tab (id int4range, valid_at tsrange, b float);
+insert into uv_fpo_atomic_tab values ('[1,1]', '[2000-01-01, 2030-01-01)', 0);
+create view uv_fpo_atomic_view as select id, valid_at, b from uv_fpo_atomic_tab;
+create function uv_fpo_atomic_fn() returns void language sql begin atomic
+  update uv_fpo_atomic_view
+    for portion of valid_at from '2010-01-01' to '2020-01-01'
+    set b = 99 where id = '[1,1]';
+end;
+create trigger uv_fpo_atomic_instead
+  instead of update on uv_fpo_atomic_view
+  for each row execute function uv_fpo_instead_trig();
+-- Would crash if we checked at parse-time:
+select uv_fpo_atomic_fn();
+ERROR:  views with INSTEAD OF triggers do not support FOR PORTION OF
+CONTEXT:  SQL function "uv_fpo_atomic_fn" statement 1
+drop function uv_fpo_atomic_fn();
+drop table uv_fpo_atomic_tab cascade;
+NOTICE:  drop cascades to view uv_fpo_atomic_view
+drop function uv_fpo_instead_trig();
diff --git a/src/test/regress/sql/updatable_views.sql b/src/test/regress/sql/updatable_views.sql
index f4a7e0327b0..2ef9aa32f36 100644
--- a/src/test/regress/sql/updatable_views.sql
+++ b/src/test/regress/sql/updatable_views.sql
@@ -2182,3 +2182,42 @@ delete from uv_fpo_instead_view
 
 drop view uv_fpo_instead_view;
 drop function uv_fpo_instead_trig();
+
+-- Forbid INSTEAD OF triggers with FOR PORTION OF even if the FOR PORTION OF
+-- statement is parsed before the trigger exists.
+-- This can happen in at least a couple ways: a rewrite rule or a BEGIN ATOMIC function.
+create function uv_fpo_instead_trig() returns trigger language plpgsql as
+$$ begin return new; end $$;
+
+-- via a rewrite rule
+create table uv_fpo_rule_tab (id int4range, valid_at tsrange, b float);
+insert into uv_fpo_rule_tab values ('[1,1]', '[2000-01-01, 2030-01-01)', 0);
+create view uv_fpo_rule_view as select id, valid_at, b from uv_fpo_rule_tab;
+create rule uv_fpo_rule as on insert to uv_fpo_rule_tab do also
+  update uv_fpo_rule_view
+    for portion of valid_at from '2010-01-01' to '2020-01-01'
+    set b = 99 where id = '[1,1]';
+create trigger uv_fpo_rule_instead
+  instead of update on uv_fpo_rule_view
+  for each row execute function uv_fpo_instead_trig();
+-- Would crash if we checked at parse-time:
+insert into uv_fpo_rule_tab values ('[2,2]', '[2000-01-01,2030-01-01)', 0);
+drop table uv_fpo_rule_tab cascade;
+
+-- via a BEGIN ATOMIC function body
+create table uv_fpo_atomic_tab (id int4range, valid_at tsrange, b float);
+insert into uv_fpo_atomic_tab values ('[1,1]', '[2000-01-01, 2030-01-01)', 0);
+create view uv_fpo_atomic_view as select id, valid_at, b from uv_fpo_atomic_tab;
+create function uv_fpo_atomic_fn() returns void language sql begin atomic
+  update uv_fpo_atomic_view
+    for portion of valid_at from '2010-01-01' to '2020-01-01'
+    set b = 99 where id = '[1,1]';
+end;
+create trigger uv_fpo_atomic_instead
+  instead of update on uv_fpo_atomic_view
+  for each row execute function uv_fpo_instead_trig();
+-- Would crash if we checked at parse-time:
+select uv_fpo_atomic_fn();
+drop function uv_fpo_atomic_fn();
+drop table uv_fpo_atomic_tab cascade;
+drop function uv_fpo_instead_trig();
-- 
2.47.3