Re: support create index on virtual generated column.

Original
>From: Kirill Reshke <reshkekirill(at)gmail(dot)com>
>Date: 2026-11-05 13:18
>To: ZizhuanLiu X-MAN <44973863(at)qq(dot)com>
>Cc: jian he <jian(dot)universality(at)gmail(dot)com>, Peter Eisentraut <peter(at)eisentraut(dot)org>, pgsql-hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, Corey Huinker <corey(dot)huinker(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, chengpeng_yan <chengpeng_yan(at)outlook(dot)com>, guofenglinux <guofenglinux(at)gmail(dot)com>
>Subject: Re: support create index on virtual generated column.
>.....
>
>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

Hi,

Thank you for your testing and feedback.
V9 did not adequately address some aspects of dependencies and catalog handling.
After giving it some more thought, I agree with Tom's suggestion:

https://www.postgresql.org/message-id/1178617.1753217685%40sss.pgh.pa.us

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
>To: Corey Huinker <corey(dot)huinker(at)gmail(dot)com>
>Cc: jian he <jian(dot)universality(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
>Subject: Re: support create index on virtual generated column.
>Date: 2025-07-22 20:54:45
>
>Corey Huinker <corey(dot)huinker(at)gmail(dot)com> writes:
>> I'm interested in this feature, specifically whether the optimizer uses the
>> index in situations where the expression is used rather than the virtual
>> column name.
>
>
>
>Hmm, I kinda think we should not do this. The entire point of a
>virtual column is that its values are not stored and so you can
>(for example) change the generation expression "for free".
>If it's referenced in an index that advantage goes out the window
>because we'll have to rebuild the index.
>
>
>
>Besides, this does nothing you haven't been able to do for
>decades with expression indexes.
>
>
>
> regards, tom lane

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