Hi,
I get an odd error if a CTE inserts a GENERATED ALWAYS AS IDENTITY
column, and then tries to modify an automatically updatable view.
create table t(i int generated always as identity);
create table base(j int);
create view v as select * from base;
with cte as (insert into t default values returning i)
delete from v using cte where j = i;
ERROR: cannot insert a non-DEFAULT value into column "i" Column "i" is
an identity column defined as GENERATED ALWAYS.
After much digging and thinking, I discovered it's because
RewriteQuery is processed twice on the CTE, because of the updatable
view recursion. The first time, the target entry is added. The second
time, it errors because it's already added.
cte parse tree: ... {TARGETENTRY :expr {NEXTVALUEEXPR: seqid ...
In rewriteTargetListIU:
apply_default = ((new_tle == NULL && commandType == CMD_INSERT) || ...
if (att_tup->attidentity == ATTRIBUTE_IDENTITY_ALWAYS && !apply_default) ...
ereport(ERROR, (errcode(ERRCODE_GENERATED_ALWAYS)
I wondered, can anything new be added by a re-rewrite of the same
cteList? Nothing in what I could follow of the subsequent RewriteQuery
code makes me believe so. This patch is premised on this belief, and
my above investigation. I may very well be mistaken, given my
inexperience and gaping knowledge gaps.
* As a first-time patch submitter, and first-time much else here, I
fully expect to be flamed for stupidity. This is a crazy function for
me to touch.
* I deliberately didn't indent the if-block in the patch, because the
tab diffs add 140 superfluous interleaved lines of unreadable mess.
* I was uncertain where to add my new test, and if there's more tests
that should be added.
* Comments are the hardest part.
I've learned so much from attempting this, that it was worth it just for that.
Thanks, Bernice
From 9639a8e1b15c664489eab5d4f66f602c45f0c061 Mon Sep 17 00:00:00 2001
From: Bernice Southey <[email protected]>
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
