MIME-Version: 1.0
From: SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com>
Date: Thu, 16 Apr 2026 13:48:55 -0700
Message-ID: 
 <CAHg+QDf7wTLz_vqb1wi1EJ_4Uh+Vxm75+b4c-Ky=6P+yOAHjbQ@mail.gmail.com>
Subject: [BUG] ON CONFLICT DO UPDATE SET x =
 EXCLUDED.<virtual-generated-column>
 errors or silently writes NULL
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/mixed; boundary="000000000000aa2f3d064f99f6ef"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHg%2BQDf7wTLz_vqb1wi1EJ_4Uh%2BVxm75%2Bb4c-Ky%3D6P%2ByOAHjbQ%40mail.gmail.com>
Precedence: bulk

--000000000000aa2f3d064f99f6ef
Content-Type: multipart/alternative; boundary="000000000000aa2f3b064f99f6ed"

--000000000000aa2f3b064f99f6ed
Content-Type: text/plain; charset="UTF-8"

Hi Hackers,

Virtual generated column (bgc) behavior for plain and partitioned tables 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).


Repro:

-- plan table

    DROP TABLE IF EXISTS t;
    CREATE TABLE t (id int PRIMARY KEY,
                    a  int,
                    c  int GENERATED ALWAYS AS (a * 10) VIRTUAL);
    INSERT INTO t VALUES (1, 5);

    INSERT INTO t VALUES (1, 7)
        ON CONFLICT (id) DO UPDATE SET a = EXCLUDED.c;
    -- ERROR:  unexpected virtual generated column reference


-- Partitioned table:

    DROP TABLE IF EXISTS tp;
    CREATE TABLE tp (id int PRIMARY KEY,
                     a  int,
                     c  int GENERATED ALWAYS AS (a * 10) VIRTUAL)
        PARTITION BY RANGE (id);
    CREATE TABLE tp1 PARTITION OF tp FOR VALUES FROM (1) TO (100);
    INSERT INTO tp VALUES (1, 5);

    INSERT INTO tp VALUES (1, 7)
        ON CONFLICT (id) DO UPDATE SET a = EXCLUDED.c;

    SELECT * FROM tp

 id | a | c
----+---+---
  1 |   |


We have two options to fix, (1) throw an error for partitioned tables
similar to plain tables or
(2) support the scenario fixing for both the cases.

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 of
exclRelTlist and
leaves has_non_vars = 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).

I am not super familiar with this area so I am not sure if this breaks
anything. Ran the existing
tests and they seem to be passing.


Thanks,
Satya

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

<div dir=3D"ltr">Hi Hackers,<br><br>Virtual generated column (bgc) behavior=
 for plain and partitioned tables is different<div>when EXCLUDED.&lt;vgc&gt=
; references inside for INSERT ... ON CONFLICT DO UPDATE.</div><div>For pla=
in table it errors out with the message &quot;unexpected virtual generated =
column reference&quot;</div><div>and for partitioned tables, it silently wr=
ites NULL (wrong data).</div><div><br></div><div><div><br>Repro:<br><br>-- =
plan table<br><br>=C2=A0 =C2=A0 DROP TABLE IF EXISTS t;<br>=C2=A0 =C2=A0 CR=
EATE TABLE t (id int PRIMARY KEY,<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 a =C2=A0int,<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0=
 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 c =C2=A0int GENERATED ALWAYS AS =
(a * 10) VIRTUAL);<br>=C2=A0 =C2=A0 INSERT INTO t VALUES (1, 5);<br><br>=C2=
=A0 =C2=A0 INSERT INTO t VALUES (1, 7)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 ON CO=
NFLICT (id) DO UPDATE SET a =3D EXCLUDED.c;<br>=C2=A0 =C2=A0 -- ERROR: =C2=
=A0unexpected virtual generated column reference<br>=C2=A0 =C2=A0<br><br>--=
 Partitioned table:=C2=A0<br><br>=C2=A0 =C2=A0 DROP TABLE IF EXISTS tp;<br>=
