Re: Disallow whole-row index references with virtual generated columns? - Mailing list pgsql-hackers

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

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