Thread

Re: Disallow whole-row index references with virtual generated columns?

Ayush Tiwari <ayushtiwari.slg01@gmail.com> — 2026-05-08T14:40:10Z

Hi,

On Fri, 8 May 2026 at 19:42, Álvaro Herrera <alvherre@kurilemu.de> wrote:

> On 2026-May-08, Ayush Tiwari wrote:
>
> > The shape that worried me the most was a partial unique index whose
> > predicate uses a whole-row reference, e.g. WHERE rel IS NOT NULL.  As
> > far as I can tell, the predicate can be true at the SQL level, but
> > index build and maintenance evaluate the stored predicate against the
> > physical heap tuple, where the virtual column is not stored.  If that
> > reading is right, the index could end up with no entries for rows that
> > satisfy the predicate, which would mean uniqueness is silently not
> > enforced.  Does that sound like a bug, or am I missing something?
> >
> > This is what I tried on master:
> >
> >     CREATE TABLE t (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
> >     CREATE UNIQUE INDEX t_a_wholerow_pred_idx ON t (a) WHERE t IS NOT
> NULL;
> >     INSERT INTO t(a) VALUES (1);
> >     INSERT INTO t(a) VALUES (1);  -- accepted, two rows with a = 1
>
> Hmm, but this also works just fine when the column b is a normal column,
> so I don't see why you would want to restrict this specifically for
> virtual generated columns.  If you want that to fail, you would use
> WHERE t IS DISTINCT FROM NULL in the index predicate, and that makes the
> second insert fail both for regular columns and for virtual generated
> columns alike.
>

Good point, the IS NOT NULL example was a poor example.  For a normal
nullable column, (1, NULL) makes "row IS NOT NULL" false at the SQL
level, so excluding it from the partial index is correct.  For a virtual
generated column the SQL-visible row is (1, 2):

    CREATE TABLE virtual_t (a int, b int GENERATED ALWAYS AS (a * 2)
VIRTUAL);
    INSERT INTO virtual_t(a) VALUES (1);
    SELECT virtual_t, virtual_t IS NOT NULL FROM virtual_t;
     virtual_t | ?column?
    -----------+----------
     (1,2)     | t

so SQL says the predicate is true, but the partial index appears to
evaluate it against the physical heap tuple (1,) and excludes the row
anyway.  That mismatch is what I was trying to point at.

Does the rowtype index contain the right values for the generated column
> though?
>

AFAICT the answer is no:

    CREATE TABLE virtual_expr_u (a int, b int GENERATED ALWAYS AS (a * 2)
VIRTUAL);
    CREATE UNIQUE INDEX virtual_expr_u_idx ON virtual_expr_u
((virtual_expr_u));
    INSERT INTO virtual_expr_u(a) VALUES (1);
    INSERT INTO virtual_expr_u(a) VALUES (1);
    ERROR:  duplicate key value violates unique constraint
"virtual_expr_u_idx"
    DETAIL:  Key ((virtual_expr_u.*))=((1,)) already exists.

while SELECT virtual_expr_u FROM virtual_expr_u; returns (1,2).  So the
whole-row index expression keys on the unexpanded heap tuple and
the generated column is missing - same root cause as the partial-predicate
case.

The existing comment in DefineIndex already says virtual generated
columns in index expressions/predicates "could be supported, but it
needs support in RelationGetIndexExpressions() and
RelationGetIndexPredicate()".  So this patch is just intended as a
conservative consistent extension of that existing restriction to
whole-row Vars, until that support is added.

Regards,
Ayush