=C2=A0 =C2=A0 CREATE TABLE tp (id int PRIMARY KEY,<br>=C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0a =C2=A0int,<br>=C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0c =
=C2=A0int GENERATED ALWAYS AS (a * 10) VIRTUAL)<br>=C2=A0 =C2=A0 =C2=A0 =C2=
=A0 PARTITION BY RANGE (id);<br>=C2=A0 =C2=A0 CREATE TABLE tp1 PARTITION OF=
 tp FOR VALUES FROM (1) TO (100);<br>=C2=A0 =C2=A0 INSERT INTO tp VALUES (1=
, 5);<br><br>=C2=A0 =C2=A0 INSERT INTO tp VALUES (1, 7)<br>=C2=A0 =C2=A0 =
=C2=A0 =C2=A0 ON CONFLICT (id) DO UPDATE SET a =3D EXCLUDED.c;</div><div><b=
r></div><div>=C2=A0 =C2=A0 SELECT * FROM tp</div><div><br>=C2=A0id | a | c =
<br>----+---+---<br>=C2=A0 1 | =C2=A0 |=C2=A0=C2=A0<br><br><br>We have two =
options to fix, (1) throw an error for partitioned tables similar to plain =
tables or</div><div>(2) support the scenario fixing for both the cases.</di=
v><div><br></div><div>I tried fixing this by replacing build_tlist_index wi=
th build_tlist_index_other_vars . This fix</div><div>works because build_tl=
ist_index_other_vars only indexes plain-Var TEs of exclRelTlist and=C2=A0</=
div><div>leaves has_non_vars =3D false, so fix_join_expr skips whole-subtre=
e matching and never collapses</div><div>=C2=A0the VGC-expanded (EXCLUDED.a=
 * 10) in onConflictSet back into a Var(INNER_VAR, vgc_attno).</div><div><b=
r></div><div>I am not super familiar with this area so I am not sure if thi=
s breaks anything. Ran the existing=C2=A0</div><div>tests and they seem to =
be passing.</div><div><br><br>Thanks,<br>Satya=C2=A0</div></div></div>

--000000000000aa2f3b064f99f6ed--

