[BUG] ON CONFLICT DO UPDATE SET x = EXCLUDED.<virtual-generated-column> errors or silently writes NULL

From: SATYANARAYANA NARLAPURAM <satyanarlapuram(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: [BUG] ON CONFLICT DO UPDATE SET x = EXCLUDED.<virtual-generated-column> errors or silently writes NULL
Date: 2026-04-16 20:48:55
Message-ID: CAHg+QDf7wTLz_vqb1wi1EJ_4Uh+Vxm75+b4c-Ky=6P+yOAHjbQ@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
vgc-excluded-fix.patch application/octet-stream 5.0 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message Andrew Dunstan 2026-04-16 20:58:53 Re: Add errdetail() with PID and UID about source of termination signal
Previous Message John Mikk 2026-04-16 20:35:34 Re: POC: Comparison of partitioning key values