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

On 2026-May-08, Matthias van de Meent wrote:

> On Fri, 8 May 2026 at 16:12, Álvaro Herrera <alvherre(at)kurilemu(dot)de> wrote:

> > > 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.
>
> Are you sure it works fine?

I only meant that the sequence that Ayush was saying should fail with
virtual columns and actually fails to fail, also fails to fail with
regular columns. Therefore this is not a virtual column-specific
problem, but something more general that perhaps we have not studied
enough.

TBH I'm not really sure what's the usefulness of indexes over whole-row
types. I imagine the use cases for them are rather infrequent, if not
outright non-existent.

--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/