On Mon Jun 15, 2026 at 8:38 PM CEST, Laurenz Albe wrote: > On Wed, 2026-05-27 at 19:43 +0200, Alberto Piai wrote: >> Design iteration 2 >> ------------------ > > I like this proposal. It avoids the question "to rewrite or not to > rewrite" by just outright failing if there is no suitable constraint. > > The idea to avoid the problem with NULL by forcing IS NOT DISTINCT FROM > in the constraint is a good solution. Perhaps you could also allow > the equality operator if the column in question is defined NOT NULL.
In the attached v5 patch I've implemented this design, and went one step further (let me know what you think). While discussing this with my colleagues at work, the question came up (thanks, Philip!): now that we mention the constraint explicitly, what's the point of repeating the expression too? The constraint already defines an equality to an expression. I think this is a very good point, and it removes one further way in which the operation could fail, so I went ahead and changed the command to not mention the expression. It takes the expression defined in the constraint and uses _that_ as the generator expression of the column. Design iteration 3 ------------------ Syntax: ALTER TABLE t ALTER COLUMN c ADD GENERATED ALWAYS STORED USING CONSTRAINT check_name check_name must be a valid constraint of a specific shape. If the column is nullable: CHECK (c IS NOT DISTINCT FROM expr) If the column is NOT NULL, either of the following is acceptable: CHECK (c = expr) CHECK (c IS NOT DISTINCT FROM expr) The column is then changed to be a stored generated column, with the "expr" from the constraint as its generator expression. >> Any thoughts about this? > > Yes. I think that you should not drop the constraint. That's what I'd > expect, similar to how we don't drop the check constraint that allows > to skip the table scan in ALTER TABLE ... ALTER COLUMN ... SET NOT > NULL or ALTER TABLE ... ATTACK PARTITION. > > I feel that automatically dropping the constraint is a bit too much > black magic, but it is more a feeling than a conviction. I don't have a strong opinion on whether to cleanup or not, I'll gladly take your input. This version of the patch does not drop the constraint anymore. This version addresses your inputs from the last review: - I added documentation for the new alter table form to alter_table.sgml - Tab completion for psql is there now - The missing error conditions in case of an identity column or sequences are now handled, more about this in the next section. Failure conditions ------------------ There's quite a few invalid states that cannot be reached via CREATE TABLE and should not be reachable via ALTER TABLE either. The following are detected and fail the operation: - the column is already a generated column - the column is an identity column - the column is referenced by a sequence (it is most likely a serial column) - the column is referenced by another column's default expression - the column references another generated column - the column is referenced in a partition key, either directly or through a whole row expression - the new default expression is not immutable Additionally, we of course bail if the constraint is not found, not valid, not enforced or doesn't match the specific structure we need. Another case I considered is the column being referenced in the body of a pre-parsed function (BEGIN ATOMIC SQL functions). In this case though, it seems to me that we don't need to fail here: we are not altering the type of the column, and when reading a stored generated column there's no expression replacement happening (as it does when reading virtual columns). Partitioning/inheritance is supported only on the whole hierarchy at once (see 8bf6ec3ba3a44448817af47a080587f3b71bee08). Trying to change the column at only one level will fail, as well as any of the subtrees. I also added a test to explictily check that we're not accidentally enqueuing a table scan for verification in phase 3, as avoiding this kind of work is the whole point of the command. Logical replication ------------------- The interaction with logical replication is tricky, since a publication can have the option to publish generated columns or not (which is the default). When not publishing stored generated columns, inserts or updates would be replicated while backfilling, and would then suddenly stop when the column is turned into a stored generated column. One way to avoid this is to set up triggers on the subscriber too, before altering the column on the publisher. This way updates and inserts would not lose the column's value on the subscriber, which can then be migrated by using the new alter table command. When publishing stored generated columns instead, it is not possible to have the same column be stored generated on both the publisher and the subscriber (see Table 29.2 in section 29.6. Generated Column Replication). The only supported configuration has a regular column on the side of the subscriber. (Note that this is not specific to this new command.) This makes this scenario a lot easier: the column is migrated on the publisher only, and the subscriber won't lose any value. To test these two scenarios, I wrote TAP tests for the subscription suite. However, I'm inclined to not add them to the test suite. I have attached them to this email separately. Other changes since v4 ---------------------- I have changed phase 2 to be ran at AT_PASS_ADD_OTHERCONSTR, before it was at AT_PASS_SET_EXPRESSION. The reason to do it there was to reuse the cleanup steps in ATPostAlterTypeCleanup when a table rewrite did happen. But since now never rewrite, this is not necessary anymore. Looking forward to your thoughts on this! Alberto -- Alberto Piai Sensational AG Zürich, Switzerland
>From fd54518377cf8991574733a1577a63d1f905b23f Mon Sep 17 00:00:00 2001 From: Alberto Piai <[email protected]> Date: Tue, 30 Jun 2026 15:21:29 +0200 Subject: [PATCH v5] Support changing a column into a stored generated column This adds an ALTER TABLE subcommand which turns a regular column into a stored generated column: ... ALTER col ADD GENERATED ALWAYS STORED USING CONSTRAINT constr_name The main purpose of this command is to make it possible to add a stored generated column without rewriting the table under an AccessExclusive lock. Before running this command, the table should have been prepared by adding a regular column, backfilling it with data according to the intended generation expression, and adding a CHECK constraint to prove that the data does satisfy said generation expression. The constraint must have a specific structure to be usable for this operation. If the column is nullable, the constraint must be of the form: CHECK (column_name IS NOT DISTINCT FROM expr) if the column is NOT NULL, either of the following is acceptable: CHECK (column_name IS NOT DISTINCT FROM expr) CHECK (column_name = expr) The column will then be changed into a stored generated column, with the "expr" from the constraint as its generator expression. The operation will be performed without rewriting the table, and without any verification scan. The syntax is chosen for its similarity 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: the latter works in the opposite direction, turning a generated column into a regular column. 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. --- doc/src/sgml/ref/alter_table.sgml | 43 ++ src/backend/commands/tablecmds.c | 468 +++++++++++++++++ src/backend/parser/gram.y | 30 ++ src/bin/psql/t/010_tab_completion.pl | 19 + src/bin/psql/tab-complete.in.c | 38 +- src/include/nodes/parsenodes.h | 2 + .../test_ddl_deparse/test_ddl_deparse.c | 3 + src/test/regress/expected/alter_table.out | 490 ++++++++++++++++++ src/test/regress/sql/alter_table.sql | 314 +++++++++++ 9 files changed, 1404 insertions(+), 3 deletions(-) diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 6dd518752c0..362096bfa9b 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -52,6 +52,7 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET DEFAULT <replaceable class="parameter">expression</replaceable> ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> DROP DEFAULT ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> { SET | DROP } NOT NULL + ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> ADD GENERATED ALWAYS STORED USING CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET EXPRESSION AS ( <replaceable class="parameter">expression</replaceable> ) 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> ) ] @@ -272,6 +273,48 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM </listitem> </varlistentry> + <varlistentry id="sql-altertable-desc-add-generated-always-stored-using-constraint"> + <term><literal>ADD GENERATED ALWAYS STORED USING CONSTRAINT</literal></term> + <listitem> + <para> + This form changes a regular column into a stored generated column, using + the expression from the given constraint. The constraint must be a + <literal>CHECK</literal> constraint proving that the values of the + column already satisfy the generation expression. The operation will + then be performed without rewriting the table. + </para> + + <para> + The main purpose of this form is to allow adding a stored generated + column to a table without performing a table rewrite while holding an + <literal>ACCESS EXCLUSIVE</literal> lock. + </para> + + <para> + Before using this command, the table will usually have been prepared by + adding a regular column, backfilling it with values matching the intended + generation expression and adding a constraint to ensure that the + generation expression is satisfied. Note that the constraint can also be + added without holding an <literal>ACCESS EXCLUSIVE</literal> lock while + the table is scanned, using <literal>NOT VALID</literal> and + <literal>VALIDATE CONSTRAINT</literal>. + </para> + + <para> + If the column being modified is nullable, the constraint must be of the + form <literal>CHECK (column_name IS NOT DISTINCT FROM expr)</literal>. + If the column is <literal>NOT NULL</literal>, then the form + <literal>CHECK (column_name = expr)</literal> is also allowed. + </para> + + <para> + After this command is run, <literal>column_name</literal> will be a stored + generated column with <literal>expr</literal> as its generation + expression. + </para> + </listitem> + </varlistentry> + <varlistentry id="sql-altertable-desc-set-expression"> <term><literal>SET EXPRESSION AS</literal></term> <listitem> diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 472db112fa7..a5a809f49d0 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -790,6 +790,21 @@ static void ATExecMergePartitions(List **wqueue, AlteredTableInfo *tab, Relation static void ATExecSplitPartition(List **wqueue, AlteredTableInfo *tab, Relation rel, PartitionCmd *cmd, AlterTableUtilityContext *context); +static void ATPrepAddExpressionStored(Relation rel, + AlterTableCmd *cmd, + bool recurse, bool recursing, + LOCKMODE lockmode); +static void checkDependenciesForAddExprStored(Relation rel, + AttrNumber attnum, + const char *colName); +static Node *findUsableConstraintForAddExprStored(Relation rel, AttrNumber attnum, + bool attisnotnull, + const char *conname); +static Node *reconstructRawExpr(Relation rel, Node *cookedExpr); +static ObjectAddress ATExecAddExpressionStored(AlteredTableInfo *tab, + Relation rel, + const char *colName, + Constraint *def); static List *collectPartitionIndexExtDeps(List *partitionOids); static void applyPartitionIndexExtDeps(Oid newPartOid, List *extDepState); static void freePartitionIndexExtDeps(List *extDepState); @@ -4776,6 +4791,7 @@ AlterTableGetLockLevel(List *cmds) case AT_AddIdentity: case AT_DropIdentity: case AT_SetIdentity: + case AT_AddExpressionStored: case AT_SetExpression: case AT_DropExpression: case AT_SetCompression: @@ -5100,6 +5116,14 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode, context); pass = AT_PASS_SET_EXPRESSION; break; + case AT_AddExpressionStored: /* ALTER COLUMN ADD GENERATED ALWAYS + * STORED USING CONSTRAINT */ + ATSimplePermissions(cmd->subtype, rel, + ATT_TABLE | ATT_PARTITIONED_TABLE | ATT_FOREIGN_TABLE); + ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode, context); + ATPrepAddExpressionStored(rel, cmd, recurse, recursing, lockmode); + pass = AT_PASS_ADD_OTHERCONSTR; + break; case AT_DropExpression: /* ALTER COLUMN DROP EXPRESSION */ ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_PARTITIONED_TABLE | ATT_FOREIGN_TABLE); @@ -5494,6 +5518,12 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, case AT_SetExpression: address = ATExecSetExpression(tab, rel, cmd->name, cmd->def, lockmode); break; + case AT_AddExpressionStored: + Assert(IsA(cmd->def, Constraint)); + address = ATExecAddExpressionStored(tab, rel, + cmd->name, + (Constraint *) cmd->def); + break; case AT_DropExpression: address = ATExecDropExpression(rel, cmd->name, cmd->missing_ok, lockmode); break; @@ -6702,6 +6732,8 @@ alter_table_type_to_string(AlterTableType cmdtype) return "ALTER COLUMN ... SET NOT NULL"; case AT_SetExpression: return "ALTER COLUMN ... SET EXPRESSION"; + case AT_AddExpressionStored: + return "ALTER COLUMN ... ADD GENERATED STORED"; case AT_DropExpression: return "ALTER COLUMN ... DROP EXPRESSION"; case AT_SetStatistics: @@ -8833,6 +8865,442 @@ ATExecSetExpression(AlteredTableInfo *tab, Relation rel, const char *colName, return address; } +/* + * Preparation phase for + * + * ALTER COLUMN col ADD GENERATED ALWAYS STORED USING CONSTRAINT name + * + * In an inheritance hierarchy, it is only valid to alter the type of the + * whole hierarchy at once. + */ +static void +ATPrepAddExpressionStored(Relation rel, + AlterTableCmd *cmd, + bool recurse, bool recursing, + LOCKMODE lockmode) +{ + /* + * Reject ONLY if there are child tables. + */ + if (!recursing && !recurse && + find_inheritance_children(RelationGetRelid(rel), lockmode)) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("ALTER COLUMN / ADD GENERATED ALWAYS 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"))); + } +} + +/* + * Detect dependencies which should stop us from turning a regular column + * into a stored generated column. + */ +static void +checkDependenciesForAddExprStored(Relation rel, + AttrNumber attnum, + const char *colName) +{ + Relation pg_depend; + ScanKeyData keys[3]; + SysScanDesc scan; + HeapTuple depTup; + + pg_depend = table_open(DependRelationId, AccessShareLock); + + ScanKeyInit(&keys[0], + Anum_pg_depend_refclassid, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(RelationRelationId)); + ScanKeyInit(&keys[1], + Anum_pg_depend_refobjid, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(RelationGetRelid(rel))); + ScanKeyInit(&keys[2], + Anum_pg_depend_refobjsubid, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(attnum)); + + scan = systable_beginscan(pg_depend, DependReferenceIndexId, true, + NULL, 3, keys); + + while (HeapTupleIsValid(depTup = systable_getnext(scan))) + { + Form_pg_depend dep = GETSTRUCT(depTup); + ObjectAddress foundObject; + + foundObject.classId = dep->classid; + foundObject.objectId = dep->objid; + foundObject.objectSubId = dep->objsubid; + + switch (foundObject.classId) + { + case RelationRelationId: + { + char relKind = get_rel_relkind(foundObject.objectId); + + if (relKind == RELKIND_SEQUENCE) + ereport(ERROR, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("cannot convert a serial column to a stored generated column"), + errdetail("\"%s\" of relation \"%s\" depends on sequence %s", + colName, RelationGetRelationName(rel), + getObjectDescription(&foundObject, false)))); + break; + } + case AttrDefaultRelationId: + { + ObjectAddress col = GetAttrDefaultColumnAddress(foundObject.objectId); + + if (col.objectId == RelationGetRelid(rel) && + col.objectSubId == attnum) + { + /* + * Ignore the column's own default expression. We + * handle sequences above, and for a column which is + * already a generated column we should never get + * here. + */ + } + else + { + ereport(ERROR, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("cannot convert a column referenced in a default expression to a stored generated column"), + errdetail("Column \"%s\" is referenced by generated column \"%s\".", + colName, + get_attname(col.objectId, col.objectSubId, false)))); + } + break; + } + default: + /* We're not interested in the row */ + break; + } + } + + systable_endscan(scan); + table_close(pg_depend, NoLock); +} + +/* + * Subroutine for ATExecAddExpressionStored, used to find a CHECK constraint + * to prove that the column values statisfy what will be the generator + * expression. + * + * Given a rel, a column and a constraint name, we look up a valid CHECK + * constraint on the rel, with the given name, with a specific shape. + * + * If the column is nullable: + * CHECK (column IS NOT DISTINCT FROM expr) + * + * If the column is NOT NULL, any of: + * CHECK (column IS NOT DISTINCT FROM expr) + * CHECK (column = expr) + * + * If a valid constraint is found, this returns both the Oid of the constraint + * and the unpacked expression. + */ +static Node * +findUsableConstraintForAddExprStored(Relation rel, AttrNumber attnum, + bool attisnotnull, + const char *conname) +{ + Relation pg_constraint; + HeapTuple conTup; + SysScanDesc scan; + ScanKeyData key; + Node *foundExpr; + + pg_constraint = table_open(ConstraintRelationId, AccessShareLock); + ScanKeyInit(&key, + Anum_pg_constraint_conrelid, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(rel->rd_id)); + scan = systable_beginscan(pg_constraint, ConstraintRelidTypidNameIndexId, + true, NULL, 1, &key); + + foundExpr = NULL; + + 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 (strcmp(conname, NameStr(con->conname)) != 0) + continue; + /* !conenforced implies !convalidated, but let's be explicit about it */ + if (!con->convalidated || !con->conenforced) + continue; + + val = SysCacheGetAttrNotNull(CONSTROID, conTup, + Anum_pg_constraint_conbin); + conbin = TextDatumGetCString(val); + conexpr = stringToNode(conbin); + + /* Try to match IS NOT DISTINCT */ + if (IsA(conexpr, BoolExpr)) + { + BoolExpr *negation = (BoolExpr *) conexpr; + + if (list_length(negation->args) == 1 + && negation->boolop == NOT_EXPR + && IsA(linitial(negation->args), DistinctExpr)) + { + DistinctExpr *dist = linitial(negation->args); + + Assert(list_length(dist->args) == 2); + + if (IsA(linitial(dist->args), Var)) + { + Var *var = linitial(dist->args); + + if (var->varattno == attnum && + op_mergejoinable(dist->opno, exprType((Node *) var))) + { + foundExpr = lsecond(dist->args); + break; + } + } + } + } + /* If the column is NOT NULL, try to match = as well */ + if (attisnotnull && 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))) + { + foundExpr = lsecond(op->args); + break; + } + } + } + } + + systable_endscan(scan); + table_close(pg_constraint, AccessShareLock); + + return foundExpr; +} + +/* + * Reconstruct a raw expression from a given cooked expression by deparsing it + * and running it through raw_parser(). + */ +static Node * +reconstructRawExpr(Relation rel, Node *cookedExpr) +{ + char *deparsedExpr; + List *ctx, + *parseResult = NIL; + + ctx = deparse_context_for(RelationGetRelationName(rel), + RelationGetRelid(rel)); + + deparsedExpr = deparse_expression(cookedExpr, ctx, false, false); + + parseResult = raw_parser(deparsedExpr, RAW_PARSE_PLPGSQL_EXPR); + if (list_length(parseResult) != 1) + ereport(ERROR, + (errcode(ERRCODE_INTERNAL_ERROR), + errmsg("cannot re-parse constraint expr into a raw expression"))); + + if (IsA(linitial(parseResult), RawStmt)) + { + RawStmt *stmt = linitial(parseResult); + + if (IsA(stmt->stmt, SelectStmt)) + { + SelectStmt *select = (SelectStmt *) stmt->stmt; + + if (list_length(select->targetList) == 1 && + IsA(linitial(select->targetList), ResTarget)) + { + ResTarget *resTarget = linitial(select->targetList); + + return resTarget->val; + } + } + } + + ereport(ERROR, + errcode(ERRCODE_INTERNAL_ERROR), + errmsg_internal("re-parsed expr does not match the expected structure")); +} + +/* + * ALTER COLUMN col ADD GENERATED ALWAYS STORED USING CONSTRAINT name + * + * Change a regular column into a stored generated column without a table + * rewrite, using the expression contained in the given constraint. + * + * The constraint must be a CHECK constraint proving that the expression is + * already satisfied by all the values in the column (see + * findUsableConstraintForAddExprStored). + */ +static ObjectAddress +ATExecAddExpressionStored(AlteredTableInfo *tab, + Relation rel, + const char *colName, + Constraint *def) +{ + HeapTuple tuple; + Form_pg_attribute attTup; + AttrNumber attnum; + Bitmapset *colRefs; + bool is_expr; + ObjectAddress address; + Relation pg_attribute; + Node *foundConstraintExpr = NULL; + Node *newRawDefExpr; + RawColumnDefault *rawDefault; + List *cookedResult = NIL; + + Assert(def->raw_expr == NULL); + Assert(def->cooked_expr == NULL); + Assert(def->conname != 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->attidentity) + ereport(ERROR, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("Cannot convert an identity column to a stored generated column"), + errdetail("column \"%s\" of relation \"%s\" is an identity column", + colName, RelationGetRelationName(rel)))); + + 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)))); + + /* + * This column might be referenced directly in a partition key, or through + * a whole-row expression. + */ + colRefs = bms_make_singleton(attnum - FirstLowInvalidHeapAttributeNumber); + colRefs = bms_add_member(colRefs, 0 - FirstLowInvalidHeapAttributeNumber); + if (has_partition_attrs(rel, colRefs, &is_expr)) + ereport(ERROR, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("cannot convert a column into a stored generated column if it's referenced by a partition key"), + errdetail("column \"%s\" is part of the partition key of relation \"%s\"", + colName, RelationGetRelationName(rel)))); + + checkDependenciesForAddExprStored(rel, attnum, colName); + + /* + * Now, try to find the constraint by name, and see if it has the + * necessary structure to prove that the values are consistent. + */ + foundConstraintExpr = findUsableConstraintForAddExprStored(rel, attnum, + attTup->attnotnull, + def->conname); + if (foundConstraintExpr == NULL) + ereport(ERROR, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("cannot convert a column into a stored generated column without a constraint to prove that the values are consistent"), + attTup->attnotnull ? + errdetail("could not find a valid constraint \"%s\" CHECK (\"%s\" = expr) or CHECK (\"%s\" IS NOT DISTINCT FROM (expr))", + def->conname, + colName, + colName) : + errdetail("could not find a valid constraint \"%s\" CHECK (\"%s\" IS NOT DISTINCT FROM (expr))", + def->conname, + colName))); + + /* 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); + + ReleaseSysCache(tuple); + + /* Make above changes visible */ + CommandCounterIncrement(); + + /* Recover a raw parse tree for the expression found in the constraint */ + newRawDefExpr = reconstructRawExpr(rel, foundConstraintExpr); + + /* + * Remove previous default value, if any, and store the new generator + * expression. + */ + RemoveAttrDefault(RelationGetRelid(rel), attnum, DROP_RESTRICT, + false, false); + + rawDefault = palloc0_object(RawColumnDefault); + rawDefault->attnum = attnum; + rawDefault->raw_default = newRawDefExpr; + rawDefault->generated = ATTRIBUTE_GENERATED_STORED; + + cookedResult = AddRelationNewConstraints(rel, list_make1(rawDefault), NIL, + false /* allow_merge */ , + true /* is_local */ , + false /* is_internal */ , + NULL /* queryString */ ); + + if (list_length(cookedResult) != 1) + ereport(ERROR, + (errcode(ERRCODE_INTERNAL_ERROR), + errmsg_internal("cannot store constraint as default value"))); + + InvokeObjectPostAlterHook(RelationRelationId, + RelationGetRelid(rel), attnum); + + ObjectAddressSubSet(address, RelationRelationId, + RelationGetRelid(rel), attnum); + return address; +} + /* * ALTER TABLE ALTER COLUMN DROP EXPRESSION */ diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index ff4e1388c55..7bbde3d4e23 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -2725,6 +2725,36 @@ alter_table_cmd: n->name = $3; n->def = (Node *) c; + $$ = (Node *) n; + } + /* ALTER TABLE <name> ALTER [COLUMN] <colname> ADD GENERATED ALWAYS STORED USING CONSTRAINT constraint_name */ + | ALTER opt_column ColId ADD_P GENERATED generated_when STORED USING CONSTRAINT name + { + AlterTableCmd *n = makeNode(AlterTableCmd); + Constraint *c = makeNode(Constraint); + + c->conname = $10; + c->contype = CONSTR_GENERATED; + c->generated_when = $6; + c->generated_kind = ATTRIBUTE_GENERATED_STORED; + c->location = @10; + + /* + * 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_AddExpressionStored; + n->name = $3; + n->def = (Node *) c; + $$ = (Node *) n; } /* ALTER TABLE <name> ALTER [COLUMN] <colname> SET <sequence options>/RESET */ diff --git a/src/bin/psql/t/010_tab_completion.pl b/src/bin/psql/t/010_tab_completion.pl index 64e27ef87a3..5d381433004 100644 --- a/src/bin/psql/t/010_tab_completion.pl +++ b/src/bin/psql/t/010_tab_completion.pl @@ -46,6 +46,8 @@ $node->safe_psql('postgres', . "CREATE TYPE enum1 AS ENUM ('foo', 'bar', 'baz', 'BLACK');\n" . "CREATE PUBLICATION some_publication;\n" . "CREATE TABLE fpo_test (id int4range, valid_at daterange, name text);\n" + . "CREATE TABLE gencol_test (a int primary key, b int);\n" + . "ALTER TABLE gencol_test ADD CONSTRAINT check_gen CHECK (b IS NOT DISTINCT FROM (a + 1));\n" ); # In a VPATH build, we'll be started in the source directory, but we want @@ -460,6 +462,23 @@ check_completion("FR\t", qr/FROM /, clear_query(); +check_completion("ALTER TABLE gencol_test ALTER COLUMN b A\t", qr/ADD /, + "complete ALTER COLUMN <col> ADD"); + +check_completion("G\t", qr/GENERATED /, + "complete ALTER COLUMN <col> ADD GENERATED"); + +check_completion("A\t", qr/ALWAYS /, + "complete ALTER COLUMN <col> ADD GENERATED ALWAYS"); + +check_completion("S\t", qr/STORED USING CONSTRAINT /, + "complete ALTER COLUMN <col> ADD GENERATED ALWAYS STORED USING CONSTRAINT"); + +check_completion("\t\t", qr/check_gen/, + "complete ALTER COLUMN <col> ADD GENERATED ALWAYS STORED USING CONSTRAINT offers check constraint names"); + +clear_query(); + # send psql an explicit \q to shut it down, else pty won't close properly $h->quit or die "psql returned $?"; diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c index 46b9add0604..f318287edd2 100644 --- a/src/bin/psql/tab-complete.in.c +++ b/src/bin/psql/tab-complete.in.c @@ -489,6 +489,15 @@ static const SchemaQuery Query_for_constraint_of_table_not_validated = { .refnamespace = "c1.relnamespace", }; +static const SchemaQuery Query_for_check_constraint_of_table = { + .catname = "pg_catalog.pg_constraint con, pg_catalog.pg_class c1", + .selcondition = "con.conrelid=c1.oid and con.contype='c'", + .result = "con.conname", + .refname = "c1.relname", + .refviscondition = "pg_catalog.pg_table_is_visible(c1.oid)", + .refnamespace = "c1.relnamespace", +}; + static const SchemaQuery Query_for_constraint_of_type = { .catname = "pg_catalog.pg_constraint con, pg_catalog.pg_type t", .selcondition = "con.contypid=t.oid", @@ -1898,6 +1907,7 @@ psql_completion(const char *text, int start, int end) #define prev7_wd (previous_words[6]) #define prev8_wd (previous_words[7]) #define prev9_wd (previous_words[8]) +#define prev10_wd (previous_words[9]) /* Match the last N words before point, case-insensitively. */ #define TailMatches(...) \ @@ -2984,12 +2994,34 @@ match_previous_words(int pattern_id, else if (Matches("ALTER", "TABLE", MatchAny, "ALTER", "COLUMN", MatchAny, "ADD", "GENERATED") || Matches("ALTER", "TABLE", MatchAny, "ALTER", MatchAny, "ADD", "GENERATED")) COMPLETE_WITH("ALWAYS", "BY DEFAULT"); - /* ALTER TABLE ALTER [COLUMN] <foo> ADD GENERATED */ + /* ALTER TABLE ALTER [COLUMN] <foo> ADD GENERATED ALWAYS */ else if (Matches("ALTER", "TABLE", MatchAny, "ALTER", "COLUMN", MatchAny, "ADD", "GENERATED", "ALWAYS") || - Matches("ALTER", "TABLE", MatchAny, "ALTER", MatchAny, "ADD", "GENERATED", "ALWAYS") || - Matches("ALTER", "TABLE", MatchAny, "ALTER", "COLUMN", MatchAny, "ADD", "GENERATED", "BY", "DEFAULT") || + Matches("ALTER", "TABLE", MatchAny, "ALTER", MatchAny, "ADD", "GENERATED", "ALWAYS")) + COMPLETE_WITH("AS IDENTITY", "STORED USING CONSTRAINT"); + /* ALTER TABLE ALTER [COLUMN] <foo> ADD GENERATED BY DEFAULT */ + else if (Matches("ALTER", "TABLE", MatchAny, "ALTER", "COLUMN", MatchAny, "ADD", "GENERATED", "BY", "DEFAULT") || Matches("ALTER", "TABLE", MatchAny, "ALTER", MatchAny, "ADD", "GENERATED", "BY", "DEFAULT")) COMPLETE_WITH("AS IDENTITY"); + + /* + * ALTER TABLE ALTER [COLUMN] <foo> ADD GENERATED ALWAYS STORED USING + * CONSTRAINT + */ + else if (Matches("ALTER", "TABLE", MatchAny, "ALTER", "COLUMN", MatchAny, "ADD", "GENERATED", "ALWAYS", "STORED", "USING", "CONSTRAINT")) + { + set_completion_reference(prev10_wd); + COMPLETE_WITH_SCHEMA_QUERY(Query_for_check_constraint_of_table); + } + + /* + * ALTER TABLE ALTER [COLUMN] <foo> ADD GENERATED ALWAYS STORED USING + * CONSTRAINT + */ + else if (Matches("ALTER", "TABLE", MatchAny, "ALTER", MatchAny, "ADD", "GENERATED", "ALWAYS", "STORED", "USING", "CONSTRAINT")) + { + set_completion_reference(prev9_wd); + COMPLETE_WITH_SCHEMA_QUERY(Query_for_check_constraint_of_table); + } /* ALTER TABLE ALTER [COLUMN] <foo> SET */ else if (Matches("ALTER", "TABLE", MatchAny, "ALTER", "COLUMN", MatchAny, "SET") || Matches("ALTER", "TABLE", MatchAny, "ALTER", MatchAny, "SET")) diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 4133c404a6b..91c5630b98f 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -2524,6 +2524,8 @@ 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_AddExpressionStored, /* add generated always stored using + * constraint */ 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..fb5ab479aab 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_AddExpressionStored: + strtype = "ADD GENERATED 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 b891d68d4a7..caee39f773a 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -4876,3 +4876,493 @@ 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 STORED USING CONSTRAINT name +-- turning a regular column into a stored generated column without a rewrite +create schema testgen; +create table testgen.t1 (a int, b int); +insert into testgen.t1 (a, b) + select x, x * 2 from generate_series(1, 10) x; +alter table testgen.t1 add constraint chk_gen_clause check (b is not distinct from a * 2); +alter table testgen.t1 alter column b + add generated always stored using constraint chk_gen_clause; +\d+ testgen.t1 + Table "testgen.t1" + 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 (NOT b IS DISTINCT FROM (a * 2)) + +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; +-- accepts = instead of IS NOT DISTINCT FROM when the destination +-- column is NOT NULL +create table testgen.t1 (a int, b int not null); +insert into testgen.t1 (a, b) +select x, x * 2 from generate_series(1, 10) x; +alter table testgen.t1 add constraint chk_gen_clause_equal check (b = a * 2); +alter table testgen.t1 alter column b + add generated always stored using constraint chk_gen_clause_equal; +\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 | | +Check constraints: + "chk_gen_clause_equal" CHECK (b = (a * 2)) +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) + +drop table testgen.t1; +-- turning a regular column into a stored generated column +-- fails when the constraint does not exist +create table testgen.t1 (a int, b int); +alter table testgen.t1 alter column b + add generated always stored using constraint chk_gen_clause_does_not_exist; +ERROR: cannot convert a column into a stored generated column without a constraint to prove that the values are consistent +DETAIL: could not find a valid constraint "chk_gen_clause_does_not_exist" CHECK ("b" IS NOT DISTINCT FROM (expr)) +drop table testgen.t1; +-- turning a regular column into a stored generated column +-- fails when the constraint does not exist. When the destination +-- column is NOT NULL, the error message mentions both constraint +-- shapes which would be valid +create table testgen.t1 (a int, b int not null); +alter table testgen.t1 alter column b + add generated always stored using constraint chk_gen_clause_does_not_exist; +ERROR: cannot convert a column into a stored generated column without a constraint to prove that the values are consistent +DETAIL: could not find a valid constraint "chk_gen_clause_does_not_exist" CHECK ("b" = expr) or CHECK ("b" IS NOT DISTINCT FROM (expr)) +drop table testgen.t1; +-- turning a regular column into a stored generated column +-- fails when the constraint is not valid +create table testgen.t1 (a int, b int); +alter table testgen.t1 add constraint chk_gen_clause check (b is not distinct from a * 2) not valid; +alter table testgen.t1 alter column b + add generated always stored using constraint chk_gen_clause; +ERROR: cannot convert a column into a stored generated column without a constraint to prove that the values are consistent +DETAIL: could not find a valid constraint "chk_gen_clause" CHECK ("b" IS NOT DISTINCT FROM (expr)) +drop table testgen.t1; +-- turning a regular column into a stored generated column +-- fails when the constraint is not enforced +create table testgen.t1 (a int, b int); +alter table testgen.t1 add constraint chk_gen_clause check (b is not distinct from a * 2) not enforced; +alter table testgen.t1 alter column b + add generated always stored using constraint chk_gen_clause; +ERROR: cannot convert a column into a stored generated column without a constraint to prove that the values are consistent +DETAIL: could not find a valid constraint "chk_gen_clause" CHECK ("b" IS NOT DISTINCT FROM (expr)) +drop table testgen.t1; +-- turning a regular column into a stored generated column +-- without rewriting the table doesn't touch the index either +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 stored using constraint chk_gen_clause; +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 +-- fails when the constraint exists but doesn't have the expected shape +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 stored using constraint chk_gen_clause; +ERROR: cannot convert a column into a stored generated column without a constraint to prove that the values are consistent +DETAIL: could not find a valid constraint "chk_gen_clause" CHECK ("b" = expr) or CHECK ("b" IS NOT DISTINCT FROM (expr)) +select pg_relation_filenode('testgen.t4') as t4_filenode_after \gset +select :t4_filenode_before != :t4_filenode_after as did_rewrite; + did_rewrite +------------- + f +(1 row) + +\d+ testgen.t4 + Table "testgen.t4" + 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: + "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; +-- test nulls, too +insert into testgen.t5 (a) values (null); +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 is not distinct from 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 doesn't need to rewrite the table thanks to +-- the constraint +begin; +alter table testgen.t5 alter column b + add generated always stored using constraint chk_gen_clause; +select locktype, mode from pg_locks +where relation = 'testgen.t5'::regclass and granted; + locktype | mode +----------+--------------------- + relation | AccessExclusiveLock +(1 row) + +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) + +select * from testgen.t5; + a | b +-----+----- + 100 | 200 + 200 | 400 + 300 | 600 + 1 | 2 + 2 | 4 + 3 | 6 + 4 | 8 + 5 | 10 + | +(9 rows) + +-- verify that it's still possible to insert rows (the trigger is still +-- running at this point) +insert into testgen.t5 (a) values (400); +drop trigger testgen_gen on testgen.t5; +drop function testgen.gen(); +insert into testgen.t5 (a) values (500); +\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 (NOT b IS DISTINCT FROM (a * 2)) + +select * from testgen.t5 order by a nulls first; + a | b +-----+------ + | + 1 | 2 + 2 | 4 + 3 | 6 + 4 | 8 + 5 | 10 + 100 | 200 + 200 | 400 + 300 | 600 + 400 | 800 + 500 | 1000 +(11 rows) + +drop table testgen.t5; +-- check that the table isn't being scanned during phase 3, even if other +-- objects depend on the column we are changing. Lowering client_min_messages +-- makes the message 'verifying table...' be shown here when that happens. +create table testgen.t6 (a int, b int not null); +insert into testgen.t6 (a, b) values (1, 2); +alter table testgen.t6 add constraint c1 check (b > 0); +alter table testgen.t6 add constraint c2 check (b = a * 2); +create index on testgen.t6 (b); +set client_min_messages = 'DEBUG1'; +alter table testgen.t6 alter b + add generated always stored using constraint c2; +-- we expect to *not* see a "verifying table" message here +reset client_min_messages; +drop table testgen.t6; +-- test support for partitioned tables and inheritance +create table testgen.tpart (a int, b int) partition by hash (a); +alter table testgen.tpart + add constraint chk_gen_clause check (b is not distinct from a * 2); +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 * 2 from generate_series(1, 5) x; +-- altering the parent table, recursing +begin; +alter table testgen.tpart alter column b + add generated always stored using constraint chk_gen_clause; +-- 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 stored using constraint chk_gen_clause; +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 stored using constraint chk_gen_clause; +ERROR: ALTER COLUMN / ADD GENERATED ALWAYS STORED must be applied to child tables too +rollback; +drop table testgen.tpart; +-- test support for inheritance and subpartitions +create table testgen.root (a int, b int, c int); +create table testgen.intermediate () inherits (testgen.root); +create table testgen.leaf () inherits (testgen.intermediate); +alter table testgen.tpart + add constraint chk_gen_clause check (b is not distinct from a + b); +ERROR: relation "testgen.tpart" does not exist +-- it's only allowed to change the whole hierarchy at once... +begin; +alter table testgen.root alter column c + add generated always stored using constraint chk_gen_clause; +ERROR: cannot convert a column into a stored generated column without a constraint to prove that the values are consistent +DETAIL: could not find a valid constraint "chk_gen_clause" CHECK ("c" IS NOT DISTINCT FROM (expr)) +rollback; +-- ... hence all these should result in an error +begin; +alter table only testgen.root alter column c + add generated always stored using constraint chk_gen_clause; +ERROR: ALTER COLUMN / ADD GENERATED ALWAYS STORED must be applied to child tables too +rollback; +begin; +alter table testgen.intermediate alter column c + add generated always stored using constraint chk_gen_clause; +ERROR: cannot change inherited column to be a stored generated column +rollback; +begin; +alter table only testgen.intermediate alter column c + add generated always stored using constraint chk_gen_clause; +ERROR: ALTER COLUMN / ADD GENERATED ALWAYS STORED must be applied to child tables too +rollback; +begin; +alter table testgen.leaf alter column c + add generated always stored using constraint chk_gen_clause; +ERROR: cannot change inherited column to be a stored generated column +rollback; +begin; +alter table only testgen.leaf alter column c + add generated always stored using constraint chk_gen_clause; +ERROR: cannot change inherited column to be a stored generated column +rollback; +drop table testgen.root cascade; +NOTICE: drop cascades to 2 other objects +DETAIL: drop cascades to table testgen.intermediate +drop cascades to table testgen.leaf +-- tests for invalid invocations +alter table doesnotexist alter column foo + add generated always stored using constraint cdoesnotexist; +ERROR: relation "doesnotexist" does not exist +create table testgen.t1 (a int); +alter table testgen.t1 add constraint chk_gen_clause check (1); +ERROR: argument of CHECK must be type boolean, not type integer +alter table testgen.t1 alter column doesnotexist + add generated always stored using constraint chk_gen_clause; +ERROR: column "doesnotexist" of relation "t1" does not exist +alter table testgen.t1 add column b int; +-- invalid: only supports ALWAYS +alter table testgen.t1 alter column b + add generated by default stored using constraint chk_gen_clause; +ERROR: for a generated column, GENERATED ALWAYS must be specified +LINE 2: add generated by default stored using constraint chk_gen... + ^ +-- invalid: only supports STORED. These are all syntax errors. +alter table testgen.t1 alter column b add generated always; +ERROR: syntax error at or near ";" +LINE 1: alter table testgen.t1 alter column b add generated always; + ^ +alter table testgen.t1 alter column b add generated always virtual; +ERROR: syntax error at or near "virtual" +LINE 1: ...able testgen.t1 alter column b add generated always virtual; + ^ +alter table testgen.t1 alter column b add generated always using constraint chk_gen_clause; +ERROR: syntax error at or near "using" +LINE 1: ...le testgen.t1 alter column b add generated always using cons... + ^ +alter table testgen.t1 alter column b add generated always virtual using constraint chk_gen_clause; +ERROR: syntax error at or near "virtual" +LINE 1: ...le testgen.t1 alter column b add generated always virtual us... + ^ +drop table testgen.t1; +-- 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 stored using constraint doesnotexist; +ERROR: column "b" of relation "t2" is already a generated column +drop table testgen.t2; +-- invalid: b is an identity column +create table testgen.t2 (a int, b int generated always as identity); +alter table testgen.t2 alter column b add generated always stored using constraint doesnotexist; +ERROR: Cannot convert an identity column to a stored generated column +DETAIL: column "b" of relation "t2" is an identity column +drop table testgen.t2; +create table testgen.t2 (a int, b int generated by default as identity ); +alter table testgen.t2 alter column b add generated always stored using constraint doesnotexist; +ERROR: Cannot convert an identity column to a stored generated column +DETAIL: column "b" of relation "t2" is an identity column +drop table testgen.t2; +-- invalid: b is a serial column +create table testgen.t2 (a int, b bigserial); +alter table testgen.t2 add constraint chk_gen_clause check (b is not distinct from (1)); +alter table testgen.t2 alter column b add generated always stored using constraint chk_gen_clause; +ERROR: cannot convert a serial column to a stored generated column +DETAIL: "b" of relation "t2" depends on sequence sequence testgen.t2_b_seq +drop table testgen.t2; +-- invalid: c is referenced by another column's default expr +create table testgen.t3 (a int, b int generated always as (c + 1), c int); +alter table testgen.t3 add constraint chk_gen_clause check (c is not distinct from (1)); +alter table testgen.t3 alter column c add generated always stored using constraint chk_gen_clause; +ERROR: cannot convert a column referenced in a default expression to a stored generated column +DETAIL: Column "c" is referenced by generated column "b". +drop table testgen.t3; +-- invalid: c references another generated column +create table testgen.t3 (a int, b int generated always as (a + 1), c int); +alter table testgen.t3 add constraint chk_gen_clause check (c is not distinct from (b + 1)); +alter table testgen.t3 alter column c add generated always stored using constraint chk_gen_clause; +ERROR: cannot use generated column "b" in column generation expression +DETAIL: A generated column cannot reference another generated column. +drop table testgen.t3; +-- invalid: c is referenced in a partition key +create table testgen.t3 (a int, b int, c int) partition by hash (c); +alter table testgen.t3 alter column c add generated always stored using constraint doesnotexist; +ERROR: cannot convert a column into a stored generated column if it's referenced by a partition key +DETAIL: column "c" is part of the partition key of relation "t3" +drop table testgen.t3; +create table testgen.t3 (a int, b int, c int) partition by hash ((c)); +alter table testgen.t3 alter column c add generated always stored using constraint doesnotexist; +ERROR: cannot convert a column into a stored generated column if it's referenced by a partition key +DETAIL: column "c" is part of the partition key of relation "t3" +drop table testgen.t3; +-- test for a whole-row reference +-- since it's not possible to reference schema.table in partition by range, +-- temporarily hack the search_path +show search_path \gset +set search_path to testgen, public; +create table t3 (a int, b int, c int) partition by range ((t3)); +alter table testgen.t3 alter column c add generated always stored using constraint doesnotexist; +ERROR: cannot convert a column into a stored generated column if it's referenced by a partition key +DETAIL: column "c" is part of the partition key of relation "t3" +drop table testgen.t3; +create table t3 (a int, b int, c int) partition by range ((t3 is null)); +alter table testgen.t3 alter column c add generated always stored using constraint doesnotexist; +ERROR: cannot convert a column into a stored generated column if it's referenced by a partition key +DETAIL: column "c" is part of the partition key of relation "t3" +drop table testgen.t3; +set search_path to :search_path; +create table testgen.t3 (a int, b int); +-- invalid: expr must be immutable +alter table testgen.t3 add constraint chk_gen_clause check (b is not distinct from (a + random()::int)); +alter table testgen.t3 alter column b + add generated always stored using constraint chk_gen_clause; +ERROR: generation expression is not immutable +alter table testgen.t3 drop constraint chk_gen_clause; +drop table testgen.t3; +drop schema testgen cascade; diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql index f5f13bbd3e7..4a79e3b7219 100644 --- a/src/test/regress/sql/alter_table.sql +++ b/src/test/regress/sql/alter_table.sql @@ -3159,3 +3159,317 @@ 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 STORED USING CONSTRAINT name +-- turning a regular column into a stored generated column without a rewrite +create schema testgen; + +create table testgen.t1 (a int, b int); +insert into testgen.t1 (a, b) + select x, x * 2 from generate_series(1, 10) x; +alter table testgen.t1 add constraint chk_gen_clause check (b is not distinct from a * 2); +alter table testgen.t1 alter column b + add generated always stored using constraint chk_gen_clause; +\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; + +-- accepts = instead of IS NOT DISTINCT FROM when the destination +-- column is NOT NULL +create table testgen.t1 (a int, b int not null); +insert into testgen.t1 (a, b) +select x, x * 2 from generate_series(1, 10) x; +alter table testgen.t1 add constraint chk_gen_clause_equal check (b = a * 2); +alter table testgen.t1 alter column b + add generated always stored using constraint chk_gen_clause_equal; +\d+ testgen.t1 +select a, b, a * 2 as expected, b = (a * 2) as correct +from testgen.t1 order by a; +drop table testgen.t1; + +-- turning a regular column into a stored generated column +-- fails when the constraint does not exist +create table testgen.t1 (a int, b int); +alter table testgen.t1 alter column b + add generated always stored using constraint chk_gen_clause_does_not_exist; +drop table testgen.t1; + +-- turning a regular column into a stored generated column +-- fails when the constraint does not exist. When the destination +-- column is NOT NULL, the error message mentions both constraint +-- shapes which would be valid +create table testgen.t1 (a int, b int not null); +alter table testgen.t1 alter column b + add generated always stored using constraint chk_gen_clause_does_not_exist; +drop table testgen.t1; + +-- turning a regular column into a stored generated column +-- fails when the constraint is not valid +create table testgen.t1 (a int, b int); +alter table testgen.t1 add constraint chk_gen_clause check (b is not distinct from a * 2) not valid; +alter table testgen.t1 alter column b + add generated always stored using constraint chk_gen_clause; +drop table testgen.t1; + +-- turning a regular column into a stored generated column +-- fails when the constraint is not enforced +create table testgen.t1 (a int, b int); +alter table testgen.t1 add constraint chk_gen_clause check (b is not distinct from a * 2) not enforced; +alter table testgen.t1 alter column b + add generated always stored using constraint chk_gen_clause; +drop table testgen.t1; + +-- turning a regular column into a stored generated column +-- without rewriting the table doesn't touch the index either +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 stored using constraint chk_gen_clause; +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 +-- fails when the constraint exists but doesn't have the expected shape +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 stored using constraint chk_gen_clause; +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; +-- test nulls, too +insert into testgen.t5 (a) values (null); +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 is not distinct from 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 doesn't need to rewrite the table thanks to +-- the constraint +begin; +alter table testgen.t5 alter column b + add generated always stored using constraint chk_gen_clause; +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; +select * from testgen.t5; +-- verify that it's still possible to insert rows (the trigger is still +-- running at this point) +insert into testgen.t5 (a) values (400); +drop trigger testgen_gen on testgen.t5; +drop function testgen.gen(); +insert into testgen.t5 (a) values (500); +\d+ testgen.t5 +select * from testgen.t5 order by a nulls first; +drop table testgen.t5; + +-- check that the table isn't being scanned during phase 3, even if other +-- objects depend on the column we are changing. Lowering client_min_messages +-- makes the message 'verifying table...' be shown here when that happens. +create table testgen.t6 (a int, b int not null); +insert into testgen.t6 (a, b) values (1, 2); +alter table testgen.t6 add constraint c1 check (b > 0); +alter table testgen.t6 add constraint c2 check (b = a * 2); +create index on testgen.t6 (b); +set client_min_messages = 'DEBUG1'; +alter table testgen.t6 alter b + add generated always stored using constraint c2; +-- we expect to *not* see a "verifying table" message here +reset client_min_messages; +drop table testgen.t6; + +-- test support for partitioned tables and inheritance +create table testgen.tpart (a int, b int) partition by hash (a); +alter table testgen.tpart + add constraint chk_gen_clause check (b is not distinct from a * 2); +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 * 2 from generate_series(1, 5) x; + +-- altering the parent table, recursing +begin; +alter table testgen.tpart alter column b + add generated always stored using constraint chk_gen_clause; +-- 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 stored using constraint chk_gen_clause; +rollback; + +-- altering only the parent table is not allowed +begin; +-- expected: error +alter table only testgen.tpart alter column b + add generated always stored using constraint chk_gen_clause; +rollback; + +drop table testgen.tpart; + +-- test support for inheritance and subpartitions +create table testgen.root (a int, b int, c int); +create table testgen.intermediate () inherits (testgen.root); +create table testgen.leaf () inherits (testgen.intermediate); +alter table testgen.tpart + add constraint chk_gen_clause check (b is not distinct from a + b); + +-- it's only allowed to change the whole hierarchy at once... +begin; +alter table testgen.root alter column c + add generated always stored using constraint chk_gen_clause; +rollback; + +-- ... hence all these should result in an error +begin; +alter table only testgen.root alter column c + add generated always stored using constraint chk_gen_clause; +rollback; +begin; +alter table testgen.intermediate alter column c + add generated always stored using constraint chk_gen_clause; +rollback; +begin; +alter table only testgen.intermediate alter column c + add generated always stored using constraint chk_gen_clause; +rollback; +begin; +alter table testgen.leaf alter column c + add generated always stored using constraint chk_gen_clause; +rollback; +begin; +alter table only testgen.leaf alter column c + add generated always stored using constraint chk_gen_clause; +rollback; + +drop table testgen.root cascade; + +-- tests for invalid invocations +alter table doesnotexist alter column foo + add generated always stored using constraint cdoesnotexist; + +create table testgen.t1 (a int); +alter table testgen.t1 add constraint chk_gen_clause check (1); + +alter table testgen.t1 alter column doesnotexist + add generated always stored using constraint chk_gen_clause; + +alter table testgen.t1 add column b int; + +-- invalid: only supports ALWAYS +alter table testgen.t1 alter column b + add generated by default stored using constraint chk_gen_clause; + +-- invalid: only supports STORED. These are all syntax errors. +alter table testgen.t1 alter column b add generated always; +alter table testgen.t1 alter column b add generated always virtual; +alter table testgen.t1 alter column b add generated always using constraint chk_gen_clause; +alter table testgen.t1 alter column b add generated always virtual using constraint chk_gen_clause; +drop table testgen.t1; + +-- 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 stored using constraint doesnotexist; +drop table testgen.t2; + +-- invalid: b is an identity column +create table testgen.t2 (a int, b int generated always as identity); +alter table testgen.t2 alter column b add generated always stored using constraint doesnotexist; +drop table testgen.t2; +create table testgen.t2 (a int, b int generated by default as identity ); +alter table testgen.t2 alter column b add generated always stored using constraint doesnotexist; +drop table testgen.t2; + +-- invalid: b is a serial column +create table testgen.t2 (a int, b bigserial); +alter table testgen.t2 add constraint chk_gen_clause check (b is not distinct from (1)); +alter table testgen.t2 alter column b add generated always stored using constraint chk_gen_clause; +drop table testgen.t2; + +-- invalid: c is referenced by another column's default expr +create table testgen.t3 (a int, b int generated always as (c + 1), c int); +alter table testgen.t3 add constraint chk_gen_clause check (c is not distinct from (1)); +alter table testgen.t3 alter column c add generated always stored using constraint chk_gen_clause; +drop table testgen.t3; + +-- invalid: c references another generated column +create table testgen.t3 (a int, b int generated always as (a + 1), c int); +alter table testgen.t3 add constraint chk_gen_clause check (c is not distinct from (b + 1)); +alter table testgen.t3 alter column c add generated always stored using constraint chk_gen_clause; +drop table testgen.t3; + +-- invalid: c is referenced in a partition key +create table testgen.t3 (a int, b int, c int) partition by hash (c); +alter table testgen.t3 alter column c add generated always stored using constraint doesnotexist; +drop table testgen.t3; +create table testgen.t3 (a int, b int, c int) partition by hash ((c)); +alter table testgen.t3 alter column c add generated always stored using constraint doesnotexist; +drop table testgen.t3; +-- test for a whole-row reference +-- since it's not possible to reference schema.table in partition by range, +-- temporarily hack the search_path +show search_path \gset +set search_path to testgen, public; +create table t3 (a int, b int, c int) partition by range ((t3)); +alter table testgen.t3 alter column c add generated always stored using constraint doesnotexist; +drop table testgen.t3; +create table t3 (a int, b int, c int) partition by range ((t3 is null)); +alter table testgen.t3 alter column c add generated always stored using constraint doesnotexist; +drop table testgen.t3; +set search_path to :search_path; + +create table testgen.t3 (a int, b int); +-- invalid: expr must be immutable +alter table testgen.t3 add constraint chk_gen_clause check (b is not distinct from (a + random()::int)); +alter table testgen.t3 alter column b + add generated always stored using constraint chk_gen_clause; +alter table testgen.t3 drop constraint chk_gen_clause; +drop table testgen.t3; + +drop schema testgen cascade; base-commit: c776550e4662385b0ebeac653ae86755008d29f3 -- 2.47.0
039_stored_generated_published.pl
Description: Perl program
040_stored_generated_not_published.pl
Description: Perl program
