From 9639a8e1b15c664489eab5d4f66f602c45f0c061 Mon Sep 17 00:00:00 2001 From: Bernice Southey 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