Re: support create index on virtual generated column.

On 08.01.26 07:16, jian he wrote:
> On Thu, Jul 31, 2025 at 2:24 AM Corey Huinker <corey(dot)huinker(at)gmail(dot)com> wrote:
>>
>>>
>>> Besides, this does nothing you haven't been able to do for
>>> decades with expression indexes.
>>
>>
>> What I'm hoping for with this feature is a smoother transition when people start introducing virtual columns. If there was already an index on that expression, and some queries start using the new virtual column (with the same expression), will that cause those queries to miss the index we already have? If it doesn't, then a customer can roll out the query changes at will and not need to do some cut-over from using the expression to using the virtual column.
>>
>
> hi.
> I am not sure whether this concern has been addressed, as I am still somewhat
> confused by the above paragraph.
>
> As noted earlier, creating an index on a virtual generated column results in a
> new index, and that index behaves the same as a regular expression index.
>
> An updated and polished patch is attached. The regress tests are quite verbose
> at the moment, since I make it covered all index types (btree, gist, spgist,
> hash, gin, and brin).

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.

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.)