On Thu, Aug 28, 2025 at 12:00 PM jian he <[email protected]> wrote: > > hi. > I realized that we can also modify attgenerated when updating the generated > expression using ALTER COLUMN SET EXPRESSION. > so POC attached. >
hi. rebased with minor updates to comments and test cases. -- jian https://www.enterprisedb.com/
From 2ab10d94ca9bbc388bb39b1f77d6ed4ec4f6b237 Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Wed, 12 Nov 2025 15:41:59 +0800 Subject: [PATCH v2 1/1] ALTER COLUMN SET EXPRESSION [GENERATED|STORED] example 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, column b is stored generated column now, 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, column b is virtual generated column now, table rewrite will not happen. context: https://git.postgresql.org/cgit/postgresql.git/commit/?id=83ea6c54025bea67bcd4949a6d58d3fc11c3e21b discussion: https://postgr.es/m/cacjufxgxlu400qbbgdoboaza0xk58rqqscaprxbamumo0f8...@mail.gmail.com --- doc/src/sgml/ref/alter_table.sgml | 21 ++- src/backend/access/heap/heapam_handler.c | 12 ++ src/backend/commands/tablecmds.c | 141 +++++++++++++++++- src/backend/parser/gram.y | 28 ++++ src/include/nodes/parsenodes.h | 8 + .../test_ddl_deparse/expected/alter_table.out | 9 ++ .../test_ddl_deparse/sql/alter_table.sql | 3 + .../test_ddl_deparse/test_ddl_deparse.c | 15 +- src/test/regress/expected/fast_default.out | 12 ++ .../regress/expected/generated_stored.out | 138 +++++++++++++++++ .../regress/expected/generated_virtual.out | 123 +++++++++++++++ src/test/regress/expected/publication.out | 3 + src/test/regress/sql/fast_default.sql | 10 ++ src/test/regress/sql/generated_stored.sql | 62 ++++++++ src/test/regress/sql/generated_virtual.sql | 41 +++++ src/test/regress/sql/publication.sql | 2 + src/tools/pgindent/typedefs.list | 1 + 17 files changed, 618 insertions(+), 11 deletions(-) diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 9d23ad5a0fb..910466430ee 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 bcbac844bb6..3f704ef284e 100644 --- a/src/backend/access/heap/heapam_handler.c +++ b/src/backend/access/heap/heapam_handler.c @@ -2375,6 +2375,10 @@ 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 make a stored generated column to a + * virtual one; in that case, the corresponding tuple value needs to be set + * to NULL. + * * So, we must reconstruct the tuple from component Datums. */ static void @@ -2394,6 +2398,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 23ebaa3f230..bbd6cb15fd2 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -523,6 +523,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); @@ -5021,6 +5022,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 */ @@ -8604,8 +8606,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), @@ -8632,7 +8635,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), @@ -8660,7 +8663,75 @@ 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); + + /* + * Virtual generated columns currently do not support indexes, + * statistics, user-defined types, or publications. + */ + 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) { @@ -8706,7 +8777,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 */ @@ -8787,6 +8858,68 @@ ATPrepDropExpression(Relation rel, AlterTableCmd *cmd, bool recurse, bool recurs } } +/* + * ALTER TABLE ALTER COLUMN SET EXPRESSION [STORED | VIRTUAL] + * + * This needs to recurse into all child tables; otherwise, the parent and child + * may end up with different storage types for the generated column. + */ +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 *genexpr = castNode(GenerationExpr, cmd->def); + if (genexpr->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"), + errhint("Do not specify the ONLY keyword.")); + else + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("ALTER TABLE ... SET EXPRESSION VIRTUAL must be applied to child tables too"), + errhint("Do not specify the ONLY keyword.")); + } + + /* + * Cannot change generation expression kind 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 *genexpr = castNode(GenerationExpr, cmd->def); + + if (genexpr->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 57fe0186547..8a9a0b238ba 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -2524,6 +2524,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 d14294a4ece..4f8a1520394 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -2877,6 +2877,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/modules/test_ddl_deparse/expected/alter_table.out b/src/test/modules/test_ddl_deparse/expected/alter_table.out index 50d0354a341..a5fb293f357 100644 --- a/src/test/modules/test_ddl_deparse/expected/alter_table.out +++ b/src/test/modules/test_ddl_deparse/expected/alter_table.out @@ -122,6 +122,15 @@ CREATE TABLE tbl ( b text ); NOTICE: DDL test: type simple, tag CREATE TABLE +ALTER TABLE tbl ALTER COLUMN a SET EXPRESSION AS (b::int * 2); +NOTICE: DDL test: type alter table, tag ALTER TABLE +NOTICE: subcommand: type SET EXPRESSION desc column a of table tbl +ALTER TABLE tbl ALTER COLUMN a SET EXPRESSION AS (b::int * 2) VIRTUAL; +NOTICE: DDL test: type alter table, tag ALTER TABLE +NOTICE: subcommand: type SET EXPRESSION VIRTUAL desc column a of table tbl +ALTER TABLE tbl ALTER COLUMN a SET EXPRESSION AS (b::int * 2) STORED; +NOTICE: DDL test: type alter table, tag ALTER TABLE +NOTICE: subcommand: type SET EXPRESSION STORED desc column a of table tbl ALTER TABLE tbl ALTER COLUMN a DROP EXPRESSION; NOTICE: DDL test: type alter table, tag ALTER TABLE NOTICE: subcommand: type DROP EXPRESSION desc column a of table tbl diff --git a/src/test/modules/test_ddl_deparse/sql/alter_table.sql b/src/test/modules/test_ddl_deparse/sql/alter_table.sql index 9ad1cf908d4..e29d108a8f7 100644 --- a/src/test/modules/test_ddl_deparse/sql/alter_table.sql +++ b/src/test/modules/test_ddl_deparse/sql/alter_table.sql @@ -67,6 +67,9 @@ CREATE TABLE tbl ( b text ); +ALTER TABLE tbl ALTER COLUMN a SET EXPRESSION AS (b::int * 2); +ALTER TABLE tbl ALTER COLUMN a SET EXPRESSION AS (b::int * 2) VIRTUAL; +ALTER TABLE tbl ALTER COLUMN a SET EXPRESSION AS (b::int * 2) STORED; ALTER TABLE tbl ALTER COLUMN a DROP EXPRESSION; ALTER TABLE tbl ALTER COLUMN b SET COMPRESSION pglz; diff --git a/src/test/modules/test_ddl_deparse/test_ddl_deparse.c b/src/test/modules/test_ddl_deparse/test_ddl_deparse.c index 193669f2bc1..d9dc87136e9 100644 --- a/src/test/modules/test_ddl_deparse/test_ddl_deparse.c +++ b/src/test/modules/test_ddl_deparse/test_ddl_deparse.c @@ -129,8 +129,21 @@ get_altertable_subcmdinfo(PG_FUNCTION_ARGS) strtype = "SET NOT NULL"; break; case AT_SetExpression: - strtype = "SET EXPRESSION"; + { + if (IsA(subcmd->def, GenerationExpr)) + { + GenerationExpr *genexpr = castNode(GenerationExpr, subcmd->def); + + if (genexpr->generated_kind == ATTRIBUTE_GENERATED_STORED) + strtype = "SET EXPRESSION STORED"; + else + strtype = "SET EXPRESSION VIRTUAL"; + } + else + strtype = "SET EXPRESSION"; + break; + } case AT_DropExpression: strtype = "DROP EXPRESSION"; break; diff --git a/src/test/regress/expected/fast_default.out b/src/test/regress/expected/fast_default.out index ccbcdf8403f..4d566550405 100644 --- a/src/test/regress/expected/fast_default.out +++ b/src/test/regress/expected/fast_default.out @@ -70,6 +70,18 @@ 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 +ALTER TABLE has_volatile ALTER COLUMN col8 SET EXPRESSION AS (55); +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 8b7a71d8f0c..ea90d20ab1d 100644 --- a/src/test/regress/expected/generated_stored.out +++ b/src/test/regress/expected/generated_stored.out @@ -2,6 +2,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 table, attname, attnum, attgenerated, atthasdef, pg_get_expr(pd.adbin, pa.attrelid) +FROM pg_attribute pa JOIN pg_attrdef pd +ON pd.adrelid = pa.attrelid +WHERE pa.attrelid = ANY($1) AND pa.attname =ANY($2) AND pa.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; @@ -559,6 +565,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 ( @@ -573,6 +584,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 @@ -666,6 +680,8 @@ 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 * 3) VIRTUAL; -- violates constraint +ERROR: check constraint "gtest20a_b_check" of relation "gtest20a" is violated by some row 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); @@ -833,6 +849,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)); @@ -858,6 +877,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); @@ -980,7 +1003,17 @@ 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 +HINT: Do not specify the ONLY keyword. +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 +HINT: Do not specify the ONLY keyword. 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 @@ -1065,6 +1098,34 @@ 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 * 11) VIRTUAL; +EXECUTE get_generated_info('{gtest_parent, gtest_child, gtest_child2, gtest_child3}', '{f3}'); + table | attname | attnum | attgenerated | atthasdef | pg_get_expr +--------------+---------+--------+--------------+-----------+------------- + gtest_child | f3 | 3 | v | t | (f2 * 11) + gtest_child2 | f3 | 3 | v | t | (f2 * 11) + gtest_child3 | f3 | 3 | v | t | (f2 * 11) + gtest_parent | f3 | 3 | v | t | (f2 * 11) +(4 rows) + +SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3; + tableoid | f1 | f2 | f3 +--------------+------------+----+---- + gtest_child | 07-15-2016 | 2 | 22 + gtest_child2 | 08-15-2016 | 3 | 33 + gtest_child3 | 09-13-2016 | 1 | 11 +(3 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}'); + table | attname | attnum | attgenerated | atthasdef | pg_get_expr +--------------+---------+--------+--------------+-----------+------------- + gtest_child | f3 | 3 | s | t | (f2 * 2) + gtest_child2 | f3 | 3 | s | t | (f2 * 2) + gtest_child3 | f3 | 3 | s | t | (f2 * 2) + gtest_parent | f3 | 3 | s | t | (f2 * 2) +(4 rows) + SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3; tableoid | f1 | f2 | f3 --------------+------------+----+---- @@ -1141,6 +1202,10 @@ SELECT * FROM gtest25 ORDER BY a; Indexes: "gtest25_pkey" PRIMARY KEY, btree (a) +--erorr, can not add and set generation expression in one statement +ALTER TABLE gtest25 ADD COLUMN d1 int GENERATED ALWAYS AS (a * 2) VIRTUAL, + ALTER COLUMN d1 SET EXPRESSION AS (a * 3) STORED; +ERROR: column "d1" of relation "gtest25" does not exist -- ALTER TABLE ... ALTER COLUMN CREATE TABLE gtest27 ( a int, @@ -1279,6 +1344,46 @@ ALTER TABLE gtest29 DROP COLUMN a; -- should not drop b --------+---------+-----------+----------+--------- b | integer | | | +-- ALTER COLUMN ... SET EXPRESSION VIRTUAL +-- ALTER COLUMN ... SET EXPRESSION 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}'); + table | attname | attnum | attgenerated | atthasdef | pg_get_expr +---------+---------+--------+--------------+-----------+------------- + gtest29 | b | 2 | v | t | (a * 3) +(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}'); + table | attname | attnum | attgenerated | atthasdef | pg_get_expr +---------+---------+--------+--------------+-----------+------------- + gtest29 | b | 2 | v | t | (a * 3) +(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}'); + table | attname | attnum | attgenerated | atthasdef | pg_get_expr +---------+---------+--------+--------------+-----------+------------- + gtest29 | b | 2 | s | t | (a * 3) +(1 row) + -- with inheritance CREATE TABLE gtest30 ( a int, @@ -1329,6 +1434,27 @@ 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}'); + table | attname | attnum | attgenerated | atthasdef | pg_get_expr +-----------+---------+--------+--------------+-----------+------------- + gtest30 | b | 2 | s | t | (a * 2) + gtest30_1 | b | 2 | s | t | (a * 2) +(2 rows) + +ALTER TABLE gtest30 ALTER COLUMN b SET EXPRESSION AS (a * 2) VIRTUAL; +EXECUTE get_generated_info('{gtest30, gtest30_1}', '{b}'); + table | attname | attnum | attgenerated | atthasdef | pg_get_expr +-----------+---------+--------+--------------+-----------+------------- + gtest30 | b | 2 | v | t | (a * 2) + gtest30_1 | b | 2 | v | t | (a * 2) +(2 rows) + +DEALLOCATE get_generated_info; -- 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); @@ -1347,6 +1473,18 @@ 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 int GENERATED ALWAYS AS (a) STORED); +CREATE STATISTICS gtest31_2_stat ON (b IS NOT NULL) FROM gtest31_1; +ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS (a) STORED; --ok +ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS (a) VIRTUAL; --error +ERROR: cannot changed generated column (b) from STORED to VIRTUAL +DETAIL: statistics creation on virtual generated columns is not supported. +DROP STATISTICS gtest31_2_stat; +CREATE INDEX ON gtest31_1(b); +ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS (a) VIRTUAL; --error +ERROR: cannot changed generated column (b) from STORED to VIRTUAL +DETAIL: indexes on virtual generated columns are 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); diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out index dde325e46c6..f1960b18a96 100644 --- a/src/test/regress/expected/generated_virtual.out +++ b/src/test/regress/expected/generated_virtual.out @@ -2,6 +2,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 table, attname, attnum, attgenerated, atthasdef, pg_get_expr(pd.adbin, pa.attrelid) +FROM pg_attribute pa JOIN pg_attrdef pd +ON pd.adrelid = pa.attrelid +WHERE pa.attrelid = ANY($1) AND pa.attname =ANY($2) AND pa.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; @@ -645,6 +651,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); @@ -943,6 +963,17 @@ 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 gtest_child ALTER COLUMN f3 SET EXPRESSION AS (f2 * 10); +-- SET EXPRESSION VIRTUAL | STORED need apply to all inherited tables +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 +HINT: Do not specify the ONLY keyword. +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 +HINT: Do not specify the ONLY keyword. +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 @@ -1027,6 +1058,34 @@ 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}'); + table | attname | attnum | attgenerated | atthasdef | pg_get_expr +--------------+---------+--------+--------------+-----------+------------- + gtest_child | f3 | 3 | s | t | (f2 * 11) + gtest_child2 | f3 | 3 | s | t | (f2 * 11) + gtest_child3 | f3 | 3 | s | t | (f2 * 11) + gtest_parent | f3 | 3 | s | t | (f2 * 11) +(4 rows) + +SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3; + tableoid | f1 | f2 | f3 +--------------+------------+----+---- + gtest_child | 07-15-2016 | 2 | 22 + gtest_child2 | 08-15-2016 | 3 | 33 + gtest_child3 | 09-13-2016 | 1 | 11 +(3 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}'); + table | attname | attnum | attgenerated | atthasdef | pg_get_expr +--------------+---------+--------+--------------+-----------+------------- + gtest_child | f3 | 3 | v | t | (f2 * 2) + gtest_child2 | f3 | 3 | v | t | (f2 * 2) + gtest_child3 | f3 | 3 | v | t | (f2 * 2) + gtest_parent | f3 | 3 | v | t | (f2 * 2) +(4 rows) + SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3; tableoid | f1 | f2 | f3 --------------+------------+----+---- @@ -1103,6 +1162,10 @@ SELECT * FROM gtest25 ORDER BY a; Indexes: "gtest25_pkey" PRIMARY KEY, btree (a) +--erorr, can not add and set generation expression in one statement +ALTER TABLE gtest25 ADD COLUMN d1 int GENERATED ALWAYS AS (a * 2) VIRTUAL, + ALTER COLUMN d1 SET EXPRESSION AS (a * 3) STORED; +ERROR: column "d1" of relation "gtest25" does not exist -- ALTER TABLE ... ALTER COLUMN CREATE TABLE gtest27 ( a int, @@ -1244,6 +1307,45 @@ SELECT * FROM gtest29; a | integer | | | b | integer | | | generated always as (a * 3) +-- ALTER COLUMN ... SET EXPRESSION VIRTUAL +-- ALTER COLUMN ... SET EXPRESSION STORED +ALTER TABLE gtest29 ALTER COLUMN b SET EXPRESSION AS (a * 3) STORED; +EXECUTE get_generated_info('{gtest29}', '{b}'); + table | attname | attnum | attgenerated | atthasdef | pg_get_expr +---------+---------+--------+--------------+-----------+------------- + gtest29 | b | 2 | s | t | (a * 3) +(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}'); + table | attname | attnum | attgenerated | atthasdef | pg_get_expr +---------+---------+--------+--------------+-----------+------------- + gtest29 | b | 2 | s | t | (a * 3) +(1 row) + +ALTER TABLE gtest29 ALTER COLUMN b SET EXPRESSION AS (a * 3) VIRTUAL; +EXECUTE get_generated_info('{gtest29}', '{b}'); + table | attname | attnum | attgenerated | atthasdef | pg_get_expr +---------+---------+--------+--------------+-----------+------------- + gtest29 | b | 2 | v | t | (a * 3) +(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 @@ -1299,6 +1401,27 @@ 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}'); + table | attname | attnum | attgenerated | atthasdef | pg_get_expr +-----------+---------+--------+--------------+-----------+------------- + gtest30 | b | 2 | s | t | (a * 2) + gtest30_1 | b | 2 | s | t | (a * 2) +(2 rows) + +ALTER TABLE gtest30 ALTER COLUMN b SET EXPRESSION AS (a * 2) VIRTUAL; +EXECUTE get_generated_info('{gtest30, gtest30_1}', '{b}'); + table | attname | attnum | attgenerated | atthasdef | pg_get_expr +-----------+---------+--------+--------------+-----------+------------- + gtest30 | b | 2 | v | t | (a * 2) + gtest30_1 | b | 2 | v | t | (a * 2) +(2 rows) + +DEALLOCATE get_generated_info; -- 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); diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out index e72d1308967..22161160427 100644 --- a/src/test/regress/expected/publication.out +++ b/src/test/regress/expected/publication.out @@ -882,6 +882,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..2a8ae18d2b8 100644 --- a/src/test/regress/sql/fast_default.sql +++ b/src/test/regress/sql/fast_default.sql @@ -77,6 +77,16 @@ 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; +ALTER TABLE has_volatile ALTER COLUMN col8 SET EXPRESSION AS (55); + +-- 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 2001a47bcc6..960e91b8b66 100644 --- a/src/test/regress/sql/generated_stored.sql +++ b/src/test/regress/sql/generated_stored.sql @@ -5,6 +5,13 @@ 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 table, attname, attnum, attgenerated, atthasdef, pg_get_expr(pd.adbin, pa.attrelid) +FROM pg_attribute pa JOIN pg_attrdef pd +ON pd.adrelid = pa.attrelid +WHERE pa.attrelid = ANY($1) AND pa.attname =ANY($2) AND pa.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); @@ -251,6 +258,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 ( @@ -260,6 +273,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; @@ -326,6 +340,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 * 3) VIRTUAL; -- violates constraint CREATE TABLE gtest20b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED); INSERT INTO gtest20b (a) VALUES (10); @@ -403,6 +418,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; @@ -428,6 +445,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); @@ -486,7 +506,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 @@ -499,6 +523,11 @@ 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) 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; +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 @@ -523,6 +552,9 @@ 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 +--erorr, can not add and set generation expression in one statement +ALTER TABLE gtest25 ADD COLUMN d1 int GENERATED ALWAYS AS (a * 2) VIRTUAL, + ALTER COLUMN d1 SET EXPRESSION AS (a * 3) STORED; -- ALTER TABLE ... ALTER COLUMN CREATE TABLE gtest27 ( @@ -578,6 +610,20 @@ SELECT * FROM gtest29; ALTER TABLE gtest29 DROP COLUMN a; -- should not drop b \d gtest29 +-- ALTER COLUMN ... SET EXPRESSION VIRTUAL +-- ALTER COLUMN ... SET EXPRESSION 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, @@ -597,6 +643,13 @@ 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}'); +DEALLOCATE get_generated_info; -- composite type dependencies CREATE TABLE gtest31_1 (a int, b text GENERATED ALWAYS AS ('hello') STORED, c text); @@ -617,6 +670,15 @@ ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS ('hello3'); DROP TABLE gtest31_1, gtest31_2; +CREATE TABLE gtest31_1 (a int, b int GENERATED ALWAYS AS (a) STORED); +CREATE STATISTICS gtest31_2_stat ON (b IS NOT NULL) FROM gtest31_1; +ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS (a) STORED; --ok +ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS (a) VIRTUAL; --error +DROP STATISTICS gtest31_2_stat; +CREATE INDEX ON gtest31_1(b); +ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS (a) 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); diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql index 2911439776c..f25454821b8 100644 --- a/src/test/regress/sql/generated_virtual.sql +++ b/src/test/regress/sql/generated_virtual.sql @@ -5,6 +5,13 @@ 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 table, attname, attnum, attgenerated, atthasdef, pg_get_expr(pd.adbin, pa.attrelid) +FROM pg_attribute pa JOIN pg_attrdef pd +ON pd.adrelid = pa.attrelid +WHERE pa.attrelid = ANY($1) AND pa.attname =ANY($2) AND pa.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); @@ -319,6 +326,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); @@ -530,6 +540,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 gtest_child ALTER COLUMN f3 SET EXPRESSION AS (f2 * 10); +-- SET EXPRESSION VIRTUAL | STORED need apply to all inherited tables +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) 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 @@ -542,6 +557,11 @@ 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}'); +SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3; +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 @@ -566,6 +586,9 @@ 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 +--erorr, can not add and set generation expression in one statement +ALTER TABLE gtest25 ADD COLUMN d1 int GENERATED ALWAYS AS (a * 2) VIRTUAL, + ALTER COLUMN d1 SET EXPRESSION AS (a * 3) STORED; -- ALTER TABLE ... ALTER COLUMN CREATE TABLE gtest27 ( @@ -625,6 +648,17 @@ INSERT INTO gtest29 (a, b) VALUES (6, 66); SELECT * FROM gtest29; \d gtest29 +-- ALTER COLUMN ... SET EXPRESSION VIRTUAL +-- ALTER COLUMN ... SET EXPRESSION 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 @@ -648,6 +682,13 @@ 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}'); +DEALLOCATE get_generated_info; -- composite type dependencies CREATE TABLE gtest31_1 (a int, b text GENERATED ALWAYS AS ('hello') VIRTUAL, c text); diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql index 00390aecd47..fce608a3e63 100644 --- a/src/test/regress/sql/publication.sql +++ b/src/test/regress/sql/publication.sql @@ -549,6 +549,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 432509277c9..3f77cee4b23 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -1038,6 +1038,7 @@ Gene GeneratePruningStepsContext GenerationBlock GenerationContext +GenerationExpr GenerationPointer GenericCosts GenericXLogPageData -- 2.34.1
