Re: support create index on virtual generated column.

On Wed, 22 Jul 2026 at 18:02, ZizhuanLiu X-MAN <44973863(at)qq(dot)com> wrote:
>
> >Hi everyone,
> >
> >While revisiting the discussion for `CF6749 — Disallow whole-row index references with virtual generated columns? `
> >Based on version V7, I’ve added support for whole-row references within partial indexes, attached V8 patch.
> >
> >There is one point I may have overlooked: the current implementation appears to only work for btree indexes.
> >
> >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)
> >
> >
> >regards,
> >--
> >ZizhuanLiu (X-MAN)
> >44973863(at)qq(dot)com
>
>
> Hi, everyone
>
> Rebase V8 to V9.
>
>
> regards,
> --
> ZizhuanLiu (X-MAN)
> 44973863(at)qq(dot)com

Hi!

There is a different implementation here[0] and there [1], which is
more simple, it simply makes a functional index on VGC. In new v9 the
proposed solution (SGC-like) is much more complex (and ambitious). It
makes a real index on column value itself.

First, patch does not applies after dc5f8cb93467

On dc5f8cb93467~1 there are problems in v9, which are new to jian's v8 [1]:

1)
ALTER TABLE SET EXPRESSION corrupts index.

CREATE TABLE t(a int, v int GENERATED ALWAYS AS(a*2) VIRTUAL)
WITH(fillfactor=50);
CREATE INDEX t_v ON t(v);
INSERT INTO t(a) VALUES(1);
ALTER TABLE t ALTER COLUMN v SET EXPRESSION AS(a*3);

db1=# SELECT bt_index_check('t_v',true);
ERROR: heap tuple (0,1) from table "t" lacks matching index tuple
within index "t_v"
HINT: Retrying verification using the function
bt_index_parent_check() might provide a more specific error.

2)
CREATE TABLE defer_v(a int, v int GENERATED ALWAYS AS(a*2) VIRTUAL
UNIQUE DEFERRABLE INITIALLY DEFERRED);
BEGIN; INSERT INTO defer_v(a) VALUES(1),(1); COMMIT;

-- server fail due to SIGSEGV

[0] https://www.postgresql.org/message-id/flat/CAMbWs4811nG3w_3sLxps+EAuUsffA_83ZQ-1acEH_jQeq117mg(at)mail(dot)gmail(dot)com
[1] https://www.postgresql.org/message-id/CACJufxGhTjcc-z1%3DejgtXCk7L3iy%2BB8aoDAvczaszDM4NPpDVw%40mail.gmail.com

--
Best regards,
Kirill Reshke