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

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.

I'm not really sure exactly why we disallow indexes on virtual generated
columns -- I suspect it's just because we haven't implemented it yet --
it doesn't seem a fundamental restriction.

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

--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"Uno puede defenderse de los ataques; contra los elogios se esta indefenso"