MIME-Version: 1.0
References: 
 <CAHg+QDf7wTLz_vqb1wi1EJ_4Uh+Vxm75+b4c-Ky=6P+yOAHjbQ@mail.gmail.com>
 <CAEZATCXajfi_rRQNtUe7ZPDuuVFsJXw6qo9-KZ=+vL0Aem-7eA@mail.gmail.com>
In-Reply-To: 
 <CAEZATCXajfi_rRQNtUe7ZPDuuVFsJXw6qo9-KZ=+vL0Aem-7eA@mail.gmail.com>
From: SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com>
Date: Sun, 19 Apr 2026 17:55:26 -0700
Message-ID: 
 <CAHg+QDfk7PrpV7eso7DLqn=fCt1ts9axDsUmu6-RZrWost-oDg@mail.gmail.com>
Subject: Re: [BUG] ON CONFLICT DO UPDATE SET x =
 EXCLUDED.<virtual-generated-column>
 errors or silently writes NULL
To: Dean Rasheed <dean.a.rasheed@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000dcda8c064fd9c157"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHg%2BQDfk7PrpV7eso7DLqn%3DfCt1ts9axDsUmu6-RZrWost-oDg%40mail.gmail.com>
Precedence: bulk

--000000000000dcda8c064fd9c157
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

HI,

On Sat, Apr 18, 2026 at 11:14=E2=80=AFAM Dean Rasheed <dean.a.rasheed@gmail=
.com>
wrote:

> On Thu, 16 Apr 2026 at 21:49, SATYANARAYANA NARLAPURAM
> <satyanarlapuram@gmail.com> wrote:
> >
> > Virtual generated column (bgc) behavior for plain and partitioned table=
s
> is different
> > when EXCLUDED.<vgc> references inside for INSERT ... ON CONFLICT DO
> UPDATE.
> > For plain table it errors out with the message "unexpected virtual
> generated column reference"
> > and for partitioned tables, it silently writes NULL (wrong data).
>
> Nice catch!
>
> > I tried fixing this by replacing build_tlist_index with
> build_tlist_index_other_vars . This fix
> > works because build_tlist_index_other_vars only indexes plain-Var TEs o=
f
> exclRelTlist and
> > leaves has_non_vars =3D false, so fix_join_expr skips whole-subtree
> matching and never collapses
> >  the VGC-expanded (EXCLUDED.a * 10) in onConflictSet back into a
> Var(INNER_VAR, vgc_attno).
>
> This doesn't quite work in all cases -- if the generated expression is
> simply a Var, then it is found in the indexed tlist without the
> non_var matching code, leading to the same problem. For example,
> modifying your original test case to this:
>
> CREATE TABLE t (id int PRIMARY KEY,
>                 c  int GENERATED ALWAYS AS (a) VIRTUAL, a int);
>
> Admittedly, that's a rather silly example, but we really ought to have
> a fix that works for all cases.
>

Agreed.


>
> Looking more closely, I think the right fix is to not expand virtual
> generated columns in the targetlist of EXCLUDED (exclRelTlist), so
> then they will not be found as matching expressions in the setrefs.c
> code.
>
> I also noticed that there are already a couple of places in the
> planner that claim that exclRelTlist contains only Vars, so this
> approach makes that claim true (though I don't think those other
> places represented actual bugs).
>
> Attached is a v2 patch doing it that way, with the same tests, which all
> pass.
>

Reran the failing tests and they all passed. Additionally ran the
regression tests.
 Patch looks good to me.


>
> Regards,
> Dean
>

--000000000000dcda8c064fd9c157
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">HI,</div><br><div class=3D"gmail_quote gm=
ail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Sat, Apr 18, =
2026 at 11:14=E2=80=AFAM Dean Rasheed &lt;<a href=3D"mailto:dean.a.rasheed@=
gmail.com">dean.a.rasheed@gmail.com</a>&gt; wrote:<br></div><blockquote cla=
ss=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid =
rgb(204,204,204);padding-left:1ex">On Thu, 16 Apr 2026 at 21:49, SATYANARAY=
ANA NARLAPURAM<br>
&lt;<a href=3D"mailto:satyanarlapuram@gmail.com" target=3D"_blank">satyanar=
lapuram@gmail.com</a>&gt; wrote:<br>
&gt;<br>
&gt; Virtual generated column (bgc) behavior for plain and partitioned tabl=
es is different<br>
&gt; when EXCLUDED.&lt;vgc&gt; references inside for INSERT ... ON CONFLICT=
 DO UPDATE.<br>
&gt; For plain table it errors out with the message &quot;unexpected virtua=
l generated column reference&quot;<br>
&gt; and for partitioned tables, it silently writes NULL (wrong data).<br>
<br>
Nice catch!<br>
<br>
&gt; I tried fixing this by replacing build_tlist_index with build_tlist_in=
dex_other_vars . This fix<br>
&gt; works because build_tlist_index_other_vars only indexes plain-Var TEs =
of exclRelTlist and<br>
&gt; leaves has_non_vars =3D false, so fix_join_expr skips whole-subtree ma=
tching and never collapses<br>
&gt;=C2=A0 the VGC-expanded (EXCLUDED.a * 10) in onConflictSet back into a =
Var(INNER_VAR, vgc_attno).<br>
<br>
This doesn&#39;t quite work in all cases -- if the generated expression is<=
br>
simply a Var, then it is found in the indexed tlist without the<br>
non_var matching code, leading to the same problem. For example,<br>
modifying your original test case to this:<br>
<br>
CREATE TABLE t (id int PRIMARY KEY,<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 c=C2=A0 int GENERAT=
ED ALWAYS AS (a) VIRTUAL, a int);<br>
<br>
Admittedly, that&#39;s a rather silly example, but we really ought to have<=
br>
a fix that works for all cases.<br></blockquote><div><br></div><div>Agreed.=
</div><div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0p=
x 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<br>
Looking more closely, I think the right fix is to not expand virtual<br>
generated columns in the targetlist of EXCLUDED (exclRelTlist), so<br>
then they will not be found as matching expressions in the setrefs.c<br>
code.<br>
<br>
I also noticed that there are already a couple of places in the<br>
planner that claim that exclRelTlist contains only Vars, so this<br>
approach makes that claim true (though I don&#39;t think those other<br>
places represented actual bugs).<br>
<br>
Attached is a v2 patch doing it that way, with the same tests, which all pa=
ss.<br></blockquote><div><br></div><div>Reran the failing tests and they al=
l passed. Additionally ran the regression tests.</div><div>=C2=A0Patch look=
s good to me.</div><div>=C2=A0</div><blockquote class=3D"gmail_quote" style=
=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding=
-left:1ex">
<br>
Regards,<br>
Dean<br>
</blockquote></div></div>

--000000000000dcda8c064fd9c157--