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

>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.
>https://www.postgresql.org/message-id/flat/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.
https://www.postgresql.org/message-id/flat/CACJufxGao-cypdNhifHAdt8jHfK6-HX=tRBovBkgRuxw063GaA(at)mail(dot)gmail(dot)com

Rebase V8 to V9.

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