Обсуждение: [BUG] ON CONFLICT DO UPDATE SET x = EXCLUDED. errors or silently writes NULL
[BUG] ON CONFLICT DO UPDATE SET x = EXCLUDED. errors or silently writes NULL
От
SATYANARAYANA NARLAPURAM
Дата:
Hi Hackers,
Virtual generated column (bgc) behavior for plain and partitioned tables is different
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;
id | a | c
----+---+---
1 | |
We have two options to fix, (1) throw an error for partitioned tables similar to plain tables or
Thanks,
Satya
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
Вложения
Re: [BUG] ON CONFLICT DO UPDATE SET x = EXCLUDED. errors or silently writes NULL
От
Dean Rasheed
Дата:
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
Вложения
Re: [BUG] ON CONFLICT DO UPDATE SET x = EXCLUDED. errors or silently writes NULL
От
SATYANARAYANA NARLAPURAM
Дата:
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.
Regards,
Dean
Re: [BUG] ON CONFLICT DO UPDATE SET x = EXCLUDED. errors or silently writes NULL
От
Dean Rasheed
Дата:
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