v1-0001-Rewrites-CTEs-only-once-to-prevent-a-spurious-error.patch

From 9639a8e1b15c664489eab5d4f66f602c45f0c061 Mon Sep 17 00:00:00 2001
From: Bernice Southey <bernice.southey@gmail.com>
Date: Tue, 25 Nov 2025 15:35:41 +0000
Subject: [PATCH] Rewrites CTEs only once to prevent a spurious error

Re-Rewriting a CTE that inserts an identity-always target entry,
causes the second rewrite to complain about the first rewrite.
Data-modifying WITH clauses need only be rewritten once per query,
and only at the top level as they are unsupported at lower levels.
---
 src/backend/rewrite/rewriteHandler.c |  7 +++++++
 src/test/regress/expected/with.out   | 12 ++++++++++++
 src/test/regress/sql/with.sql        |  9 +++++++++
 3 files changed, 28 insertions(+)

diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index adc9e7600e1..a11a0f6e5d8 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -3888,7 +3888,13 @@ RewriteQuery(Query *parsetree, List *rewrite_events, int orig_rt_length)
 	 * First, recursively process any insert/update/delete/merge statements in
 	 * WITH clauses.  (We have to do this first because the WITH clauses may
 	 * get copied into rule actions below.)
+
+	 * Only do this once per query and only at the top level. (Data-modifying 
+	 * WITH statements are unsupported at lower levels. Repeating this rewrite 
+	 * for view recursion adds nothing and complicates target list checking.) 
 	 */
+	if (rewrite_events == NIL)
+	{
 	foreach(lc1, parsetree->cteList)
 	{
 		CommonTableExpr *cte = lfirst_node(CommonTableExpr, lc1);
@@ -3958,6 +3964,7 @@ RewriteQuery(Query *parsetree, List *rewrite_events, int orig_rt_length)
 					 errmsg("multi-statement DO INSTEAD rules are not supported for data-modifying statements in WITH")));
 		}
 	}
+	}
 
 	/*
 	 * If the statement is an insert, update, delete, or merge, adjust its
diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out
index f4caedf272f..fceefd4867d 100644
--- a/src/test/regress/expected/with.out
+++ b/src/test/regress/expected/with.out
@@ -2872,6 +2872,18 @@ SELECT * FROM bug6051_3;
 ---
 (0 rows)
 
+-- check IDENTITY ALWAYS in WITH can then write to updatable view
+CREATE TEMP TABLE id_alw (i int GENERATED ALWAYS AS IDENTITY);
+CREATE TEMP TABLE id_alw_base (i int);
+CREATE TEMP VIEW id_alw_view AS SELECT * FROM id_alw_base;
+WITH t_cte AS (INSERT INTO id_alw DEFAULT VALUES RETURNING i)
+INSERT INTO id_alw_view SELECT i FROM t_cte;
+SELECT * from id_alw_view;
+ i 
+---
+ 1
+(1 row)
+
 -- check case where CTE reference is removed due to optimization
 EXPLAIN (VERBOSE, COSTS OFF)
 SELECT q1 FROM
diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql
index cd25a5e7154..41047c9cbce 100644
--- a/src/test/regress/sql/with.sql
+++ b/src/test/regress/sql/with.sql
@@ -1360,6 +1360,15 @@ COMMIT;
 
 SELECT * FROM bug6051_3;
 
+-- check IDENTITY ALWAYS in WITH can then write to updatable view
+CREATE TEMP TABLE id_alw (i int GENERATED ALWAYS AS IDENTITY);
+CREATE TEMP TABLE id_alw_base (i int);
+CREATE TEMP VIEW id_alw_view AS SELECT * FROM id_alw_base;
+
+WITH t_cte AS (INSERT INTO id_alw DEFAULT VALUES RETURNING i)
+INSERT INTO id_alw_view SELECT i FROM t_cte;
+SELECT * from id_alw_view;
+
 -- check case where CTE reference is removed due to optimization
 EXPLAIN (VERBOSE, COSTS OFF)
 SELECT q1 FROM
-- 
2.43.0