Horton
Menu

Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Fix expansion of EXCLUDED virtual generated columns.

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

    SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com> — 2026-04-16T20:48:55Z

    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

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

    Dean Rasheed <dean.a.rasheed@gmail.com> — 2026-04-18T18:14:20Z

    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

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

    SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com> — 2026-04-20T00:55:26Z

    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
>

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

    Dean Rasheed <dean.a.rasheed@gmail.com> — 2026-04-22T08:12:15Z

    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