Thread

Re: Second RewriteQuery complains about first RewriteQuery in edge case

Dean Rasheed <dean.a.rasheed@gmail.com> — 2025-11-26T15:04:20Z

On Wed, 26 Nov 2025 at 12:13, Kirill Reshke <reshkekirill@gmail.com> wrote:
>
> On Wed, 26 Nov 2025 at 13:34, Bernice Southey <bernice.southey@gmail.com> wrote:
>
> > 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.
>
> Nice catch!

Indeed.

> I think we have a bug worth fixing here. However, I am not sure about
> the approach. For me, a safer option would be
> to disallow queries which try to rewrite something for a second time...

No, I disagree, there's no reason a query like this should be
disallowed. The fact that the rewriter is attempting to rewrite parts
of the query multiple times is the bug, albeit one that was probably
harmless before the introduction of generated columns.

The majority of RewriteQuery() is safe if it's called a second time on
the same query, because it fully expands all non-SELECT rules and
auto-updatable target views, so the second time round, it would do
nothing of that kind. However, evidently it's not safe to call
rewriteTargetListIU() twice. Of course, we could attempt to make
rewriteTargetListIU() idempotent, but that might be more effort, and
in any case, processing parts of the query multiple times is a waste
of effort even if it doesn't fail, so I think the approach taken by
the patch is the right one.

Regards,
Dean