On Tue Mar 17, 2026 at 5:31 PM +07, Alberto Piai wrote: > I recently needed to add a stored generated column to a table of > nontrivial size, and realized that currently there is no way to do > that without rewriting the table under an AccessExclusiveLock.
[...] > To this effect, I started prototyping an alter table command We currently have a way to change the expression of generated columns (SET EXPRESSION) and a way to turn a generated column into a regular one (DROP EXPRESSION). The new command would fit nicely and provide the missing piece of functionality: turning an existing column into a generated column. A few thoughts: - since this is specifically useful for *stored* generated columns (to have a way to avoid a rewrite while the table is locked), I would stick to my first proposal and require that STORED is specified explicitly. It would still be possible to remove this requirement and expand to virtual generated columns, should the need for this arise in the future (I just don't see the use case right now). - realizing that this is the opposite operation of DROP EXPRESSION gave me a clue about how to support partitioning/inheritance. AT_DropExpression can be applied only to the whole inheritance tree at once (see 8bf6ec3ba3a44448817af47a080587f3b71bee08 and the associated discussion at https://postgr.es/m/[email protected]), it refuses to be applied to either the parent table ONLY, or directly to partitions. This new command should work the same way. - while researching the above, I stumbled upon a restriction of current DROP EXPRESSION: it doesn't seem to be possible to apply it to partition trees deeper than just one level (parent / child tables). This is probably an oversight, but to avoid feature-creeping this patch, I made the new command act the same way (see test case). I'll try to address this separately. - I added some note in the commit message to clarify why I added the new command to AT_PASS_SET_EXPRESSION, since this wasn't clear enough in my first mail/patch. - I am not particularly attached to the syntax. Alternatives that would come to mind would be: SET GENERATED ALWAYS AS (expr) STORED or to match the two existing commands: ADD EXPRESSION (expr) STORED As I said above, I think the explicit STORED is necessary. It would be nice if the command would make it crystal clear to the user that it implies rewriting the table, i.e. overwriting existing data. (To me, all three forms are clear enough, especially considering that by this point I would have already typed ALTER twice :-)) The attached v2 patches take care of the points above. They are again split in two commits for ease of review. Looking forward to any comment / feedback! Alberto PS: A note about the timing of this mail, as I am just getting acquainted with all of this. I am aware that we're super short of a feature freeze, and this thread is by no means an attempt to push for this to go in now, nor to steal brain bandwidth from more important active threads. I just thought it's OK to put the patches and the mails out there as I make progress, even if it's just to bring this up and revisit at a later point in time. Let me know if instead it would be better to sit on my thoughts until a more appropriate time in the release cycle. -- Alberto Piai Sensational AG Zürich, Switzerland
>From be429e5b7e1ae7a84b564d04e6f126c62176c5cd Mon Sep 17 00:00:00 2001 From: Alberto Piai <[email protected]> Date: Sun, 29 Mar 2026 21:45:44 +0200 Subject: [PATCH v2 1/2] Support changing a column into a stored generated column This adds an ALTER TABLE subcommand to turn a regular column into a stored generated column: ... ALTER COLUMN c ADD GENERATED ALWAYS as (expr) STORED The syntax is chosen to be similar to ... ALTER COLUMN ... ADD GENERATED ... AS IDENTITY with the difference that in this case, since we're dealing with a generated column, only ALWAYS is supported. Additionally, STORED must always be specified. This new operation fits together with SET EXPRESSION and DROP EXPRESSION. Phase 2 happens in the same pass as the former, in order to run the cleanup code in ATPostAlterTypeCleanup, without which for example we would not re-check constraints when rewriting the table. Partitioning/inheritance is supported in the same way as DROP EXPRESSION: it is allowed to change the whole inheritace tree to/from a generated column at once; it is forbidden to change the parent table ONLY and it is forbidden to change a partition directly. See 8bf6ec3ba3a44448817af47a080587f3b71bee08 and the associated discussion. There is one limitation: currently DROP EXPRESSION does not allow to change an inheritance tree of depth > 2. This seems like an oversight, but in order to not feature-creep this commit, this is postponed for later; it should then be fixed for both DROP EXPRESSION and this new command. This is mostly useful as a first step to be able to add a stored generated column without rewriting the table under an exclusive lock. For ease of review, the operation as of this commit always rewrites the contents of the column using the new generated expression. --- src/backend/commands/tablecmds.c | 187 +++++++++++++++++- src/backend/parser/gram.y | 31 +++ src/include/nodes/parsenodes.h | 1 + .../test_ddl_deparse/test_ddl_deparse.c | 3 + src/test/regress/expected/alter_table.out | 126 ++++++++++++ src/test/regress/sql/alter_table.sql | 76 +++++++ 6 files changed, 423 insertions(+), 1 deletion(-) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index c69c12dc014..66622bf4837 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -760,6 +760,14 @@ static void ATExecMergePartitions(List **wqueue, AlteredTableInfo *tab, Relation static void ATExecSplitPartition(List **wqueue, AlteredTableInfo *tab, Relation rel, PartitionCmd *cmd, AlterTableUtilityContext *context); +static void ATPrepAddGeneratedAsExprStored(Relation rel, + AlterTableCmd *cmd, + bool recurse, + bool recursing, LOCKMODE lockmode); +static ObjectAddress ATExecAddGeneratedAsExprStored(AlteredTableInfo *tab, + Relation rel, + const char *colName, + Constraint *def); /* ---------------------------------------------------------------- * DefineRelation @@ -4743,6 +4751,7 @@ AlterTableGetLockLevel(List *cmds) case AT_AddIdentity: case AT_DropIdentity: case AT_SetIdentity: + case AT_AddGeneratedAsExprStored: case AT_SetExpression: case AT_DropExpression: case AT_SetCompression: @@ -5067,6 +5076,13 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode, context); pass = AT_PASS_SET_EXPRESSION; break; + case AT_AddGeneratedAsExprStored: + ATSimplePermissions(cmd->subtype, rel, + ATT_TABLE | ATT_PARTITIONED_TABLE | ATT_FOREIGN_TABLE); + ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode, context); + ATPrepAddGeneratedAsExprStored(rel, cmd, recurse, recursing, lockmode); + pass = AT_PASS_SET_EXPRESSION; + break; case AT_DropExpression: /* ALTER COLUMN DROP EXPRESSION */ ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_PARTITIONED_TABLE | ATT_FOREIGN_TABLE); @@ -5461,6 +5477,12 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, case AT_SetExpression: address = ATExecSetExpression(tab, rel, cmd->name, cmd->def, lockmode); break; + case AT_AddGeneratedAsExprStored: + Assert(IsA(cmd->def, Constraint)); + address = ATExecAddGeneratedAsExprStored(tab, rel, + cmd->name, + (Constraint *) cmd->def); + break; case AT_DropExpression: address = ATExecDropExpression(rel, cmd->name, cmd->missing_ok, lockmode); break; @@ -6667,6 +6689,8 @@ alter_table_type_to_string(AlterTableType cmdtype) return "ALTER COLUMN ... SET NOT NULL"; case AT_SetExpression: return "ALTER COLUMN ... SET EXPRESSION"; + case AT_AddGeneratedAsExprStored: + return "ALTER COLUMN ... ADD GENERATED ALWAYS AS (...) STORED"; case AT_DropExpression: return "ALTER COLUMN ... DROP EXPRESSION"; case AT_SetStatistics: @@ -8823,6 +8847,165 @@ ATExecSetExpression(AlteredTableInfo *tab, Relation rel, const char *colName, return address; } +/* + * Preparation for + * + * ALTER TABLE ALTER COLUMN ADD GENERATED ALWAYS AS expr STORED + * + * Checks whether recursion is allowed, following the same logic as ATPrepDropExpression. + */ +static void +ATPrepAddGeneratedAsExprStored(Relation rel, + AlterTableCmd *cmd, + bool recurse, + bool recursing, LOCKMODE lockmode +) +{ + /* + * Reject ONLY if there are child tables. See ATPrepDropExpression. + */ + if (!recurse && + find_inheritance_children(RelationGetRelid(rel), lockmode)) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("ALTER TABLE / ADD GENERATED ALWAYS AS (expr) STORED must be applied to child tables too"))); + + /* + * Cannot change only inherited columns to be stored generated columns. + */ + if (!recursing) + { + 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) + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot change inherited column to be a stored generated column"))); + } +} + +/* + * ALTER TABLE ALTER COLUMN ADD GENERATED ALWAYS AS expr STORED + */ +static ObjectAddress +ATExecAddGeneratedAsExprStored(AlteredTableInfo *tab, + Relation rel, + const char *colName, + Constraint *def) +{ + HeapTuple tuple; + Form_pg_attribute attTup; + AttrNumber attnum; + ObjectAddress address; + Expr *defval; + NewColumnValue *newval; + RawColumnDefault *rawEnt; + Relation pg_attribute; + + Assert(def->raw_expr != NULL); + Assert(def->cooked_expr == NULL); + Assert(def->generated_when == ATTRIBUTE_IDENTITY_ALWAYS); + Assert(def->generated_kind == ATTRIBUTE_GENERATED_STORED); + + tuple = SearchSysCacheAttName(RelationGetRelid(rel), colName); + if (!HeapTupleIsValid(tuple)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_COLUMN), + errmsg("column \"%s\" of relation \"%s\" does not exist", + colName, RelationGetRelationName(rel)))); + + attTup = (Form_pg_attribute) GETSTRUCT(tuple); + + attnum = attTup->attnum; + if (attnum <= 0) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot alter system column \"%s\"", + colName))); + + if (attTup->attgenerated) + ereport(ERROR, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("column \"%s\" of relation \"%s\" is already a generated column", + colName, RelationGetRelationName(rel)))); + + /* Mark as generated stored in pg_attribute */ + pg_attribute = table_open(AttributeRelationId, RowExclusiveLock); + attTup->attgenerated = ATTRIBUTE_GENERATED_STORED; + CatalogTupleUpdate(pg_attribute, &tuple->t_self, tuple); + table_close(pg_attribute, RowExclusiveLock); + + /* Make above changes visible */ + CommandCounterIncrement(); + + ReleaseSysCache(tuple); + + /* + * Find everything that depends on the column (constraints, indexes, etc), + * and record enough information to let us recreate the objects. + */ + RememberAllDependentForRebuilding(tab, AT_AddGeneratedAsExprStored, + rel, attnum, colName); + + /* + * Remove previous default value, if any, and store the new generator + * expression. + */ + RemoveAttrDefault(RelationGetRelid(rel), attnum, DROP_RESTRICT, + false, false); + + rawEnt = palloc_object(RawColumnDefault); + rawEnt->attnum = attnum; + rawEnt->raw_default = def->raw_expr; + rawEnt->generated = def->generated_kind; + AddRelationNewConstraints(rel, list_make1(rawEnt), NIL, + false, true, false, NULL); + + /* Make above changes visible */ + CommandCounterIncrement(); + + /* + * Clear all the missing values if we're rewriting the table, since this + * renders them pointless. + */ + RelationClearMissing(rel); + + /* Make above changes visible */ + CommandCounterIncrement(); + + /* Drop any pg_statistic entry for the column */ + RemoveStatistics(RelationGetRelid(rel), attnum); + + /* Build a concrete expression for the new default (generated) value */ + defval = (Expr *) build_column_default(rel, attnum); + defval = expression_planner(defval); + + /* Schedule a rewrite */ + newval = palloc0_object(NewColumnValue); + newval->attnum = attnum; + newval->expr = defval; + newval->is_generated = true; + tab->newvals = lappend(tab->newvals, newval); + tab->rewrite |= AT_REWRITE_DEFAULT_VAL; + + InvokeObjectPostAlterHook(RelationRelationId, + RelationGetRelid(rel), attnum); + + ObjectAddressSubSet(address, RelationRelationId, + RelationGetRelid(rel), attnum); + return address; +} + /* * ALTER TABLE ALTER COLUMN DROP EXPRESSION */ @@ -15290,7 +15473,9 @@ RememberAllDependentForRebuilding(AlteredTableInfo *tab, AlterTableType subtype, SysScanDesc scan; HeapTuple depTup; - Assert(subtype == AT_AlterColumnType || subtype == AT_SetExpression); + Assert(subtype == AT_AlterColumnType + || subtype == AT_SetExpression + || subtype == AT_AddGeneratedAsExprStored); depRel = table_open(DependRelationId, RowExclusiveLock); diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 0fea726cdd5..315ee7d94e2 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -2717,6 +2717,37 @@ alter_table_cmd: n->name = $3; n->def = (Node *) c; + $$ = (Node *) n; + } + /* ALTER TABLE <name> ALTER [COLUMN] <colname> ADD GENERATED ALWAYS AS ( <expression> ) STORED */ + | ALTER opt_column ColId ADD_P GENERATED generated_when AS '(' a_expr ')' STORED + { + AlterTableCmd *n = makeNode(AlterTableCmd); + Constraint *c = makeNode(Constraint); + + c->contype = CONSTR_GENERATED; + c->generated_when = $6; + c->raw_expr = $9; + c->cooked_expr = NULL; + c->generated_kind = ATTRIBUTE_GENERATED_STORED; + c->location = @5; + + /* + * Like in the case of ColConstraintElem, we cannot handle + * this in the grammar because IDENTITY allows both ALWAYS + * and BY DEFAULT, while generated columns only allow + * ALWAYS. This would lead to shift/reduce conflicts. + */ + if (c->generated_when != ATTRIBUTE_IDENTITY_ALWAYS) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("for a generated column, GENERATED ALWAYS must be specified"), + parser_errposition(@6))); + + n->subtype = AT_AddGeneratedAsExprStored; + n->name = $3; + n->def = (Node *) c; + $$ = (Node *) n; } /* ALTER TABLE <name> ALTER [COLUMN] <colname> SET <sequence options>/RESET */ diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index df431220ac5..74958ef0dfa 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -2506,6 +2506,7 @@ typedef enum AlterTableType AT_CookedColumnDefault, /* add a pre-cooked column default */ AT_DropNotNull, /* alter column drop not null */ AT_SetNotNull, /* alter column set not null */ + AT_AddGeneratedAsExprStored, /* add generated always as (...) stored */ AT_SetExpression, /* alter column set expression */ AT_DropExpression, /* alter column drop expression */ AT_SetStatistics, /* alter column set statistics */ 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 64a1dfa9f79..3132ceac61f 100644 --- a/src/test/modules/test_ddl_deparse/test_ddl_deparse.c +++ b/src/test/modules/test_ddl_deparse/test_ddl_deparse.c @@ -129,6 +129,9 @@ get_altertable_subcmdinfo(PG_FUNCTION_ARGS) case AT_SetNotNull: strtype = "SET NOT NULL"; break; + case AT_AddGeneratedAsExprStored: + strtype = "ADD GENERATED ALWAYS AS (...) STORED"; + break; case AT_SetExpression: strtype = "SET EXPRESSION"; break; diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out index ccd79dfecc0..2567d918ec3 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -4863,3 +4863,129 @@ drop publication pub1; drop schema alter1 cascade; drop schema alter2 cascade; NOTICE: drop cascades to table alter2.t1 +-- Tests for ALTER COLUMN ... ADD GENERATED ALWAYS as ( expr ) STORED +-- turning a regular column into a stored generated column +create schema testgen; +create table testgen.t1 (a int, b int not null); +insert into testgen.t1 (a, b) + select x, x from generate_series(1, 10) x; +alter table testgen.t1 alter column b + add generated always as (a * 2) stored; +\d+ testgen.t1 + Table "testgen.t1" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +--------+---------+-----------+----------+------------------------------------+---------+--------------+------------- + a | integer | | | | plain | | + b | integer | | not null | generated always as (a * 2) stored | plain | | +Not-null constraints: + "t1_b_not_null" NOT NULL "b" + +select a, b, a * 2 as expected, b = (a * 2) as correct + from testgen.t1 order by a; + a | b | expected | correct +----+----+----------+--------- + 1 | 2 | 2 | t + 2 | 4 | 4 | t + 3 | 6 | 6 | t + 4 | 8 | 8 | t + 5 | 10 | 10 | t + 6 | 12 | 12 | t + 7 | 14 | 14 | t + 8 | 16 | 16 | t + 9 | 18 | 18 | t + 10 | 20 | 20 | t +(10 rows) + +insert into testgen.t1 (a, b) values (10, 20); +ERROR: cannot insert a non-DEFAULT value into column "b" +DETAIL: Column "b" is a generated column. +insert into testgen.t1 (a, b) values (10, 21); +ERROR: cannot insert a non-DEFAULT value into column "b" +DETAIL: Column "b" is a generated column. +drop table testgen.t1; +-- turning a regular column into a stored generated column +-- fails when another constraint conflicts with the new expression +create table testgen.t2 (a int, b int not null); +insert into testgen.t2 (a, b) select x, x * 2 from generate_series(0, 5) x; +alter table testgen.t2 add constraint chk_gen_clause check (b = a * 2); +select pg_relation_filenode('testgen.t2') as t2_filenode_before \gset +alter table testgen.t2 alter column b add generated always as (a * 3) stored; +ERROR: check constraint "chk_gen_clause" of relation "t2" is violated by some row +select pg_relation_filenode('testgen.t2') as t2_filenode_after \gset +select :t2_filenode_before = :t2_filenode_after as did_not_rewrite; + did_not_rewrite +----------------- + t +(1 row) + +\d+ testgen.t2 + Table "testgen.t2" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+--------------+------------- + a | integer | | | | plain | | + b | integer | | not null | | plain | | +Check constraints: + "chk_gen_clause" CHECK (b = (a * 2)) +Not-null constraints: + "t2_b_not_null" NOT NULL "b" + +drop table testgen.t2; +-- rewrite an indexed column +create table testgen.t3 (a int, b int); +create index idx_b on testgen.t3 (b); +insert into testgen.t3 (a, b) select x, x from generate_series(1, 10) x; +select pg_relation_filenode('testgen.idx_b') as idx_filenode_before \gset +alter table testgen.t3 alter column b add generated always as (a * 2) stored; +select pg_relation_filenode('testgen.idx_b') as idx_filenode_after \gset +select :idx_filenode_before != :idx_filenode_after as did_rewrite_idx; + did_rewrite_idx +----------------- + t +(1 row) + +-- tests for invalid invocations +alter table doesnotexist alter column foo + add generated always as (bar * 2) stored; +ERROR: relation "doesnotexist" does not exist +create table testgen.t1 (a int); +alter table testgen.t1 alter column doesnotexist + add generated always as (bar * 2) stored; +ERROR: column "doesnotexist" of relation "t1" does not exist +alter table testgen.t1 add column b int; +alter table testgen.t1 alter column b + add generated always as (doesnotexist * 2) stored; +ERROR: column "doesnotexist" does not exist +-- invalid: only supports ALWAYS +alter table testgen.t1 alter column b + add generated by default as (a * 2) stored; +ERROR: for a generated column, GENERATED ALWAYS must be specified +LINE 2: add generated by default as (a * 2) stored; + ^ +-- invalid: only supports STORED +alter table testgen.t1 alter column b add generated always as (a * 2); +ERROR: syntax error at or near ";" +LINE 1: ...e testgen.t1 alter column b add generated always as (a * 2); + ^ +alter table testgen.t1 alter column b add generated always as (a * 2) virtual; +ERROR: syntax error at or near "virtual" +LINE 1: ...n.t1 alter column b add generated always as (a * 2) virtual; + ^ +-- invalid: b is already a generated column +create table testgen.t2 (a int, b int generated always as (a * 2) stored); +alter table testgen.t2 alter column b add generated always as (a * 2) stored; +ERROR: column "b" of relation "t2" is already a generated column +drop table testgen.t2; +create table testgen.t3 (a int, b int); +-- invalid: expr must be immutable +alter table testgen.t3 alter column b + add generated always as (a + random()) stored; +ERROR: generation expression is not immutable +-- invalid: expr cannot use subselects +alter table testgen.t3 alter column b + add generated always as (a + (select 1)) stored; +ERROR: cannot use subquery in column generation expression +drop table testgen.t3; +drop schema testgen cascade; +NOTICE: drop cascades to 4 other objects +DETAIL: drop cascades to table testgen.t3 +drop cascades to table testgen.t1 diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql index f5f13bbd3e7..76187083289 100644 --- a/src/test/regress/sql/alter_table.sql +++ b/src/test/regress/sql/alter_table.sql @@ -3159,3 +3159,79 @@ alter table alter1.t1 set schema alter2; drop publication pub1; drop schema alter1 cascade; drop schema alter2 cascade; + +-- Tests for ALTER COLUMN ... ADD GENERATED ALWAYS as ( expr ) STORED +-- turning a regular column into a stored generated column +create schema testgen; + +create table testgen.t1 (a int, b int not null); +insert into testgen.t1 (a, b) + select x, x from generate_series(1, 10) x; +alter table testgen.t1 alter column b + add generated always as (a * 2) stored; +\d+ testgen.t1 +select a, b, a * 2 as expected, b = (a * 2) as correct + from testgen.t1 order by a; +insert into testgen.t1 (a, b) values (10, 20); +insert into testgen.t1 (a, b) values (10, 21); +drop table testgen.t1; + +-- turning a regular column into a stored generated column +-- fails when another constraint conflicts with the new expression +create table testgen.t2 (a int, b int not null); +insert into testgen.t2 (a, b) select x, x * 2 from generate_series(0, 5) x; +alter table testgen.t2 add constraint chk_gen_clause check (b = a * 2); +select pg_relation_filenode('testgen.t2') as t2_filenode_before \gset +alter table testgen.t2 alter column b add generated always as (a * 3) stored; +select pg_relation_filenode('testgen.t2') as t2_filenode_after \gset +select :t2_filenode_before = :t2_filenode_after as did_not_rewrite; +\d+ testgen.t2 +drop table testgen.t2; + +-- rewrite an indexed column +create table testgen.t3 (a int, b int); +create index idx_b on testgen.t3 (b); +insert into testgen.t3 (a, b) select x, x from generate_series(1, 10) x; +select pg_relation_filenode('testgen.idx_b') as idx_filenode_before \gset +alter table testgen.t3 alter column b add generated always as (a * 2) stored; +select pg_relation_filenode('testgen.idx_b') as idx_filenode_after \gset +select :idx_filenode_before != :idx_filenode_after as did_rewrite_idx; +drop table testgen.t3; + +-- tests for invalid invocations +alter table doesnotexist alter column foo + add generated always as (bar * 2) stored; + +create table testgen.t1 (a int); + +alter table testgen.t1 alter column doesnotexist + add generated always as (bar * 2) stored; + +alter table testgen.t1 add column b int; + +alter table testgen.t1 alter column b + add generated always as (doesnotexist * 2) stored; + +-- invalid: only supports ALWAYS +alter table testgen.t1 alter column b + add generated by default as (a * 2) stored; + +-- invalid: only supports STORED +alter table testgen.t1 alter column b add generated always as (a * 2); +alter table testgen.t1 alter column b add generated always as (a * 2) virtual; + +-- invalid: b is already a generated column +create table testgen.t2 (a int, b int generated always as (a * 2) stored); +alter table testgen.t2 alter column b add generated always as (a * 2) stored; +drop table testgen.t2; + +create table testgen.t3 (a int, b int); +-- invalid: expr must be immutable +alter table testgen.t3 alter column b + add generated always as (a + random()) stored; +-- invalid: expr cannot use subselects +alter table testgen.t3 alter column b + add generated always as (a + (select 1)) stored; +drop table testgen.t3; + +drop schema testgen cascade; base-commit: 01d58d7e3ff3f7482ff478cb4a49c48aad276138 -- 2.47.0
>From ada2e9aed8f03a177ec63c31e25fc474a281ee41 Mon Sep 17 00:00:00 2001 From: Alberto Piai <[email protected]> Date: Sun, 29 Mar 2026 21:45:50 +0200 Subject: [PATCH v2 2/2] Try to avoid a rewrite when adding a stored generated column This builds upon basic support for ... ALTER COLUMN ... ADD GENERATED ALWAYS AS (expr) STORED If we can find a constraint which proves that the given column is already always equal to the new generated column expression, skip the expensive rewrite of the table. The check constraint must use an equality operator which is mergejoinable, and the expression must match exactly the generated column's default expression. --- src/backend/catalog/pg_constraint.c | 75 +++++++ src/backend/commands/tablecmds.c | 46 +++-- src/include/catalog/pg_constraint.h | 2 + src/test/regress/expected/alter_table.out | 232 +++++++++++++++++++++- src/test/regress/sql/alter_table.sql | 147 ++++++++++++++ 5 files changed, 481 insertions(+), 21 deletions(-) diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c index b12765ae691..cfb0a0068cf 100644 --- a/src/backend/catalog/pg_constraint.c +++ b/src/backend/catalog/pg_constraint.c @@ -17,6 +17,7 @@ #include "access/genam.h" #include "access/gist.h" #include "access/htup_details.h" +#include "access/relation.h" #include "access/sysattr.h" #include "access/table.h" #include "catalog/catalog.h" @@ -29,6 +30,8 @@ #include "catalog/pg_type.h" #include "commands/defrem.h" #include "common/int.h" +#include "nodes/nodeFuncs.h" +#include "parser/parse_relation.h" #include "utils/array.h" #include "utils/builtins.h" #include "utils/fmgroids.h" @@ -694,6 +697,78 @@ findDomainNotNullConstraint(Oid typid) return retval; } +/* + * Given a relation, an attnum and a (cooked) expression, this returns true if + * it finds a CHECK constraint which proves that the given column is equal to + * the expression. + * + * The constraint must use a mergejoinable operator for the type of the column, + * a concept used by the planner as well to infer equivalence classes on the + * terms in a query (see op_mergejoinable()). + * + * The expressions are compared structurally, so they must match exactly for + * this check to succeed. + */ +bool +findStructuralCheckConstraintOnAttr(Oid relid, AttrNumber attnum, + const Node *target_expr) +{ + Relation pg_constraint; + HeapTuple conTup; + SysScanDesc scan; + ScanKeyData key; + bool found = false; + + pg_constraint = table_open(ConstraintRelationId, AccessShareLock); + ScanKeyInit(&key, + Anum_pg_constraint_conrelid, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(relid)); + scan = systable_beginscan(pg_constraint, ConstraintRelidTypidNameIndexId, + true, NULL, 1, &key); + + while (HeapTupleIsValid(conTup = systable_getnext(scan))) + { + Form_pg_constraint con = GETSTRUCT(conTup); + char *conbin; + Datum val; + Node *conexpr; + + if (con->contype != CONSTRAINT_CHECK) + continue; + if (!con->convalidated) + continue; + + val = SysCacheGetAttrNotNull(CONSTROID, conTup, + Anum_pg_constraint_conbin); + conbin = TextDatumGetCString(val); + conexpr = stringToNode(conbin); + + if (IsA(conexpr, OpExpr)) + { + OpExpr *op = (OpExpr *) conexpr; + + if (list_length(op->args) == 2 && IsA(linitial(op->args), Var)) + { + Var *var = linitial(op->args); + + if (var->varattno == attnum && + op_mergejoinable(op->opno, exprType((Node *) var)) && + equal(lsecond(op->args), target_expr)) + { + found = true; + break; + } + } + } + } + + systable_endscan(scan); + table_close(pg_constraint, AccessShareLock); + + return found; +} + /* * Given a pg_constraint tuple for a not-null constraint, return the column * number it is for. diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 66622bf4837..1d98287af62 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -8911,6 +8911,7 @@ ATExecAddGeneratedAsExprStored(AlteredTableInfo *tab, NewColumnValue *newval; RawColumnDefault *rawEnt; Relation pg_attribute; + bool rewrite; Assert(def->raw_expr != NULL); Assert(def->cooked_expr == NULL); @@ -8974,29 +8975,36 @@ ATExecAddGeneratedAsExprStored(AlteredTableInfo *tab, /* Make above changes visible */ CommandCounterIncrement(); - /* - * Clear all the missing values if we're rewriting the table, since this - * renders them pointless. - */ - RelationClearMissing(rel); - - /* Make above changes visible */ - CommandCounterIncrement(); - - /* Drop any pg_statistic entry for the column */ - RemoveStatistics(RelationGetRelid(rel), attnum); - /* Build a concrete expression for the new default (generated) value */ defval = (Expr *) build_column_default(rel, attnum); defval = expression_planner(defval); - /* Schedule a rewrite */ - newval = palloc0_object(NewColumnValue); - newval->attnum = attnum; - newval->expr = defval; - newval->is_generated = true; - tab->newvals = lappend(tab->newvals, newval); - tab->rewrite |= AT_REWRITE_DEFAULT_VAL; + rewrite = !findStructuralCheckConstraintOnAttr(RelationGetRelid(rel), + attnum, + (Node *) defval); + + if (rewrite) + { + /* + * Clear all the missing values if we're rewriting the table, since + * this renders them pointless. + */ + RelationClearMissing(rel); + + /* Make above changes visible */ + CommandCounterIncrement(); + + /* Drop any pg_statistic entry for the column */ + RemoveStatistics(RelationGetRelid(rel), attnum); + + /* Schedule a rewrite */ + newval = palloc0_object(NewColumnValue); + newval->attnum = attnum; + newval->expr = defval; + newval->is_generated = true; + tab->newvals = lappend(tab->newvals, newval); + tab->rewrite |= AT_REWRITE_DEFAULT_VAL; + } InvokeObjectPostAlterHook(RelationRelationId, RelationGetRelid(rel), attnum); diff --git a/src/include/catalog/pg_constraint.h b/src/include/catalog/pg_constraint.h index 1b7fedf1750..7ac9e00c28b 100644 --- a/src/include/catalog/pg_constraint.h +++ b/src/include/catalog/pg_constraint.h @@ -266,6 +266,8 @@ extern char *ChooseConstraintName(const char *name1, const char *name2, extern HeapTuple findNotNullConstraintAttnum(Oid relid, AttrNumber attnum); extern HeapTuple findNotNullConstraint(Oid relid, const char *colname); extern HeapTuple findDomainNotNullConstraint(Oid typid); +extern bool findStructuralCheckConstraintOnAttr(Oid relid, AttrNumber attnum, + const Node *target_expr); extern AttrNumber extractNotNullColumn(HeapTuple constrTup); extern bool AdjustNotNullInheritance(Oid relid, AttrNumber attnum, const char *new_conname, bool is_local, bool is_no_inherit, bool is_notvalid); diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out index 2567d918ec3..b3ee82f401e 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -4943,6 +4943,233 @@ select :idx_filenode_before != :idx_filenode_after as did_rewrite_idx; t (1 row) +drop table testgen.t3; +-- turning a regular column into a stored generated column +-- without rewriting the table (when a check constraint proves it isn't needed) +create table testgen.t4 (a int, b int not null); +insert into testgen.t4 (a, b) select x, x * 2 from generate_series(0, 5) x; +alter table testgen.t4 add constraint chk_gen_clause check (b = a * 2); +select pg_relation_filenode('testgen.t4') as t4_filenode_before \gset +alter table testgen.t4 alter column b add generated always as (a * 2) stored; +select pg_relation_filenode('testgen.t4') as t4_filenode_after \gset +select :t4_filenode_before = :t4_filenode_after as did_skip_rewrite; + did_skip_rewrite +------------------ + t +(1 row) + +\d+ testgen.t4 + Table "testgen.t4" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +--------+---------+-----------+----------+------------------------------------+---------+--------------+------------- + a | integer | | | | plain | | + b | integer | | not null | generated always as (a * 2) stored | plain | | +Check constraints: + "chk_gen_clause" CHECK (b = (a * 2)) +Not-null constraints: + "t4_b_not_null" NOT NULL "b" + +drop table testgen.t4; +-- turning a regular column into a stored generated column +-- same as the previous case, but a rewrite happens since the constraint is not +-- valid +create table testgen.t4 (a int, b int not null); +insert into testgen.t4 (a, b) select x, x * 2 from generate_series(0, 5) x; +alter table testgen.t4 add constraint chk_gen_clause check (b = a * 2) not valid; +select pg_relation_filenode('testgen.t4') as t4_filenode_before \gset +alter table testgen.t4 alter column b add generated always as (a * 2) stored; +select pg_relation_filenode('testgen.t4') as t4_filenode_after \gset +select :t4_filenode_before != :t4_filenode_after as did_rewrite; + did_rewrite +------------- + t +(1 row) + +\d+ testgen.t4 + Table "testgen.t4" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +--------+---------+-----------+----------+------------------------------------+---------+--------------+------------- + a | integer | | | | plain | | + b | integer | | not null | generated always as (a * 2) stored | plain | | +Check constraints: + "chk_gen_clause" CHECK (b = (a * 2)) NOT VALID +Not-null constraints: + "t4_b_not_null" NOT NULL "b" + +drop table testgen.t4; +-- turning a regular column into a stored generated column +-- same as the previous case, but a rewrite happens since the constraint +-- operator is not mergejoinable +create table testgen.t4 (a int, b int not null); +insert into testgen.t4 (a, b) select x, x * 2 from generate_series(0, 5) x; +alter table testgen.t4 add constraint chk_gen_clause check (b >= a * 2); +select pg_relation_filenode('testgen.t4') as t4_filenode_before \gset +alter table testgen.t4 alter column b add generated always as (a * 3) stored; +select pg_relation_filenode('testgen.t4') as t4_filenode_after \gset +select :t4_filenode_before != :t4_filenode_after as did_rewrite; + did_rewrite +------------- + t +(1 row) + +\d+ testgen.t4 + Table "testgen.t4" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +--------+---------+-----------+----------+------------------------------------+---------+--------------+------------- + a | integer | | | | plain | | + b | integer | | not null | generated always as (a * 3) stored | plain | | +Check constraints: + "chk_gen_clause" CHECK (b >= (a * 2)) +Not-null constraints: + "t4_b_not_null" NOT NULL "b" + +drop table testgen.t4; +-- test the whole process for adding a stored generated column without +-- long-lived exclusive locks +create table testgen.t5 (a int); +select pg_relation_filenode('testgen.t5') as t5_filenode_before \gset +insert into testgen.t5 select x from generate_series(1, 5) x; +alter table testgen.t5 add column b int; +-- take care of new and updated columns +create function testgen.gen () returns trigger language plpgsql as $$ +begin + new.b = new.a * 2; return new; +end +$$; +create trigger testgen_gen + before insert or update on testgen.t5 + for each row execute function testgen.gen(); +-- add the constraint as not valid: enforced only for new and updated rows +begin; +alter table testgen.t5 + add constraint chk_gen_clause check (b = a * 2) not valid; +select locktype, mode from pg_locks + where relation = 'testgen.t5'::regclass and granted; + locktype | mode +----------+--------------------- + relation | AccessExclusiveLock +(1 row) + +commit; +insert into testgen.t5 (a) values (100), (200), (300); +-- backfill existing rows at the appropriate pace +update testgen.t5 set b = a * 2 where b is null; +-- validate: this scans the table, but without an exclusive lock +begin; +alter table testgen.t5 validate constraint chk_gen_clause; +select locktype, mode from pg_locks + where relation = 'testgen.t5'::regclass and granted; + locktype | mode +----------+-------------------------- + relation | ShareUpdateExclusiveLock +(1 row) + +commit; +-- now the schema update, which skips the rewrite because of the check +begin; +drop trigger testgen_gen on testgen.t5; +alter table testgen.t5 alter column b + add generated always as (a * 2) stored; +select locktype, mode from pg_locks +where relation = 'testgen.t5'::regclass and granted; + locktype | mode +----------+--------------------- + relation | AccessShareLock + relation | AccessExclusiveLock +(2 rows) + +commit; +select pg_relation_filenode('testgen.t5') as t5_filenode_after \gset +select :t5_filenode_before = :t5_filenode_after as did_skip_rewrite; + did_skip_rewrite +------------------ + t +(1 row) + +\d+ testgen.t5 + Table "testgen.t5" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +--------+---------+-----------+----------+------------------------------------+---------+--------------+------------- + a | integer | | | | plain | | + b | integer | | | generated always as (a * 2) stored | plain | | +Check constraints: + "chk_gen_clause" CHECK (b = (a * 2)) + +-- test support for partitioned tables and inheritance +create table testgen.tpart (a int, b int) partition by hash (a); +create table testgen.tpart_p1 partition of testgen.tpart + for values with (modulus 2, remainder 0); +create table testgen.tpart_p2 partition of testgen.tpart + for values with (modulus 2, remainder 1); +insert into testgen.tpart (a, b) select x, x from generate_series(1, 5) x; +-- altering the parent table, recursing +begin; +alter table testgen.tpart alter column b + add generated always as (a * 2) stored; +-- expected: all the partitions have been rewritten +select a, b, a * 2 as expected, b = (a * 2) as correct + from testgen.tpart_p1 order by a; + a | b | expected | correct +---+---+----------+--------- + 1 | 2 | 2 | t + 2 | 4 | 4 | t +(2 rows) + +select a, b, a * 2 as expected, b = (a * 2) as correct + from testgen.tpart_p2 order by a; + a | b | expected | correct +---+----+----------+--------- + 3 | 6 | 6 | t + 4 | 8 | 8 | t + 5 | 10 | 10 | t +(3 rows) + +rollback; +-- altering a single partition is not allowed +begin; +-- expected: error +alter table testgen.tpart_p1 alter column b + add generated always as (a * 2) stored; +ERROR: cannot change inherited column to be a stored generated column +rollback; +-- altering only the parent table is not allowed +begin; +-- expected: error +alter table only testgen.tpart alter column b + add generated always as (a * 2) stored; +ERROR: ALTER TABLE / ADD GENERATED ALWAYS AS (expr) STORED must be applied to child tables too +rollback; +drop table testgen.tpart; +-- subpartitions +create table testgen.tpart (a int, b int, c int) + partition by hash (a); +create table testgen.tpart_p1 partition of testgen.tpart + for values with (modulus 2, remainder 0) + partition by hash (b); +create table testgen.tpart_p1_1 partition of testgen.tpart_p1 + for values with (modulus 2, remainder 0); +create table testgen.tpart_p1_2 partition of testgen.tpart_p1 + for values with (modulus 2, remainder 1); +create table testgen.tpart_p2 partition of testgen.tpart + for values with (modulus 2, remainder 1) + partition by hash (b); +create table testgen.tpart_p2_1 partition of testgen.tpart_p2 + for values with (modulus 2, remainder 0); +create table testgen.tpart_p2_2 partition of testgen.tpart_p2 + for values with (modulus 2, remainder 1); +insert into testgen.tpart (a, b) + select x, y + from generate_series(1, 5) x + cross join generate_series(1, 5) y; +-- currently, it is not possible to change the generated state of an +-- inheritance tree of depth >= 2 (same as in DROP EXPRESSION), so we expect an +-- error here. This might be fixed later. +begin; +alter table testgen.tpart alter column c + add generated always as (a + b) stored; +ERROR: ALTER TABLE / ADD GENERATED ALWAYS AS (expr) STORED must be applied to child tables too +rollback; +drop table testgen.tpart; -- tests for invalid invocations alter table doesnotexist alter column foo add generated always as (bar * 2) stored; @@ -4986,6 +5213,7 @@ alter table testgen.t3 alter column b ERROR: cannot use subquery in column generation expression drop table testgen.t3; drop schema testgen cascade; -NOTICE: drop cascades to 4 other objects -DETAIL: drop cascades to table testgen.t3 +NOTICE: drop cascades to 3 other objects +DETAIL: drop cascades to table testgen.t5 +drop cascades to function testgen.gen() drop cascades to table testgen.t1 diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql index 76187083289..efb08b9ff70 100644 --- a/src/test/regress/sql/alter_table.sql +++ b/src/test/regress/sql/alter_table.sql @@ -3198,6 +3198,153 @@ select pg_relation_filenode('testgen.idx_b') as idx_filenode_after \gset select :idx_filenode_before != :idx_filenode_after as did_rewrite_idx; drop table testgen.t3; +-- turning a regular column into a stored generated column +-- without rewriting the table (when a check constraint proves it isn't needed) +create table testgen.t4 (a int, b int not null); +insert into testgen.t4 (a, b) select x, x * 2 from generate_series(0, 5) x; +alter table testgen.t4 add constraint chk_gen_clause check (b = a * 2); +select pg_relation_filenode('testgen.t4') as t4_filenode_before \gset +alter table testgen.t4 alter column b add generated always as (a * 2) stored; +select pg_relation_filenode('testgen.t4') as t4_filenode_after \gset +select :t4_filenode_before = :t4_filenode_after as did_skip_rewrite; +\d+ testgen.t4 +drop table testgen.t4; + +-- turning a regular column into a stored generated column +-- same as the previous case, but a rewrite happens since the constraint is not +-- valid +create table testgen.t4 (a int, b int not null); +insert into testgen.t4 (a, b) select x, x * 2 from generate_series(0, 5) x; +alter table testgen.t4 add constraint chk_gen_clause check (b = a * 2) not valid; +select pg_relation_filenode('testgen.t4') as t4_filenode_before \gset +alter table testgen.t4 alter column b add generated always as (a * 2) stored; +select pg_relation_filenode('testgen.t4') as t4_filenode_after \gset +select :t4_filenode_before != :t4_filenode_after as did_rewrite; +\d+ testgen.t4 +drop table testgen.t4; + +-- turning a regular column into a stored generated column +-- same as the previous case, but a rewrite happens since the constraint +-- operator is not mergejoinable +create table testgen.t4 (a int, b int not null); +insert into testgen.t4 (a, b) select x, x * 2 from generate_series(0, 5) x; +alter table testgen.t4 add constraint chk_gen_clause check (b >= a * 2); +select pg_relation_filenode('testgen.t4') as t4_filenode_before \gset +alter table testgen.t4 alter column b add generated always as (a * 3) stored; +select pg_relation_filenode('testgen.t4') as t4_filenode_after \gset +select :t4_filenode_before != :t4_filenode_after as did_rewrite; +\d+ testgen.t4 +drop table testgen.t4; + +-- test the whole process for adding a stored generated column without +-- long-lived exclusive locks +create table testgen.t5 (a int); +select pg_relation_filenode('testgen.t5') as t5_filenode_before \gset +insert into testgen.t5 select x from generate_series(1, 5) x; +alter table testgen.t5 add column b int; +-- take care of new and updated columns +create function testgen.gen () returns trigger language plpgsql as $$ +begin + new.b = new.a * 2; return new; +end +$$; +create trigger testgen_gen + before insert or update on testgen.t5 + for each row execute function testgen.gen(); +-- add the constraint as not valid: enforced only for new and updated rows +begin; +alter table testgen.t5 + add constraint chk_gen_clause check (b = a * 2) not valid; +select locktype, mode from pg_locks + where relation = 'testgen.t5'::regclass and granted; +commit; +insert into testgen.t5 (a) values (100), (200), (300); +-- backfill existing rows at the appropriate pace +update testgen.t5 set b = a * 2 where b is null; +-- validate: this scans the table, but without an exclusive lock +begin; +alter table testgen.t5 validate constraint chk_gen_clause; +select locktype, mode from pg_locks + where relation = 'testgen.t5'::regclass and granted; +commit; +-- now the schema update, which skips the rewrite because of the check +begin; +drop trigger testgen_gen on testgen.t5; +alter table testgen.t5 alter column b + add generated always as (a * 2) stored; +select locktype, mode from pg_locks +where relation = 'testgen.t5'::regclass and granted; +commit; +select pg_relation_filenode('testgen.t5') as t5_filenode_after \gset +select :t5_filenode_before = :t5_filenode_after as did_skip_rewrite; +\d+ testgen.t5 + +-- test support for partitioned tables and inheritance +create table testgen.tpart (a int, b int) partition by hash (a); +create table testgen.tpart_p1 partition of testgen.tpart + for values with (modulus 2, remainder 0); +create table testgen.tpart_p2 partition of testgen.tpart + for values with (modulus 2, remainder 1); +insert into testgen.tpart (a, b) select x, x from generate_series(1, 5) x; + +-- altering the parent table, recursing +begin; +alter table testgen.tpart alter column b + add generated always as (a * 2) stored; +-- expected: all the partitions have been rewritten +select a, b, a * 2 as expected, b = (a * 2) as correct + from testgen.tpart_p1 order by a; +select a, b, a * 2 as expected, b = (a * 2) as correct + from testgen.tpart_p2 order by a; +rollback; + +-- altering a single partition is not allowed +begin; +-- expected: error +alter table testgen.tpart_p1 alter column b + add generated always as (a * 2) stored; +rollback; + +-- altering only the parent table is not allowed +begin; +-- expected: error +alter table only testgen.tpart alter column b + add generated always as (a * 2) stored; +rollback; + +drop table testgen.tpart; + +-- subpartitions +create table testgen.tpart (a int, b int, c int) + partition by hash (a); +create table testgen.tpart_p1 partition of testgen.tpart + for values with (modulus 2, remainder 0) + partition by hash (b); +create table testgen.tpart_p1_1 partition of testgen.tpart_p1 + for values with (modulus 2, remainder 0); +create table testgen.tpart_p1_2 partition of testgen.tpart_p1 + for values with (modulus 2, remainder 1); +create table testgen.tpart_p2 partition of testgen.tpart + for values with (modulus 2, remainder 1) + partition by hash (b); +create table testgen.tpart_p2_1 partition of testgen.tpart_p2 + for values with (modulus 2, remainder 0); +create table testgen.tpart_p2_2 partition of testgen.tpart_p2 + for values with (modulus 2, remainder 1); +insert into testgen.tpart (a, b) + select x, y + from generate_series(1, 5) x + cross join generate_series(1, 5) y; +-- currently, it is not possible to change the generated state of an +-- inheritance tree of depth >= 2 (same as in DROP EXPRESSION), so we expect an +-- error here. This might be fixed later. +begin; +alter table testgen.tpart alter column c + add generated always as (a + b) stored; +rollback; + +drop table testgen.tpart; + -- tests for invalid invocations alter table doesnotexist alter column foo add generated always as (bar * 2) stored; -- 2.47.0
