[BUG] ON CONFLICT DO UPDATE SET x = EXCLUDED.<virtual-generated-column> errors or silently writes NULL
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
Attachments:
vgc-excluded-fix.patchapplication/octet-stream; name=vgc-excluded-fix.patchDownload+84-1
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
Attachments:
v2-0001-Fix-expansion-of-virtual-generated-columns-in-EXC.patchtext/x-patch; charset=US-ASCII; name=v2-0001-Fix-expansion-of-virtual-generated-columns-in-EXC.patchDownload+92-1
HI,
On Sat, Apr 18, 2026 at 11:14 AM 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 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.
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.
Show quoted text
Regards,
Dean
On Mon, 20 Apr 2026 at 01:55, SATYANARAYANA NARLAPURAM
<satyanarlapuram@gmail.com> wrote:
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.
Thanks for checking. Patch pushed and back-patched to v18.
Regards,
Dean
Hello Dean and Satyanarayana,
22.04.2026 11:12, Dean Rasheed wrote:
On Mon, 20 Apr 2026 at 01:55, SATYANARAYANA NARLAPURAM
<satyanarlapuram@gmail.com> wrote:Reran the failing tests and they all passed. Additionally ran the regression tests.
Patch looks good to me.Thanks for checking. Patch pushed and back-patched to v18.
Please look at a case broken by 783425175:
CREATE TABLE t (id int PRIMARY KEY, c int GENERATED ALWAYS AS (tableoid));
INSERT INTO t VALUES (1) ON CONFLICT (id) DO UPDATE SET id = 1 WHERE excluded.c > 0;
ERROR: XX000: variable not found in subplan target lists
LOCATION: fix_join_expr_mutator, setrefs.c:3229
At 783425175~1, it succeeds with:
INSERT 0 1
Best regards,
Alexander
Hi,
On Fri, 11 Sept 2026 at 09:30, Alexander Lakhin <exclusion@gmail.com> wrote:
Hello Dean and Satyanarayana,
22.04.2026 11:12, Dean Rasheed wrote:
On Mon, 20 Apr 2026 at 01:55, SATYANARAYANA NARLAPURAM
<satyanarlapuram@gmail.com> wrote:Reran the failing tests and they all passed. Additionally ran the regression tests.
Patch looks good to me.Thanks for checking. Patch pushed and back-patched to v18.
Please look at a case broken by 783425175:
CREATE TABLE t (id int PRIMARY KEY, c int GENERATED ALWAYS AS (tableoid));
INSERT INTO t VALUES (1) ON CONFLICT (id) DO UPDATE SET id = 1 WHERE excluded.c > 0;ERROR: XX000: variable not found in subplan target lists
LOCATION: fix_join_expr_mutator, setrefs.c:3229
Thanks for the report.
ISTM virtual generated column is expanded to the tableoid reference, but the
EXCLUDED target list has no tableoid entry for setrefs.c to resolve.
Attached patch adds that entry and the statement you sent works fine with it.
Thoughts?
Regards,
Ayush
On Fri, 11 Sept 2026 at 08:45, Ayush Tiwari <ayushtiwari.slg01@gmail.com> wrote:
On Fri, 11 Sept 2026 at 09:30, Alexander Lakhin <exclusion@gmail.com> wrote:
Please look at a case broken by 783425175:
CREATE TABLE t (id int PRIMARY KEY, c int GENERATED ALWAYS AS (tableoid));
INSERT INTO t VALUES (1) ON CONFLICT (id) DO UPDATE SET id = 1 WHERE excluded.c > 0;ERROR: XX000: variable not found in subplan target lists
LOCATION: fix_join_expr_mutator, setrefs.c:3229ISTM virtual generated column is expanded to the tableoid reference, but the
EXCLUDED target list has no tableoid entry for setrefs.c to resolve.Attached patch adds that entry and the statement you sent works fine with it.
Hmm, I'm not sure that this is the right approach. For example,
consider this case, without a generated column:
CREATE TABLE t (id int PRIMARY KEY, val int);
INSERT INTO t VALUES (1,2)
ON CONFLICT (id) DO UPDATE SET val = 0 WHERE excluded.tableoid > 0;
ERROR: column excluded.tableoid does not exist
That error is on point -- "excluded" is not a real table, it is a
pseudo-table constructed from the values proposed for insertion, not
the existing values. As such, it has no system columns.
So, ISTM that when "c" is a virtual generated column, and "excluded.c"
is expanded to "excluded.tableoid", it *should* produce an error.
So rather than trying to fix this, I think it would be better to have
it produce a more meaningful error message.
Regards,
Dean
Hi,
On Sat, 12 Sept 2026 at 23:10, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
On Fri, 11 Sept 2026 at 08:45, Ayush Tiwari <ayushtiwari.slg01@gmail.com> wrote:
On Fri, 11 Sept 2026 at 09:30, Alexander Lakhin <exclusion@gmail.com> wrote:
Please look at a case broken by 783425175:
CREATE TABLE t (id int PRIMARY KEY, c int GENERATED ALWAYS AS (tableoid));
INSERT INTO t VALUES (1) ON CONFLICT (id) DO UPDATE SET id = 1 WHERE excluded.c > 0;ERROR: XX000: variable not found in subplan target lists
LOCATION: fix_join_expr_mutator, setrefs.c:3229ISTM virtual generated column is expanded to the tableoid reference, but the
EXCLUDED target list has no tableoid entry for setrefs.c to resolve.Attached patch adds that entry and the statement you sent works fine with it.
Hmm, I'm not sure that this is the right approach. For example,
consider this case, without a generated column:CREATE TABLE t (id int PRIMARY KEY, val int);
INSERT INTO t VALUES (1,2)
ON CONFLICT (id) DO UPDATE SET val = 0 WHERE excluded.tableoid > 0;ERROR: column excluded.tableoid does not exist
That error is on point -- "excluded" is not a real table, it is a
pseudo-table constructed from the values proposed for insertion, not
the existing values. As such, it has no system columns.So, ISTM that when "c" is a virtual generated column, and "excluded.c"
is expanded to "excluded.tableoid", it *should* produce an error.So rather than trying to fix this, I think it would be better to have
it produce a more meaningful error message.
Thanks for looking at this.
My thinking was that excluded.c could use the table that the row would
have been inserted into when evaluating its generation expression.
Would that be a reasonable meaning for tableoid here, or should it be
considered unavailable because EXCLUDED represents only a proposed row?
That was the distinction I had in mind, but I may be missing something.
Regards,
Ayush
On Sat, 12 Sept 2026 at 19:10, Ayush Tiwari <ayushtiwari.slg01@gmail.com> wrote:
On Sat, 12 Sept 2026 at 23:10, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
Hmm, I'm not sure that this is the right approach. For example,
consider this case, without a generated column:CREATE TABLE t (id int PRIMARY KEY, val int);
INSERT INTO t VALUES (1,2)
ON CONFLICT (id) DO UPDATE SET val = 0 WHERE excluded.tableoid > 0;ERROR: column excluded.tableoid does not exist
That error is on point -- "excluded" is not a real table, it is a
pseudo-table constructed from the values proposed for insertion, not
the existing values. As such, it has no system columns.So, ISTM that when "c" is a virtual generated column, and "excluded.c"
is expanded to "excluded.tableoid", it *should* produce an error.My thinking was that excluded.c could use the table that the row would
have been inserted into when evaluating its generation expression.Would that be a reasonable meaning for tableoid here, or should it be
considered unavailable because EXCLUDED represents only a proposed row?
That was the distinction I had in mind, but I may be missing something.
I think it shouldn't be allowed. Besides the fact that EXCLUDED is the
proposed row, and doesn't have a tableoid, it would be inconsistent to
allow a virtual generated column to magic-up EXCLUDED.tableoid, when
that isn't available when referenced directly in SQL.
Regards,
Dean
Hi,
On Sun, 13 Sept 2026 at 17:46, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
On Sat, 12 Sept 2026 at 19:10, Ayush Tiwari <ayushtiwari.slg01@gmail.com> wrote:
On Sat, 12 Sept 2026 at 23:10, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
Hmm, I'm not sure that this is the right approach. For example,
consider this case, without a generated column:CREATE TABLE t (id int PRIMARY KEY, val int);
INSERT INTO t VALUES (1,2)
ON CONFLICT (id) DO UPDATE SET val = 0 WHERE excluded.tableoid > 0;ERROR: column excluded.tableoid does not exist
That error is on point -- "excluded" is not a real table, it is a
pseudo-table constructed from the values proposed for insertion, not
the existing values. As such, it has no system columns.So, ISTM that when "c" is a virtual generated column, and "excluded.c"
is expanded to "excluded.tableoid", it *should* produce an error.My thinking was that excluded.c could use the table that the row would
have been inserted into when evaluating its generation expression.Would that be a reasonable meaning for tableoid here, or should it be
considered unavailable because EXCLUDED represents only a proposed row?
That was the distinction I had in mind, but I may be missing something.I think it shouldn't be allowed. Besides the fact that EXCLUDED is the
proposed row, and doesn't have a tableoid, it would be inconsistent to
allow a virtual generated column to magic-up EXCLUDED.tableoid, when
that isn't available when referenced directly in SQL.
Thanks for the feedback. I reworked the patch to reject the expanded
tableoid reference with a proper error and added a regression test for
the reported case.
[Unsure whether this should be backpatched to PG 18 though]
Attaching v2.
Thoughts on the placement and error message?
Regards,
Ayush
Attachments:
t139355_10v2-0001-Reject-tableoid-in-EXCLUDED-virtual-generated-columns.patchapplication/octet-stream; name=v2-0001-Reject-tableoid-in-EXCLUDED-virtual-generated-columns.patchDownload+32-0
Hi Ayush,
Thank you for the patch. I tried v2 (the cfbot branch cf/7295, CF
entry 7295) and would like to share what I saw. I can send the scripts
and the output if they are useful.
Setup
- Master at c1694b7b962c (11 Nov 2026) plus the patch as one diff (3
files: prepjointree.c and the generated_virtual test files). `git
apply --check` was clean on that master, so no rebase is needed at the
moment.
- configure with --enable-cassert --enable-debug --enable-tap-tests
--without-icu, on Linux x86_64. I built the unpatched tree first, then
the patched tree. Both builds had 0 compiler warnings.
Tests that passed
- `make check` with the patch: all 243 tests passed.
- With only the prepjointree.c change reverted and the new test kept,
generated_virtual fails (1 of 243 tests failed). The diff is exactly
the new test: the expected `ERROR: cannot use system column
"tableoid" in EXCLUDED` becomes `ERROR: variable not found in subplan
target lists`. So the new test does exercise the fix.
Manual tests (temporary cluster, one run each on the unpatched and the
patched build)
- Plain table, virtual column `c regclass GENERATED ALWAYS AS
(tableoid) VIRTUAL`, INSERT ... ON CONFLICT DO UPDATE:
- `SET b = (excluded.c::oid)::int`: unpatched gives "variable not
found in subplan target lists"; patched gives the new tableoid error.
- `WHERE excluded.c IS NOT NULL`: unpatched did not fail and the
update ran (b became 9); patched gives the new error and the row stays
unchanged.
- Partitioned table with two partitions (tableoid is different per
partition): `WHERE excluded.c IS NOT NULL` on the unpatched build ran
without error and updated both rows (in pt1 and pt2); I did not check
which tableoid value it used. `SET b = (excluded.c::oid)::int` gave
"variable not found in subplan target lists". With the patch, both
give the new tableoid error and no row is changed.
- Expression that combines tableoid with other columns (`a * 100 +
(tableoid::oid)::int % 7`): with `SET b = excluded.c::int` and with
`WHERE excluded.c > 0`, unpatched gives "variable not found in subplan
target lists" for both; patched gives the new error for both.
- Cases that still work with the patch, and gave the same output
before and after:
- a stored generated column (`b * 10`) referenced as excluded.c;
- a virtual column without tableoid (`a * 10`, the example from the
first message of the thread), where excluded.c in SET still works on
both builds;
- excluded.b (an ordinary column) on the plain and the partitioned table;
- reading the target table's own virtual column (`m1.c`) in SET;
- reading the tableoid virtual column with a plain SELECT.
- The server did not crash during these runs (assert-enabled build);
the only ERROR lines in the server log were the ones listed above. I
did not keep the full server logs.
Small question and comment
- The message is `cannot use system column "tableoid" in EXCLUDED`,
which seems clear to me. When the user wrote `excluded.c` (the virtual
column), the message does not name `c`, so a hint or the column name
might help. This is only a suggestion, and I think the message is fine
as it is.
- I did not check the placement of the check in
expand_virtual_generated_columns() against other options, and I do not
have an opinion on the back-patch to PG 18.
Not tested
- Back-patching, and PG 18 or any other branch.
- Other system columns (ctid, xmin, and so on) in a generation
expression; only tableoid was tried.
- Domain, inheritance (non-partition) and multi-level partition cases;
INSERT ... ON CONFLICT with RETURNING; MERGE; views and rules;
prepared statements and plan caching.
- make check-world, the isolation tests and the other TAP suites (I
ran only make check).
- Meson build, non-assert builds, Windows, 32-bit, other compilers, ICU builds.
- The wording of the error message beyond the points above, the code
style (pgindent), and a full code review.
Thanks again, and please let me know if you want me to rerun anything
with different settings.
Best regards,
João Rito