On Wed, 22 Jul 2026 at 18:02, ZizhuanLiu X-MAN <[email protected]> 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)
> >[email protected]
>
>
> Hi, everyone
>
> Rebase V8 to V9.
>
>
> regards,
> --
> ZizhuanLiu (X-MAN)
> [email protected]
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_jqeq11...@mail.gmail.com
[1]
https://www.postgresql.org/message-id/CACJufxGhTjcc-z1%3DejgtXCk7L3iy%2BB8aoDAvczaszDM4NPpDVw%40mail.gmail.com
--
Best regards,
Kirill Reshke
