public inbox for [email protected]
help / color / mirror / Atom feedFrom: SATYANARAYANA NARLAPURAM <[email protected]>
To: PostgreSQL Hackers <[email protected]>
Subject: [BUG] ON CONFLICT DO UPDATE SET x = EXCLUDED.<virtual-generated-column> errors or silently writes NULL
Date: Thu, 16 Apr 2026 13:48:55 -0700
Message-ID: <CAHg+QDf7wTLz_vqb1wi1EJ_4Uh+Vxm75+b4c-Ky=6P+yOAHjbQ@mail.gmail.com> (raw)
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:
[application/octet-stream] vgc-excluded-fix.patch (5.0K, 3-vgc-excluded-fix.patch)
download | inline diff:
diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c
index ff0e875f..bb254079 100644
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -167,6 +167,7 @@ static void set_param_references(PlannerInfo *root, Plan *plan);
static Node *convert_combining_aggrefs(Node *node, void *context);
static void set_dummy_tlist_references(Plan *plan, int rtoffset);
static indexed_tlist *build_tlist_index(List *tlist);
+static indexed_tlist *build_tlist_index_other_vars(List *tlist, int ignore_rel);
static Var *search_indexed_tlist_for_var(Var *var,
indexed_tlist *itlist,
int newvarno,
@@ -1168,7 +1169,8 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset)
{
indexed_tlist *itlist;
- itlist = build_tlist_index(splan->exclRelTlist);
+ itlist = build_tlist_index_other_vars(splan->exclRelTlist,
+ 0);
splan->onConflictSet =
fix_join_expr(root, splan->onConflictSet,
diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out
index fc41c480..8f5ad91b 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -1723,3 +1723,55 @@ select * from gtest33 where b is null;
reset constraint_exclusion;
drop table gtest33;
+-- Ensure that EXCLUDED.<virtual-generated-column> in INSERT ... ON CONFLICT
+-- DO UPDATE is expanded to the generation expression, both for plain and
+-- partitioned target relations. Previously, the plan-time setrefs step
+-- would re-collapse the expanded expression into a Var referring to the
+-- virtual attnum of the EXCLUDED slot, producing either an "unexpected
+-- virtual generated column reference" error (plain table) or a silent NULL
+-- (partitioned table, via tuple routing's attribute remapping).
+create table gtest34 (id int primary key, a int,
+ c int generated always as (a * 10) virtual);
+insert into gtest34 values (1, 5);
+insert into gtest34 values (1, 7)
+ on conflict (id) do update set a = excluded.c returning *;
+ id | a | c
+----+----+-----
+ 1 | 70 | 700
+(1 row)
+
+insert into gtest34 values (1, 2)
+ on conflict (id) do update set a = gtest34.c + excluded.c returning *;
+ id | a | c
+----+-----+------
+ 1 | 720 | 7200
+(1 row)
+
+insert into gtest34 values (1, 3)
+ on conflict (id) do update set a = 999 where excluded.c > 20 returning *;
+ id | a | c
+----+-----+------
+ 1 | 999 | 9990
+(1 row)
+
+drop table gtest34;
+create table gtest34p (id int primary key, a int,
+ c int generated always as (a * 10) virtual)
+ partition by range (id);
+create table gtest34p_1 partition of gtest34p for values from (1) to (100);
+insert into gtest34p values (1, 5);
+insert into gtest34p values (1, 7)
+ on conflict (id) do update set a = excluded.c returning *;
+ id | a | c
+----+----+-----
+ 1 | 70 | 700
+(1 row)
+
+insert into gtest34p values (1, 2)
+ on conflict (id) do update set a = gtest34p.c + excluded.c returning *;
+ id | a | c
+----+-----+------
+ 1 | 720 | 7200
+(1 row)
+
+drop table gtest34p;
diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql
index 9b32413e..6e958011 100644
--- a/src/test/regress/sql/generated_virtual.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -906,3 +906,32 @@ select * from gtest33 where b is null;
reset constraint_exclusion;
drop table gtest33;
+
+-- Ensure that EXCLUDED.<virtual-generated-column> in INSERT ... ON CONFLICT
+-- DO UPDATE is expanded to the generation expression, both for plain and
+-- partitioned target relations. Previously, the plan-time setrefs step
+-- would re-collapse the expanded expression into a Var referring to the
+-- virtual attnum of the EXCLUDED slot, producing either an "unexpected
+-- virtual generated column reference" error (plain table) or a silent NULL
+-- (partitioned table, via tuple routing's attribute remapping).
+create table gtest34 (id int primary key, a int,
+ c int generated always as (a * 10) virtual);
+insert into gtest34 values (1, 5);
+insert into gtest34 values (1, 7)
+ on conflict (id) do update set a = excluded.c returning *;
+insert into gtest34 values (1, 2)
+ on conflict (id) do update set a = gtest34.c + excluded.c returning *;
+insert into gtest34 values (1, 3)
+ on conflict (id) do update set a = 999 where excluded.c > 20 returning *;
+drop table gtest34;
+
+create table gtest34p (id int primary key, a int,
+ c int generated always as (a * 10) virtual)
+ partition by range (id);
+create table gtest34p_1 partition of gtest34p for values from (1) to (100);
+insert into gtest34p values (1, 5);
+insert into gtest34p values (1, 7)
+ on conflict (id) do update set a = excluded.c returning *;
+insert into gtest34p values (1, 2)
+ on conflict (id) do update set a = gtest34p.c + excluded.c returning *;
+drop table gtest34p;
view thread (4+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: [BUG] ON CONFLICT DO UPDATE SET x = EXCLUDED.<virtual-generated-column> errors or silently writes NULL
In-Reply-To: <CAHg+QDf7wTLz_vqb1wi1EJ_4Uh+Vxm75+b4c-Ky=6P+yOAHjbQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox