hi.
I realized that we can also modify attgenerated when updating the generated
expression using ALTER COLUMN SET EXPRESSION.
so POC attached.

I have also considered using ALTER TABLE ALTER COLUMN STORED/VIRTUAL
to change attgenerated,
but since we can update both the generated expression and attgenerated
simultaneously, extending ALTER COLUMN SET EXPRESSION seems more better
than  ALTER COLUMN {STORED|VIRTUAL}

example syntax explanation:
ALTER TABLE gtest29 ALTER COLUMN b SET EXPRESSION AS (a * 3);
exists syntax, nothing changed.

ALTER TABLE gtest29 ALTER COLUMN b SET EXPRESSION AS (a * 3) STORED;
column b attgenerated will be set to ATTRIBUTE_GENERATED_STORED,
table rewrite will happen.

ALTER TABLE gtest29 ALTER COLUMN b SET EXPRESSION AS (a * 3) VIRTUAL;
column b attgenerated will set to ATTRIBUTE_GENERATED_VIRTUAL,
table rewrite will not happen. (VACUUM FULL will make that column
values as 0, isnull as true).

what do you think?
From cc458eec71fd100ea420629ae9d84d9e80bbdd88 Mon Sep 17 00:00:00 2001
From: jian he <jian.universal...@gmail.com>
Date: Thu, 28 Aug 2025 11:53:46 +0800
Subject: [PATCH v1 1/1] ALTER COLUMN SET EXPRESSION [GENERATED|STORED]

example syntax explanation:
ALTER TABLE gtest29 ALTER COLUMN b SET EXPRESSION AS (a * 3);
nohting changed, work as is.

ALTER TABLE gtest29 ALTER COLUMN b SET EXPRESSION AS (a * 3) STORED;
column b attgenerated will set to ATTRIBUTE_GENERATED_STORED,
table rewrite will happen.

ALTER TABLE gtest29 ALTER COLUMN b SET EXPRESSION AS (a * 3) VIRTUAL;
column b attgenerated will set to ATTRIBUTE_GENERATED_VIRTUAL,
table rewrite will not happen.

TODO: add tests on src/test/modules/test_ddl_deparse/sql/alter_table.sql
context: https://git.postgresql.org/cgit/postgresql.git/commit/?id=83ea6c54025bea67bcd4949a6d58d3fc11c3e21b
discussion: https://postgr.es/m/
---
 doc/src/sgml/ref/alter_table.sgml             |  21 ++-
 src/backend/access/heap/heapam_handler.c      |  11 ++
 src/backend/commands/tablecmds.c              | 134 +++++++++++++++++-
 src/backend/parser/gram.y                     |  28 ++++
 src/include/nodes/parsenodes.h                |   8 ++
 src/test/regress/expected/fast_default.out    |  10 ++
 .../regress/expected/generated_stored.out     | 120 ++++++++++++++++
 .../regress/expected/generated_virtual.out    | 110 ++++++++++++++
 src/test/regress/expected/publication.out     |   3 +
 src/test/regress/sql/fast_default.sql         |   9 ++
 src/test/regress/sql/generated_stored.sql     |  56 ++++++++
 src/test/regress/sql/generated_virtual.sql    |  38 +++++
 src/test/regress/sql/publication.sql          |   2 +
 src/tools/pgindent/typedefs.list              |   1 +
 14 files changed, 541 insertions(+), 10 deletions(-)

diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 8867da6c693..6149ac38f24 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -46,7 +46,7 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
     ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET DEFAULT <replaceable class="parameter">expression</replaceable>
     ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> DROP DEFAULT
     ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> { SET | DROP } NOT NULL
-    ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET EXPRESSION AS ( <replaceable class="parameter">expression</replaceable> )
+    ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET EXPRESSION AS ( <replaceable class="parameter">expression</replaceable> ) [STORED | VIRTUAL]
     ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> DROP EXPRESSION [ IF EXISTS ]
     ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <replaceable>sequence_options</replaceable> ) ]
     ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> { SET GENERATED { ALWAYS | BY DEFAULT } | SET <replaceable>sequence_option</replaceable> | RESTART [ [ WITH ] <replaceable class="parameter">restart</replaceable> ] } [...]
@@ -266,16 +266,25 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
    </varlistentry>
 
    <varlistentry id="sql-altertable-desc-set-expression">
-    <term><literal>SET EXPRESSION AS</literal></term>
+    <term><literal>SET EXPRESSION AS ( <replaceable class="parameter">expression</replaceable> ) <optional>STORED | VIRTUAL </optional> </literal></term>
     <listitem>
      <para>
-      This form replaces the expression of a generated column.  Existing data
-      in a stored generated column is rewritten and all the future changes
-      will apply the new generation expression.
+      This form replaces the expression of a generated column and optionally modify its storage persistence type.
+      If <literal>STORED</literal> is specified or the column is stored generated column,
+      existing data is rewritten and all the future changes will apply the new generation expression.
      </para>
 
      <para>
-      When this form is used on a stored generated column, its statistics
+      If <literal>VIRTUAL</literal> is specified, existing data won’t be rewritten,
+      and <link linkend="catalog-pg-attribute"><structname>pg_attribute</structname></link>.<structfield>attgenerated</structfield>
+      is set to <literal>v</literal>.
+      If <literal>STORED</literal> is specified,
+      <link linkend="catalog-pg-attribute"><structname>pg_attribute</structname></link>.<structfield>attgenerated</structfield>
+      set to <literal>s</literal>.
+     </para>
+
+     <para>
+      When this form is used on a stored generated column or <literal>STORED</literal> is specified, its statistics
       are removed, so running
       <link linkend="sql-analyze"><command>ANALYZE</command></link>
       on the table afterwards is recommended.
