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