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

Hi,

On Fri, 8 May 2026 at 19:42, Álvaro Herrera <alvherre(at)kurilemu(dot)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