Re: support create index on virtual generated column.

On Fri, Mar 13, 2026 at 10:01 PM Peter Eisentraut <peter(at)eisentraut(dot)org> wrote:
>
> I think you could do a much simpler initial version of this if you just
> supported virtual generated columns in expression indexes. And then
> prohibit SET EXPRESSION if the column is used in an index. Then you
> don't need to worry about index rebuilding, ALTER TABLE recursion, new
> catalog columns, and all that.
>

CREATE TABLE gtest22c (a int, b int GENERATED ALWAYS AS (a) VIRTUAL);
CREATE INDEX gtest22c_a_idx ON gtest22c (a);
CREATE INDEX gtest22c_b_idx ON gtest22c (b);

If we don't add a new catalog column (just a single boolean
indisvirtual is not enough, i think),
how can we distinguish between the gtest22c_a_idx and gtest22c_b_idx
indexes in the example above?

If CREATE INDEX simply expands the virtual generated column expression
without dependency tracking, that would be quite easy, see the
attached v8.
If so, we need to explicitly document that SET EXPRESSION has no
effect on existing indexes that originally referenced the virtual
generated column when CREATE INDEX was used.

> But there is a comment in DefineIndex():
>
> /*
> * XXX Virtual generated columns in index expressions or predicates
> * could be supported, but it needs support in
> * RelationGetIndexExpressions() and RelationGetIndexPredicate().
> */
>
> which you delete, but you don't make any changes to those mentioned
> functions. Maybe the comment is wrong, in which case, let's discuss
> that and fix it. (If the comment is indeed wrong, then the feature
> might even be very easy.)
>

I don't think it's a good idea to store the virtual generated columns
as is in Form_pg_index->indkey
because IndexInfo->ii_IndexAttrNumbers and Form->pg_index->indkey are
referenced in too many places (see BuildIndexInfo).
For every single occurrence of ndkey.values[i], we need to consider
whether it's ok for it be a virtual generated column.
Instead, Anum_pg_index_indexprs and Anum_pg_index_indpred store the
expressions after the virtual generated columns expansion,
then we don't need to worry about Form_pg_index->indkey.values[i] is
virtual generated column or not.

Therefore, i think RelationGetIndexExpressions and
RelationGetIndexPredicate don't need to
deal with virtual generated column expressions at all.
Overall, I think the comment above is wrong.

--
jian
https://www.enterprisedb.com/