diff --git a/src/backend/access/heap/heapam_handler.c b/src/backend/access/heap/heapam_handler.c
index cb4bc35c93e..176072c8b61 100644
--- a/src/backend/access/heap/heapam_handler.c
+++ b/src/backend/access/heap/heapam_handler.c
@@ -2375,6 +2375,9 @@ heapam_scan_sample_next_tuple(TableScanDesc scan, SampleScanState *scanstate,
  * currently only known to happen as an after-effect of ALTER TABLE
  * SET WITHOUT OIDS.
  *
+ * 3. ALTER TABLE SET EXPRESSION VIRTUAL may set the stored tuple value to
+ * virtual, we can not stored any large values of it.
+ *
  * So, we must reconstruct the tuple from component Datums.
  */
 static void
@@ -2394,6 +2397,14 @@ reform_and_rewrite_tuple(HeapTuple tuple,
 	{
 		if (TupleDescCompactAttr(newTupDesc, i)->attisdropped)
 			isnull[i] = true;
+		if (TupleDescCompactAttr(newTupDesc, i)->attgenerated)
+		{
+			if (TupleDescAttr(newTupDesc, i)->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+			{
+				values[i] = (Datum) 0;
+				isnull[i] = true;
+			}
+		}
 	}
 
 	copiedTuple = heap_form_tuple(newTupDesc, values, isnull);
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 082a3575d62..accd50424b6 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -522,6 +522,7 @@ static ObjectAddress ATExecDropIdentity(Relation rel, const char *colName, bool
 static ObjectAddress ATExecSetExpression(AlteredTableInfo *tab, Relation rel, const char *colName,
 										 Node *newExpr, LOCKMODE lockmode);
 static void ATPrepDropExpression(Relation rel, AlterTableCmd *cmd, bool recurse, bool recursing, LOCKMODE lockmode);
+static void ATPrepSetExpression(Relation rel, AlterTableCmd *cmd, bool recurse, bool recursing, LOCKMODE lockmode);
 static ObjectAddress ATExecDropExpression(Relation rel, const char *colName, bool missing_ok, LOCKMODE lockmode);
 static ObjectAddress ATExecSetStatistics(Relation rel, const char *colName, int16 colNum,
 										 Node *newValue, LOCKMODE lockmode);
@@ -5018,6 +5019,7 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
 			ATSimplePermissions(cmd->subtype, rel,
 								ATT_TABLE | ATT_PARTITIONED_TABLE | ATT_FOREIGN_TABLE);
 			ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode, context);
+			ATPrepSetExpression(rel, cmd, recurse, recursing, lockmode);
 			pass = AT_PASS_SET_EXPRESSION;
 			break;
 		case AT_DropExpression: /* ALTER COLUMN DROP EXPRESSION */
@@ -8576,8 +8578,9 @@ ATExecSetExpression(AlteredTableInfo *tab, Relation rel, const char *colName,
 	Expr	   *defval;
 	NewColumnValue *newval;
 	RawColumnDefault *rawEnt;
+	Node	   *raw_default;
 
-	tuple = SearchSysCacheAttName(RelationGetRelid(rel), colName);
+	tuple = SearchSysCacheCopyAttName(RelationGetRelid(rel), colName);
 	if (!HeapTupleIsValid(tuple))
 		ereport(ERROR,
 				(errcode(ERRCODE_UNDEFINED_COLUMN),
@@ -8604,7 +8607,7 @@ ATExecSetExpression(AlteredTableInfo *tab, Relation rel, const char *colName,
 	 * TODO: This could be done, just need to recheck any constraints
 	 * afterwards.
 	 */
-	if (attgenerated == ATTRIBUTE_GENERATED_VIRTUAL &&
+	if (!IsA(newExpr, GenerationExpr) && attgenerated == ATTRIBUTE_GENERATED_VIRTUAL &&
 		rel->rd_att->constr && rel->rd_att->constr->num_check > 0)
 		ereport(ERROR,
 				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
@@ -8632,7 +8635,72 @@ ATExecSetExpression(AlteredTableInfo *tab, Relation rel, const char *colName,
 
 	rewrite = (attgenerated == ATTRIBUTE_GENERATED_STORED);
 
-	ReleaseSysCache(tuple);
+	/*
+	 * ALTER TABLE ALTER COLUMN SET EXPRESSION STORED/VIRTUAL
+	 * Change the generation expression, may also change the attgenerated.
+	*/
+	if (IsA(newExpr, GenerationExpr))
+	{
+		GenerationExpr   *g = (GenerationExpr *) newExpr;
+
+		raw_default = g->raw_expr;
+
+		if (attgenerated != g->generated_kind)
+		{
+			Relation	pg_attribute;
+
+			attgenerated = g->generated_kind;
+			attTup->attgenerated = g->generated_kind;
+			if (g->generated_kind == ATTRIBUTE_GENERATED_VIRTUAL)
+			{
+				RememberAllDependentForRebuilding(tab, AT_SetExpression, rel, attnum, colName);
+				/*
+				 * see https://git.postgresql.org/cgit/postgresql.git/commit/?id=83ea6c54025bea67bcd4949a6d58d3fc11c3e21b
+				*/
+				if (tab->changedIndexOids != NIL)
+					ereport(ERROR,
+							errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+							errmsg("cannot changed generated column (%s) from STORED to VIRTUAL", colName),
+							errdetail("indexes on virtual generated columns are not supported."));
+
+				if (tab->changedStatisticsOids != NIL)
+					ereport(ERROR,
+							errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+							errmsg("cannot changed generated column (%s) from STORED to VIRTUAL", colName),
+							errdetail("statistics creation on virtual generated columns is not supported."));
+
+				if (attTup->atttypid >= FirstUnpinnedObjectId)
+					ereport(ERROR,
+							errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+							errmsg("cannot changed generated column (%s) from STORED to VIRTUAL", colName),
+							errdetail("Virtual generated columns that make use of user-defined types are not yet supported."));
+
+				if (GetRelationPublications(RelationGetRelid(rel)) != NIL)
+					ereport(ERROR,
+							errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+							errmsg("cannot changed generated column (%s) from STORED to VIRTUAL", colName),
+							errmsg("ALTER TABLE / SET EXPRESSION is not supported for virtual generated columns in tables that are part of a publication"));
+				/*
+				 * change a stored generated column to virtual no need table
+				 * rewrite
+				 */
+				rewrite = false;
+			}
+			else
+				rewrite = true;
+
+			pg_attribute = table_open(AttributeRelationId, RowExclusiveLock);
+			CatalogTupleUpdate(pg_attribute, &tuple->t_self, tuple);
+			InvokeObjectPostAlterHook(RelationRelationId,
+									RelationGetRelid(rel),
+									attnum);
+			table_close(pg_attribute, RowExclusiveLock);
+		}
+	}
+	else
+		raw_default = newExpr;
+
+	heap_freetuple(tuple);
 
 	if (rewrite)
 	{
@@ -8678,7 +8746,7 @@ ATExecSetExpression(AlteredTableInfo *tab, Relation rel, const char *colName,
 	/* Prepare to store the new expression, in the catalogs */
 	rawEnt = (RawColumnDefault *) palloc(sizeof(RawColumnDefault));
 	rawEnt->attnum = attnum;
-	rawEnt->raw_default = newExpr;
+	rawEnt->raw_default = raw_default;
 	rawEnt->generated = attgenerated;
 
 	/* Store the generated expression */
@@ -8759,6 +8827,64 @@ ATPrepDropExpression(Relation rel, AlterTableCmd *cmd, bool recurse, bool recurs
 	}
 }
 
+/*
+ * ALTER TABLE ALTER COLUMN SET EXPRESSION
+ */
+static void
+ATPrepSetExpression(Relation rel, AlterTableCmd *cmd, bool recurse, bool recursing, LOCKMODE lockmode)
+{
+	/*
+	 * Reject ONLY if there are child tables.
+	 */
+	if (!recurse &&
+		!recursing &&
+		IsA(cmd->def, GenerationExpr) &&
+		find_inheritance_children(RelationGetRelid(rel), lockmode))
+	{
+		GenerationExpr *genexp = castNode(GenerationExpr, cmd->def);
+		if (genexp->generated_kind == ATTRIBUTE_GENERATED_STORED)
+			ereport(ERROR,
+					errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					errmsg("ALTER TABLE ... SET EXPRESSION STORED must be applied to child tables too"));
+		else
+			ereport(ERROR,
+					errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					errmsg("ALTER TABLE ... SET EXPRESSION VIRTUAL must be applied to child tables too"));
+	}
+
+	/*
+	 * Cannot drop generation expression from inherited columns.
+	 */
+	if (!recursing && IsA(cmd->def, GenerationExpr))
+	{
+		HeapTuple	tuple;
+		Form_pg_attribute attTup;
+
+		tuple = SearchSysCacheCopyAttName(RelationGetRelid(rel), cmd->name);
+		if (!HeapTupleIsValid(tuple))
+			ereport(ERROR,
+					errcode(ERRCODE_UNDEFINED_COLUMN),
+					errmsg("column \"%s\" of relation \"%s\" does not exist",
+							cmd->name, RelationGetRelationName(rel)));
+
+		attTup = (Form_pg_attribute) GETSTRUCT(tuple);
+
+		if (attTup->attinhcount > 0)
+		{
+			GenerationExpr *genexp = castNode(GenerationExpr, cmd->def);
+
+			if (genexp->generated_kind == ATTRIBUTE_GENERATED_STORED)
+				ereport(ERROR,
+						errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+						errmsg("cannot use ALTER TABLE ... SET EXPRESSION STORED change generation expression from inherited column"));
+			else
+				ereport(ERROR,
+						errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+						errmsg("cannot use ALTER TABLE ... SET EXPRESSION VIRTUAL change generation expression from inherited column"));
+		}
+	}
+}
+
 /*
  * Return the address of the affected column.
  */
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index db43034b9db..d60d75ce690 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -2496,6 +2496,34 @@ alter_table_cmd:
 					n->def = $8;
 					$$ = (Node *) n;
 				}
+			/* ALTER TABLE <name> ALTER [COLUMN] <colname> SET EXPRESSION AS <expr> VIRTUAL */
+			| ALTER opt_column ColId SET EXPRESSION AS '(' a_expr ')' VIRTUAL
+				{
+					AlterTableCmd *n = makeNode(AlterTableCmd);
+					GenerationExpr *g = makeNode(GenerationExpr);
+
+					g->raw_expr = $8;
+					g->generated_kind = ATTRIBUTE_GENERATED_VIRTUAL;
+
+					n->subtype = AT_SetExpression;
+					n->name = $3;
+					n->def = (Node *) g;
+					$$ = (Node *) n;
+				}
+			/* ALTER TABLE <name> ALTER [COLUMN] <colname> SET EXPRESSION AS <expr> STORED */
+			| ALTER opt_column ColId SET EXPRESSION AS '(' a_expr ')' STORED
+				{
+					AlterTableCmd *n = makeNode(AlterTableCmd);
+					GenerationExpr *g = makeNode(GenerationExpr);
+
+					g->raw_expr = $8;
+					g->generated_kind = ATTRIBUTE_GENERATED_STORED;
+
+					n->subtype = AT_SetExpression;
+					n->name = $3;
+					n->def = (Node *) g;
+					$$ = (Node *) n;
+				}
 			/* ALTER TABLE <name> ALTER [COLUMN] <colname> DROP EXPRESSION */
 			| ALTER opt_column ColId DROP EXPRESSION
 				{
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 86a236bd58b..cb0ad67b17a 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2874,6 +2874,14 @@ typedef struct Constraint
 	ParseLoc	location;		/* token location, or -1 if unknown */
 } Constraint;
 
+typedef struct GenerationExpr
+{
+	NodeTag		type;
+	Node	   *raw_expr; 		/* generation expression as a
+								 * untransformed a_expr node */
+	char		generated_kind; /* STORED or VIRTUAL */
+} GenerationExpr;
+
 /* ----------------------
  *		Create/Drop Table Space Statements
  * ----------------------
diff --git a/src/test/regress/expected/fast_default.out b/src/test/regress/expected/fast_default.out
index ccbcdf8403f..a88c3c8b6a6 100644
--- a/src/test/regress/expected/fast_default.out
+++ b/src/test/regress/expected/fast_default.out
@@ -70,6 +70,16 @@ 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
+-- change generated columns from virtual to stored need a rewrite
+ALTER TABLE has_volatile ADD col8 int GENERATED ALWAYS AS (55) VIRTUAL;
+ALTER TABLE has_volatile ALTER COLUMN col8 SET EXPRESSION AS (55) STORED;
+NOTICE:  rewriting table has_volatile for reason 2
+ALTER TABLE has_volatile ALTER COLUMN col8 SET EXPRESSION AS (55) STORED;
+NOTICE:  rewriting table has_volatile for reason 2
+-- change generated columns from stored to virtual no need a rewrite
+ALTER TABLE has_volatile ALTER COLUMN col8 SET EXPRESSION AS (55) VIRTUAL;
+ALTER TABLE has_volatile ALTER COLUMN col8 SET EXPRESSION AS (55) VIRTUAL;
+ALTER TABLE has_volatile ALTER COLUMN col8 SET EXPRESSION AS (55);
 -- 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_stored.out b/src/test/regress/expected/generated_stored.out
index adac2cedfb2..2620bf54266 100644
--- a/src/test/regress/expected/generated_stored.out
+++ b/src/test/regress/expected/generated_stored.out
@@ -2,6 +2,11 @@
 CREATE SCHEMA generated_stored_tests;
 GRANT USAGE ON SCHEMA generated_stored_tests TO PUBLIC;
 SET search_path = generated_stored_tests;
+PREPARE get_generated_info(regclass[], text[]) AS
+SELECT attrelid::regclass as tabname, attname,attnum,attgenerated, atthasdef
+FROM  pg_attribute
+WHERE attrelid = ANY($1) AND attname =ANY($2) AND attnum > 0
+ORDER BY attrelid::regclass::text COLLATE "C", attnum;
 CREATE TABLE gtest0 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (55) STORED);
 CREATE TABLE gtest1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED);
 SELECT table_name, column_name, column_default, is_nullable, is_generated, generation_expression FROM information_schema.columns WHERE table_schema = 'generated_stored_tests' ORDER BY 1, 2;
@@ -553,6 +558,11 @@ SELECT * FROM gtest_varlena ORDER BY a;
 (2 rows)
 
 DROP TABLE gtest_varlena;
+-- varlena types change from stored to virtual
+CREATE TABLE gtest_varlena (a int, b text STORAGE EXTERNAL GENERATED ALWAYS AS (repeat('g', a)) STORED);
+INSERT INTO  gtest_varlena VALUES(2001), (1);
+ALTER TABLE gtest_varlena ALTER COLUMN b SET EXPRESSION AS (repeat('g', a)) VIRTUAL;
+VACUUM(FULL) gtest_varlena; --should ok
 -- composite types
 CREATE TYPE double_int as (a int, b int);
 CREATE TABLE gtest4 (
@@ -567,6 +577,9 @@ SELECT * FROM gtest4;
  6 | (12,18)
 (2 rows)
 
+ALTER TABLE gtest4 ALTER COLUMN b SET EXPRESSION AS ((a * 2, a * 3)) VIRTUAL; --error
+ERROR:  cannot changed generated column (b) from STORED to VIRTUAL
+DETAIL:  Virtual generated columns that make use of user-defined types are not yet supported.
 DROP TABLE gtest4;
 DROP TYPE double_int;
 -- using tableoid is allowed
@@ -660,6 +673,7 @@ ERROR:  check constraint "gtest20a_b_check" of relation "gtest20a" is violated b
 ALTER TABLE gtest20a ADD COLUMN c float8 DEFAULT random() CHECK (b < 50); -- fails on existing row
 ERROR:  check constraint "gtest20a_b_check" of relation "gtest20a" is violated by some row
 ALTER TABLE gtest20a ADD COLUMN c float8 DEFAULT random() CHECK (b < 61); -- ok
+ALTER TABLE gtest20a ALTER COLUMN b SET EXPRESSION AS (a * 2) VIRTUAL; --error
 CREATE TABLE gtest20b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED);
 INSERT INTO gtest20b (a) VALUES (10);
 INSERT INTO gtest20b (a) VALUES (30);
@@ -827,6 +841,9 @@ ALTER TABLE gtest23b ALTER COLUMN b SET EXPRESSION AS (a * 5); -- error
 ERROR:  insert or update on table "gtest23b" violates foreign key constraint "gtest23b_b_fkey"
 DETAIL:  Key (b)=(5) is not present in table "gtest23a".
 ALTER TABLE gtest23b ALTER COLUMN b SET EXPRESSION AS (a * 1); -- ok
+--error, virutal generated column does not support foreign key
+ALTER TABLE gtest23b ALTER COLUMN b SET EXPRESSION AS (a * 11) VIRTUAL;
+ERROR:  foreign key constraints on virtual generated columns are not supported
 DROP TABLE gtest23b;
 DROP TABLE gtest23a;
 CREATE TABLE gtest23p (x int, y int GENERATED ALWAYS AS (x * 2) STORED, PRIMARY KEY (y));
@@ -852,6 +869,10 @@ CREATE TABLE gtest24nn (a int, b gtestdomainnn GENERATED ALWAYS AS (a * 2) STORE
 INSERT INTO gtest24nn (a) VALUES (4);  -- ok
 INSERT INTO gtest24nn (a) VALUES (NULL);  -- error
 ERROR:  value for domain gtestdomainnn violates check constraint "gtestdomainnn_check"
+--error, virutal generated column does not support domains
+ALTER TABLE gtest24nn ALTER COLUMN b SET EXPRESSION AS (a * 2) VIRTUAL;
+ERROR:  cannot changed generated column (b) from STORED to VIRTUAL
+DETAIL:  Virtual generated columns that make use of user-defined types are not yet supported.
 -- typed tables (currently not supported)
 CREATE TYPE gtest_type AS (f1 integer, f2 text, f3 bigint);
 CREATE TABLE gtest28 OF gtest_type (f1 WITH OPTIONS GENERATED ALWAYS AS (f2 *2) STORED);
@@ -974,7 +995,15 @@ SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
 
 -- alter only parent's and one child's generation expression
 ALTER TABLE ONLY gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 4);
+ALTER TABLE ONLY gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 4) STORED; --error
+ERROR:  ALTER TABLE ... SET EXPRESSION STORED must be applied to child tables too
+ALTER TABLE ONLY gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 4) VIRTUAL; --error
+ERROR:  ALTER TABLE ... SET EXPRESSION VIRTUAL must be applied to child tables too
 ALTER TABLE gtest_child ALTER COLUMN f3 SET EXPRESSION AS (f2 * 10);
+ALTER TABLE gtest_child ALTER COLUMN f3 SET EXPRESSION AS (f2 * 10) VIRTUAL; --error
+ERROR:  cannot use ALTER TABLE ... SET EXPRESSION VIRTUAL change generation expression from inherited column
+ALTER TABLE gtest_child ALTER COLUMN f3 SET EXPRESSION AS (f2 * 10) STORED; --error
+ERROR:  cannot use ALTER TABLE ... SET EXPRESSION STORED change generation expression from inherited column
 \d gtest_parent
            Partitioned table "generated_stored_tests.gtest_parent"
  Column |  Type  | Collation | Nullable |               Default               
@@ -1059,6 +1088,26 @@ Partition of: gtest_parent FOR VALUES FROM ('08-01-2016') TO ('09-01-2016')
  f3     | bigint |           |          | generated always as (f2 * 2) stored
 Partition of: gtest_parent FOR VALUES FROM ('09-01-2016') TO ('10-01-2016')
 
+ALTER TABLE gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 3) VIRTUAL;
+EXECUTE get_generated_info('{gtest_parent, gtest_child, gtest_child2, gtest_child3}', '{f3}');
+   tabname    | attname | attnum | attgenerated | atthasdef 
+--------------+---------+--------+--------------+-----------
+ gtest_child  | f3      |      3 | v            | t
+ gtest_child2 | f3      |      3 | v            | t
+ gtest_child3 | f3      |      3 | v            | t
+ gtest_parent | f3      |      3 | v            | t
+(4 rows)
+
+ALTER TABLE gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 2) STORED;
+EXECUTE get_generated_info('{gtest_parent, gtest_child, gtest_child2, gtest_child3}', '{f3}');
+   tabname    | attname | attnum | attgenerated | atthasdef 
+--------------+---------+--------+--------------+-----------
+ gtest_child  | f3      |      3 | s            | t
+ gtest_child2 | f3      |      3 | s            | t
+ gtest_child3 | f3      |      3 | s            | t
+ gtest_parent | f3      |      3 | s            | t
+(4 rows)
+
 SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
    tableoid   |     f1     | f2 | f3 
 --------------+------------+----+----
@@ -1120,6 +1169,9 @@ SELECT * FROM gtest25 ORDER BY a;
 Indexes:
     "gtest25_pkey" PRIMARY KEY, btree (a)
 
+ALTER TABLE gtest25 ADD COLUMN d1 int GENERATED ALWAYS AS (a * 2) VIRTUAL,
+  ALTER COLUMN d1 SET EXPRESSION AS (a * 3) STORED; --erorr, not supported
+ERROR:  column "d1" of relation "gtest25" does not exist
 -- ALTER TABLE ... ALTER COLUMN
 CREATE TABLE gtest27 (
     a int,
@@ -1258,6 +1310,46 @@ ALTER TABLE gtest29 DROP COLUMN a;  -- should not drop b
 --------+---------+-----------+----------+---------
  b      | integer |           |          | 
 
+-- Change the expression and the attgenerated kind
+-- ALTER TABLE ... ALTER COLUMN ... SET EXPRESSION VIRTUAL|STORED
+DROP TABLE gtest29;
+CREATE TABLE gtest29 (a int, b int GENERATED ALWAYS AS (a * 2) STORED);
+INSERT INTO gtest29 (a) VALUES (3), (4);
+ALTER TABLE gtest29 ALTER COLUMN b SET EXPRESSION AS (a * 3) VIRTUAL;
+EXECUTE get_generated_info('{gtest29}', '{b}');
+ tabname | attname | attnum | attgenerated | atthasdef 
+---------+---------+--------+--------------+-----------
+ gtest29 | b       |      2 | v            | t
+(1 row)
+
+SELECT * FROM gtest29;
+ a | b  
+---+----
+ 3 |  9
+ 4 | 12
+(2 rows)
+
+ALTER TABLE gtest29 ALTER COLUMN b SET EXPRESSION AS (a * 3);
+EXECUTE get_generated_info('{gtest29}', '{b}');
+ tabname | attname | attnum | attgenerated | atthasdef 
+---------+---------+--------+--------------+-----------
+ gtest29 | b       |      2 | v            | t
+(1 row)
+
+ALTER TABLE gtest29 ALTER COLUMN b SET EXPRESSION AS (a * 3) STORED;
+SELECT * FROM gtest29;
+ a | b  
+---+----
+ 3 |  9
+ 4 | 12
+(2 rows)
+
+EXECUTE get_generated_info('{gtest29}', '{b}');
+ tabname | attname | attnum | attgenerated | atthasdef 
+---------+---------+--------+--------------+-----------
+ gtest29 | b       |      2 | s            | t
+(1 row)
+
 -- with inheritance
 CREATE TABLE gtest30 (
     a int,
@@ -1308,6 +1400,26 @@ Inherits: gtest30
 
 ALTER TABLE gtest30_1 ALTER COLUMN b DROP EXPRESSION;  -- error
 ERROR:  cannot drop generation expression from inherited column
+ALTER TABLE gtest30_1 ALTER COLUMN b SET EXPRESSION AS (a * 2) STORED;  -- error
+ERROR:  cannot use ALTER TABLE ... SET EXPRESSION STORED change generation expression from inherited column
+ALTER TABLE gtest30_1 ALTER COLUMN b SET EXPRESSION AS (a * 2) VIRTUAL;  -- error
+ERROR:  cannot use ALTER TABLE ... SET EXPRESSION VIRTUAL change generation expression from inherited column
+ALTER TABLE gtest30 ALTER COLUMN b SET EXPRESSION AS (a * 2) STORED;
+EXECUTE get_generated_info('{gtest30, gtest30_1}', '{b}');
+  tabname  | attname | attnum | attgenerated | atthasdef 
+-----------+---------+--------+--------------+-----------
+ gtest30   | b       |      2 | s            | t
+ gtest30_1 | b       |      2 | s            | t
+(2 rows)
+
+ALTER TABLE gtest30 ALTER COLUMN b SET EXPRESSION AS (a * 2) VIRTUAL;
+EXECUTE get_generated_info('{gtest30, gtest30_1}', '{b}');
+  tabname  | attname | attnum | attgenerated | atthasdef 
+-----------+---------+--------+--------------+-----------
+ gtest30   | b       |      2 | v            | t
+ gtest30_1 | b       |      2 | v            | t
+(2 rows)
+
 -- composite type dependencies
 CREATE TABLE gtest31_1 (a int, b text GENERATED ALWAYS AS ('hello') STORED, c text);
 CREATE TABLE gtest31_2 (x int, y gtest31_1);
@@ -1326,6 +1438,13 @@ CREATE INDEX gtest31_2_y_idx ON gtest31_2(((y).b));
 ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS ('hello3');
 ERROR:  cannot alter table "gtest31_1" because column "gtest31_2.y" uses its row type
 DROP TABLE gtest31_1, gtest31_2;
+CREATE TABLE gtest31_1 (a int, b text GENERATED ALWAYS AS ('hello') STORED);
+CREATE STATISTICS gtest31_2_stat ON (b IS NOT NULL) FROM gtest31_1;
+ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS ('hello2') STORED; --ok
+ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS ('hello2') VIRTUAL; --error
+ERROR:  cannot changed generated column (b) from STORED to VIRTUAL
+DETAIL:  statistics creation on virtual generated columns is not supported.
+DROP TABLE gtest31_1;
 -- Check it for a partitioned table, too
 CREATE TABLE gtest31_1 (a int, b text GENERATED ALWAYS AS ('hello') STORED, c text) PARTITION BY LIST (a);
 CREATE TABLE gtest31_2 (x int, y gtest31_1);
@@ -1506,6 +1625,7 @@ CREATE TABLE gtest28b (LIKE gtest28a INCLUDING GENERATED);
  c      | integer |           |          | 
  x      | integer |           |          | generated always as (b * 2) stored
 
+DEALLOCATE get_generated_info;
 -- sanity check of system catalog
 SELECT attrelid, attname, attgenerated FROM pg_attribute WHERE attgenerated NOT IN ('', 's', 'v');
  attrelid | attname | attgenerated 
diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out
index aca6347babe..c7af751bfe8 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -2,6 +2,11 @@
 CREATE SCHEMA generated_virtual_tests;
 GRANT USAGE ON SCHEMA generated_virtual_tests TO PUBLIC;
 SET search_path = generated_virtual_tests;
+PREPARE get_generated_info(regclass[], text[]) AS
+SELECT attrelid::regclass as tabname, attname,attnum,attgenerated, atthasdef
+FROM  pg_attribute
+WHERE attrelid = ANY($1) AND attname =ANY($2) AND attnum > 0
+ORDER BY attrelid::regclass::text COLLATE "C", attnum;
 CREATE TABLE gtest0 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (55) VIRTUAL);
 CREATE TABLE gtest1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
 SELECT table_name, column_name, column_default, is_nullable, is_generated, generation_expression FROM information_schema.columns WHERE table_schema = 'generated_virtual_tests' ORDER BY 1, 2;
@@ -639,6 +644,20 @@ DETAIL:  Column "b" of relation "gtest20" is a virtual generated column.
 ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 3);  -- ok (currently not supported)
 ERROR:  ALTER TABLE / SET EXPRESSION is not supported for virtual generated columns in tables with check constraints
 DETAIL:  Column "b" of relation "gtest20" is a virtual generated column.
+ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 5) STORED;  --error, violates constraint
+ERROR:  check constraint "gtest20_b_check" of relation "gtest20" is violated by some row
+ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 3) STORED;  --ok
+\d gtest20
+                   Table "generated_virtual_tests.gtest20"
+ Column |  Type   | Collation | Nullable |              Default               
+--------+---------+-----------+----------+------------------------------------
+ a      | integer |           | not null | 
+ b      | integer |           |          | generated always as (a * 3) stored
+Indexes:
+    "gtest20_pkey" PRIMARY KEY, btree (a)
+Check constraints:
+    "gtest20_b_check" CHECK (b < 50)
+
 CREATE TABLE gtest20a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
 INSERT INTO gtest20a (a) VALUES (10);
 INSERT INTO gtest20a (a) VALUES (30);
@@ -936,7 +955,15 @@ SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
 
 -- alter only parent's and one child's generation expression
 ALTER TABLE ONLY gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 4);
+ALTER TABLE ONLY gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 4) STORED; --error
+ERROR:  ALTER TABLE ... SET EXPRESSION STORED must be applied to child tables too
+ALTER TABLE ONLY gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 4) VIRTUAL; --error
+ERROR:  ALTER TABLE ... SET EXPRESSION VIRTUAL must be applied to child tables too
 ALTER TABLE gtest_child ALTER COLUMN f3 SET EXPRESSION AS (f2 * 10);
+ALTER TABLE gtest_child ALTER COLUMN f3 SET EXPRESSION AS (f2 * 10) VIRTUAL; --error
+ERROR:  cannot use ALTER TABLE ... SET EXPRESSION VIRTUAL change generation expression from inherited column
+ALTER TABLE gtest_child ALTER COLUMN f3 SET EXPRESSION AS (f2 * 10) STORED; --error
+ERROR:  cannot use ALTER TABLE ... SET EXPRESSION STORED change generation expression from inherited column
 \d gtest_parent
        Partitioned table "generated_virtual_tests.gtest_parent"
  Column |  Type  | Collation | Nullable |           Default            
@@ -1021,6 +1048,26 @@ Partition of: gtest_parent FOR VALUES FROM ('08-01-2016') TO ('09-01-2016')
  f3     | bigint |           |          | generated always as (f2 * 2)
 Partition of: gtest_parent FOR VALUES FROM ('09-01-2016') TO ('10-01-2016')
 
+ALTER TABLE gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 11) STORED;
+EXECUTE get_generated_info('{gtest_parent, gtest_child, gtest_child2, gtest_child3}', '{f3}');
+   tabname    | attname | attnum | attgenerated | atthasdef 
+--------------+---------+--------+--------------+-----------
+ gtest_child  | f3      |      3 | s            | t
+ gtest_child2 | f3      |      3 | s            | t
+ gtest_child3 | f3      |      3 | s            | t
+ gtest_parent | f3      |      3 | s            | t
+(4 rows)
+
+ALTER TABLE gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 2) VIRTUAL;
+EXECUTE get_generated_info('{gtest_parent, gtest_child, gtest_child2, gtest_child3}', '{f3}');
+   tabname    | attname | attnum | attgenerated | atthasdef 
+--------------+---------+--------+--------------+-----------
+ gtest_child  | f3      |      3 | v            | t
+ gtest_child2 | f3      |      3 | v            | t
+ gtest_child3 | f3      |      3 | v            | t
+ gtest_parent | f3      |      3 | v            | t
+(4 rows)
+
 SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
    tableoid   |     f1     | f2 | f3 
 --------------+------------+----+----
@@ -1082,6 +1129,9 @@ SELECT * FROM gtest25 ORDER BY a;
 Indexes:
     "gtest25_pkey" PRIMARY KEY, btree (a)
 
+ALTER TABLE gtest25 ADD COLUMN d1 int GENERATED ALWAYS AS (a * 2) VIRTUAL,
+  ALTER COLUMN d1 SET EXPRESSION AS (a * 3) STORED; --erorr, not supported
+ERROR:  column "d1" of relation "gtest25" does not exist
 -- ALTER TABLE ... ALTER COLUMN
 CREATE TABLE gtest27 (
     a int,
@@ -1223,6 +1273,45 @@ SELECT * FROM gtest29;
  a      | integer |           |          | 
  b      | integer |           |          | generated always as (a * 3)
 
+-- Change the expression and the attgenerated kind
+-- ALTER TABLE ... ALTER COLUMN ... SET EXPRESSION VIRTUAL|STORED
+ALTER TABLE gtest29 ALTER COLUMN b SET EXPRESSION AS (a * 3) STORED;
+EXECUTE get_generated_info('{gtest29}', '{b}');
+ tabname | attname | attnum | attgenerated | atthasdef 
+---------+---------+--------+--------------+-----------
+ gtest29 | b       |      2 | s            | t
+(1 row)
+
+SELECT * FROM gtest29;
+ a | b  
+---+----
+ 3 |  9
+ 4 | 12
+ 5 | 15
+(3 rows)
+
+ALTER TABLE gtest29 ALTER COLUMN b SET EXPRESSION AS (a * 3);
+EXECUTE get_generated_info('{gtest29}', '{b}');
+ tabname | attname | attnum | attgenerated | atthasdef 
+---------+---------+--------+--------------+-----------
+ gtest29 | b       |      2 | s            | t
+(1 row)
+
+ALTER TABLE gtest29 ALTER COLUMN b SET EXPRESSION AS (a * 3) VIRTUAL;
+EXECUTE get_generated_info('{gtest29}', '{b}');
+ tabname | attname | attnum | attgenerated | atthasdef 
+---------+---------+--------+--------------+-----------
+ gtest29 | b       |      2 | v            | t
+(1 row)
+
+SELECT * FROM gtest29;
+ a | b  
+---+----
+ 3 |  9
+ 4 | 12
+ 5 | 15
+(3 rows)
+
 -- check that dependencies between columns have also been removed
 --ALTER TABLE gtest29 DROP COLUMN a;  -- should not drop b
 --\d gtest29
@@ -1278,6 +1367,26 @@ Inherits: gtest30
 
 ALTER TABLE gtest30_1 ALTER COLUMN b DROP EXPRESSION;  -- error
 ERROR:  cannot drop generation expression from inherited column
+ALTER TABLE gtest30_1 ALTER COLUMN b SET EXPRESSION AS (a * 2) STORED;  -- error
+ERROR:  cannot use ALTER TABLE ... SET EXPRESSION STORED change generation expression from inherited column
+ALTER TABLE gtest30_1 ALTER COLUMN b SET EXPRESSION AS (a * 2) VIRTUAL;  -- error
+ERROR:  cannot use ALTER TABLE ... SET EXPRESSION VIRTUAL change generation expression from inherited column
+ALTER TABLE gtest30 ALTER COLUMN b SET EXPRESSION AS (a * 2) STORED;
+EXECUTE get_generated_info('{gtest30, gtest30_1}', '{b}');
+  tabname  | attname | attnum | attgenerated | atthasdef 
+-----------+---------+--------+--------------+-----------
+ gtest30   | b       |      2 | s            | t
+ gtest30_1 | b       |      2 | s            | t
+(2 rows)
+
+ALTER TABLE gtest30 ALTER COLUMN b SET EXPRESSION AS (a * 2) VIRTUAL;
+EXECUTE get_generated_info('{gtest30, gtest30_1}', '{b}');
+  tabname  | attname | attnum | attgenerated | atthasdef 
+-----------+---------+--------+--------------+-----------
+ gtest30   | b       |      2 | v            | t
+ gtest30_1 | b       |      2 | v            | t
+(2 rows)
+
 -- composite type dependencies
 CREATE TABLE gtest31_1 (a int, b text GENERATED ALWAYS AS ('hello') VIRTUAL, c text);
 CREATE TABLE gtest31_2 (x int, y gtest31_1);
@@ -1636,3 +1745,4 @@ select 1 from gtest32 t1 where exists
 (1 row)
 
 drop table gtest32;
+DEALLOCATE get_generated_info;
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index 53268059142..3e3b50beb25 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -796,6 +796,9 @@ DETAIL:  Column list used by the publication does not cover the replica identity
 ALTER PUBLICATION testpub_fortable DROP TABLE testpub_tbl5;
 -- ok: stored generated column "d" can be in the list too
 ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl5 (a, d);
+--error
+ALTER TABLE testpub_tbl5 ALTER COLUMN d SET EXPRESSION AS (a + length(b)) VIRTUAL;
+ERROR:  ALTER TABLE / SET EXPRESSION is not supported for virtual generated columns in tables that are part of a publication
 ALTER PUBLICATION testpub_fortable DROP TABLE testpub_tbl5;
 -- error: virtual generated column "e" can't be in list
 ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl5 (a, e);
diff --git a/src/test/regress/sql/fast_default.sql b/src/test/regress/sql/fast_default.sql
index 068dd0bc8aa..f15520b53ce 100644
--- a/src/test/regress/sql/fast_default.sql
+++ b/src/test/regress/sql/fast_default.sql
@@ -77,6 +77,15 @@ 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;
 
+-- change generated columns from virtual to stored need a rewrite
+ALTER TABLE has_volatile ADD col8 int GENERATED ALWAYS AS (55) VIRTUAL;
+ALTER TABLE has_volatile ALTER COLUMN col8 SET EXPRESSION AS (55) STORED;
+ALTER TABLE has_volatile ALTER COLUMN col8 SET EXPRESSION AS (55) STORED;
+
+-- change generated columns from stored to virtual no need a rewrite
+ALTER TABLE has_volatile ALTER COLUMN col8 SET EXPRESSION AS (55) VIRTUAL;
+ALTER TABLE has_volatile ALTER COLUMN col8 SET EXPRESSION AS (55) VIRTUAL;
+ALTER TABLE has_volatile ALTER COLUMN col8 SET EXPRESSION AS (55);
 
 
 -- Test a large sample of different datatypes
diff --git a/src/test/regress/sql/generated_stored.sql b/src/test/regress/sql/generated_stored.sql
index f56fde8d4e5..58ab45fa050 100644
--- a/src/test/regress/sql/generated_stored.sql
+++ b/src/test/regress/sql/generated_stored.sql
@@ -5,6 +5,12 @@ CREATE SCHEMA generated_stored_tests;
 GRANT USAGE ON SCHEMA generated_stored_tests TO PUBLIC;
 SET search_path = generated_stored_tests;
 
+PREPARE get_generated_info(regclass[], text[]) AS
+SELECT attrelid::regclass as tabname, attname,attnum,attgenerated, atthasdef
+FROM  pg_attribute
+WHERE attrelid = ANY($1) AND attname =ANY($2) AND attnum > 0
+ORDER BY attrelid::regclass::text COLLATE "C", attnum;
+
 CREATE TABLE gtest0 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (55) STORED);
 CREATE TABLE gtest1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED);
 
@@ -247,6 +253,12 @@ INSERT INTO gtest_varlena (a) VALUES(NULL);
 SELECT * FROM gtest_varlena ORDER BY a;
 DROP TABLE gtest_varlena;
 
+-- varlena types change from stored to virtual
+CREATE TABLE gtest_varlena (a int, b text STORAGE EXTERNAL GENERATED ALWAYS AS (repeat('g', a)) STORED);
+INSERT INTO  gtest_varlena VALUES(2001), (1);
+ALTER TABLE gtest_varlena ALTER COLUMN b SET EXPRESSION AS (repeat('g', a)) VIRTUAL;
+VACUUM(FULL) gtest_varlena; --should ok
+
 -- composite types
 CREATE TYPE double_int as (a int, b int);
 CREATE TABLE gtest4 (
@@ -256,6 +268,7 @@ CREATE TABLE gtest4 (
 INSERT INTO gtest4 VALUES (1), (6);
 SELECT * FROM gtest4;
 
+ALTER TABLE gtest4 ALTER COLUMN b SET EXPRESSION AS ((a * 2, a * 3)) VIRTUAL; --error
 DROP TABLE gtest4;
 DROP TYPE double_int;
 
@@ -322,6 +335,7 @@ ALTER TABLE gtest20a ADD CHECK (b < 50);  -- fails on existing row
 -- table rewrite cases
 ALTER TABLE gtest20a ADD COLUMN c float8 DEFAULT random() CHECK (b < 50); -- fails on existing row
 ALTER TABLE gtest20a ADD COLUMN c float8 DEFAULT random() CHECK (b < 61); -- ok
+ALTER TABLE gtest20a ALTER COLUMN b SET EXPRESSION AS (a * 2) VIRTUAL; --error
 
 CREATE TABLE gtest20b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED);
 INSERT INTO gtest20b (a) VALUES (10);
@@ -399,6 +413,8 @@ INSERT INTO gtest23b VALUES (5);  -- error
 ALTER TABLE gtest23b ALTER COLUMN b SET EXPRESSION AS (a * 5); -- error
 ALTER TABLE gtest23b ALTER COLUMN b SET EXPRESSION AS (a * 1); -- ok
 
+--error, virutal generated column does not support foreign key
+ALTER TABLE gtest23b ALTER COLUMN b SET EXPRESSION AS (a * 11) VIRTUAL;
 DROP TABLE gtest23b;
 DROP TABLE gtest23a;
 
@@ -424,6 +440,9 @@ CREATE TABLE gtest24nn (a int, b gtestdomainnn GENERATED ALWAYS AS (a * 2) STORE
 INSERT INTO gtest24nn (a) VALUES (4);  -- ok
 INSERT INTO gtest24nn (a) VALUES (NULL);  -- error
 
+--error, virutal generated column does not support domains
+ALTER TABLE gtest24nn ALTER COLUMN b SET EXPRESSION AS (a * 2) VIRTUAL;
+
 -- typed tables (currently not supported)
 CREATE TYPE gtest_type AS (f1 integer, f2 text, f3 bigint);
 CREATE TABLE gtest28 OF gtest_type (f1 WITH OPTIONS GENERATED ALWAYS AS (f2 *2) STORED);
@@ -482,7 +501,11 @@ SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
 
 -- alter only parent's and one child's generation expression
 ALTER TABLE ONLY gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 4);
+ALTER TABLE ONLY gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 4) STORED; --error
+ALTER TABLE ONLY gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 4) VIRTUAL; --error
 ALTER TABLE gtest_child ALTER COLUMN f3 SET EXPRESSION AS (f2 * 10);
+ALTER TABLE gtest_child ALTER COLUMN f3 SET EXPRESSION AS (f2 * 10) VIRTUAL; --error
+ALTER TABLE gtest_child ALTER COLUMN f3 SET EXPRESSION AS (f2 * 10) STORED; --error
 \d gtest_parent
 \d gtest_child
 \d gtest_child2
@@ -495,6 +518,10 @@ ALTER TABLE gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 2);
 \d gtest_child
 \d gtest_child2
 \d gtest_child3
+ALTER TABLE gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 3) VIRTUAL;
+EXECUTE get_generated_info('{gtest_parent, gtest_child, gtest_child2, gtest_child3}', '{f3}');
+ALTER TABLE gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 2) STORED;
+EXECUTE get_generated_info('{gtest_parent, gtest_child, gtest_child2, gtest_child3}', '{f3}');
 SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
 -- we leave these tables around for purposes of testing dump/reload/upgrade
 
@@ -516,6 +543,8 @@ ALTER TABLE gtest25 ALTER COLUMN d SET DATA TYPE float8,
   ADD COLUMN y float8 GENERATED ALWAYS AS (d * 4) STORED;
 SELECT * FROM gtest25 ORDER BY a;
 \d gtest25
+ALTER TABLE gtest25 ADD COLUMN d1 int GENERATED ALWAYS AS (a * 2) VIRTUAL,
+  ALTER COLUMN d1 SET EXPRESSION AS (a * 3) STORED; --erorr, not supported
 
 -- ALTER TABLE ... ALTER COLUMN
 CREATE TABLE gtest27 (
@@ -571,6 +600,20 @@ SELECT * FROM gtest29;
 ALTER TABLE gtest29 DROP COLUMN a;  -- should not drop b
 \d gtest29
 
+-- Change the expression and the attgenerated kind
+-- ALTER TABLE ... ALTER COLUMN ... SET EXPRESSION VIRTUAL|STORED
+DROP TABLE gtest29;
+CREATE TABLE gtest29 (a int, b int GENERATED ALWAYS AS (a * 2) STORED);
+INSERT INTO gtest29 (a) VALUES (3), (4);
+ALTER TABLE gtest29 ALTER COLUMN b SET EXPRESSION AS (a * 3) VIRTUAL;
+EXECUTE get_generated_info('{gtest29}', '{b}');
+SELECT * FROM gtest29;
+ALTER TABLE gtest29 ALTER COLUMN b SET EXPRESSION AS (a * 3);
+EXECUTE get_generated_info('{gtest29}', '{b}');
+ALTER TABLE gtest29 ALTER COLUMN b SET EXPRESSION AS (a * 3) STORED;
+SELECT * FROM gtest29;
+EXECUTE get_generated_info('{gtest29}', '{b}');
+
 -- with inheritance
 CREATE TABLE gtest30 (
     a int,
@@ -590,6 +633,12 @@ ALTER TABLE ONLY gtest30 ALTER COLUMN b DROP EXPRESSION;  -- error
 \d gtest30
 \d gtest30_1
 ALTER TABLE gtest30_1 ALTER COLUMN b DROP EXPRESSION;  -- error
+ALTER TABLE gtest30_1 ALTER COLUMN b SET EXPRESSION AS (a * 2) STORED;  -- error
+ALTER TABLE gtest30_1 ALTER COLUMN b SET EXPRESSION AS (a * 2) VIRTUAL;  -- error
+ALTER TABLE gtest30 ALTER COLUMN b SET EXPRESSION AS (a * 2) STORED;
+EXECUTE get_generated_info('{gtest30, gtest30_1}', '{b}');
+ALTER TABLE gtest30 ALTER COLUMN b SET EXPRESSION AS (a * 2) VIRTUAL;
+EXECUTE get_generated_info('{gtest30, gtest30_1}', '{b}');
 
 -- composite type dependencies
 CREATE TABLE gtest31_1 (a int, b text GENERATED ALWAYS AS ('hello') STORED, c text);
@@ -610,6 +659,12 @@ ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS ('hello3');
 
 DROP TABLE gtest31_1, gtest31_2;
 
+CREATE TABLE gtest31_1 (a int, b text GENERATED ALWAYS AS ('hello') STORED);
+CREATE STATISTICS gtest31_2_stat ON (b IS NOT NULL) FROM gtest31_1;
+ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS ('hello2') STORED; --ok
+ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS ('hello2') VIRTUAL; --error
+DROP TABLE gtest31_1;
+
 -- Check it for a partitioned table, too
 CREATE TABLE gtest31_1 (a int, b text GENERATED ALWAYS AS ('hello') STORED, c text) PARTITION BY LIST (a);
 CREATE TABLE gtest31_2 (x int, y gtest31_1);
@@ -744,6 +799,7 @@ CREATE TABLE gtest28b (LIKE gtest28a INCLUDING GENERATED);
 
 \d gtest28*
 
+DEALLOCATE get_generated_info;
 
 -- sanity check of system catalog
 SELECT attrelid, attname, attgenerated FROM pg_attribute WHERE attgenerated NOT IN ('', 's', 'v');
diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql
index ba19bc4c701..1b8af8c75ef 100644
--- a/src/test/regress/sql/generated_virtual.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -5,6 +5,12 @@ CREATE SCHEMA generated_virtual_tests;
 GRANT USAGE ON SCHEMA generated_virtual_tests TO PUBLIC;
 SET search_path = generated_virtual_tests;
 
+PREPARE get_generated_info(regclass[], text[]) AS
+SELECT attrelid::regclass as tabname, attname,attnum,attgenerated, atthasdef
+FROM  pg_attribute
+WHERE attrelid = ANY($1) AND attname =ANY($2) AND attnum > 0
+ORDER BY attrelid::regclass::text COLLATE "C", attnum;
+
 CREATE TABLE gtest0 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (55) VIRTUAL);
 CREATE TABLE gtest1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
 
@@ -315,6 +321,9 @@ INSERT INTO gtest20 (a) VALUES (30);  -- violates constraint
 
 ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 100);  -- violates constraint (currently not supported)
 ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 3);  -- ok (currently not supported)
+ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 5) STORED;  --error, violates constraint
+ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 3) STORED;  --ok
+\d gtest20
 
 CREATE TABLE gtest20a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
 INSERT INTO gtest20a (a) VALUES (10);
@@ -525,7 +534,11 @@ SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
 
 -- alter only parent's and one child's generation expression
 ALTER TABLE ONLY gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 4);
+ALTER TABLE ONLY gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 4) STORED; --error
+ALTER TABLE ONLY gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 4) VIRTUAL; --error
 ALTER TABLE gtest_child ALTER COLUMN f3 SET EXPRESSION AS (f2 * 10);
+ALTER TABLE gtest_child ALTER COLUMN f3 SET EXPRESSION AS (f2 * 10) VIRTUAL; --error
+ALTER TABLE gtest_child ALTER COLUMN f3 SET EXPRESSION AS (f2 * 10) STORED; --error
 \d gtest_parent
 \d gtest_child
 \d gtest_child2
@@ -538,6 +551,10 @@ ALTER TABLE gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 2);
 \d gtest_child
 \d gtest_child2
 \d gtest_child3
+ALTER TABLE gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 11) STORED;
+EXECUTE get_generated_info('{gtest_parent, gtest_child, gtest_child2, gtest_child3}', '{f3}');
+ALTER TABLE gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 2) VIRTUAL;
+EXECUTE get_generated_info('{gtest_parent, gtest_child, gtest_child2, gtest_child3}', '{f3}');
 SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
 -- we leave these tables around for purposes of testing dump/reload/upgrade
 
@@ -559,6 +576,8 @@ ALTER TABLE gtest25 ALTER COLUMN d SET DATA TYPE float8,
   ADD COLUMN y float8 GENERATED ALWAYS AS (d * 4) VIRTUAL;
 SELECT * FROM gtest25 ORDER BY a;
 \d gtest25
+ALTER TABLE gtest25 ADD COLUMN d1 int GENERATED ALWAYS AS (a * 2) VIRTUAL,
+  ALTER COLUMN d1 SET EXPRESSION AS (a * 3) STORED; --erorr, not supported
 
 -- ALTER TABLE ... ALTER COLUMN
 CREATE TABLE gtest27 (
@@ -618,6 +637,17 @@ INSERT INTO gtest29 (a, b) VALUES (6, 66);
 SELECT * FROM gtest29;
 \d gtest29
 
+-- Change the expression and the attgenerated kind
+-- ALTER TABLE ... ALTER COLUMN ... SET EXPRESSION VIRTUAL|STORED
+ALTER TABLE gtest29 ALTER COLUMN b SET EXPRESSION AS (a * 3) STORED;
+EXECUTE get_generated_info('{gtest29}', '{b}');
+SELECT * FROM gtest29;
+ALTER TABLE gtest29 ALTER COLUMN b SET EXPRESSION AS (a * 3);
+EXECUTE get_generated_info('{gtest29}', '{b}');
+ALTER TABLE gtest29 ALTER COLUMN b SET EXPRESSION AS (a * 3) VIRTUAL;
+EXECUTE get_generated_info('{gtest29}', '{b}');
+SELECT * FROM gtest29;
+
 -- check that dependencies between columns have also been removed
 --ALTER TABLE gtest29 DROP COLUMN a;  -- should not drop b
 --\d gtest29
@@ -641,6 +671,12 @@ ALTER TABLE ONLY gtest30 ALTER COLUMN b DROP EXPRESSION;  -- error
 \d gtest30
 \d gtest30_1
 ALTER TABLE gtest30_1 ALTER COLUMN b DROP EXPRESSION;  -- error
+ALTER TABLE gtest30_1 ALTER COLUMN b SET EXPRESSION AS (a * 2) STORED;  -- error
+ALTER TABLE gtest30_1 ALTER COLUMN b SET EXPRESSION AS (a * 2) VIRTUAL;  -- error
+ALTER TABLE gtest30 ALTER COLUMN b SET EXPRESSION AS (a * 2) STORED;
+EXECUTE get_generated_info('{gtest30, gtest30_1}', '{b}');
+ALTER TABLE gtest30 ALTER COLUMN b SET EXPRESSION AS (a * 2) VIRTUAL;
+EXECUTE get_generated_info('{gtest30, gtest30_1}', '{b}');
 
 -- composite type dependencies
 CREATE TABLE gtest31_1 (a int, b text GENERATED ALWAYS AS ('hello') VIRTUAL, c text);
@@ -868,3 +904,5 @@ select 1 from gtest32 t1 where exists
   (select 1 from gtest32 t2 where t1.a > t2.a and t2.b = 2);
 
 drop table gtest32;
+
+DEALLOCATE get_generated_info;
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index deddf0da844..aae3de61bc3 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -503,6 +503,8 @@ ALTER PUBLICATION testpub_fortable DROP TABLE testpub_tbl5;
 
 -- ok: stored generated column "d" can be in the list too
 ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl5 (a, d);
+--error
+ALTER TABLE testpub_tbl5 ALTER COLUMN d SET EXPRESSION AS (a + length(b)) VIRTUAL;
 ALTER PUBLICATION testpub_fortable DROP TABLE testpub_tbl5;
 -- error: virtual generated column "e" can't be in list
 ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl5 (a, e);
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index a13e8162890..f79d00789aa 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1036,6 +1036,7 @@ Gene
 GeneratePruningStepsContext
 GenerationBlock
 GenerationContext
+GenerationExpr
 GenerationPointer
 GenericCosts
 GenericXLogPageData
-- 
2.34.1

Reply via email to