hi. I found a use case for this patch: If you want to change a column from stored to virtual, or from virtual to stored, you previously had to drop the column and re-add it as a stored or virtual generated column. This would break all existing dependencies, requiring them to be recreated from scratch. With this patch, the process is much more convenient, as RememberAllDependentForRebuilding is used internally to rebuild all dependencies automatically.
A rebased patch is attached. -- jian https://www.enterprisedb.com/
From e7a3b3c963cf0ce92b97cd61dc435560820ef311 Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Wed, 7 Jan 2026 11:45:37 +0800 Subject: [PATCH v3 1/1] ALTER COLUMN SET EXPRESSION [ VIRTUAL | STORED ] MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit ALTER COLUMN SET EXPRESSION with the VIRTUAL or STORED keyword updates the generation expression and the column’s persistence simultaneously. For converting a stored generated column to a virtual one: previously, this required dropping the column and re-adding it as a virtual generated column, which would break all the existing dependencies. With this patch, the conversion can be done directly, making the process more flexible and preserving dependencies. For converting a virtual generated column to a stored one: for the same reasons as above. This will also materializes the values of the virtual generated column. context: https://git.postgresql.org/cgit/postgresql.git/commit/?id=83ea6c54025bea67bcd4949a6d58d3fc11c3e21b discussion: https://postgr.es/m/CACJufxGXLU400QBBgdoboaza0xK58rQqsCAPrxbAMUmo0f8xCQ@mail.gmail.com --- doc/src/sgml/ref/alter_table.sgml | 22 ++- src/backend/access/heap/heapam_handler.c | 12 ++ src/backend/commands/tablecmds.c | 142 +++++++++++++++++- src/backend/parser/gram.y | 28 ++++ src/include/nodes/parsenodes.h | 13 ++ .../test_ddl_deparse/expected/alter_table.out | 6 + .../test_ddl_deparse/sql/alter_table.sql | 2 + .../test_ddl_deparse/test_ddl_deparse.c | 14 +- src/test/regress/expected/fast_default.out | 14 ++ .../regress/expected/generated_stored.out | 140 ++++++++++++++++- .../regress/expected/generated_virtual.out | 121 +++++++++++++++ src/test/regress/expected/publication.out | 4 + src/test/regress/sql/fast_default.sql | 12 ++ src/test/regress/sql/generated_stored.sql | 68 +++++++++ src/test/regress/sql/generated_virtual.sql | 43 ++++++ src/test/regress/sql/publication.sql | 2 + src/tools/pgindent/typedefs.list | 1 + 17 files changed, 632 insertions(+), 12 deletions(-) diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 1bd479c917a..cfb3a114fa0 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -52,7 +52,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> ] } [...] @@ -272,16 +272,26 @@ 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> ) [ STORED | VIRTUAL ] </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 may optionally + change its storage persistence type. + </para> + <para> + If the column is stored generated column or <literal>STORED</literal> is + specified, existing data is rewritten and all the future changes will apply + the new generation expression. + 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, its statistics + 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 09a456e9966..2004f2c3b29 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 will converts the stored generated column + * into virtual; in that case, the corresponding tuple value must + * 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 f976c0e5c7e..b40019188a5 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); @@ -5032,6 +5033,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 */ @@ -8641,8 +8643,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), @@ -8669,7 +8672,8 @@ 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), @@ -8697,7 +8701,80 @@ ATExecSetExpression(AlteredTableInfo *tab, Relation rel, const char *colName, rewrite = (attgenerated == ATTRIBUTE_GENERATED_STORED); - ReleaseSysCache(tuple); + /* + * For ALTER TABLE ALTER COLUMN SET EXPRESSION STORED/VIRTUAL, newExpr is + * a GenerationExpr node. For ALTER TABLE ALTER COLUMN SET EXPRESSION + * without STORED/VIRTUAL, newExpr is a non-GenerationExpr node; see + * gram.y. + */ + if (!IsA(newExpr, GenerationExpr)) + raw_default = newExpr; + else + { + GenerationExpr *g = castNode(GenerationExpr, newExpr); + + raw_default = g->raw_expr; + + if (attgenerated == g->generated_kind && + g->generated_kind == ATTRIBUTE_GENERATED_VIRTUAL && + rel->rd_att->constr && rel->rd_att->constr->num_check > 0) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("ALTER TABLE / SET EXPRESSION VIRTUAL is not supported for virtual generated columns in tables with check constraints"), + errdetail("Column \"%s\" of relation \"%s\" is a virtual generated column.", + colName, RelationGetRelationName(rel))); + + if (attgenerated != g->generated_kind) + { + Relation pg_attribute = table_open(AttributeRelationId, + RowExclusiveLock); + + attgenerated = g->generated_kind; + attTup->attgenerated = g->generated_kind; + + if (g->generated_kind == ATTRIBUTE_GENERATED_STORED) + rewrite = true; + else + { + char *errdetail_msg = NULL; + + RememberAllDependentForRebuilding(tab, AT_SetExpression, rel, attnum, colName); + + /* + * Virtual generated columns do not currently support indexes, + * statistics, user-defined types, or publications. + */ + if (tab->changedIndexOids != NIL) + errdetail_msg = _("Indexes on virtual generated columns are not supported."); + else if (tab->changedStatisticsOids != NIL) + errdetail_msg = _("Statistics creation on virtual generated columns is not supported."); + else if (attTup->atttypid >= FirstUnpinnedObjectId) + errdetail_msg = _("Virtual generated columns that make use of user-defined types are not yet supported."); + else if (GetRelationPublications(RelationGetRelid(rel)) != NIL) + errdetail_msg = _("Publication on virtual generated columns are not supported."); + + if (errdetail_msg != NULL) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot changed generated column (%s) from %s to %s", colName, "STORED", "VIRTUAL"), + errdetail_internal("%s", errdetail_msg)); + + /* + * change GENERATED COLUMN from stored to virtual do not need + * table rewrite + */ + rewrite = false; + } + + CatalogTupleUpdate(pg_attribute, &tuple->t_self, tuple); + + InvokeObjectPostAlterHook(RelationRelationId, + RelationGetRelid(rel), + attnum); + table_close(pg_attribute, RowExclusiveLock); + } + } + heap_freetuple(tuple); if (rewrite) { @@ -8743,7 +8820,7 @@ ATExecSetExpression(AlteredTableInfo *tab, Relation rel, const char *colName, /* Prepare to store the new expression, in the catalogs */ rawEnt = palloc_object(RawColumnDefault); rawEnt->attnum = attnum; - rawEnt->raw_default = newExpr; + rawEnt->raw_default = raw_default; rawEnt->generated = attgenerated; /* Store the generated expression */ @@ -8824,6 +8901,63 @@ 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) +{ + GenerationExpr *genexpr = NULL; + + if (IsA(cmd->def, GenerationExpr)) + genexpr = castNode(GenerationExpr, cmd->def); + + /* + * Reject ONLY if there are child tables. + */ + if (!recurse && !recursing && + genexpr != NULL && + find_inheritance_children(RelationGetRelid(rel), lockmode)) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("%s must be applied to child tables too", + (genexpr->generated_kind == ATTRIBUTE_GENERATED_STORED) ? + "ALTER TABLE ... SET EXPRESSION STORED" : + "ALTER TABLE ... SET EXPRESSION VIRTUAL"), + errhint("Do not specify the ONLY keyword.")); + + /* + * Cannot change generation expression kind from inherited columns. + */ + if (!recursing && genexpr != NULL) + { + Form_pg_attribute attTup; + + HeapTuple tuple = SearchSysCacheAttName(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) + ereport(ERROR, + errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot use %s change generation expression from inherited column", + (genexpr->generated_kind == ATTRIBUTE_GENERATED_STORED) ? + "ALTER TABLE ... SET EXPRESSION STORED" : + "ALTER TABLE ... SET EXPRESSION VIRTUAL")); + + ReleaseSysCache(tuple); + } +} + /* * Return the address of the affected column. */ diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 713ee5c10a2..883dccd8e3b 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -2576,6 +2576,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 aac4bfc70d9..8a73b76a7ca 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -2906,6 +2906,19 @@ typedef struct Constraint ParseLoc location; /* token location, or -1 if unknown */ } Constraint; +/* ---------------------- + * ALTER COLUMN SET EXPRESSION STORED + * ALTER COLUMN SET EXPRESSION VIRTUAL + * ---------------------- + */ +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 3a2f576f3b6..11d000f2f4f 100644 --- a/src/test/modules/test_ddl_deparse/expected/alter_table.out +++ b/src/test/modules/test_ddl_deparse/expected/alter_table.out @@ -135,6 +135,12 @@ NOTICE: DDL test: type simple, tag CREATE TABLE ALTER TABLE tbl ALTER COLUMN a SET EXPRESSION AS (b::int * 3); 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 0980097048e..6e65605cf8a 100644 --- a/src/test/modules/test_ddl_deparse/sql/alter_table.sql +++ b/src/test/modules/test_ddl_deparse/sql/alter_table.sql @@ -75,6 +75,8 @@ CREATE TABLE tbl ( ); ALTER TABLE tbl ALTER COLUMN a SET EXPRESSION AS (b::int * 3); +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 380b3e754b7..b9b62341ce5 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,20 @@ 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..4fc5986f9c1 100644 --- a/src/test/regress/expected/fast_default.out +++ b/src/test/regress/expected/fast_default.out @@ -70,6 +70,20 @@ 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 +-- Changing a generated column from virtual to stored does not require a table +-- 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 +-- Changing a generated column from stored to virtual does not require a table +-- 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..d8a507668ac 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, 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 AND atthasdef +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,14 @@ 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 +) WITH (autovacuum_enabled = OFF); +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 +587,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 +683,10 @@ 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 +ALTER TABLE gtest20a ALTER COLUMN b SET EXPRESSION AS (a * 3) STORED; -- 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 +854,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 +--virutal generated column does not support foreign key +ALTER TABLE gtest23b ALTER COLUMN b SET EXPRESSION AS (a * 11) VIRTUAL; --error +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 +882,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 +1008,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 +1103,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 * 11) VIRTUAL; +EXECUTE get_generated_info('{gtest_parent, gtest_child, gtest_child2, gtest_child3}', '{f3}'); + table | attname | attnum | attgenerated | pg_get_expr +--------------+---------+--------+--------------+------------- + gtest_child | f3 | 3 | v | (f2 * 11) + gtest_child2 | f3 | 3 | v | (f2 * 11) + gtest_child3 | f3 | 3 | v | (f2 * 11) + gtest_parent | f3 | 3 | v | (f2 * 11) +(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}'); + table | attname | attnum | attgenerated | pg_get_expr +--------------+---------+--------+--------------+------------- + gtest_child | f3 | 3 | s | (f2 * 2) + gtest_child2 | f3 | 3 | s | (f2 * 2) + gtest_child3 | f3 | 3 | s | (f2 * 2) + gtest_parent | f3 | 3 | s | (f2 * 2) +(4 rows) + SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3; tableoid | f1 | f2 | f3 --------------+------------+----+---- @@ -1141,6 +1199,10 @@ SELECT * FROM gtest25 ORDER BY a; Indexes: "gtest25_pkey" PRIMARY KEY, btree (a) +--error: adding a column and setting its generation expression cannot be done in a single 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 +1341,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 | pg_get_expr +---------+---------+--------+--------------+------------- + gtest29 | b | 2 | v | (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 * 4); +EXECUTE get_generated_info('{gtest29}', '{b}'); + table | attname | attnum | attgenerated | pg_get_expr +---------+---------+--------+--------------+------------- + gtest29 | b | 2 | v | (a * 4) +(1 row) + +ALTER TABLE gtest29 ALTER COLUMN b SET EXPRESSION AS (a * 5) STORED; +SELECT * FROM gtest29; + a | b +---+---- + 3 | 15 + 4 | 20 +(2 rows) + +EXECUTE get_generated_info('{gtest29}', '{b}'); + table | attname | attnum | attgenerated | pg_get_expr +---------+---------+--------+--------------+------------- + gtest29 | b | 2 | s | (a * 5) +(1 row) + -- with inheritance CREATE TABLE gtest30 ( a int, @@ -1329,6 +1431,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 | pg_get_expr +-----------+---------+--------+--------------+------------- + gtest30 | b | 2 | s | (a * 2) + gtest30_1 | b | 2 | s | (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 | pg_get_expr +-----------+---------+--------+--------------+------------- + gtest30 | b | 2 | v | (a * 2) + gtest30_1 | b | 2 | v | (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); @@ -1338,15 +1461,30 @@ ERROR: cannot alter table "gtest31_1" because column "gtest31_2.y" uses its row ALTER TABLE gtest31_2 ADD CONSTRAINT cc CHECK ((y).b IS NOT NULL); ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS ('hello1'); ERROR: cannot alter table "gtest31_1" because column "gtest31_2.y" uses its row type +ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS ('hello1') STORED; +ERROR: cannot alter table "gtest31_1" because column "gtest31_2.y" uses its row type +ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS ('hello1') VIRTUAL; ALTER TABLE gtest31_2 DROP CONSTRAINT cc; CREATE STATISTICS gtest31_2_stat ON ((y).b is not null) FROM gtest31_2; ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS ('hello2'); +ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS ('hello2') STORED; ERROR: cannot alter table "gtest31_1" because column "gtest31_2.y" uses its row type +ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS ('hello2') VIRTUAL; DROP STATISTICS gtest31_2_stat; 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_1_stat ON (b is not null) FROM gtest31_1; +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_1_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 249e68be654..68ffc938665 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, 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 AND atthasdef +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,23 @@ 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 * 3) VIRTUAL; -- ok (currently not supported) +ERROR: ALTER TABLE / SET EXPRESSION VIRTUAL 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 +966,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); +-- ALTER COLUMN SET EXPRESSION VIRTUAL/STORED must 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 +1061,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}'); + table | attname | attnum | attgenerated | pg_get_expr +--------------+---------+--------+--------------+------------- + gtest_child | f3 | 3 | s | (f2 * 11) + gtest_child2 | f3 | 3 | s | (f2 * 11) + gtest_child3 | f3 | 3 | s | (f2 * 11) + gtest_parent | f3 | 3 | s | (f2 * 11) +(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}'); + table | attname | attnum | attgenerated | pg_get_expr +--------------+---------+--------+--------------+------------- + gtest_child | f3 | 3 | v | (f2 * 2) + gtest_child2 | f3 | 3 | v | (f2 * 2) + gtest_child3 | f3 | 3 | v | (f2 * 2) + gtest_parent | f3 | 3 | v | (f2 * 2) +(4 rows) + SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3; tableoid | f1 | f2 | f3 --------------+------------+----+---- @@ -1103,6 +1157,10 @@ SELECT * FROM gtest25 ORDER BY a; Indexes: "gtest25_pkey" PRIMARY KEY, btree (a) +--error, 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 +1302,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 * 2) STORED; +EXECUTE get_generated_info('{gtest29}', '{b}'); + table | attname | attnum | attgenerated | pg_get_expr +---------+---------+--------+--------------+------------- + gtest29 | b | 2 | s | (a * 2) +(1 row) + +SELECT * FROM gtest29; + a | b +---+---- + 3 | 6 + 4 | 8 + 5 | 10 +(3 rows) + +ALTER TABLE gtest29 ALTER COLUMN b SET EXPRESSION AS (a * 4); +EXECUTE get_generated_info('{gtest29}', '{b}'); + table | attname | attnum | attgenerated | pg_get_expr +---------+---------+--------+--------------+------------- + gtest29 | b | 2 | s | (a * 4) +(1 row) + +ALTER TABLE gtest29 ALTER COLUMN b SET EXPRESSION AS (a * 3) VIRTUAL; +EXECUTE get_generated_info('{gtest29}', '{b}'); + table | attname | attnum | attgenerated | pg_get_expr +---------+---------+--------+--------------+------------- + gtest29 | b | 2 | v | (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 +1396,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 | pg_get_expr +-----------+---------+--------+--------------+------------- + gtest30 | b | 2 | s | (a * 2) + gtest30_1 | b | 2 | s | (a * 2) +(2 rows) + +ALTER TABLE gtest30 ALTER COLUMN b SET EXPRESSION AS (a * 3) VIRTUAL; +EXECUTE get_generated_info('{gtest30, gtest30_1}', '{b}'); + table | attname | attnum | attgenerated | pg_get_expr +-----------+---------+--------+--------------+------------- + gtest30 | b | 2 | v | (a * 3) + gtest30_1 | b | 2 | v | (a * 3) +(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); @@ -1307,6 +1425,9 @@ ERROR: cannot alter table "gtest31_1" because column "gtest31_2.y" uses its row -- bug #18970 ALTER TABLE gtest31_2 ADD CONSTRAINT cc CHECK ((y).b IS NOT NULL); ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS ('hello1'); +ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS ('hello1') VIRTUAL; +ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS ('hello1') STORED; +ERROR: cannot alter table "gtest31_1" because column "gtest31_2.y" uses its row type ALTER TABLE gtest31_2 DROP CONSTRAINT cc; CREATE STATISTICS gtest31_2_stat ON ((y).b is not null) FROM gtest31_2; ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS ('hello2'); diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out index 7fb49aaf29b..2e7e2bc7af2 100644 --- a/src/test/regress/expected/publication.out +++ b/src/test/regress/expected/publication.out @@ -876,6 +876,10 @@ 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: cannot changed generated column (d) from STORED to VIRTUAL +DETAIL: Publication on virtual generated columns are not supported. 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..6e215550434 100644 --- a/src/test/regress/sql/fast_default.sql +++ b/src/test/regress/sql/fast_default.sql @@ -77,6 +77,18 @@ 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; +-- Changing a generated column from virtual to stored does not require a table +-- 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); + +-- Changing a generated column from stored to virtual does not require a table +-- 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..dfbea1a66b7 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, 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 AND atthasdef +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,15 @@ 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 +) WITH (autovacuum_enabled = OFF); +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 ( @@ -259,6 +275,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 +343,8 @@ 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 +ALTER TABLE gtest20a ALTER COLUMN b SET EXPRESSION AS (a * 3) STORED; -- 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 +422,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 +--virutal generated column does not support foreign key +ALTER TABLE gtest23b ALTER COLUMN b SET EXPRESSION AS (a * 11) VIRTUAL; --error DROP TABLE gtest23b; DROP TABLE gtest23a; @@ -428,6 +449,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 +510,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 +527,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) 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 @@ -523,6 +555,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 +--error: adding a column and setting its generation expression cannot be done in a single 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 +613,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 * 4); +EXECUTE get_generated_info('{gtest29}', '{b}'); +ALTER TABLE gtest29 ALTER COLUMN b SET EXPRESSION AS (a * 5) STORED; +SELECT * FROM gtest29; +EXECUTE get_generated_info('{gtest29}', '{b}'); + -- with inheritance CREATE TABLE gtest30 ( a int, @@ -597,6 +646,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); @@ -606,10 +662,14 @@ ALTER TABLE gtest31_1 ALTER COLUMN b TYPE varchar; -- fails -- bug #18970: these cases are unsupported, but make sure they fail cleanly ALTER TABLE gtest31_2 ADD CONSTRAINT cc CHECK ((y).b IS NOT NULL); ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS ('hello1'); +ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS ('hello1') STORED; +ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS ('hello1') VIRTUAL; ALTER TABLE gtest31_2 DROP CONSTRAINT cc; CREATE STATISTICS gtest31_2_stat ON ((y).b is not null) FROM gtest31_2; ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS ('hello2'); +ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS ('hello2') STORED; +ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS ('hello2') VIRTUAL; DROP STATISTICS gtest31_2_stat; CREATE INDEX gtest31_2_y_idx ON gtest31_2(((y).b)); @@ -617,6 +677,14 @@ 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_1_stat ON (b is not null) FROM gtest31_1; +ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS (a) VIRTUAL; --error +DROP STATISTICS gtest31_1_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 81152b39a79..1782558fd7f 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, 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 AND atthasdef +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,10 @@ 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 * 3) VIRTUAL; -- 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 +541,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); +-- ALTER COLUMN SET EXPRESSION VIRTUAL/STORED must 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 +558,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 @@ -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 +--error, 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 * 2) STORED; +EXECUTE get_generated_info('{gtest29}', '{b}'); +SELECT * FROM gtest29; +ALTER TABLE gtest29 ALTER COLUMN b SET EXPRESSION AS (a * 4); +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 * 3) 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); @@ -657,6 +698,8 @@ ALTER TABLE gtest31_1 ALTER COLUMN b TYPE varchar; -- fails -- bug #18970 ALTER TABLE gtest31_2 ADD CONSTRAINT cc CHECK ((y).b IS NOT NULL); ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS ('hello1'); +ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS ('hello1') VIRTUAL; +ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS ('hello1') STORED; ALTER TABLE gtest31_2 DROP CONSTRAINT cc; CREATE STATISTICS gtest31_2_stat ON ((y).b is not null) FROM gtest31_2; diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql index 85b00bd67c8..0ef62d4ad66 100644 --- a/src/test/regress/sql/publication.sql +++ b/src/test/regress/sql/publication.sql @@ -547,6 +547,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 b9e671fcda8..16348426f5e 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -1049,6 +1049,7 @@ Gene GeneratePruningStepsContext GenerationBlock GenerationContext +GenerationExpr GenerationPointer GenericCosts GenericXLogPageData -- 2.34.1
