v8-0004-Check-FOR-PORTION-OF-against-INSTEAD-OF-triggers-.patch

From 52aa3063d498ac7380068c1537985e9cfcff0be3 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <pj@illuminatedcomputing.com>
Date: Tue, 7 Jul 2026 17:23:01 -0700
Subject: [PATCH v8 4/4] Check FOR PORTION OF against INSTEAD OF triggers at
 execution time

Doing the test at execution time means that zero-row updates don't fail.

Discussion: https://www.postgresql.org/message-id/CAJ7c6TME%2Bix6VRf-2TPnVTsj8qn_hy6sYAOmMhZEivwsu2wS6g%40mail.gmail.com
---
 src/backend/executor/nodeModifyTable.c        | 13 +++++++++++++
 src/backend/rewrite/rewriteHandler.c          |  8 --------
 src/test/regress/expected/updatable_views.out | 10 ++++------
 src/test/regress/sql/updatable_views.sql      |  8 ++++----
 4 files changed, 21 insertions(+), 18 deletions(-)

diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index c333d7139fa..440f535dd04 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -1879,6 +1879,13 @@ ExecDelete(ModifyTableContext *context,
 		bool		dodelete;
 
 		Assert(oldtuple != NULL);
+
+		/* We don't support FOR PORTION OF on views with INSTEAD OF triggers. */
+		if (((ModifyTable *) context->mtstate->ps.plan)->forPortionOf)
+			ereport(ERROR,
+					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					 errmsg("views with INSTEAD OF triggers do not support FOR PORTION OF")));
+
 		dodelete = ExecIRDeleteTriggers(estate, resultRelInfo, oldtuple);
 
 		if (!dodelete)			/* "do nothing" */
@@ -2772,6 +2779,12 @@ ExecUpdate(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
 	if (resultRelInfo->ri_TrigDesc &&
 		resultRelInfo->ri_TrigDesc->trig_update_instead_row)
 	{
+		/* We don't support FOR PORTION OF on views with INSTEAD OF triggers. */
+		if (((ModifyTable *) context->mtstate->ps.plan)->forPortionOf)
+			ereport(ERROR,
+					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					 errmsg("views with INSTEAD OF triggers do not support FOR PORTION OF")));
+
 		if (!ExecIRUpdateTriggers(estate, resultRelInfo,
 								  oldtuple, slot))
 			return NULL;		/* "do nothing" */
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index 38f54b57eec..e7ae9cce65f 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -4171,14 +4171,6 @@ RewriteQuery(Query *parsetree, List *rewrite_events, int orig_rt_length,
 		 */
 		rt_entry_relation = relation_open(rt_entry->relid, NoLock);
 
-		/* We don't support FOR PORTION OF on views with INSTEAD OF triggers. */
-		if (parsetree->forPortionOf &&
-			rt_entry_relation->rd_rel->relkind == RELKIND_VIEW &&
-			view_has_instead_trigger(rt_entry_relation, event, NIL))
-			ereport(ERROR,
-					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
-					 errmsg("views with INSTEAD OF triggers do not support FOR PORTION OF")));
-
 		/*
 		 * Rewrite the targetlist as needed for the command type.
 		 */
diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out
index 9c6bb2219f9..68927e78ce3 100644
--- a/src/test/regress/expected/updatable_views.out
+++ b/src/test/regress/expected/updatable_views.out
@@ -4183,16 +4183,14 @@ delete from uv_fpo_instead_view
   for portion of valid_at from '2017-01-01' to '2022-01-01'
   where id = '[1,1]'; -- error
 ERROR:  views with INSTEAD OF triggers do not support FOR PORTION OF
--- The check does not depend on which rows match, so it errors even when
--- no rows do.
+-- Here the check runs per row in the executor, so a statement that matches
+-- no rows never reaches it and is allowed.
 update uv_fpo_instead_view
   for portion of valid_at from '2015-01-01' to '2020-01-01'
-  set b = 99 where id = '[9,9]'; -- error, even with no matching rows
-ERROR:  views with INSTEAD OF triggers do not support FOR PORTION OF
+  set b = 99 where id = '[9,9]'; -- ok, no rows affected
 delete from uv_fpo_instead_view
   for portion of valid_at from '2017-01-01' to '2022-01-01'
-  where id = '[9,9]'; -- error, even with no matching rows
-ERROR:  views with INSTEAD OF triggers do not support FOR PORTION OF
+  where id = '[9,9]'; -- ok, no rows affected
 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
diff --git a/src/test/regress/sql/updatable_views.sql b/src/test/regress/sql/updatable_views.sql
index 2ef9aa32f36..b0b607ec335 100644
--- a/src/test/regress/sql/updatable_views.sql
+++ b/src/test/regress/sql/updatable_views.sql
@@ -2170,15 +2170,15 @@ delete from uv_fpo_instead_view
   for portion of valid_at from '2017-01-01' to '2022-01-01'
   where id = '[1,1]'; -- error
 
--- The check does not depend on which rows match, so it errors even when
--- no rows do.
+-- Here the check runs per row in the executor, so a statement that matches
+-- no rows never reaches it and is allowed.
 update uv_fpo_instead_view
   for portion of valid_at from '2015-01-01' to '2020-01-01'
-  set b = 99 where id = '[9,9]'; -- error, even with no matching rows
+  set b = 99 where id = '[9,9]'; -- ok, no rows affected
 
 delete from uv_fpo_instead_view
   for portion of valid_at from '2017-01-01' to '2022-01-01'
-  where id = '[9,9]'; -- error, even with no matching rows
+  where id = '[9,9]'; -- ok, no rows affected
 
 drop view uv_fpo_instead_view;
 drop function uv_fpo_instead_trig();
-- 
2.47.3