v2-avoid-rewriting-CTEs-more-than-once.patch

diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
new file mode 100644
index adc9e76..bf3e517
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -3872,9 +3872,13 @@ rewriteTargetView(Query *parsetree, Rela
  * orig_rt_length is the length of the originating query's rtable, for product
  * queries created by fireRules(), and 0 otherwise.  This is used to skip any
  * already-processed VALUES RTEs from the original query.
+ *
+ * rewritten_ctes is a list of the names of any CTEs already rewritten.  When
+ * recursing, we use this to avoid rewriting a CTE more than once.
  */
 static List *
-RewriteQuery(Query *parsetree, List *rewrite_events, int orig_rt_length)
+RewriteQuery(Query *parsetree, List *rewrite_events, int orig_rt_length,
+			 List *rewritten_ctes)
 {
 	CmdType		event = parsetree->commandType;
 	bool		instead = false;
@@ -3884,21 +3888,55 @@ RewriteQuery(Query *parsetree, List *rew
 	List	   *rewritten = NIL;
 	ListCell   *lc1;
 
+	/* Need a local copy of rewritten_ctes to scribble on */
+	rewritten_ctes = list_copy(rewritten_ctes);
+
 	/*
 	 * 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.)
+	 *
+	 * Any new WITH clauses from rule actions are processed when we recurse
+	 * into product queries below.  However, when recursing, we must take care
+	 * to avoid rewriting any CTE query more than once (because expanding
+	 * generated columns in the targetlist more than once would fail).
 	 */
 	foreach(lc1, parsetree->cteList)
 	{
 		CommonTableExpr *cte = lfirst_node(CommonTableExpr, lc1);
 		Query	   *ctequery = castNode(Query, cte->ctequery);
+		bool		found;
 		List	   *newstuff;
 
 		if (ctequery->commandType == CMD_SELECT)
 			continue;
 
-		newstuff = RewriteQuery(ctequery, rewrite_events, 0);
+		/* Skip CTEs that have already been rewritten. */
+		found = false;
+		foreach_ptr(char, ctename, rewritten_ctes)
+		{
+			if (strcmp(ctename, cte->ctename) == 0)
+			{
+				found = true;
+				break;
+			}
+		}
+		if (found)
+			continue;
+
+		/*
+		 * Recursively process this CTE query.  None of its CTEs have been
+		 * processed, so we pass rewritten_ctes = NIL here.  (In fact, it
+		 * shouldn't contain any data-modifying CTEs anyway, because they are
+		 * only allowed at the top level.)
+		 */
+		newstuff = RewriteQuery(ctequery, rewrite_events, 0, NIL);
+
+		/*
+		 * Add this CTE to the list so that it is skipped when we rewrite
+		 * product queries below.
+		 */
+		rewritten_ctes = lappend(rewritten_ctes, cte->ctename);
 
 		/*
 		 * Currently we can only handle unconditional, single-statement DO
@@ -4289,7 +4327,8 @@ RewriteQuery(Query *parsetree, List *rew
 				newstuff = RewriteQuery(pt, rewrite_events,
 										pt == parsetree ?
 										orig_rt_length :
-										product_orig_rt_length);
+										product_orig_rt_length,
+										rewritten_ctes);
 				rewritten = list_concat(rewritten, newstuff);
 			}
 
@@ -4564,7 +4603,7 @@ QueryRewrite(Query *parsetree)
 	 *
 	 * Apply all non-SELECT rules possibly getting 0 or many queries
 	 */
-	querylist = RewriteQuery(parsetree, NIL, 0);
+	querylist = RewriteQuery(parsetree, NIL, 0, NIL);
 
 	/*
 	 * Step 2
diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out
new file mode 100644
index f4caedf..e71c3cc
--- a/src/test/regress/expected/with.out
+++ b/src/test/regress/expected/with.out
@@ -2872,6 +2872,19 @@ SELECT * FROM bug6051_3;
 ---
 (0 rows)
 
+-- check that recursive CTE processing doesn't rewrite a CTE more than once
+-- (must not try to expand IDENTITY column more than once)
+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
new file mode 100644
index cd25a5e..efeb977
--- a/src/test/regress/sql/with.sql
+++ b/src/test/regress/sql/with.sql
@@ -1360,6 +1360,17 @@ COMMIT;
 
 SELECT * FROM bug6051_3;
 
+-- check that recursive CTE processing doesn't rewrite a CTE more than once
+-- (must not try to expand IDENTITY column more than once)
+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