v1-0001-support-create-index-on-virtual-generated-column.patch

From 8a60de43a7d1abf765a16890d6da7dc7e7f8a06d Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Wed, 26 Mar 2025 15:01:28 +0800
Subject: [PATCH v1 1/1] support create index on virtual generated column.

* internally such index will be transformed into expression index.
  for example, an index on (b int GENERATED ALWAYS AS (a * 2) VIRTUAL) will be
  converted into an expression index on ((a * 2)).
* in pageinspect module, add some test to check the index content of virtual generated column.
* primary key, unique index over virtual generated column are not supported.
  not sure they make sense or not.
* expression index and predicate index over virtual generated columns are
  currently not supported.
* virtual generated column can not be in "include column"
* all types of indexes are supported, and a hash index, gist test has been added.
* To support ALTER TABLE SET EXPRESSION, in pg_index, we need to track the original
  virtual generated column attribute number, so ALTER TABLE SET EXPRESSION can
  identify which index needs to be rebuilt.
* ALTER COLUMN SET DATA TYPE will also cause table rewrite, so we really
  need to track the virtual generated column attribute number that index was built on.

discussion: https://postgr.es/m/
commitfest entry:
---
 contrib/pageinspect/expected/btree.out        |  33 +++
 contrib/pageinspect/sql/btree.sql             |  21 ++
 doc/src/sgml/catalogs.sgml                    |  15 ++
 src/backend/catalog/index.c                   |   8 +
 src/backend/commands/indexcmds.c              | 188 ++++++++++++++----
 src/backend/commands/tablecmds.c              |  46 +++++
 src/backend/utils/adt/ruleutils.c             |  29 ++-
 src/backend/utils/cache/relcache.c            |  74 +++++++
 src/include/catalog/pg_index.h                |   1 +
 src/include/nodes/execnodes.h                 |   1 +
 src/include/utils/relcache.h                  |   1 +
 src/test/regress/expected/fast_default.out    |   8 +
 .../regress/expected/generated_virtual.out    | 116 +++++++++--
 src/test/regress/sql/fast_default.sql         |   6 +
 src/test/regress/sql/generated_virtual.sql    |  60 ++++--
 15 files changed, 530 insertions(+), 77 deletions(-)

diff --git a/contrib/pageinspect/expected/btree.out b/contrib/pageinspect/expected/btree.out
index 0aa5d73322f..56d57848cf7 100644
--- a/contrib/pageinspect/expected/btree.out
+++ b/contrib/pageinspect/expected/btree.out
@@ -183,6 +183,39 @@ tids       |
 
 SELECT * FROM bt_page_items(get_raw_page('test1_a_idx', 2));
 ERROR:  block number 2 is out of range for relation "test1_a_idx"
+---test index over virtual generated column
+CREATE TABLE test3 (a int8, b int4range, c int8 generated always as (a+1) virtual);
+INSERT INTO test3 VALUES (72057594037927936, '[0,1)');
+CREATE INDEX test3_a_idx ON test3 USING btree (c);
+SELECT * FROM bt_page_items('test3_a_idx', 1);
+-[ RECORD 1 ]-----------------------
+itemoffset | 1
+ctid       | (0,1)
+itemlen    | 16
+nulls      | f
+vars       | f
+data       | 01 00 00 00 00 00 00 01
+dead       | f
+htid       | (0,1)
+tids       | 
+
+--expect zero row.
+SELECT * FROM bt_page_items(get_raw_page('test1_a_idx', 1))
+EXCEPT ALL
+SELECT * FROM bt_page_items(get_raw_page('test3_a_idx', 1));
+(0 rows)
+
+CREATE TABLE test4(a int, b int GENERATED ALWAYS AS (a + 1), c text);
+INSERT INTO test4(a,c) VALUES (10,11), (10,11);
+CREATE INDEX test4_b ON test4 USING btree (b);
+CREATE INDEX test4_c ON test4 USING btree (c);
+ALTER TABLE test4 alter column b set data type text;
+---should return zero row.
+SELECT * FROM bt_page_items('test4_b', 1)
+EXCEPT ALL
+SELECT * FROM bt_page_items('test4_c', 1);
+(0 rows)
+
 -- Failure when using a non-btree index.
 CREATE INDEX test1_a_hash ON test1 USING hash(a);
 SELECT bt_metap('test1_a_hash');
diff --git a/contrib/pageinspect/sql/btree.sql b/contrib/pageinspect/sql/btree.sql
index 102ebdefe3c..d3392c11d5f 100644
--- a/contrib/pageinspect/sql/btree.sql
+++ b/contrib/pageinspect/sql/btree.sql
@@ -32,6 +32,27 @@ SELECT * FROM bt_page_items(get_raw_page('test1_a_idx', 0));
 SELECT * FROM bt_page_items(get_raw_page('test1_a_idx', 1));
 SELECT * FROM bt_page_items(get_raw_page('test1_a_idx', 2));
 
