Thread

Second RewriteQuery complains about first RewriteQuery in edge case

Bernice Southey <bernice.southey@gmail.com> — 2025-11-26T10:33:53Z

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