Thread

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

Matthias van de Meent <boekewurm+postgres@gmail.com> — 2026-05-08T15:03:22Z

On Fri, 8 May 2026 at 16:12, Álvaro Herrera <alvherre@kurilemu.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.

Are you sure it works fine? I get differing behaviour between STORED
and VIRTUAL in the script below; though indeed that's with a generated
column. non-generated columns with the same value getting inserted do
get the expected errors, too.

But maybe whole-row IS [NOT] NULL expressions in indexes just
shouldn't be allowed (marked as immutable), because you can silently
corrupt the whole index by (e.g.) invalidating the IS NOT NULL
condition by adding a new default-(non-)NULL column... I think it's
one of the few expression types that isn't captured by the
immutable-expression-checker, though there may be more.

Kind regards,

Matthias van de Meent

---- script:

DROP TABLE IF EXISTS t5;
/* if VIRTUAL instead of STORED, the script succeeds; even if b is
marked NOT NULL. With STORED, it fails */
CREATE TABLE t5 (a int, b int GENERATED ALWAYS AS (a * 2) STORED);
CREATE UNIQUE INDEX t5_a_wholerow_pred_idx ON t5 (a) WHERE t5 IS NOT NULL;

INSERT INTO t5(a) VALUES (1); ANALYZE t5;
EXPLAIN SELECT t5 IS NOT NULL FROM t5;
SELECT t5 IS NOT NULL FROM t5;
INSERT INTO t5(a) VALUES (1); ANALYZE t5; -- insert fails if STORED,
without STORED in column definition it succeeds.
EXPLAIN SELECT t5 IS NOT NULL FROM t5;
SELECT t5 IS NOT NULL FROM t5;


vs

DROP TABLE IF EXISTS t5;
CREATE TABLE t5 (a int, b int);
CREATE UNIQUE INDEX t5_a_wholerow_pred_idx ON t5 (a) WHERE t5 IS NOT NULL;

INSERT INTO t5(a, b) VALUES (1, 2); ANALYZE t5;
explain select t5 is not null from t5;
select t5 is not null from t5;

INSERT INTO t5(a, b) VALUES (1, 2); ANALYZE t5;
EXPLAIN SELECT t5 IS NOT NULL FROM t5;
SELECT t5 IS NOT NULL FROM t5;