Hi all, I would like to express my sincere gratitude to everyone who has joined the discussion during my review of CF6749 these days. I have learned a lot about virtual generated columns and index creation logic, and I have developed a deeper interest in PostgreSQL indexes as well.
Currently, virtual generated columns are not supported in indexParams, indexIncludingParams and whereClause of index definitions. After days of debugging and exploration, I came up with an idea when I noticed the following code comment in the source code: we can add native support for virtual generated columns within indexes to enrich and enhance index capabilities. ```c /* * XXX Virtual generated columns in index expressions or predicates * could be supported, but it needs support in * RelationGetIndexExpressions() and RelationGetIndexPredicate(). */ ```c Here are my preliminary implementation plans in detail: 1. Index creation validation check During index creation, if virtual generated columns are used in indexIncludingParams or index whereClause, we need to ensure all underlying base columns that the virtual generated column depends on are included in indexParams. This is the fundamental premise to safely support virtual generated columns in indexes. Since virtual generated columns are not physically stored and computed on-the-fly during reading, they should never be added to indexParams or act as index key columns directly. 2. Supplement core relation helper functions Extend RelationGetIndexExpressions() andRelationGetIndexPredicate(). Following the existing logic of the query planner, invoke expand_virtual_generated_columns() inside these two functions to complete the expansion of virtual generated columns. 3. Relax validation restrictions in DefineIndex() Adjust the existing validation rules inside DefineIndex() for indexIncludingParams and whereClause, and lift the current restriction to allow virtual generated columns in these two fields. 4. Storage layer adjustment I have not yet figured out whether corresponding modifications are required for index physical storage. I would appreciate it if anyone could share relevant insights on this part. This is just my preliminary design proposal. There may be flawed logic or missing considerations in my thoughts. Please feel free to point out mistakes and give precious feedback. Thanks a lot for your help and guidance! regards, -- ZizhuanLiu (X-MAN) [email protected]
