On Thu, 27 Nov 2025 at 17:55, Dean Rasheed <[email protected]> wrote:
>
> On Thu, 27 Nov 2025 at 16:55, Tom Lane <[email protected]> wrote:
> >
> > I do think there's another way we could attack it. Similarly
> > to the way VALUES RTEs are either processed or skipped by
> > checking the rangetable length, we could pass down the length
> > of the outer query's cteList, and assume that the last N entries
> > in a product query's cteList have already been processed.
> > (Last N not first N because of the order in which the lists are
> > concatenated at line 596.) Maybe that's too fragile, but the
> > approach seems to have worked all right for VALUES.
>
Here's an update, doing it that way. It does appear somewhat neater.
Regards,
Dean
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
new file mode 100644
index adc9e76..f3a4d3b
--- 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.
+ *
+ * num_ctes_processed is the number of CTEs at the end of the query's cteList
+ * that have already been rewritten, and must not be rewritten again.
*/
static List *
-RewriteQuery(Query *parsetree, List *rewrite_events, int orig_rt_length)
+RewriteQuery(Query *parsetree, List *rewrite_events, int orig_rt_length,
+ int num_ctes_processed)
{
CmdType event = parsetree->commandType;
bool instead = false;
@@ -3883,22 +3887,36 @@ RewriteQuery(Query *parsetree, List *rew
Query *qual_product = NULL;
List *rewritten = NIL;
ListCell *lc1;
+ int i;
/*
* 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 a CTE query more than once (because expanding
+ * generated columns in the targetlist more than once would fail). Since
+ * new CTEs from product queries are added to the start of the list (see
+ * rewriteRuleAction), we just skip the last num_ctes_processed items.
*/
+ i = 0;
foreach(lc1, parsetree->cteList)
{
CommonTableExpr *cte = lfirst_node(CommonTableExpr, lc1);
Query *ctequery = castNode(Query, cte->ctequery);
List *newstuff;
+ /* Skip already-processed CTEs at the end of the list */
+ i++;
+ if (i > list_length(parsetree->cteList) - num_ctes_processed)
+ break;
+
if (ctequery->commandType == CMD_SELECT)
continue;
- newstuff = RewriteQuery(ctequery, rewrite_events, 0);
+ newstuff = RewriteQuery(ctequery, rewrite_events, 0, 0);
/*
* Currently we can only handle unconditional, single-statement DO
@@ -3958,6 +3976,7 @@ RewriteQuery(Query *parsetree, List *rew
errmsg("multi-statement DO INSTEAD rules are not supported for data-modifying statements in WITH")));
}
}
+ num_ctes_processed = list_length(parsetree->cteList);
/*
* If the statement is an insert, update, delete, or merge, adjust its
@@ -4289,7 +4308,8 @@ RewriteQuery(Query *parsetree, List *rew
newstuff = RewriteQuery(pt, rewrite_events,
pt == parsetree ?
orig_rt_length :
- product_orig_rt_length);
+ product_orig_rt_length,
+ num_ctes_processed);
rewritten = list_concat(rewritten, newstuff);
}
@@ -4564,7 +4584,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, 0);
/*
* 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
