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"

Hi,

On Fri, 8 May 2026 at 19:42, Á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. 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
anyway. That mismatch is what I was trying to point at.

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

though?

AFAICT the answer is no:

CREATE TABLE virtual_expr_u (a int, b int GENERATED ALWAYS AS (a * 2)
VIRTUAL);
CREATE UNIQUE INDEX virtual_expr_u_idx ON virtual_expr_u
((virtual_expr_u));
INSERT INTO virtual_expr_u(a) VALUES (1);
INSERT INTO virtual_expr_u(a) VALUES (1);
ERROR: duplicate key value violates unique constraint
"virtual_expr_u_idx"
DETAIL: Key ((virtual_expr_u.*))=((1,)) already exists.

while SELECT virtual_expr_u FROM virtual_expr_u; returns (1,2). So the
whole-row index expression keys on the unexpanded heap tuple and
the generated column is missing - same root cause as the partial-predicate
case.

The existing comment in DefineIndex already says virtual generated
columns in index expressions/predicates "could be supported, but it
needs support in RelationGetIndexExpressions() and
RelationGetIndexPredicate()". So this patch is just intended as a
conservative consistent extension of that existing restriction to
whole-row Vars, until that support is added.

Regards,
Ayush

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;

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

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

Hi everyone,

Thanks a lot for all the patches, tests and insightful discussions so far.

From the current status, PostgreSQL already permits creating indexes with whole-row references.
However, there exists inconsistent handling between such indexes and the query planner during runtime.
As a result, these indexes cannot work as expected, including failures in index scan, index-only scan and
partial predicate matching.

Given this situation, I intend to improve the related logic to provide better index support for
both whole-row references and virtual generated columns.

Therefore, I am sending this proposal: Support virtual generated columns in index expressions and predicates.

regards,
--
ZizhuanLiu (X-MAN)
44973863@qq.com

Original
From: ZizhuanLiu X-MAN <44973863@qq.com>
Date: 2026-06-24 19:54
To: álvaro Herrera <alvherre@kurilemu.de>, Matthias van de Meent <boekewurm+postgres@gmail.com>
Cc: Ayush Tiwari <ayushtiwari.slg01@gmail.com>, PostgreSQL Hackers <pgsql-hackers@postgresql.org>, Robert Haas <robertmhaas@gmail.com>
Subject: Re: Disallow whole-row index references with virtual generated columns

Hi everyone,

Thanks a lot for all the patches, tests and insightful discussions so far.

From the current status, PostgreSQL already permits creating indexes with whole-row references.
However, there exists inconsistent handling between such indexes and the query planner during runtime.
As a result, these indexes cannot work as expected, including failures in index scan, index-only scan and
partial predicate matching.

Given this situation, I intend to improve the related logic to provide better index support for
both whole-row references and virtual generated columns.

Therefore, I am sending this proposal: Support virtual generated columns in index expressions and predicates.

regards,
--
ZizhuanLiu (X-MAN)
44973863@qq.com

Hi everyone,

I have implemented a patch to support creation of a partial index using a whole-row expression.
Details can be found here:
https://commitfest.postgresql.org/patch/5667/ support create index on virtual generated column.
/messages/by-id/CACJufxGao-cypdNhifHAdt8jHfK6-HX=tRBovBkgRuxw063GaA@mail.gmail.com

I welcome everyone’s thoughts and look forward to your feedback.

Below are the test results:

-- test creation of a predicate index with a whole-row expression
DROP TABLE IF EXISTS test_pg_wholerow;
NOTICE: table "test_pg_wholerow" does not exist, skipping
CREATE TABLE test_pg_wholerow(a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL, c text);
CREATE UNIQUE INDEX test_pg_wholerow_pred_idx ON test_pg_wholerow(a) WHERE test_pg_wholerow IS NOT NULL;
INSERT INTO test_pg_wholerow(a,c) SELECT NULL, NULL FROM generate_series(1, 1000);
INSERT INTO test_pg_wholerow(a,c) SELECT 1, '1';
ANALYZE test_pg_wholerow;
SELECT relname,reltuples FROM pg_catalog.pg_class WHERE relname IN ('test_pg_wholerow', 'test_pg_wholerow_pred_idx') ORDER BY OID;
relname | reltuples
---------------------------+-----------
test_pg_wholerow | 1001
test_pg_wholerow_pred_idx | 1
(2 rows)

