Second RewriteQuery complains about first RewriteQuery in edge case

From: Bernice Southey <bernice(dot)southey(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Second RewriteQuery complains about first RewriteQuery in edge case
Date: 2025-11-26 10:33:53
Message-ID: CAEDh4nyD6MSH9bROhsOsuTqGAv_QceU_GDvN9WcHLtZTCYM1kA@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
v1-0001-Rewrites-CTEs-only-once-to-prevent-a-spurious-error.patch text/x-patch 3.3 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message shveta malik 2025-11-26 10:45:27 Re: Proposal: Conflict log history table for Logical Replication
Previous Message Soumya S Murali 2025-11-26 10:15:06 Re: [PATCH] Expose checkpoint timestamp and duration in pg_stat_checkpointer