On Sep 30, 2025, at 17:13, Richard Guo <[email protected]> wrote:
I'm looking for a solution to support virtual generated columns in index expressions or predicate. The comment in DefineIndex() seems to suggest that virtual generated columns there can be expanded in RelationGetIndexExpressions() and RelationGetIndexPredicate(). /* * XXX Virtual generated columns in index expressions or predicates * could be supported, but it needs support in * RelationGetIndexExpressions() and RelationGetIndexPredicate(). */ I'm not sure this is the appropriate approach, especially since we don't have the heap relation available in these functions. I think we're good as long as we expand the virtual columns before the index build process; otherwise, evaluating these expressions during the build will cause executor errors due to unexpanded virtual columns. I wonder if we can do the expansion in DefineIndex(), before the call to index_create(), like the attached. - Richard <v1-0001-Allow-virtual-columns-in-index-expressions-or-pre.patch> Hi Richard. This is a good usability improvement, making it easier to create an index when a virtual column is in an expression or a WHERE clause. It might also be nice if this could be extended to allow creating an index directly on the virtual column, like CREATE INDEX ON tbl (virtual_col). If we view this feature as a "syntactic sugar" to simplify creating an expression index, then I think this patch is very easy to accept. However, if we consider more complex situations, like a user running ALTER TABLE ... SET EXPRESSION, it raises more questions. Whether we need to add the kind of full support for these cases that was discussed in [1] needs a more detailed discussion. But for the simpler goal of being a syntax improvement, this patch works well. Thanks, Chengpeng Yan [1] https://www.postgresql.org/message-id/flat/CACJufxGao-cypdNhifHAdt8jHfK6-HX%3DtRBovBkgRuxw063GaA%40mail.gmail.com