+---test index over virtual generated column
+CREATE TABLE test3 (a int8, b int4range, c int8 generated always as (a+1) virtual);
+INSERT INTO test3 VALUES (72057594037927936, '[0,1)');
+CREATE INDEX test3_a_idx ON test3 USING btree (c);
+SELECT * FROM bt_page_items('test3_a_idx', 1);
+
+--expect zero row.
+SELECT * FROM bt_page_items(get_raw_page('test1_a_idx', 1))
+EXCEPT ALL
+SELECT * FROM bt_page_items(get_raw_page('test3_a_idx', 1));
+
+CREATE TABLE test4(a int, b int GENERATED ALWAYS AS (a + 1), c text);
+INSERT INTO test4(a,c) VALUES (10,11), (10,11);
+CREATE INDEX test4_b ON test4 USING btree (b);
+CREATE INDEX test4_c ON test4 USING btree (c);
+ALTER TABLE test4 alter column b set data type text;
+---should return zero row.
+SELECT * FROM bt_page_items('test4_b', 1)
+EXCEPT ALL
+SELECT * FROM bt_page_items('test4_c', 1);
+
 -- Failure when using a non-btree index.
 CREATE INDEX test1_a_hash ON test1 USING hash(a);
 SELECT bt_metap('test1_a_hash');
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index fb050635551..da37c7a45d8 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -4595,6 +4595,21 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
       </para></entry>
      </row>
 
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>indattrgenerated</structfield> <type>int2vector</type>
+       (references <link linkend="catalog-pg-attribute"><structname>pg_attribute</structname></link>.<structfield>attnum</structfield>)
+      </para>
+      <para>
+       This is an array of <structfield>indnatts</structfield> values that
+       indicate which table virtual generated columns this index indexes.
+       For example, a value of <literal>1 3</literal> would mean that the first
+       and the third table columns of this index entries are virtual generated
+       column. A zero in this array indicates that the corresponding index
+       attribute is not virtual generated column reference.
+      </para></entry>
+     </row>
+
      <row>
       <entry role="catalog_table_entry"><para role="column_definition">
        <structfield>indexprs</structfield> <type>pg_node_tree</type>
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 739a92bdcc1..105d48c49d0 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -584,6 +584,12 @@ UpdateIndexRelation(Oid indexoid,
 	Relation	pg_index;
 	HeapTuple	tuple;
 	int			i;
+	int2vector *indgenkey;
+	int16	   *colgenerated;
+
+	colgenerated = palloc_array(int16, indexInfo->ii_NumIndexAttrs);
+	for (i = 0; i < indexInfo->ii_NumIndexAttrs; i++)
+		colgenerated[i] = indexInfo->ii_IndexAttrGeneratedNumbers[i];
 
 	/*
 	 * Copy the index key, opclass, and indoption info into arrays (should we
@@ -596,6 +602,7 @@ UpdateIndexRelation(Oid indexoid,
 	indclass = buildoidvector(opclassOids, indexInfo->ii_NumIndexKeyAttrs);
 	indoption = buildint2vector(coloptions, indexInfo->ii_NumIndexKeyAttrs);
 
+	indgenkey = buildint2vector(colgenerated, indexInfo->ii_NumIndexAttrs);
 	/*
 	 * Convert the index expressions (if any) to a text datum
 	 */
@@ -653,6 +660,7 @@ UpdateIndexRelation(Oid indexoid,
 	values[Anum_pg_index_indcollation - 1] = PointerGetDatum(indcollation);
 	values[Anum_pg_index_indclass - 1] = PointerGetDatum(indclass);
 	values[Anum_pg_index_indoption - 1] = PointerGetDatum(indoption);
+	values[Anum_pg_index_indattrgenerated - 1] = PointerGetDatum(indgenkey);
 	values[Anum_pg_index_indexprs - 1] = exprsDatum;
 	if (exprsDatum == (Datum) 0)
 		nulls[Anum_pg_index_indexprs - 1] = true;
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 89cc83e8843..44e806520f8 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -54,6 +54,7 @@
 #include "parser/parse_utilcmd.h"
 #include "partitioning/partdesc.h"
 #include "pgstat.h"
+#include "rewrite/rewriteHandler.h"
 #include "rewrite/rewriteManip.h"
 #include "storage/lmgr.h"
 #include "storage/proc.h"
@@ -90,6 +91,7 @@ static void ComputeIndexAttrs(IndexInfo *indexInfo,
 							  bool amcanorder,
 							  bool isconstraint,
 							  bool iswithoutoverlaps,
+							  bool is_primary,
 							  Oid ddl_userid,
 							  int ddl_sec_context,
 							  int *ddl_save_nestlevel);
@@ -182,6 +184,7 @@ CheckIndexCompatible(Oid oldId,
 					 bool isWithoutOverlaps)
 {
 	bool		isconstraint;
+	bool		is_primary;
 	Oid		   *typeIds;
 	Oid		   *collationIds;
 	Oid		   *opclassIds;
@@ -214,6 +217,12 @@ CheckIndexCompatible(Oid oldId,
 	 */
 	isconstraint = false;
 
+	/*
+	 * We can pretend is_primary = false unconditionally.  It only serves to
+	 * decide the text of an error message that should never happen for us.
+	 */
+	is_primary = false;
+
 	numberOfAttributes = list_length(attributeList);
 	Assert(numberOfAttributes > 0);
 	Assert(numberOfAttributes <= INDEX_MAX_KEYS);
@@ -254,7 +263,7 @@ CheckIndexCompatible(Oid oldId,
 					  coloptions, attributeList,
 					  exclusionOpNames, relationId,
 					  accessMethodName, accessMethodId,
-					  amcanorder, isconstraint, isWithoutOverlaps, InvalidOid,
+					  amcanorder, isconstraint, isWithoutOverlaps, is_primary, InvalidOid,
 					  0, NULL);
 
 	/* Get the soon-obsolete pg_index tuple. */
@@ -905,6 +914,29 @@ DefineIndex(Oid tableId,
 	if (stmt->whereClause)
 		CheckPredicate((Expr *) stmt->whereClause);
 
+	/* virtual generated column over predicate index not supported */
+	if (RelationGetDescr(rel)->constr && RelationGetDescr(rel)->constr->has_generated_virtual)
+	{
+		Bitmapset  *indexattrs_pred = NULL;
+		int			j;
+
+		pull_varattnos(stmt->whereClause, 1, &indexattrs_pred);
+
+		j = -1;
+		while ((j = bms_next_member(indexattrs_pred, j)) >= 0)
+		{
+			AttrNumber	attno = j + FirstLowInvalidHeapAttributeNumber;
+
+			if (TupleDescAttr(RelationGetDescr(rel), attno - 1)->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+			{
+				ereport(ERROR,
+						errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+						errmsg("partial index on virtual generated columns are not supported"));
+				break;
+			}
+		}
+	}
+
 	/*
 	 * Parse AM-specific options, convert to text array form, validate.
 	 */
@@ -941,6 +973,7 @@ DefineIndex(Oid tableId,
 					  stmt->excludeOpNames, tableId,
 					  accessMethodName, accessMethodId,
 					  amcanorder, stmt->isconstraint, stmt->iswithoutoverlaps,
+					  stmt->primary,
 					  root_save_userid, root_save_sec_context,
 					  &root_save_nestlevel);
 
@@ -1101,10 +1134,7 @@ DefineIndex(Oid tableId,
 
 	/*
 	 * We disallow indexes on system columns.  They would not necessarily get
-	 * updated correctly, and they don't seem useful anyway.
-	 *
-	 * Also disallow virtual generated columns in indexes (use expression
-	 * index instead).
+	 * updated correctly, and they don't seem useful anyway.	
 	 */
 	for (int i = 0; i < indexInfo->ii_NumIndexAttrs; i++)
 	{
@@ -1114,24 +1144,14 @@ DefineIndex(Oid tableId,
 			ereport(ERROR,
 					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 					 errmsg("index creation on system columns is not supported")));
-
-
-		if (TupleDescAttr(RelationGetDescr(rel), attno - 1)->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
-			ereport(ERROR,
-					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
-					 stmt->isconstraint ?
-					 errmsg("unique constraints on virtual generated columns are not supported") :
-					 errmsg("indexes on virtual generated columns are not supported")));
 	}
 
 	/*
-	 * Also check for system and generated columns used in expressions or
-	 * predicates.
+	 * Also check for system used in expressions or predicates.	 
 	 */
 	if (indexInfo->ii_Expressions || indexInfo->ii_Predicate)
 	{
 		Bitmapset  *indexattrs = NULL;
-		int			j;
 
 		pull_varattnos((Node *) indexInfo->ii_Expressions, 1, &indexattrs);
 		pull_varattnos((Node *) indexInfo->ii_Predicate, 1, &indexattrs);
@@ -1144,24 +1164,6 @@ DefineIndex(Oid tableId,
 						(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 						 errmsg("index creation on system columns is not supported")));
 		}
-
-		/*
-		 * XXX Virtual generated columns in index expressions or predicates
-		 * could be supported, but it needs support in
-		 * RelationGetIndexExpressions() and RelationGetIndexPredicate().
-		 */
-		j = -1;
-		while ((j = bms_next_member(indexattrs, j)) >= 0)
-		{
-			AttrNumber	attno = j + FirstLowInvalidHeapAttributeNumber;
-
-			if (TupleDescAttr(RelationGetDescr(rel), attno - 1)->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
-				ereport(ERROR,
-						(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
-						 stmt->isconstraint ?
-						 errmsg("unique constraints on virtual generated columns are not supported") :
-						 errmsg("indexes on virtual generated columns are not supported")));
-		}
 	}
 
 	/* Is index safe for others to ignore?  See set_indexsafe_procflags() */
@@ -1879,6 +1881,7 @@ ComputeIndexAttrs(IndexInfo *indexInfo,
 				  bool amcanorder,
 				  bool isconstraint,
 				  bool iswithoutoverlaps,
+				  bool is_primary,
 				  Oid ddl_userid,
 				  int ddl_sec_context,
 				  int *ddl_save_nestlevel)
@@ -1889,6 +1892,28 @@ ComputeIndexAttrs(IndexInfo *indexInfo,
 	int			nkeycols = indexInfo->ii_NumIndexKeyAttrs;
 	Oid			save_userid;
 	int			save_sec_context;
+	Relation	rel;
+	TupleDesc	reltupldesc;
+	List		*virtual_generated = NIL;
+
+	rel	= table_open(relId, NoLock);
+	reltupldesc = RelationGetDescr(rel);
+
+	/*
+	 * currently, we do not support virtual generated columns over expression
+	 * indexes.  we accumulate the attribute number of virtual generated columns
+	 * so we can verify it later.
+	*/
+	if (reltupldesc && reltupldesc->constr && reltupldesc->constr->has_generated_virtual)
+	{
+		for (int i = 0; i < reltupldesc->natts; i++)
+		{
+			Form_pg_attribute attr = TupleDescAttr(reltupldesc, i);
+
+			if (attr->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+				virtual_generated = lappend_int(virtual_generated, attr->attnum);
+		}
+	}
 
 	/* Allocate space for exclusion operator info, if needed */
 	if (exclusionOpNames)
@@ -1931,6 +1956,7 @@ ComputeIndexAttrs(IndexInfo *indexInfo,
 		IndexElem  *attribute = (IndexElem *) lfirst(lc);
 		Oid			atttype;
 		Oid			attcollation;
+		char		attgenerated = '\0';
 
 		/*
 		 * Process the column-or-expression to be indexed.
@@ -1940,6 +1966,7 @@ ComputeIndexAttrs(IndexInfo *indexInfo,
 			/* Simple index attribute */
 			HeapTuple	atttuple;
 			Form_pg_attribute attform;
+			AttrNumber	attnum;
 
 			Assert(attribute->expr == NULL);
 			atttuple = SearchSysCacheAttName(relId, attribute->name);
@@ -1958,15 +1985,95 @@ ComputeIndexAttrs(IndexInfo *indexInfo,
 									attribute->name)));
 			}
 			attform = (Form_pg_attribute) GETSTRUCT(atttuple);
+			attnum = attform->attnum;
 			indexInfo->ii_IndexAttrNumbers[attn] = attform->attnum;
 			atttype = attform->atttypid;
 			attcollation = attform->attcollation;
+			attgenerated = attform->attgenerated;
 			ReleaseSysCache(atttuple);
+
+			if (attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+			{
+				Node	   *node;
+				Bitmapset  *genattrs = NULL;
+
+				if (attn >= nkeycols)
+					ereport(ERROR,
+							errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+							errmsg("virtual generated column are not supported in included columns"));
+
+				if (is_primary)
+					ereport(ERROR,
+							errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+							errmsg("primary key on virtual generated columns are not supported"));
+
+				if (indexInfo->ii_Unique)
+					ereport(ERROR,
+							errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+							errmsg("unique constraints on virtual generated columns are not supported"));
+
+				/* Fetch the GENERATED AS expression tree */
+				node = build_generation_expression(rel, attnum);
+				if (node == NULL)
+					elog(ERROR, "no generation expression found for column number %d of table \"%s\"",
+								attnum, RelationGetRelationName(rel));
+
+
+				/*
+				 * if the generation expression is reference another simple Var,
+				 * then set ii_IndexAttrNumbers to that Var->varattno.
+				*/
+				if (IsA(node, Var))
+				{
+					Var		   *var = (Var *) node;
+
+					Assert(var->varattno > 0);
+
+					if (atttype != var->vartype)
+						elog(ERROR, "expect type %u but get %u", atttype, var->vartype);
+
+					if (attcollation != var->varcollid)
+						elog(ERROR, "expect collation %u but get %u", attcollation, var->varcollid);
+
+					indexInfo->ii_IndexAttrNumbers[attn] = var->varattno;
+				}
+				else
+				{
+					/*
+					 * Strip any top-level COLLATE clause.  This ensures that we treat
+					 * "x COLLATE y" and "(x COLLATE y)" alike.
+					*/
+					while (IsA(node, CollateExpr))
+						node = (Node *) ((CollateExpr *) node)->arg;
+
+					/* generation expressions are immutable, so this unlikely to happen */
+					if (contain_mutable_functions_after_planning((Expr *) node))
+						elog(ERROR,"functions in index expression must be marked IMMUTABLE");
+
+					/* virtual generated column should based on pg_attribute.attcollation */
+					if (attcollation != exprCollation(node))
+						elog(ERROR, "expect collation %u but get %u", attcollation, exprCollation(node));
+
+					pull_varattnos(node, 1, &genattrs);
+
+					if (genattrs == NULL)
+						ereport(ERROR,
+								errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+								errmsg("can not create index based on variable free generation expression"));
+
+					indexInfo->ii_IndexAttrNumbers[attn] = 0;	/* marks expression */
+					indexInfo->ii_Expressions = lappend(indexInfo->ii_Expressions,
+														node);
+				}
+				indexInfo->ii_IndexAttrGeneratedNumbers[attn] = attnum;
+			}
 		}
 		else
 		{
 			/* Index expression */
 			Node	   *expr = attribute->expr;
+			Bitmapset  *indexattrs_expr = NULL;
+			int			j;
 
 			Assert(expr != NULL);
 
@@ -1977,6 +2084,18 @@ ComputeIndexAttrs(IndexInfo *indexInfo,
 			atttype = exprType(expr);
 			attcollation = exprCollation(expr);
 
+			pull_varattnos(expr, 1, &indexattrs_expr);
+
+			j = -1;
+			while ((j = bms_next_member(indexattrs_expr, j)) >= 0)
+			{
+				AttrNumber	attno = j + FirstLowInvalidHeapAttributeNumber;
+				if (list_member_int(virtual_generated, attno))
+					ereport(ERROR,
+							errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+							errmsg("expression index over virtual generated columns are not supported"));
+			}
+
 			/*
 			 * Strip any top-level COLLATE clause.  This ensures that we treat
 			 * "x COLLATE y" and "(x COLLATE y)" alike.
@@ -2246,6 +2365,7 @@ ComputeIndexAttrs(IndexInfo *indexInfo,
 
 		attn++;
 	}
+	table_close(rel, NoLock);
 }
 
 /*
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 1202544ebd0..b9561a9cf1a 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -8547,6 +8547,35 @@ ATExecSetExpression(AlteredTableInfo *tab, Relation rel, const char *colName,
 		 */
 		RememberAllDependentForRebuilding(tab, AT_SetExpression, rel, attnum, colName);
 	}
+	else
+	{
+		List	*changed_gen_IndexOids = NIL;
+
+		RememberAllDependentForRebuilding(tab, AT_SetExpression, rel, attnum, colName);
+
+		/*
+		 * Internally, we convert index of virtual generation column into an
+		 * expression index. For example, if column 'b' is defined as (b INT
+		 * GENERATED ALWAYS AS (a * 2) VIRTUAL) then index over 'b' would
+		 * transformed into an expression index as ((a * 2)). As a result, the
+		 * pg_depend refobjsubid does not retain the original attribute number
+		 * of the virtual generated column. But we need rebuild any index that
+		 * was build on virtual generated column. so we need search pg_index.
+		 */
+		changed_gen_IndexOids = RelationGetGeneratedIndexList(rel, attnum);
+
+		foreach_oid(idxoid, changed_gen_IndexOids)
+			RememberIndexForRebuilding(idxoid, tab);
+
+		/*
+		 * Changing the generation expression of the virtual generated column
+		 * does not require table rewrite. However, if an index is built on top
+		 * of it, table rewrite is necessary. So in phase 3, index_rebuild can
+		 * successfully rebuild the index based on the new generation expression
+		*/
+		if (attgenerated == ATTRIBUTE_GENERATED_VIRTUAL && tab->changedIndexOids != NIL)
+			rewrite = true;
+	}
 
 	/*
 	 * Drop the dependency records of the GENERATED expression, in particular
@@ -14035,6 +14064,7 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
 	SysScanDesc scan;
 	HeapTuple	depTup;
 	ObjectAddress address;
+	List	*changed_gen_IndexOids	= NIL;
 
 	/*
 	 * Clear all the missing values if we're rewriting the table, since this
@@ -14115,6 +14145,7 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
 						 errmsg("default for column \"%s\" cannot be cast automatically to type %s",
 								colName, format_type_be(targettype))));
 		}
+		exprSetCollation(defaultexpr, targetcollid);
 	}
 	else
 		defaultexpr = NULL;
@@ -14130,6 +14161,21 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
 	 */
 	RememberAllDependentForRebuilding(tab, AT_AlterColumnType, rel, attnum, colName);
 
+	/*
+	 * RememberAllDependentForRebuilding cannot collect everything that is
+	 * depends on virtual generated column. Because simple column index over
+	 * virtual generated column was converted to expression index. see
+	 * ATExecSetExpression also.
+	*/
+	changed_gen_IndexOids = RelationGetGeneratedIndexList(rel, attnum);
+
+	foreach_oid(idxoid, changed_gen_IndexOids)
+		RememberIndexForRebuilding(idxoid, tab);
+
+	if (attTup->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL &&
+		tab->changedIndexOids != NIL)
+		tab->rewrite |= AT_REWRITE_COLUMN_REWRITE;
+
 	/*
 	 * Now scan for dependencies of this column on other things.  The only
 	 * things we should find are the dependency on the column datatype and
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 9e90acedb91..6dffd1ca6c1 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -1290,9 +1290,11 @@ pg_get_indexdef_worker(Oid indexrelid, int colno,
 	Datum		indcollDatum;
 	Datum		indclassDatum;
 	Datum		indoptionDatum;
+	Datum		indgenkeyDatum;
 	oidvector  *indcollation;
 	oidvector  *indclass;
 	int2vector *indoption;
+	int2vector *indgenkey;
 	StringInfoData buf;
 	char	   *str;
 	char	   *sep;
@@ -1325,6 +1327,10 @@ pg_get_indexdef_worker(Oid indexrelid, int colno,
 											Anum_pg_index_indoption);
 	indoption = (int2vector *) DatumGetPointer(indoptionDatum);
 
+	indgenkeyDatum = SysCacheGetAttrNotNull(INDEXRELID, ht_idx,
+											Anum_pg_index_indattrgenerated);
+	indgenkey = (int2vector *) DatumGetPointer(indgenkeyDatum);
+
 	/*
 	 * Fetch the pg_class tuple of the index relation
 	 */
@@ -1398,6 +1404,7 @@ pg_get_indexdef_worker(Oid indexrelid, int colno,
 	for (keyno = 0; keyno < idxrec->indnatts; keyno++)
 	{
 		AttrNumber	attnum = idxrec->indkey.values[keyno];
+		AttrNumber	gennum = indgenkey->values[keyno];
 		Oid			keycoltype;
 		Oid			keycolcollation;
 
@@ -1418,7 +1425,27 @@ pg_get_indexdef_worker(Oid indexrelid, int colno,
 			appendStringInfoString(&buf, sep);
 		sep = ", ";
 
-		if (attnum != 0)
+		if (attnum == 0 && gennum != 0)
+			indexpr_item = lnext(indexprs, indexpr_item);
+
+		if (gennum != 0)
+		{
+			/*
+			 * index over virtual generated column was converted into a
+			 * expression index, but we need restore the original attribute
+			 * number for recreate it.
+			*/
+			char	   *virtual_attname;
+			int32		geneycoltypmod;
+
+			virtual_attname = get_attname(indrelid, gennum, false);
+			if (!colno || colno == keyno + 1)
+				appendStringInfoString(&buf, quote_identifier(virtual_attname));
+			get_atttypetypmodcoll(indrelid, gennum,
+								  &keycoltype, &geneycoltypmod,
+								  &keycolcollation);
+		}
+		else if (attnum != 0)
 		{
 			/* Simple index column */
 			char	   *attname;
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 9f54a9e72b7..e931f2f0390 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -4880,6 +4880,80 @@ RelationGetIndexList(Relation relation)
 	return result;
 }
 
+/*
+ * RelationGetGeneratedIndexList
+ *
+ * attnum is the virtual generated column attribute number
+ * get the index that is build based on virtual generation column.
+ * note: currently we do not support primary key/unique index over virtual
+ * generated column.
+*/
+List *
+RelationGetGeneratedIndexList(Relation relation, int attnum)
+{
+	Relation	indrel;
+	SysScanDesc indscan;
+	ScanKeyData skey;
+	HeapTuple	htup;
+	List	   *result;
+	Datum		datum;
+	int2vector *indattrgenerated;
+
+	result = NIL;
+
+	/* Prepare to scan pg_index for entries having indrelid = this relation. */
+	ScanKeyInit(&skey,
+				Anum_pg_index_indrelid,
+				BTEqualStrategyNumber, F_OIDEQ,
+				ObjectIdGetDatum(RelationGetRelid(relation)));
+
+	indrel = table_open(IndexRelationId, AccessShareLock);
+	indscan = systable_beginscan(indrel, IndexIndrelidIndexId, true,
+								 NULL, 1, &skey);
+
+	while (HeapTupleIsValid(htup = systable_getnext(indscan)))
+	{
+		Form_pg_index index = (Form_pg_index) GETSTRUCT(htup);
+
+		/*
+		 * Ignore any indexes that are currently being dropped.  This will
+		 * prevent them from being searched, inserted into, or considered in
+		 * HOT-safety decisions.  It's unsafe to touch such an index at all
+		 * since its catalog entries could disappear at any instant.
+		 */
+		if (!index->indislive)
+			continue;
+
+		if (!index->indisvalid)
+			continue;
+
+		datum = SysCacheGetAttrNotNull(INDEXRELID, htup, Anum_pg_index_indattrgenerated);
+		indattrgenerated = ((int2vector *) DatumGetPointer(datum));
+
+		if (indattrgenerated->dim1 > 0)
+		{
+			int			i;
+			for (i = 0; i < indattrgenerated->dim1; i++)
+			{
+				if (indattrgenerated->values[i] == attnum)
+				{
+					result = lappend_oid(result, index->indexrelid);
+					break;
+				}
+			}
+		}
+	}
+
+	systable_endscan(indscan);
+
+	table_close(indrel, AccessShareLock);
+
+	/* Sort the result list into OID order, per API spec. */
+	list_sort(result, list_oid_cmp);
+
+	return result;
+}
+
 /*
  * RelationGetStatExtList
  *		get a list of OIDs of statistics objects on this relation
diff --git a/src/include/catalog/pg_index.h b/src/include/catalog/pg_index.h
index 4392b9d221d..7ed74a593a4 100644
--- a/src/include/catalog/pg_index.h
+++ b/src/include/catalog/pg_index.h
@@ -54,6 +54,7 @@ CATALOG(pg_index,2610,IndexRelationId) BKI_SCHEMA_MACRO
 	oidvector	indclass BKI_LOOKUP(pg_opclass) BKI_FORCE_NOT_NULL; /* opclass identifiers */
 	int2vector	indoption BKI_FORCE_NOT_NULL;	/* per-column flags
 												 * (AM-specific meanings) */
+	int2vector	indattrgenerated BKI_FORCE_NOT_NULL; /* the attribute of virtual generated column? */
 	pg_node_tree indexprs;		/* expression trees for index attributes that
 								 * are not simple column references; one for
 								 * each zero entry in indkey[] */
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index d4d4e655180..ba544f33cb4 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -196,6 +196,7 @@ typedef struct IndexInfo
 	int			ii_NumIndexAttrs;	/* total number of columns in index */
 	int			ii_NumIndexKeyAttrs;	/* number of key columns in index */
 	AttrNumber	ii_IndexAttrNumbers[INDEX_MAX_KEYS];
+	AttrNumber	ii_IndexAttrGeneratedNumbers[INDEX_MAX_KEYS]; /* XXX more better comments */
 	List	   *ii_Expressions; /* list of Expr */
 	List	   *ii_ExpressionsState;	/* list of ExprState */
 	List	   *ii_Predicate;	/* list of Expr */
diff --git a/src/include/utils/relcache.h b/src/include/utils/relcache.h
index a7c55db339e..56bcc03dc51 100644
--- a/src/include/utils/relcache.h
+++ b/src/include/utils/relcache.h
@@ -45,6 +45,7 @@ extern void RelationClose(Relation relation);
  */
 extern List *RelationGetFKeyList(Relation relation);
 extern List *RelationGetIndexList(Relation relation);
+extern List *RelationGetGeneratedIndexList(Relation relation, int attnum);
 extern List *RelationGetStatExtList(Relation relation);
 extern Oid	RelationGetPrimaryKeyIndex(Relation relation, bool deferrable_ok);
 extern Oid	RelationGetReplicaIndex(Relation relation);
diff --git a/src/test/regress/expected/fast_default.out b/src/test/regress/expected/fast_default.out
index ccbcdf8403f..ef19f667cc1 100644
--- a/src/test/regress/expected/fast_default.out
+++ b/src/test/regress/expected/fast_default.out
@@ -70,6 +70,14 @@ NOTICE:  rewriting table has_volatile for reason 4
 -- stored generated columns need a rewrite
 ALTER TABLE has_volatile ADD col7 int GENERATED ALWAYS AS (55) stored;
 NOTICE:  rewriting table has_volatile for reason 2
+-- if there is any index over virtual generated columns,
+-- change generation expression need rewrite
+CREATE INDEX on has_volatile(col6);
+ALTER TABLE has_volatile ALTER COLUMN col6 SET EXPRESSION AS (col1 * 3);
+NOTICE:  rewriting table has_volatile for reason 2
+-- table rewrite again.
+ALTER TABLE has_volatile ALTER COLUMN col6 SET DATA TYPE INT8;
+NOTICE:  rewriting table has_volatile for reason 4
 -- Test a large sample of different datatypes
 CREATE TABLE T(pk INT NOT NULL PRIMARY KEY, c_int INT DEFAULT 1);
 SELECT set('t');
diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out
index dc09c85938e..21173f3b205 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -697,30 +697,106 @@ ERROR:  not-null constraints are not supported on virtual generated columns
 --INSERT INTO gtest22b VALUES (2);
 --INSERT INTO gtest22b VALUES (2);
 -- indexes
-CREATE TABLE gtest22c (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
---CREATE INDEX gtest22c_b_idx ON gtest22c (b);
---CREATE INDEX gtest22c_expr_idx ON gtest22c ((b * 3));
---CREATE INDEX gtest22c_pred_idx ON gtest22c (a) WHERE b > 0;
---\d gtest22c
---INSERT INTO gtest22c VALUES (1), (2), (3);
---SET enable_seqscan TO off;
---SET enable_bitmapscan TO off;
---EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b = 4;
---SELECT * FROM gtest22c WHERE b = 4;
---EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 6;
---SELECT * FROM gtest22c WHERE b * 3 = 6;
---EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
---SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
---ALTER TABLE gtest22c ALTER COLUMN b SET EXPRESSION AS (a * 4);
---ANALYZE gtest22c;
---EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b = 8;
---SELECT * FROM gtest22c WHERE b = 8;
+CREATE TABLE gtest22c (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL
+                      ,c int GENERATED ALWAYS AS (11) VIRTUAL
+                      ,d int GENERATED ALWAYS AS (a *3) VIRTUAL
+                      ,e int4range GENERATED ALWAYS AS (int4range(a, a+10)) VIRTUAL
+                      ,f int GENERATED ALWAYS AS (a) VIRTUAL);
+CREATE INDEX gtest22c_b_idx ON gtest22c (b);
+--variable free generation expression have no pratical usage, so error out.
+CREATE INDEX gtest22c_c_idx ON gtest22c (c);
+ERROR:  can not create index based on variable free generation expression
+CREATE INDEX gtest22c_d_idx ON gtest22c USING hash(d);
+CREATE INDEX gtest22c_e_idx ON gtest22c USING gist(e);
+--error. include columns are not supported.
+CREATE INDEX gtest22c_idx1 ON gtest22c USING btree(a) include (b,c);
+ERROR:  virtual generated column are not supported in included columns
+CREATE INDEX gtest22c_idx1 ON gtest22c USING btree(a) include (f);
+ERROR:  virtual generated column are not supported in included columns
+-- CREATE INDEX gtest22c_expr_idx ON gtest22c ((b * 3));
+-- CREATE INDEX gtest22c_pred_idx ON gtest22c (a) WHERE b > 0;
+\d gtest22c
+                        Table "generated_virtual_tests.gtest22c"
+ Column |   Type    | Collation | Nullable |                  Default                   
+--------+-----------+-----------+----------+--------------------------------------------
+ a      | integer   |           |          | 
+ b      | integer   |           |          | generated always as (a * 2)
+ c      | integer   |           |          | generated always as (11)
+ d      | integer   |           |          | generated always as (a * 3)
+ e      | int4range |           |          | generated always as (int4range(a, a + 10))
+ f      | integer   |           |          | generated always as (a)
+Indexes:
+    "gtest22c_b_idx" btree (b)
+    "gtest22c_d_idx" hash (d)
+    "gtest22c_e_idx" gist (e)
+
+INSERT INTO gtest22c(a) VALUES (1), (2), (3), (10);
+SET enable_seqscan TO off;
+SET enable_bitmapscan TO off;
+EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b = 4;
+                 QUERY PLAN                  
+---------------------------------------------
+ Index Scan using gtest22c_b_idx on gtest22c
+   Index Cond: ((a * 2) = 4)
+(2 rows)
+
+SELECT * FROM gtest22c WHERE b = 4;
+ a | b | c  | d |   e    | f 
+---+---+----+---+--------+---
+ 2 | 4 | 11 | 6 | [2,12) | 2
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE d = 6;
+                 QUERY PLAN                  
+---------------------------------------------
+ Index Scan using gtest22c_d_idx on gtest22c
+   Index Cond: ((a * 3) = 6)
+(2 rows)
+
+SELECT * FROM gtest22c WHERE d = 6;
+ a | b | c  | d |   e    | f 
+---+---+----+---+--------+---
+ 2 | 4 | 11 | 6 | [2,12) | 2
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE e @> 12;
+                  QUERY PLAN                  
+----------------------------------------------
+ Index Scan using gtest22c_e_idx on gtest22c
+   Index Cond: (int4range(a, (a + 10)) @> 12)
+(2 rows)
+
+select count(*) from gtest22c where e @> 12;
+ count 
+-------
+     2
+(1 row)
+
+-- EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 6;
+-- SELECT * FROM gtest22c WHERE b * 3 = 6;
+-- EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
+-- SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
+ALTER TABLE gtest22c ALTER COLUMN b SET EXPRESSION AS (a * 4);
+ANALYZE gtest22c;
+EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b = 8;
+                 QUERY PLAN                  
+---------------------------------------------
+ Index Scan using gtest22c_b_idx on gtest22c
+   Index Cond: ((a * 4) = 8)
+(2 rows)
+
+SELECT * FROM gtest22c WHERE b = 8;
+ a | b | c  | d |   e    | f 
+---+---+----+---+--------+---
+ 2 | 8 | 11 | 6 | [2,12) | 2
+(1 row)
+
 --EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 12;
 --SELECT * FROM gtest22c WHERE b * 3 = 12;
 --EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
 --SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
---RESET enable_seqscan;
---RESET enable_bitmapscan;
+RESET enable_seqscan;
+RESET enable_bitmapscan;
 -- foreign keys
 CREATE TABLE gtest23a (x int PRIMARY KEY, y int);
 --INSERT INTO gtest23a VALUES (1, 11), (2, 22), (3, 33);
diff --git a/src/test/regress/sql/fast_default.sql b/src/test/regress/sql/fast_default.sql
index 068dd0bc8aa..b39e76bcfc3 100644
--- a/src/test/regress/sql/fast_default.sql
+++ b/src/test/regress/sql/fast_default.sql
@@ -77,6 +77,12 @@ ALTER TABLE has_volatile ALTER COLUMN col1 SET DATA TYPE float8,
 -- stored generated columns need a rewrite
 ALTER TABLE has_volatile ADD col7 int GENERATED ALWAYS AS (55) stored;
 
+-- if there is any index over virtual generated columns,
+-- change generation expression need rewrite
+CREATE INDEX on has_volatile(col6);
+ALTER TABLE has_volatile ALTER COLUMN col6 SET EXPRESSION AS (col1 * 3);
+-- table rewrite again.
+ALTER TABLE has_volatile ALTER COLUMN col6 SET DATA TYPE INT8;
 
 
 -- Test a large sample of different datatypes
diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql
index dab8c92ef99..ae58cff7d22 100644
--- a/src/test/regress/sql/generated_virtual.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -1,6 +1,4 @@
 -- keep these tests aligned with generated_stored.sql
-
-
 CREATE SCHEMA generated_virtual_tests;
 GRANT USAGE ON SCHEMA generated_virtual_tests TO PUBLIC;
 SET search_path = generated_virtual_tests;
@@ -363,32 +361,50 @@ CREATE TABLE gtest22b (a int, b int GENERATED ALWAYS AS (a / 2) VIRTUAL, PRIMARY
 --INSERT INTO gtest22b VALUES (2);
 
 -- indexes
-CREATE TABLE gtest22c (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
---CREATE INDEX gtest22c_b_idx ON gtest22c (b);
---CREATE INDEX gtest22c_expr_idx ON gtest22c ((b * 3));
---CREATE INDEX gtest22c_pred_idx ON gtest22c (a) WHERE b > 0;
---\d gtest22c
+CREATE TABLE gtest22c (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL
+                      ,c int GENERATED ALWAYS AS (11) VIRTUAL
+                      ,d int GENERATED ALWAYS AS (a *3) VIRTUAL
+                      ,e int4range GENERATED ALWAYS AS (int4range(a, a+10)) VIRTUAL
+                      ,f int GENERATED ALWAYS AS (a) VIRTUAL);
+CREATE INDEX gtest22c_b_idx ON gtest22c (b);
+--variable free generation expression have no pratical usage, so error out.
+CREATE INDEX gtest22c_c_idx ON gtest22c (c);
+CREATE INDEX gtest22c_d_idx ON gtest22c USING hash(d);
+CREATE INDEX gtest22c_e_idx ON gtest22c USING gist(e);
+--error. include columns are not supported.
+CREATE INDEX gtest22c_idx1 ON gtest22c USING btree(a) include (b,c);
+CREATE INDEX gtest22c_idx1 ON gtest22c USING btree(a) include (f);
 
---INSERT INTO gtest22c VALUES (1), (2), (3);
---SET enable_seqscan TO off;
---SET enable_bitmapscan TO off;
---EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b = 4;
---SELECT * FROM gtest22c WHERE b = 4;
---EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 6;
---SELECT * FROM gtest22c WHERE b * 3 = 6;
---EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
---SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
+-- CREATE INDEX gtest22c_expr_idx ON gtest22c ((b * 3));
+-- CREATE INDEX gtest22c_pred_idx ON gtest22c (a) WHERE b > 0;
+\d gtest22c
 
---ALTER TABLE gtest22c ALTER COLUMN b SET EXPRESSION AS (a * 4);
---ANALYZE gtest22c;
---EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b = 8;
---SELECT * FROM gtest22c WHERE b = 8;
+INSERT INTO gtest22c(a) VALUES (1), (2), (3), (10);
+SET enable_seqscan TO off;
+SET enable_bitmapscan TO off;
+EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b = 4;
+SELECT * FROM gtest22c WHERE b = 4;
+EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE d = 6;
+SELECT * FROM gtest22c WHERE d = 6;
+EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE e @> 12;
+select count(*) from gtest22c where e @> 12;
+
+-- EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 6;
+-- SELECT * FROM gtest22c WHERE b * 3 = 6;
+-- EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
+-- SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
+
+ALTER TABLE gtest22c ALTER COLUMN b SET EXPRESSION AS (a * 4);
+ANALYZE gtest22c;
+EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b = 8;
+SELECT * FROM gtest22c WHERE b = 8;
 --EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 12;
 --SELECT * FROM gtest22c WHERE b * 3 = 12;
 --EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
 --SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
---RESET enable_seqscan;
---RESET enable_bitmapscan;
+
+RESET enable_seqscan;
+RESET enable_bitmapscan;
 
 -- foreign keys
 CREATE TABLE gtest23a (x int PRIMARY KEY, y int);
-- 
2.34.1