MIME-Version: 1.0
References: 
 <CAHg+QDf7wTLz_vqb1wi1EJ_4Uh+Vxm75+b4c-Ky=6P+yOAHjbQ@mail.gmail.com>
In-Reply-To: 
 <CAHg+QDf7wTLz_vqb1wi1EJ_4Uh+Vxm75+b4c-Ky=6P+yOAHjbQ@mail.gmail.com>
From: Dean Rasheed <dean.a.rasheed@gmail.com>
Date: Sat, 18 Apr 2026 19:14:20 +0100
Message-ID: 
 <CAEZATCXajfi_rRQNtUe7ZPDuuVFsJXw6qo9-KZ=+vL0Aem-7eA@mail.gmail.com>
Subject: Re: [BUG] ON CONFLICT DO UPDATE SET x =
 EXCLUDED.<virtual-generated-column>
 errors or silently writes NULL
To: SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/mixed; boundary="0000000000008199f7064fc0093f"
Archived-At: 
 <https://www.postgresql.org/message-id/CAEZATCXajfi_rRQNtUe7ZPDuuVFsJXw6qo9-KZ%3D%2BvL0Aem-7eA%40mail.gmail.com>
Precedence: bulk

--0000000000008199f7064fc0093f
Content-Type: text/plain; charset="UTF-8"

On Thu, 16 Apr 2026 at 21:49, SATYANARAYANA NARLAPURAM
<satyanarlapuram@gmail.com> wrote:
>
> 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).

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 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).

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.

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.

Regards,
Dean

--0000000000008199f7064fc0093f
Content-Type: text/x-patch; charset="US-ASCII";
	name="v2-0001-Fix-expansion-of-virtual-generated-columns-in-EXC.patch"
Content-Disposition: attachment;
	filename="v2-0001-Fix-expansion-of-virtual-generated-columns-in-EXC.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_mo4ngbz40>
X-Attachment-Id: f_mo4ngbz40

