Thread

Disallow whole-row index references with virtual generated columns?

Ayush Tiwari <ayushtiwari.slg01@gmail.com> — 2026-05-08T12:41:08Z

Hi,

While playing with virtual generated columns, I noticed that DefineIndex
seems to reject indexes on virtual generated columns when they are
referenced directly in index expressions or predicates, but it seems it is
missing whole-row Vars.  My understanding is that a whole-row
Var on a relation that has any virtual generated column logically
includes that column, so I was wondering whether allowing it could
bypass the existing virtual-column index restriction.

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

If this is indeed something we want to disallow, I have attached a small
patch that I think extends the existing check in DefineIndex so that
varattno == 0 (whole-row Var) is also rejected when the indexed relation
has virtual generated columns.  I tried to keep whole-row references on
relations without virtual generated columns working, so existing
whole-row expression indexes should not be affected, but I would
appreciate other eyes on whether that is the right scope.

Thoughts?

Regards,
Ayush