--000000000000aa2f3d064f99f6ef
Content-Type: application/octet-stream; name="vgc-excluded-fix.patch"
Content-Disposition: attachment; filename="vgc-excluded-fix.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_mo1ybvt40>
X-Attachment-Id: f_mo1ybvt40

ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL29wdGltaXplci9wbGFuL3NldHJlZnMuYyBiL3NyYy9i
YWNrZW5kL29wdGltaXplci9wbGFuL3NldHJlZnMuYwppbmRleCBmZjBlODc1Zi4uYmIyNTQwNzkg
MTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL29wdGltaXplci9wbGFuL3NldHJlZnMuYworKysgYi9z
cmMvYmFja2VuZC9vcHRpbWl6ZXIvcGxhbi9zZXRyZWZzLmMKQEAgLTE2Nyw2ICsxNjcsNyBAQCBz
dGF0aWMgdm9pZCBzZXRfcGFyYW1fcmVmZXJlbmNlcyhQbGFubmVySW5mbyAqcm9vdCwgUGxhbiAq
cGxhbik7CiBzdGF0aWMgTm9kZSAqY29udmVydF9jb21iaW5pbmdfYWdncmVmcyhOb2RlICpub2Rl
LCB2b2lkICpjb250ZXh0KTsKIHN0YXRpYyB2b2lkIHNldF9kdW1teV90bGlzdF9yZWZlcmVuY2Vz
KFBsYW4gKnBsYW4sIGludCBydG9mZnNldCk7CiBzdGF0aWMgaW5kZXhlZF90bGlzdCAqYnVpbGRf
dGxpc3RfaW5kZXgoTGlzdCAqdGxpc3QpOworc3RhdGljIGluZGV4ZWRfdGxpc3QgKmJ1aWxkX3Rs
aXN0X2luZGV4X290aGVyX3ZhcnMoTGlzdCAqdGxpc3QsIGludCBpZ25vcmVfcmVsKTsKIHN0YXRp
YyBWYXIgKnNlYXJjaF9pbmRleGVkX3RsaXN0X2Zvcl92YXIoVmFyICp2YXIsCiAJCQkJCQkJCQkJ
IGluZGV4ZWRfdGxpc3QgKml0bGlzdCwKIAkJCQkJCQkJCQkgaW50IG5ld3Zhcm5vLApAQCAtMTE2
OCw3ICsxMTY5LDggQEAgc2V0X3BsYW5fcmVmcyhQbGFubmVySW5mbyAqcm9vdCwgUGxhbiAqcGxh
biwgaW50IHJ0b2Zmc2V0KQogCQkJCXsKIAkJCQkJaW5kZXhlZF90bGlzdCAqaXRsaXN0OwogCi0J
CQkJCWl0bGlzdCA9IGJ1aWxkX3RsaXN0X2luZGV4KHNwbGFuLT5leGNsUmVsVGxpc3QpOworCQkJ
CQlpdGxpc3QgPSBidWlsZF90bGlzdF9pbmRleF9vdGhlcl92YXJzKHNwbGFuLT5leGNsUmVsVGxp
c3QsCisJCQkJCQkJCQkJCQkJCSAgMCk7CiAKIAkJCQkJc3BsYW4tPm9uQ29uZmxpY3RTZXQgPQog
CQkJCQkJZml4X2pvaW5fZXhwcihyb290LCBzcGxhbi0+b25Db25mbGljdFNldCwKZGlmZiAtLWdp
dCBhL3NyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQvZ2VuZXJhdGVkX3ZpcnR1YWwub3V0IGIvc3Jj
L3Rlc3QvcmVncmVzcy9leHBlY3RlZC9nZW5lcmF0ZWRfdmlydHVhbC5vdXQKaW5kZXggZmM0MWM0
ODAuLjhmNWFkOTFiIDEwMDY0NAotLS0gYS9zcmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVkL2dlbmVy
YXRlZF92aXJ0dWFsLm91dAorKysgYi9zcmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVkL2dlbmVyYXRl
ZF92aXJ0dWFsLm91dApAQCAtMTcyMywzICsxNzIzLDU1IEBAIHNlbGVjdCAqIGZyb20gZ3Rlc3Qz
MyB3aGVyZSBiIGlzIG51bGw7CiAKIHJlc2V0IGNvbnN0cmFpbnRfZXhjbHVzaW9uOwogZHJvcCB0
YWJsZSBndGVzdDMzOworLS0gRW5zdXJlIHRoYXQgRVhDTFVERUQuPHZpcnR1YWwtZ2VuZXJhdGVk
LWNvbHVtbj4gaW4gSU5TRVJUIC4uLiBPTiBDT05GTElDVAorLS0gRE8gVVBEQVRFIGlzIGV4cGFu
ZGVkIHRvIHRoZSBnZW5lcmF0aW9uIGV4cHJlc3Npb24sIGJvdGggZm9yIHBsYWluIGFuZAorLS0g
cGFydGl0aW9uZWQgdGFyZ2V0IHJlbGF0aW9ucy4gIFByZXZpb3VzbHksIHRoZSBwbGFuLXRpbWUg
c2V0cmVmcyBzdGVwCistLSB3b3VsZCByZS1jb2xsYXBzZSB0aGUgZXhwYW5kZWQgZXhwcmVzc2lv
biBpbnRvIGEgVmFyIHJlZmVycmluZyB0byB0aGUKKy0tIHZpcnR1YWwgYXR0bnVtIG9mIHRoZSBF
WENMVURFRCBzbG90LCBwcm9kdWNpbmcgZWl0aGVyIGFuICJ1bmV4cGVjdGVkCistLSB2aXJ0dWFs
IGdlbmVyYXRlZCBjb2x1bW4gcmVmZXJlbmNlIiBlcnJvciAocGxhaW4gdGFibGUpIG9yIGEgc2ls
ZW50IE5VTEwKKy0tIChwYXJ0aXRpb25lZCB0YWJsZSwgdmlhIHR1cGxlIHJvdXRpbmcncyBhdHRy
aWJ1dGUgcmVtYXBwaW5nKS4KK2NyZWF0ZSB0YWJsZSBndGVzdDM0IChpZCBpbnQgcHJpbWFyeSBr
ZXksIGEgaW50LAorICAgICAgICAgICAgICAgICAgICAgIGMgaW50IGdlbmVyYXRlZCBhbHdheXMg
YXMgKGEgKiAxMCkgdmlydHVhbCk7CitpbnNlcnQgaW50byBndGVzdDM0IHZhbHVlcyAoMSwgNSk7
CitpbnNlcnQgaW50byBndGVzdDM0IHZhbHVlcyAoMSwgNykKKyAgICBvbiBjb25mbGljdCAoaWQp
IGRvIHVwZGF0ZSBzZXQgYSA9IGV4Y2x1ZGVkLmMgcmV0dXJuaW5nICo7CisgaWQgfCBhICB8ICBj
ICAKKy0tLS0rLS0tLSstLS0tLQorICAxIHwgNzAgfCA3MDAKKygxIHJvdykKKworaW5zZXJ0IGlu
dG8gZ3Rlc3QzNCB2YWx1ZXMgKDEsIDIpCisgICAgb24gY29uZmxpY3QgKGlkKSBkbyB1cGRhdGUg
c2V0IGEgPSBndGVzdDM0LmMgKyBleGNsdWRlZC5jIHJldHVybmluZyAqOworIGlkIHwgIGEgIHwg
IGMgICAKKy0tLS0rLS0tLS0rLS0tLS0tCisgIDEgfCA3MjAgfCA3MjAwCisoMSByb3cpCisKK2lu
c2VydCBpbnRvIGd0ZXN0MzQgdmFsdWVzICgxLCAzKQorICAgIG9uIGNvbmZsaWN0IChpZCkgZG8g
dXBkYXRlIHNldCBhID0gOTk5IHdoZXJlIGV4Y2x1ZGVkLmMgPiAyMCByZXR1cm5pbmcgKjsKKyBp
ZCB8ICBhICB8ICBjICAgCistLS0tKy0tLS0tKy0tLS0tLQorICAxIHwgOTk5IHwgOTk5MAorKDEg
cm93KQorCitkcm9wIHRhYmxlIGd0ZXN0MzQ7CitjcmVhdGUgdGFibGUgZ3Rlc3QzNHAgKGlkIGlu
dCBwcmltYXJ5IGtleSwgYSBpbnQsCisgICAgICAgICAgICAgICAgICAgICAgIGMgaW50IGdlbmVy
YXRlZCBhbHdheXMgYXMgKGEgKiAxMCkgdmlydHVhbCkKKyAgICBwYXJ0aXRpb24gYnkgcmFuZ2Ug
KGlkKTsKK2NyZWF0ZSB0YWJsZSBndGVzdDM0cF8xIHBhcnRpdGlvbiBvZiBndGVzdDM0cCBmb3Ig
dmFsdWVzIGZyb20gKDEpIHRvICgxMDApOworaW5zZXJ0IGludG8gZ3Rlc3QzNHAgdmFsdWVzICgx
LCA1KTsKK2luc2VydCBpbnRvIGd0ZXN0MzRwIHZhbHVlcyAoMSwgNykKKyAgICBvbiBjb25mbGlj
dCAoaWQpIGRvIHVwZGF0ZSBzZXQgYSA9IGV4Y2x1ZGVkLmMgcmV0dXJuaW5nICo7CisgaWQgfCBh
ICB8ICBjICAKKy0tLS0rLS0tLSstLS0tLQorICAxIHwgNzAgfCA3MDAKKygxIHJvdykKKworaW5z
ZXJ0IGludG8gZ3Rlc3QzNHAgdmFsdWVzICgxLCAyKQorICAgIG9uIGNvbmZsaWN0IChpZCkgZG8g
dXBkYXRlIHNldCBhID0gZ3Rlc3QzNHAuYyArIGV4Y2x1ZGVkLmMgcmV0dXJuaW5nICo7CisgaWQg
fCAgYSAgfCAgYyAgIAorLS0tLSstLS0tLSstLS0tLS0KKyAgMSB8IDcyMCB8IDcyMDAKKygxIHJv
dykKKworZHJvcCB0YWJsZSBndGVzdDM0cDsKZGlmZiAtLWdpdCBhL3NyYy90ZXN0L3JlZ3Jlc3Mv
c3FsL2dlbmVyYXRlZF92aXJ0dWFsLnNxbCBiL3NyYy90ZXN0L3JlZ3Jlc3Mvc3FsL2dlbmVyYXRl
ZF92aXJ0dWFsLnNxbAppbmRleCA5YjMyNDEzZS4uNmU5NTgwMTEgMTAwNjQ0Ci0tLSBhL3NyYy90
ZXN0L3JlZ3Jlc3Mvc3FsL2dlbmVyYXRlZF92aXJ0dWFsLnNxbAorKysgYi9zcmMvdGVzdC9yZWdy
ZXNzL3NxbC9nZW5lcmF0ZWRfdmlydHVhbC5zcWwKQEAgLTkwNiwzICs5MDYsMzIgQEAgc2VsZWN0
ICogZnJvbSBndGVzdDMzIHdoZXJlIGIgaXMgbnVsbDsKIAogcmVzZXQgY29uc3RyYWludF9leGNs
dXNpb247CiBkcm9wIHRhYmxlIGd0ZXN0MzM7CisKKy0tIEVuc3VyZSB0aGF0IEVYQ0xVREVELjx2
aXJ0dWFsLWdlbmVyYXRlZC1jb2x1bW4+IGluIElOU0VSVCAuLi4gT04gQ09ORkxJQ1QKKy0tIERP
IFVQREFURSBpcyBleHBhbmRlZCB0byB0aGUgZ2VuZXJhdGlvbiBleHByZXNzaW9uLCBib3RoIGZv
ciBwbGFpbiBhbmQKKy0tIHBhcnRpdGlvbmVkIHRhcmdldCByZWxhdGlvbnMuICBQcmV2aW91c2x5
LCB0aGUgcGxhbi10aW1lIHNldHJlZnMgc3RlcAorLS0gd291bGQgcmUtY29sbGFwc2UgdGhlIGV4
cGFuZGVkIGV4cHJlc3Npb24gaW50byBhIFZhciByZWZlcnJpbmcgdG8gdGhlCistLSB2aXJ0dWFs
IGF0dG51bSBvZiB0aGUgRVhDTFVERUQgc2xvdCwgcHJvZHVjaW5nIGVpdGhlciBhbiAidW5leHBl
Y3RlZAorLS0gdmlydHVhbCBnZW5lcmF0ZWQgY29sdW1uIHJlZmVyZW5jZSIgZXJyb3IgKHBsYWlu
IHRhYmxlKSBvciBhIHNpbGVudCBOVUxMCistLSAocGFydGl0aW9uZWQgdGFibGUsIHZpYSB0dXBs
ZSByb3V0aW5nJ3MgYXR0cmlidXRlIHJlbWFwcGluZykuCitjcmVhdGUgdGFibGUgZ3Rlc3QzNCAo
aWQgaW50IHByaW1hcnkga2V5LCBhIGludCwKKyAgICAgICAgICAgICAgICAgICAgICBjIGludCBn
ZW5lcmF0ZWQgYWx3YXlzIGFzIChhICogMTApIHZpcnR1YWwpOworaW5zZXJ0IGludG8gZ3Rlc3Qz
NCB2YWx1ZXMgKDEsIDUpOworaW5zZXJ0IGludG8gZ3Rlc3QzNCB2YWx1ZXMgKDEsIDcpCisgICAg
b24gY29uZmxpY3QgKGlkKSBkbyB1cGRhdGUgc2V0IGEgPSBleGNsdWRlZC5jIHJldHVybmluZyAq
OworaW5zZXJ0IGludG8gZ3Rlc3QzNCB2YWx1ZXMgKDEsIDIpCisgICAgb24gY29uZmxpY3QgKGlk
KSBkbyB1cGRhdGUgc2V0IGEgPSBndGVzdDM0LmMgKyBleGNsdWRlZC5jIHJldHVybmluZyAqOwor
aW5zZXJ0IGludG8gZ3Rlc3QzNCB2YWx1ZXMgKDEsIDMpCisgICAgb24gY29uZmxpY3QgKGlkKSBk
byB1cGRhdGUgc2V0IGEgPSA5OTkgd2hlcmUgZXhjbHVkZWQuYyA+IDIwIHJldHVybmluZyAqOwor
ZHJvcCB0YWJsZSBndGVzdDM0OworCitjcmVhdGUgdGFibGUgZ3Rlc3QzNHAgKGlkIGludCBwcmlt
YXJ5IGtleSwgYSBpbnQsCisgICAgICAgICAgICAgICAgICAgICAgIGMgaW50IGdlbmVyYXRlZCBh
bHdheXMgYXMgKGEgKiAxMCkgdmlydHVhbCkKKyAgICBwYXJ0aXRpb24gYnkgcmFuZ2UgKGlkKTsK
K2NyZWF0ZSB0YWJsZSBndGVzdDM0cF8xIHBhcnRpdGlvbiBvZiBndGVzdDM0cCBmb3IgdmFsdWVz
IGZyb20gKDEpIHRvICgxMDApOworaW5zZXJ0IGludG8gZ3Rlc3QzNHAgdmFsdWVzICgxLCA1KTsK
K2luc2VydCBpbnRvIGd0ZXN0MzRwIHZhbHVlcyAoMSwgNykKKyAgICBvbiBjb25mbGljdCAoaWQp
IGRvIHVwZGF0ZSBzZXQgYSA9IGV4Y2x1ZGVkLmMgcmV0dXJuaW5nICo7CitpbnNlcnQgaW50byBn
dGVzdDM0cCB2YWx1ZXMgKDEsIDIpCisgICAgb24gY29uZmxpY3QgKGlkKSBkbyB1cGRhdGUgc2V0
IGEgPSBndGVzdDM0cC5jICsgZXhjbHVkZWQuYyByZXR1cm5pbmcgKjsKK2Ryb3AgdGFibGUgZ3Rl
c3QzNHA7Cg==
--000000000000aa2f3d064f99f6ef--