EXPLAIN (COSTS OFF) SELECT * FROM test_pg_wholerow WHERE test_pg_wholerow IS NOT NULL;
QUERY PLAN
----------------------------------------------------------------
Index Scan using test_pg_wholerow_pred_idx on test_pg_wholerow
(1 row)

EXPLAIN (COSTS OFF) SELECT a FROM test_pg_wholerow WHERE test_pg_wholerow IS NOT NULL;
QUERY PLAN
---------------------------------------------------------------------
Index Only Scan using test_pg_wholerow_pred_idx on test_pg_wholerow
(1 row)

EXPLAIN (COSTS OFF) SELECT a,b FROM test_pg_wholerow WHERE test_pg_wholerow IS NOT NULL;
QUERY PLAN
---------------------------------------------------------------------
Index Only Scan using test_pg_wholerow_pred_idx on test_pg_wholerow
(1 row)

regress=# INSERT INTO test_pg_wholerow(a,c) SELECT 1, '1';
ERROR: duplicate key value violates unique constraint "test_pg_wholerow_pred_idx"
DETAIL: Key (a)=(1) already exists.
regress=#

regards,
ZizhuanLiu (X-MAN)
44973863@qq.com

Hi everyone,
I have implemented a patch to support creation of a partial index using a whole-row expression.
Details can be found here:
https://commitfest.postgresql.org/patch/5667/ support create index on virtual generated column.
/messages/by-id/CACJufxGao-cypdNhifHAdt8jHfK6-HX=tRBovBkgRuxw063GaA(at)mail(dot)gmail(dot)com
I welcome everyone’s thoughts and look forward to your feedback.
Below are the test results:
-- test creation of a predicate index with a whole-row expression
DROP TABLE IF EXISTS test_pg_wholerow;
NOTICE: table "test_pg_wholerow" does not exist, skipping
CREATE TABLE test_pg_wholerow(a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL, c text);
CREATE UNIQUE INDEX test_pg_wholerow_pred_idx ON test_pg_wholerow(a) WHERE test_pg_wholerow IS NOT NULL;
INSERT INTO test_pg_wholerow(a,c) SELECT NULL, NULL FROM generate_series(1, 1000);
INSERT INTO test_pg_wholerow(a,c) SELECT 1, '1';
ANALYZE test_pg_wholerow;
SELECT relname,reltuples FROM pg_catalog.pg_class WHERE relname IN ('test_pg_wholerow', 'test_pg_wholerow_pred_idx') ORDER BY OID;
relname | reltuples
---------------------------+-----------
test_pg_wholerow | 1001
test_pg_wholerow_pred_idx | 1
(2 rows)

EXPLAIN (COSTS OFF) SELECT * FROM test_pg_wholerow WHERE test_pg_wholerow IS NOT NULL;
QUERY PLAN
----------------------------------------------------------------
Index Scan using test_pg_wholerow_pred_idx on test_pg_wholerow
(1 row)

EXPLAIN (COSTS OFF) SELECT a FROM test_pg_wholerow WHERE test_pg_wholerow IS NOT NULL;
QUERY PLAN
---------------------------------------------------------------------
Index Only Scan using test_pg_wholerow_pred_idx on test_pg_wholerow
(1 row)

EXPLAIN (COSTS OFF) SELECT a,b FROM test_pg_wholerow WHERE test_pg_wholerow IS NOT NULL;
QUERY PLAN
---------------------------------------------------------------------
Index Only Scan using test_pg_wholerow_pred_idx on test_pg_wholerow
(1 row)

regress=# INSERT INTO test_pg_wholerow(a,c) SELECT 1, '1';
ERROR: duplicate key value violates unique constraint "test_pg_wholerow_pred_idx"
DETAIL: Key (a)=(1) already exists.
regress=#

regards,
ZizhuanLiu (X-MAN)
44973863(at)qq(dot)com

Hi, everyone

https://commitfest.postgresql.org/patch/5667/ support create index on virtual generated column.
/messages/by-id/CACJufxGao-cypdNhifHAdt8jHfK6-HX=tRBovBkgRuxw063GaA(at)mail(dot)gmail(dot)com

Rebase V8 to V9.

regards,
--
ZizhuanLiu (X-MAN)
44973863(at)qq(dot)com