On Fri, Apr 10, 2026 at 3:42 AM SATYANARAYANA NARLAPURAM
<[email protected]> wrote:
>
>> Repro:
>>
>> CREATE TABLE t (id INT, valid_at daterange, val INT);
>> INSERT INTO t VALUES (1, '[2026-01-01,2026-12-31)', 100);
>> CREATE VIEW v AS SELECT * FROM t;
>>
>> CREATE FUNCTION v_trig() RETURNS trigger LANGUAGE plpgsql AS $$
>> BEGIN
>> UPDATE t SET val = NEW.val WHERE id = OLD.id;
>> RETURN NEW;
>> END;
>> $$;
>> CREATE TRIGGER trg INSTEAD OF UPDATE ON v
>> FOR EACH ROW EXECUTE FUNCTION v_trig();
>>
>> -- This crashes the server:
>> UPDATE v FOR PORTION OF valid_at FROM '2026-04-01' TO '2026-08-01'
>> SET val = 999 WHERE id = 1;
>>
>> I am thinking we should just reject this case. Attached a draft patch to fix
>> the issue.
>
Yech, we should reject it.
In RewriteQuery, we have:
/*
* If there was no unqualified INSTEAD rule, and the target relation
* is a view without any INSTEAD OF triggers, see if the view can be
* automatically updated. If so, we perform the necessary query
* transformation here and add the resulting query to the
* product_queries list, so that it gets recursively rewritten if
* necessary. For MERGE, the view must be automatically updatable if
* any of the merge actions lack a corresponding INSTEAD OF trigger.
*
* If the view cannot be automatically updated, we throw an error here
* which is OK since the query would fail at runtime anyway. Throwing
* the error here is preferable to the executor check since we have
* more detailed information available about why the view isn't
* updatable.
*/
if (!instead &&
rt_entry_relation->rd_rel->relkind == RELKIND_VIEW &&
!view_has_instead_trigger(rt_entry_relation, event,
parsetree->mergeActionList))
Per above, RewriteQuery does not rewrite the view relation to its base
relation when the view has an INSTEAD OF trigger.
In such cases, ExecInitModifyTable->ExecInitResultRelation initialize
mtstate->resultRelInfo
using the view relation itself (rather than the underlying base table).
But ExecForPortionOfLeftovers->table_tuple_fetch_row_version requires the
relation to physical storage.
Therefore DELETE/UPDATE ... FOR PORTION OF operations cannot cope with
views that have INSTEAD OF triggers.
IMHO, rejecting it at RewriteQuery make more sense to me.
Now the error message is:
ERROR: UPDATE FOR PORTION OF is not supported for views with INSTEAD
OF triggers
ERROR: DELETE FOR PORTION OF is not supported for views with INSTEAD
OF triggers
--
jian
https://www.enterprisedb.com/
From f37b8d41c211d8f05b3fe7b96a0bf618890e7170 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Tue, 14 Apr 2026 12:10:09 +0800
Subject: [PATCH v10 1/1] reject instead of view with DELETE/UPDATE FOR PORTION
OF
Views with INSTEAD OF triggers cannot rewrite the query to the base relation.
Views do not have physical rows. FOR PORTION OF requires access to the physical
row to compute temporal leftovers, so we must disallow it here.
discussion: https://postgr.es/m/CAHg%2BQDd74fnd4obCRMqVS0AVWf%3DcSFH%3DCv7trTJWgm%2B_bhTK6w%40mail.gmail.com
commitfest entry: https://commitfest.postgresql.org/patch/
---
src/backend/rewrite/rewriteHandler.c | 28 ++++++++++++++++++++
src/test/regress/expected/for_portion_of.out | 21 +++++++++++++++
src/test/regress/sql/for_portion_of.sql | 23 ++++++++++++++++
3 files changed, 72 insertions(+)
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index 021c73f1b67..dbfbfcaf34a 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -4166,6 +4166,20 @@ RewriteQuery(Query *parsetree, List *rewrite_events, int orig_rt_length,
parsetree->targetList = lappend(parsetree->targetList, tle);
}
}
+ else if (view_has_instead_trigger(rt_entry_relation, event, NIL))
+ {
+ /*
+ * Views with INSTEAD OF triggers cannot rewrite the query
+ * to the base relation. Views do not have physical rows.
+ * FOR PORTION OF requires access to the physical row to
+ * compute temporal leftovers, so we must disallow it
+ * here.
+ */
+ if (parsetree->forPortionOf)
+ ereport(ERROR,
+ errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("UPDATE FOR PORTION OF is not supported for views with INSTEAD OF triggers"));
+ }
}
parsetree->targetList =
@@ -4231,6 +4245,20 @@ RewriteQuery(Query *parsetree, List *rewrite_events, int orig_rt_length,
*/
AddQual(parsetree, parsetree->forPortionOf->overlapsExpr);
}
+ else if (view_has_instead_trigger(rt_entry_relation, event, NIL))
+ {
+ /*
+ * Views with INSTEAD OF triggers cannot rewrite the query
+ * to the base relation. Views do not have physical rows.
+ * FOR PORTION OF requires access to the physical row to
+ * compute temporal leftovers, so we must disallow it
+ * here.
+ */
+ if (parsetree->forPortionOf)
+ ereport(ERROR,
+ errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("DELETE FOR PORTION OF is not supported for views with INSTEAD OF triggers"));
+ }
}
}
else
diff --git a/src/test/regress/expected/for_portion_of.out b/src/test/regress/expected/for_portion_of.out
index 31f772c723d..9e3c337e0e0 100644
--- a/src/test/regress/expected/for_portion_of.out
+++ b/src/test/regress/expected/for_portion_of.out
@@ -2097,4 +2097,25 @@ SELECT * FROM temporal_partitioned_5 ORDER BY id, valid_at;
(4 rows)
DROP TABLE temporal_partitioned;
+-- Test: FOR PORTION OF should be rejected on views with INSTEAD OF triggers
+CREATE TABLE fpo_instead_base (id int, valid_at daterange, val int);
+INSERT INTO fpo_instead_base VALUES (1, '[2024-01-01,2024-12-31)', 100);
+CREATE VIEW fpo_instead_view AS SELECT * FROM fpo_instead_base;
+CREATE FUNCTION fpo_instead_trig_fn() RETURNS trigger LANGUAGE plpgsql AS $$
+BEGIN
+ RETURN NEW;
+END;
+$$;
+CREATE TRIGGER fpo_instead_trig INSTEAD OF UPDATE ON fpo_instead_view
+ FOR EACH ROW EXECUTE FUNCTION fpo_instead_trig_fn();
+CREATE TRIGGER fpo_instead_del_trig INSTEAD OF DELETE ON fpo_instead_view
+ FOR EACH ROW EXECUTE FUNCTION fpo_instead_trig_fn();
+UPDATE fpo_instead_view FOR PORTION OF valid_at FROM '2024-04-01' TO '2024-08-01'
+ SET val = 999 WHERE id = 1; -- error
+ERROR: UPDATE FOR PORTION OF is not supported for views with INSTEAD OF triggers
+DELETE FROM fpo_instead_view FOR PORTION OF valid_at FROM '2024-04-01' TO '2024-08-01'
+ WHERE id = 1; -- error
+ERROR: DELETE FOR PORTION OF is not supported for views with INSTEAD OF triggers
+DROP VIEW fpo_instead_view;
+DROP TABLE fpo_instead_base;
RESET datestyle;
diff --git a/src/test/regress/sql/for_portion_of.sql b/src/test/regress/sql/for_portion_of.sql
index d4062acf1d1..d4602171c90 100644
--- a/src/test/regress/sql/for_portion_of.sql
+++ b/src/test/regress/sql/for_portion_of.sql
@@ -1365,4 +1365,27 @@ SELECT * FROM temporal_partitioned_5 ORDER BY id, valid_at;
DROP TABLE temporal_partitioned;
+-- Test: FOR PORTION OF should be rejected on views with INSTEAD OF triggers
+CREATE TABLE fpo_instead_base (id int, valid_at daterange, val int);
+INSERT INTO fpo_instead_base VALUES (1, '[2024-01-01,2024-12-31)', 100);
+CREATE VIEW fpo_instead_view AS SELECT * FROM fpo_instead_base;
+CREATE FUNCTION fpo_instead_trig_fn() RETURNS trigger LANGUAGE plpgsql AS $$
+BEGIN
+ RETURN NEW;
+END;
+$$;
+CREATE TRIGGER fpo_instead_trig INSTEAD OF UPDATE ON fpo_instead_view
+ FOR EACH ROW EXECUTE FUNCTION fpo_instead_trig_fn();
+CREATE TRIGGER fpo_instead_del_trig INSTEAD OF DELETE ON fpo_instead_view
+ FOR EACH ROW EXECUTE FUNCTION fpo_instead_trig_fn();
+
+UPDATE fpo_instead_view FOR PORTION OF valid_at FROM '2024-04-01' TO '2024-08-01'
+ SET val = 999 WHERE id = 1; -- error
+
+DELETE FROM fpo_instead_view FOR PORTION OF valid_at FROM '2024-04-01' TO '2024-08-01'
+ WHERE id = 1; -- error
+
+DROP VIEW fpo_instead_view;
+DROP TABLE fpo_instead_base;
+
RESET datestyle;
--
2.34.1