RnJvbSBhMGIzNTJhOGI5OGVlMjhhN2ZhYmEwZWQ3Yjg2ODRiYjBmOWQyNzNjIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBEZWFuIFJhc2hlZWQgPGRlYW4uYS5yYXNoZWVkQGdtYWlsLmNv
bT4KRGF0ZTogU2F0LCAxOCBBcHIgMjAyNiAxODo0OTozOCArMDEwMApTdWJqZWN0OiBbUEFUQ0gg
djJdIEZpeCBleHBhbnNpb24gb2YgdmlydHVhbCBnZW5lcmF0ZWQgY29sdW1ucyBpbiBFWENMVURF
RC4KCklmIHRoZSBTRVQgb3IgV0hFUkUgY2xhdXNlIG9mIGFuIElOU0VSVCAuLi4gT04gQ09ORkxJ
Q1QgRE8gVVBEQVRFCmNvbnRhaW5lZCByZWZlcmVuY2VzIHRvIHZpcnR1YWwgZ2VuZXJhdGVkIGNv
bHVtbnMgb2YgdGhlIEVYQ0xVREVECnBzZXVkby1yZWxhdGlvbiwgdGhleSB3ZXJlIG5vdCBiZSBw
cm9wZXJseSBleHBhbmRlZCwgbGVhZGluZyB0byBhbgplcnJvciwgb3Igd3JvbmcgcmVzdWx0cy4K
ClRoZSBwcm9ibGVtIHdhcyB0aGF0IGV4cGFuZF92aXJ0dWFsX2dlbmVyYXRlZF9jb2x1bW5zKCkg
d291bGQgZXhwYW5kCnZpcnR1YWwgZ2VuZXJhdGVkIGNvbHVtbnMgaW4gYm90aCB0aGUgU0VUIGFu
ZCBXSEVSRSBjbGF1c2VzIGFuZCBpbiB0aGUKdGFyZ2V0bGlzdCBvZiB0aGUgRVhDTFVERUQgcHNl
dWRvLXJlbGF0aW9uLiBUaGVuIGZpeF9qb2luX2V4cHIoKSBmcm9tCnNldF9wbGFuX3JlZnMoKSB3
b3VsZCB0dXJuIHRoZSBleHBhbmRlZCBleHByZXNzaW9ucyBpbiB0aGUgU0VUIGFuZApXSEVSRSBj
bGF1c2VzIGJhY2sgaW50byBWYXJzLCBiZWNhdXNlIHRoZXkgd291bGQgYmUgZm91bmQgdG8gbWF0
Y2ggdGhlCmVudHJpZXMgaW4gdGhlIGluZGV4ZWQgdGxpc3QgcHJvZHVjZWQgZnJvbSBleGNsUmVs
VGxpc3QuIFRvIHByZXZlbnQKdGhhdCBmcm9tIGhhcHBlbmluZywgZG8gbm90IGV4cGFuZCB2aXJ0
dWFsIGdlbmVyYXRlZCBjb2x1bW5zIGluIHRoZQpFWENMVURFRCBwc2V1ZG8tcmVsYXRpb24ncyB0
YXJnZXRsaXN0LgoKQXMgYSByZXN1bHQsIGV4Y2xSZWxUbGlzdCBub3cgYWx3YXlzIGNvbnRhaW5z
IG9ubHkgVmFycyAtLSBzb21ldGhpbmcKYWxyZWFkeSBjbGFpbWVkIGluIGEgY291cGxlIG9mIGV4
aXN0aW5nIGNvbW1lbnRzIGluIHRoZSBwbGFubmVyLCB3aGljaApyZWxpZWQgb24gdGhhdCB0byBz
a2lwIHByb2Nlc3NpbmcgaXQgKHRob3VnaCB0aG9zZSBkaWQgbm90IGFwcGVhciB0bwpjb25zdGl0
dXRlIGFjdGl2ZSBidWdzKS4KClJlcG9ydGVkLWJ5OiBTYXR5YW5hcmF5YW5hIE5hcmxhcHVyYW0g
PHNhdHlhbmFybGFwdXJhbUBnbWFpbC5jb20+CkF1dGhvcjogU2F0eWFuYXJheWFuYSBOYXJsYXB1
cmFtIDxzYXR5YW5hcmxhcHVyYW1AZ21haWwuY29tPgpBdXRob3I6IERlYW4gUmFzaGVlZCA8ZGVh
bi5hLnJhc2hlZWRAZ21haWwuY29tPgpEaXNjdXNzaW9uOiBodHRwczovL3Bvc3Rnci5lcy9tL0NB
SGcrUURmN3dUTHpfdnFiMXdpMUVKXzRVaCtWeG03NStiNGMtS3k9NlAreU9BSGpiUUBtYWlsLmdt
YWlsLmNvbQpCYWNrcGF0Y2gtdGhyb3VnaDogMTgKLS0tCiBzcmMvYmFja2VuZC9vcHRpbWl6ZXIv
cHJlcC9wcmVwam9pbnRyZWUuYyAgICAgfCAxOSArKysrKysrKwogLi4uL3JlZ3Jlc3MvZXhwZWN0
ZWQvZ2VuZXJhdGVkX3ZpcnR1YWwub3V0ICAgIHwgNDggKysrKysrKysrKysrKysrKysrKwogc3Jj
L3Rlc3QvcmVncmVzcy9zcWwvZ2VuZXJhdGVkX3ZpcnR1YWwuc3FsICAgIHwgMjUgKysrKysrKysr
KwogMyBmaWxlcyBjaGFuZ2VkLCA5MiBpbnNlcnRpb25zKCspCgpkaWZmIC0tZ2l0IGEvc3JjL2Jh
Y2tlbmQvb3B0aW1pemVyL3ByZXAvcHJlcGpvaW50cmVlLmMgYi9zcmMvYmFja2VuZC9vcHRpbWl6
ZXIvcHJlcC9wcmVwam9pbnRyZWUuYwppbmRleCA5NWJmNTE2MDZjYy4uNDQyNGZkYmU5MDYgMTAw
NjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL29wdGltaXplci9wcmVwL3ByZXBqb2ludHJlZS5jCisrKyBi
L3NyYy9iYWNrZW5kL29wdGltaXplci9wcmVwL3ByZXBqb2ludHJlZS5jCkBAIC01MDEsNiArNTAx
LDcgQEAgZXhwYW5kX3ZpcnR1YWxfZ2VuZXJhdGVkX2NvbHVtbnMoUGxhbm5lckluZm8gKnJvb3Qs
IFF1ZXJ5ICpwYXJzZSwKIAl7CiAJCUxpc3QJICAgKnRsaXN0ID0gTklMOwogCQlwdWxsdXBfcmVw
bGFjZV92YXJzX2NvbnRleHQgcnZjb250ZXh0OworCQlMaXN0CSAgICpzYXZlX2V4Y2xSZWxUbGlz
dCA9IE5JTDsKIAogCQlmb3IgKGludCBpID0gMDsgaSA8IHR1cGRlc2MtPm5hdHRzOyBpKyspCiAJ
CXsKQEAgLTU2OCw4ICs1NjksMjYgQEAgZXhwYW5kX3ZpcnR1YWxfZ2VuZXJhdGVkX2NvbHVtbnMo
UGxhbm5lckluZm8gKnJvb3QsIFF1ZXJ5ICpwYXJzZSwKIAogCQkvKgogCQkgKiBBcHBseSBwdWxs
dXAgdmFyaWFibGUgcmVwbGFjZW1lbnQgdGhyb3VnaG91dCB0aGUgcXVlcnkgdHJlZS4KKwkJICoK
KwkJICogV2UgaW50ZW50aW9uYWxseSBkbyBub3QgdG91Y2ggdGhlIEVYQ0xVREVEIHBzZXVkby1y
ZWxhdGlvbidzCisJCSAqIHRhcmdldGxpc3QgaGVyZS4gIFZhcmlvdXMgcGxhY2VzIGluIHRoZSBw
bGFubmVyIGFzc3VtZSB0aGF0IGl0CisJCSAqIGNvbnRhaW5zIG9ubHkgVmFycywgYW5kIHdlIHdh
bnQgdGhhdCB0byByZW1haW4gdGhlIGNhc2UuICBNb3JlCisJCSAqIGltcG9ydGFudGx5LCB3ZSBk
b24ndCB3YW50IHNldHJlZnMuYyB0byB0dXJuIGFueSBleHBhbmRlZAorCQkgKiBFWENMVURFRC52
aXJ0dWFsX2NvbHVtbiBleHByZXNzaW9ucyBpbiBvdGhlciBwYXJ0cyBvZiB0aGUgcXVlcnkKKwkJ
ICogYmFjayBpbnRvIFZhcnMgcmVmZXJlbmNpbmcgdGhlIG9yaWdpbmFsIHZpcnR1YWwgY29sdW1u
LCB3aGljaAorCQkgKiBzZXRfcGxhbl9yZWZzKCkgd291bGQgZG8gaWYgZXhjbFJlbFRsaXN0IGNv
bnRhaW5lZCBtYXRjaGluZworCQkgKiBleHByZXNzaW9ucy4KIAkJICovCisJCWlmIChwYXJzZS0+
b25Db25mbGljdCkKKwkJeworCQkJc2F2ZV9leGNsUmVsVGxpc3QgPSBwYXJzZS0+b25Db25mbGlj
dC0+ZXhjbFJlbFRsaXN0OworCQkJcGFyc2UtPm9uQ29uZmxpY3QtPmV4Y2xSZWxUbGlzdCA9IE5J
TDsKKwkJfQorCiAJCXBhcnNlID0gKFF1ZXJ5ICopIHB1bGx1cF9yZXBsYWNlX3ZhcnMoKE5vZGUg
KikgcGFyc2UsICZydmNvbnRleHQpOworCisJCWlmIChwYXJzZS0+b25Db25mbGljdCkKKwkJCXBh
cnNlLT5vbkNvbmZsaWN0LT5leGNsUmVsVGxpc3QgPSBzYXZlX2V4Y2xSZWxUbGlzdDsKIAl9CiAK
IAlyZXR1cm4gcGFyc2U7CmRpZmYgLS1naXQgYS9zcmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVkL2dl
bmVyYXRlZF92aXJ0dWFsLm91dCBiL3NyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQvZ2VuZXJhdGVk
X3ZpcnR1YWwub3V0CmluZGV4IGZjNDFjNDgwZDQwLi40ZWI1ZDUzNzZkMCAxMDA2NDQKLS0tIGEv
c3JjL3Rlc3QvcmVncmVzcy9leHBlY3RlZC9nZW5lcmF0ZWRfdmlydHVhbC5vdXQKKysrIGIvc3Jj
L3Rlc3QvcmVncmVzcy9leHBlY3RlZC9nZW5lcmF0ZWRfdmlydHVhbC5vdXQKQEAgLTE3MjMsMyAr
MTcyMyw1MSBAQCBzZWxlY3QgKiBmcm9tIGd0ZXN0MzMgd2hlcmUgYiBpcyBudWxsOwogCiByZXNl
dCBjb25zdHJhaW50X2V4Y2x1c2lvbjsKIGRyb3AgdGFibGUgZ3Rlc3QzMzsKKy0tIEVuc3VyZSB0
aGF0IEVYQ0xVREVELjx2aXJ0dWFsLWdlbmVyYXRlZC1jb2x1bW4+IGluIElOU0VSVCAuLi4gT04g
Q09ORkxJQ1QKKy0tIERPIFVQREFURSBpcyBleHBhbmRlZCB0byB0aGUgZ2VuZXJhdGlvbiBleHBy
ZXNzaW9uLCBib3RoIGZvciBwbGFpbiBhbmQKKy0tIHBhcnRpdGlvbmVkIHRhcmdldCByZWxhdGlv
bnMuCitjcmVhdGUgdGFibGUgZ3Rlc3QzNCAoaWQgaW50IHByaW1hcnkga2V5LCBhIGludCwKKyAg
ICAgICAgICAgICAgICAgICAgICBjIGludCBnZW5lcmF0ZWQgYWx3YXlzIGFzIChhICogMTApIHZp
cnR1YWwpOworaW5zZXJ0IGludG8gZ3Rlc3QzNCB2YWx1ZXMgKDEsIDUpOworaW5zZXJ0IGludG8g
Z3Rlc3QzNCB2YWx1ZXMgKDEsIDcpCisgICAgb24gY29uZmxpY3QgKGlkKSBkbyB1cGRhdGUgc2V0
IGEgPSBleGNsdWRlZC5jIHJldHVybmluZyAqOworIGlkIHwgYSAgfCAgYyAgCistLS0tKy0tLS0r
LS0tLS0KKyAgMSB8IDcwIHwgNzAwCisoMSByb3cpCisKK2luc2VydCBpbnRvIGd0ZXN0MzQgdmFs
dWVzICgxLCAyKQorICAgIG9uIGNvbmZsaWN0IChpZCkgZG8gdXBkYXRlIHNldCBhID0gZ3Rlc3Qz
NC5jICsgZXhjbHVkZWQuYyByZXR1cm5pbmcgKjsKKyBpZCB8ICBhICB8ICBjICAgCistLS0tKy0t
LS0tKy0tLS0tLQorICAxIHwgNzIwIHwgNzIwMAorKDEgcm93KQorCitpbnNlcnQgaW50byBndGVz
dDM0IHZhbHVlcyAoMSwgMykKKyAgICBvbiBjb25mbGljdCAoaWQpIGRvIHVwZGF0ZSBzZXQgYSA9
IDk5OSB3aGVyZSBleGNsdWRlZC5jID4gMjAgcmV0dXJuaW5nICo7CisgaWQgfCAgYSAgfCAgYyAg
IAorLS0tLSstLS0tLSstLS0tLS0KKyAgMSB8IDk5OSB8IDk5OTAKKygxIHJvdykKKworZHJvcCB0
YWJsZSBndGVzdDM0OworY3JlYXRlIHRhYmxlIGd0ZXN0MzRwIChpZCBpbnQgcHJpbWFyeSBrZXks
IGEgaW50LAorICAgICAgICAgICAgICAgICAgICAgICBjIGludCBnZW5lcmF0ZWQgYWx3YXlzIGFz
IChhICogMTApIHZpcnR1YWwpCisgICAgcGFydGl0aW9uIGJ5IHJhbmdlIChpZCk7CitjcmVhdGUg
dGFibGUgZ3Rlc3QzNHBfMSBwYXJ0aXRpb24gb2YgZ3Rlc3QzNHAgZm9yIHZhbHVlcyBmcm9tICgx
KSB0byAoMTAwKTsKK2luc2VydCBpbnRvIGd0ZXN0MzRwIHZhbHVlcyAoMSwgNSk7CitpbnNlcnQg
aW50byBndGVzdDM0cCB2YWx1ZXMgKDEsIDcpCisgICAgb24gY29uZmxpY3QgKGlkKSBkbyB1cGRh
dGUgc2V0IGEgPSBleGNsdWRlZC5jIHJldHVybmluZyAqOworIGlkIHwgYSAgfCAgYyAgCistLS0t
Ky0tLS0rLS0tLS0KKyAgMSB8IDcwIHwgNzAwCisoMSByb3cpCisKK2luc2VydCBpbnRvIGd0ZXN0
MzRwIHZhbHVlcyAoMSwgMikKKyAgICBvbiBjb25mbGljdCAoaWQpIGRvIHVwZGF0ZSBzZXQgYSA9
IGd0ZXN0MzRwLmMgKyBleGNsdWRlZC5jIHJldHVybmluZyAqOworIGlkIHwgIGEgIHwgIGMgICAK
Ky0tLS0rLS0tLS0rLS0tLS0tCisgIDEgfCA3MjAgfCA3MjAwCisoMSByb3cpCisKK2Ryb3AgdGFi
bGUgZ3Rlc3QzNHA7CmRpZmYgLS1naXQgYS9zcmMvdGVzdC9yZWdyZXNzL3NxbC9nZW5lcmF0ZWRf
dmlydHVhbC5zcWwgYi9zcmMvdGVzdC9yZWdyZXNzL3NxbC9nZW5lcmF0ZWRfdmlydHVhbC5zcWwK
aW5kZXggOWIzMjQxM2UzYTkuLjRmMGMyZDU3YzFmIDEwMDY0NAotLS0gYS9zcmMvdGVzdC9yZWdy
ZXNzL3NxbC9nZW5lcmF0ZWRfdmlydHVhbC5zcWwKKysrIGIvc3JjL3Rlc3QvcmVncmVzcy9zcWwv
Z2VuZXJhdGVkX3ZpcnR1YWwuc3FsCkBAIC05MDYsMyArOTA2LDI4IEBAIHNlbGVjdCAqIGZyb20g
Z3Rlc3QzMyB3aGVyZSBiIGlzIG51bGw7CiAKIHJlc2V0IGNvbnN0cmFpbnRfZXhjbHVzaW9uOwog
ZHJvcCB0YWJsZSBndGVzdDMzOworCistLSBFbnN1cmUgdGhhdCBFWENMVURFRC48dmlydHVhbC1n
ZW5lcmF0ZWQtY29sdW1uPiBpbiBJTlNFUlQgLi4uIE9OIENPTkZMSUNUCistLSBETyBVUERBVEUg
aXMgZXhwYW5kZWQgdG8gdGhlIGdlbmVyYXRpb24gZXhwcmVzc2lvbiwgYm90aCBmb3IgcGxhaW4g
YW5kCistLSBwYXJ0aXRpb25lZCB0YXJnZXQgcmVsYXRpb25zLgorY3JlYXRlIHRhYmxlIGd0ZXN0
MzQgKGlkIGludCBwcmltYXJ5IGtleSwgYSBpbnQsCisgICAgICAgICAgICAgICAgICAgICAgYyBp
bnQgZ2VuZXJhdGVkIGFsd2F5cyBhcyAoYSAqIDEwKSB2aXJ0dWFsKTsKK2luc2VydCBpbnRvIGd0
ZXN0MzQgdmFsdWVzICgxLCA1KTsKK2luc2VydCBpbnRvIGd0ZXN0MzQgdmFsdWVzICgxLCA3KQor
ICAgIG9uIGNvbmZsaWN0IChpZCkgZG8gdXBkYXRlIHNldCBhID0gZXhjbHVkZWQuYyByZXR1cm5p
bmcgKjsKK2luc2VydCBpbnRvIGd0ZXN0MzQgdmFsdWVzICgxLCAyKQorICAgIG9uIGNvbmZsaWN0
IChpZCkgZG8gdXBkYXRlIHNldCBhID0gZ3Rlc3QzNC5jICsgZXhjbHVkZWQuYyByZXR1cm5pbmcg
KjsKK2luc2VydCBpbnRvIGd0ZXN0MzQgdmFsdWVzICgxLCAzKQorICAgIG9uIGNvbmZsaWN0IChp
ZCkgZG8gdXBkYXRlIHNldCBhID0gOTk5IHdoZXJlIGV4Y2x1ZGVkLmMgPiAyMCByZXR1cm5pbmcg
KjsKK2Ryb3AgdGFibGUgZ3Rlc3QzNDsKKworY3JlYXRlIHRhYmxlIGd0ZXN0MzRwIChpZCBpbnQg
cHJpbWFyeSBrZXksIGEgaW50LAorICAgICAgICAgICAgICAgICAgICAgICBjIGludCBnZW5lcmF0
ZWQgYWx3YXlzIGFzIChhICogMTApIHZpcnR1YWwpCisgICAgcGFydGl0aW9uIGJ5IHJhbmdlIChp
ZCk7CitjcmVhdGUgdGFibGUgZ3Rlc3QzNHBfMSBwYXJ0aXRpb24gb2YgZ3Rlc3QzNHAgZm9yIHZh
bHVlcyBmcm9tICgxKSB0byAoMTAwKTsKK2luc2VydCBpbnRvIGd0ZXN0MzRwIHZhbHVlcyAoMSwg
NSk7CitpbnNlcnQgaW50byBndGVzdDM0cCB2YWx1ZXMgKDEsIDcpCisgICAgb24gY29uZmxpY3Qg
KGlkKSBkbyB1cGRhdGUgc2V0IGEgPSBleGNsdWRlZC5jIHJldHVybmluZyAqOworaW5zZXJ0IGlu
dG8gZ3Rlc3QzNHAgdmFsdWVzICgxLCAyKQorICAgIG9uIGNvbmZsaWN0IChpZCkgZG8gdXBkYXRl
IHNldCBhID0gZ3Rlc3QzNHAuYyArIGV4Y2x1ZGVkLmMgcmV0dXJuaW5nICo7Citkcm9wIHRhYmxl
IGd0ZXN0MzRwOwotLSAKMi41MS4wCgo=
--0000000000008199f7064fc0093f--