Hi Laurenz, thanks for your feedback!
On Mon Aug 24, 2026 at 7:29 PM CEST, Laurenz Albe wrote: > On Thu, 2026-08-06 at 14:36 +0200, Alberto Piai wrote: >> PFA v7, implementing this version of the command: >> >> ... ALTER col ADD GENERATED USING CONSTRAINT constr_name STORED > > Great! I think this is pretty much good to go. > >> In particular, all the error messages now follow the guidelines for >> error reporting. I tried to use a consistent error message everywhere, >> adding details and hints where appropriate. > > Much better! > > I was wondering about this hint: > > Use this command on the root table/partition without ONLY. > > You can never turn a column of a partition into a generated column, right? > How about > > Use the command on the partition root without specifying ONLY. Since this is also taking care of classic inheritance, it seems strange to only mention partitions. But I see that this way it's confusing (and I do concede that partitioning might be the more widely used of the two) so I switched it around to "on the root partition/table without specifying ONLY". Does that make more sense? > This detail message is longer than 80 characters: > > Converting a column to a stored generated column can only be done on the > whole hierarchy at once. > > How about > > Converting only part of a partitioning/inheritance hierarchy is not > supported. Much better, fixed. > There is one sentence in the documentation that sounds wrong to me: > > + <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> > > Shouldn't it be "after this command has been run"? Or perhaps "has > completed"? Thanks, changed to "has completed". The attached v8 addresses the points above. Kind regards, Alberto -- Alberto Piai Sensational AG Zürich, Switzerland
>From 2bb097c055a69863b0bfa24d1b22a852451e110d Mon Sep 17 00:00:00 2001 From: Alberto Piai <[email protected]> Date: Wed, 5 Aug 2026 17:27:50 +0200 Subject: [PATCH v8] 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 USING CONSTRAINT constr_name STORED 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 matching the intended generation expression, and adding the constr_name 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: - since we are not talking about IDENTITY columns, ALWAYS is implicit and does not need to be explicitly supplied - STORED must always be supplied, to make it clear that we are talking about stored and not virtual generated columns (VIRTUAL is the default when omitted in other commands, so we mandate STORED here to be consistent) 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 | 37 ++ src/backend/commands/tablecmds.c | 583 ++++++++++++++++++ src/backend/parser/gram.y | 18 + src/bin/psql/t/010_tab_completion.pl | 19 + src/bin/psql/tab-complete.in.c | 43 +- src/include/nodes/parsenodes.h | 1 + src/test/modules/injection_points/Makefile | 2 +- .../injection_points/expected/alter_table.out | 36 ++ src/test/modules/injection_points/meson.build | 1 + .../injection_points/sql/alter_table.sql | 24 + .../test_ddl_deparse/test_ddl_deparse.c | 3 + src/test/regress/expected/alter_table.out | 463 ++++++++++++++ src/test/regress/sql/alter_table.sql | 297 +++++++++ src/tools/pgindent/typedefs.list | 1 + 14 files changed, 1523 insertions(+), 5 deletions(-) create mode 100644 src/test/modules/injection_points/expected/alter_table.out create mode 100644 src/test/modules/injection_points/sql/alter_table.sql diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index ff7071bef5b..4d2b214da31 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 USING CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> STORED 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,42 @@ 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 USING CONSTRAINT</literal></term> + <listitem> + <para> + This form changes a regular column into a stored generated column, using + the expression from the given <literal>CHECK</literal> constraint as + generation expression. The operation will be performed without rewriting + the table, which avoids holding an <literal>ACCESS EXCLUSIVE</literal> + lock for a possibly long time. + </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 has completed, <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 9b911310f05..6b3318d7992 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -101,6 +101,7 @@ #include "utils/acl.h" #include "utils/builtins.h" #include "utils/fmgroids.h" +#include "utils/injection_point.h" #include "utils/inval.h" #include "utils/lsyscache.h" #include "utils/memutils.h" @@ -335,6 +336,18 @@ struct DropRelationCallbackState char actual_relpersistence; }; +/* + * Used by findUsableConstraintForAddGenStored to give a hint about why no + * constraint matched. + */ +typedef enum AddGenConstrError +{ + ADD_GEN_CONSTR_NOT_FOUND = 0, + ADD_GEN_CONSTR_NOT_VALID, + ADD_GEN_CONSTR_SHAPE_MISMATCH, + ADD_GEN_CONSTR_TYPE_CAST, +} AddGenConstrError; + /* Alter table target-type flags for ATSimplePermissions */ #define ATT_TABLE 0x0001 #define ATT_VIEW 0x0002 @@ -792,6 +805,22 @@ static void ATExecMergePartitions(List **wqueue, AlteredTableInfo *tab, Relation static void ATExecSplitPartition(List **wqueue, AlteredTableInfo *tab, Relation rel, PartitionCmd *cmd, AlterTableUtilityContext *context); +static void ATPrepAddGenStored(Relation rel, AlterTableCmd *cmd, bool recurse, bool recursing); +static void checkDependenciesForAddGenStored(Relation rel, + AttrNumber attnum, + const char *colName); +static Node *matchBinaryOpOnVar(List *args, AttrNumber attnum, Oid opno, + AddGenConstrError *reason); +static Node *findUsableConstraintForAddGenStored(Relation rel, + AttrNumber attnum, + bool attisnotnull, + const char *conname, + AddGenConstrError *reason); +static Node *reconstructRawExpr(Relation rel, Node *cookedExpr); +static ObjectAddress ATExecAddGeneratedStored(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); @@ -4812,6 +4841,7 @@ AlterTableGetLockLevel(List *cmds) case AT_AddIdentity: case AT_DropIdentity: case AT_SetIdentity: + case AT_AddGeneratedStored: case AT_SetExpression: case AT_DropExpression: case AT_SetCompression: @@ -5136,6 +5166,14 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode, context); pass = AT_PASS_SET_EXPRESSION; break; + case AT_AddGeneratedStored: /* ALTER COLUMN ADD GENERATED USING + * CONSTRAINT */ + ATSimplePermissions(cmd->subtype, rel, + ATT_TABLE | ATT_PARTITIONED_TABLE | ATT_FOREIGN_TABLE); + ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode, context); + ATPrepAddGenStored(rel, cmd, recurse, recursing); + 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); @@ -5530,6 +5568,12 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, case AT_SetExpression: address = ATExecSetExpression(tab, rel, cmd->name, cmd->def, lockmode); break; + case AT_AddGeneratedStored: + Assert(IsA(cmd->def, Constraint)); + address = ATExecAddGeneratedStored(tab, rel, + cmd->name, + (Constraint *) cmd->def); + break; case AT_DropExpression: address = ATExecDropExpression(rel, cmd->name, cmd->missing_ok, lockmode); break; @@ -6412,13 +6456,23 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap) } if (newrel) + { ereport(DEBUG1, (errmsg_internal("rewriting table \"%s\"", RelationGetRelationName(oldrel)))); +#ifdef USE_INJECTION_POINTS + INJECTION_POINT("alter-table-phase-3-rewrite", NULL); +#endif + } else + { ereport(DEBUG1, (errmsg_internal("verifying table \"%s\"", RelationGetRelationName(oldrel)))); +#ifdef USE_INJECTION_POINTS + INJECTION_POINT("alter-table-phase-3-verify", NULL); +#endif + } if (newrel) { @@ -6738,6 +6792,8 @@ alter_table_type_to_string(AlterTableType cmdtype) return "ALTER COLUMN ... SET NOT NULL"; case AT_SetExpression: return "ALTER COLUMN ... SET EXPRESSION"; + case AT_AddGeneratedStored: + return "ALTER COLUMN ... ADD GENERATED STORED"; case AT_DropExpression: return "ALTER COLUMN ... DROP EXPRESSION"; case AT_SetStatistics: @@ -8897,6 +8953,533 @@ ATExecSetExpression(AlteredTableInfo *tab, Relation rel, const char *colName, return address; } +/* + * Preparation phase for + * + * ALTER COLUMN col ADD GENERATED USING CONSTRAINT name STORED + * + * In an inheritance hierarchy, it is only valid to alter the type of the + * whole hierarchy at once. + */ +static void +ATPrepAddGenStored(Relation rel, AlterTableCmd *cmd, bool recurse, bool recursing) +{ + /* + * This routine is called on the top table directly with recursing=false, + * and on all children tables via ATSimpleRecursion with recursing=true. + * + * At the top level, forbid ONLY (i.e. recurse=false) if there are child + * tables. We only check this at the top level, otherwise we would prevent + * this operation from being applied to hierarchies with depth > 2. + * + * Note that when we're called with ONLY, ATSimpleRecursion hasn't seen + * any child rel yet, but having find_inheritance_children acquire locks + * is not necessary. If it found *any* child rel, we'd anyway error out. + */ + if (!recursing && !recurse && + find_inheritance_children(RelationGetRelid(rel), NoLock)) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot convert column \"%s\" to generated", cmd->name), + errdetail("Converting only part of a partitioning/inheritance hierarchy is not supported."), + errhint("Use this command on the root partition/table without specifying ONLY.")); + + /* + * Don't allow this operation to be applied to inherited columns directly. + */ + 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_FEATURE_NOT_SUPPORTED), + errmsg("cannot convert column \"%s\" to generated", cmd->name), + errdetail("Converting only part of a partitioning/inheritance hierarchy is not supported."), + errhint("Use this command on the root partition/table without specifying ONLY.")); + } +} + +/* + * Detect dependencies which should stop us from turning a regular column + * into a stored generated column. + */ +static void +checkDependenciesForAddGenStored(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); + + /* + * While it is possible to alter any sequence to be owned + * by an arbitrary column, the most likely legitimate use + * is for a serial column. Let's assume this is the case + * for the sake of a more helpful error message. + */ + if (relKind == RELKIND_SEQUENCE) + ereport(ERROR, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("cannot convert column \"%s\" to generated", colName), + errdetail("Column \"%s\" of relation \"%s\" is a serial column (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 column \"%s\" to generated", colName), + errdetail("Column \"%s\" is referenced by generated column \"%s\".", + colName, + get_attname(col.objectId, col.objectSubId, false)))); + } + break; + } + default: + /* Other dependencies aren't a problem. */ + break; + } + } + + systable_endscan(scan); + table_close(pg_depend, NoLock); +} + +/* + * Given a list of two nodes (operands of a binary function), an operator and a + * column, this matches when the operator is an equality and one of the two + * operands is a Var referencing the given column. + * + * It returns the expression tree of the other operand. + */ +static Node * +matchBinaryOpOnVar(List *args, AttrNumber attnum, Oid opno, + AddGenConstrError *reason) +{ + Node *left, + *right; + + Assert(list_length(args) == 2); + + /* Support both orders of the operands */ + if (IsA(linitial(args), Var)) + { + left = linitial(args); + right = lsecond(args); + } + else + { + right = linitial(args); + left = lsecond(args); + } + + if (IsA(left, Var)) + { + Var *var = (Var *) left; + + if (var->varattno == attnum && + op_mergejoinable(opno, exprType((Node *) var))) + return right; + } + + /* + * If we get here, the expression didn't match. Let's try to give a more + * specific reason why this was the case. + */ + + /* + * If one side of the operation is an implicit cast, we can guess that we + * are dealing with a constraint like CHECK (column = other_column + f()) + * where f() returns a different type than type of the column, and there + * is an implicit cast between the two types. It's easy to make this + * mistake, so let's try to give a helpful error message. + */ + if (IsA(left, FuncExpr) || IsA(right, FuncExpr)) + { + FuncExpr *fExpr = (FuncExpr *) (IsA(left, FuncExpr) ? left : right); + + if (list_length(fExpr->args) == 1 && + fExpr->funcformat == COERCE_IMPLICIT_CAST) + { + *reason = ADD_GEN_CONSTR_TYPE_CAST; + } + } + + return NULL; +} + +/* + * Subroutine for ATExecAddGeneratedStored, used to determine whether the given + * constraint proves that the values are equal to some 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 the expr node, otherwise + * it returns null and sets an error code in *reason, allowing the caller to + * provide an appropriate error message. + */ +static Node * +findUsableConstraintForAddGenStored(Relation rel, AttrNumber attnum, + bool attisnotnull, const char *conname, + AddGenConstrError *reason) +{ + Relation pg_constraint; + HeapTuple conTup; + SysScanDesc scan; + ScanKeyData key[3]; + Node *foundExpr; + + pg_constraint = table_open(ConstraintRelationId, AccessShareLock); + ScanKeyInit(&key[0], + Anum_pg_constraint_conrelid, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(rel->rd_id)); + ScanKeyInit(&key[1], + Anum_pg_constraint_contypid, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(InvalidOid)); + ScanKeyInit(&key[2], + Anum_pg_constraint_conname, + BTEqualStrategyNumber, F_NAMEEQ, + CStringGetDatum(conname)); + scan = systable_beginscan(pg_constraint, ConstraintRelidTypidNameIndexId, + true, NULL, 3, key); + + foundExpr = NULL; + *reason = ADD_GEN_CONSTR_NOT_FOUND; + + while (HeapTupleIsValid(conTup = systable_getnext(scan))) + { + Form_pg_constraint con = GETSTRUCT(conTup); + char *conbin; + Datum val; + Node *conexpr; + + if (con->contype != CONSTRAINT_CHECK) + continue; + /* !conenforced implies !convalidated, but let's be explicit about it */ + if (!con->convalidated || !con->conenforced) + { + *reason = ADD_GEN_CONSTR_NOT_VALID; + continue; + } + + val = SysCacheGetAttrNotNull(CONSTROID, conTup, + Anum_pg_constraint_conbin); + conbin = TextDatumGetCString(val); + conexpr = stringToNode(conbin); + + *reason = ADD_GEN_CONSTR_SHAPE_MISMATCH; + + /* 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); + + foundExpr = matchBinaryOpOnVar(dist->args, attnum, dist->opno, reason); + if (foundExpr) + break; + } + } + /* If the column is NOT NULL, try to match = as well */ + else if (attisnotnull && IsA(conexpr, OpExpr)) + { + OpExpr *op = (OpExpr *) conexpr; + + if (list_length(op->args) == 2) + { + foundExpr = matchBinaryOpOnVar(op->args, attnum, op->opno, reason); + if (foundExpr) + 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 USING CONSTRAINT name STORED + * + * 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 + * findUsableConstraintForAddGenStored). + */ +static ObjectAddress +ATExecAddGeneratedStored(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; + AddGenConstrError reason; + 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 column \"%s\" to generated", colName), + 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("cannot convert column \"%s\" to generated", colName), + errdetail("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 column \"%s\" to generated", colName), + errdetail("Column \"%s\" is referenced in the partition key of relation \"%s\".", + colName, RelationGetRelationName(rel)))); + + checkDependenciesForAddGenStored(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 = findUsableConstraintForAddGenStored(rel, attnum, + attTup->attnotnull, + def->conname, &reason); + if (foundConstraintExpr == NULL) + { + if (reason == ADD_GEN_CONSTR_NOT_VALID) + ereport(ERROR, + errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("cannot convert column \"%s\" to generated", colName), + errdetail("The constraint \"%s\" is not valid.", def->conname)); + if (reason == ADD_GEN_CONSTR_SHAPE_MISMATCH || reason == ADD_GEN_CONSTR_TYPE_CAST) + ereport(ERROR, + errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("cannot convert column \"%s\" to generated", colName), + 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), + reason == ADD_GEN_CONSTR_TYPE_CAST ? + errhint("Ensure that the type of the expression matches the type of the column.") : 0); + ereport(ERROR, + errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("cannot convert column \"%s\" to generated", colName), + errdetail("Could not find CHECK constraint \"%s\".", def->conname)); + } + + /* 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 17035fb4d15..5eeb6ebfe70 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -2735,6 +2735,24 @@ alter_table_cmd: n->name = $3; n->def = (Node *) c; + $$ = (Node *) n; + } + /* ALTER TABLE <name> ALTER [COLUMN] <colname> ADD GENERATED USING CONSTRAINT constraint_name STORED */ + | ALTER opt_column ColId ADD_P GENERATED USING CONSTRAINT name STORED + { + AlterTableCmd *n = makeNode(AlterTableCmd); + Constraint *c = makeNode(Constraint); + + c->conname = $8; + c->contype = CONSTR_GENERATED; + c->generated_when = ATTRIBUTE_IDENTITY_ALWAYS; + c->generated_kind = ATTRIBUTE_GENERATED_STORED; + c->location = @8; + + n->subtype = AT_AddGeneratedStored; + 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..edca2d019a9 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("U\t", qr/USING CONSTRAINT /, + "complete ALTER COLUMN <col> ADD GENERATED USING CONSTRAINT"); + +check_completion("\t", qr/check_gen /, + "complete ALTER COLUMN <col> ADD GENERATED USING CONSTRAINT offers check constraint names"); + +check_completion("S\t", qr/STORED /, + "complete ALTER COLUMN <col> ADD GENERATED USING CONSTRAINT constr_name STORED"); + +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 190fff7ea0e..620f9b5e566 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", @@ -2972,13 +2981,39 @@ match_previous_words(int pattern_id, /* ALTER TABLE ALTER [COLUMN] <foo> ADD GENERATED */ 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 */ + COMPLETE_WITH("ALWAYS", "BY DEFAULT", "USING CONSTRAINT"); + /* 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"); + /* 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 USING CONSTRAINT */ + else if (Matches("ALTER", "TABLE", MatchAny, "ALTER", "COLUMN", MatchAny, "ADD", "GENERATED", "USING", "CONSTRAINT")) + { + set_completion_reference(prev8_wd); + COMPLETE_WITH_SCHEMA_QUERY(Query_for_check_constraint_of_table); + } + + /* + * ALTER TABLE ALTER [COLUMN] <foo> ADD GENERATED USING CONSTRAINT + */ + else if (Matches("ALTER", "TABLE", MatchAny, "ALTER", MatchAny, "ADD", "GENERATED", "USING", "CONSTRAINT")) + { + set_completion_reference(prev7_wd); + COMPLETE_WITH_SCHEMA_QUERY(Query_for_check_constraint_of_table); + } + + /* + * ALTER TABLE ALTER [COLUMN] <foo> ADD GENERATED USING CONSTRAINT + * constr_name + */ + else if (Matches("ALTER", "TABLE", MatchAny, "ALTER", "COLUMN", MatchAny, "ADD", "GENERATED", "USING", "CONSTRAINT", MatchAny)) + COMPLETE_WITH("STORED"); + else if (Matches("ALTER", "TABLE", MatchAny, "ALTER", MatchAny, "ADD", "GENERATED", "USING", "CONSTRAINT", MatchAny)) + COMPLETE_WITH("STORED"); /* 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 8a9df884276..48a9e781469 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -2566,6 +2566,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_AddGeneratedStored, /* add generated 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/injection_points/Makefile b/src/test/modules/injection_points/Makefile index 3b136adf126..ba9a54b29df 100644 --- a/src/test/modules/injection_points/Makefile +++ b/src/test/modules/injection_points/Makefile @@ -9,7 +9,7 @@ EXTENSION = injection_points DATA = injection_points--1.0.sql PGFILEDESC = "injection_points - facility for injection points" -REGRESS = injection_points hashagg reindex_conc vacuum +REGRESS = injection_points hashagg reindex_conc vacuum alter_table REGRESS_OPTS = --dlpath=$(top_builddir)/src/test/regress ISOLATION = basic \ diff --git a/src/test/modules/injection_points/expected/alter_table.out b/src/test/modules/injection_points/expected/alter_table.out new file mode 100644 index 00000000000..427f1d1d3ab --- /dev/null +++ b/src/test/modules/injection_points/expected/alter_table.out @@ -0,0 +1,36 @@ +-- Tests for ALTER TABLE +CREATE EXTENSION injection_points; +SELECT injection_points_set_local(); + injection_points_set_local +---------------------------- + +(1 row) + +SELECT injection_points_attach('alter-table-phase-3-rewrite', 'notice'); + injection_points_attach +------------------------- + +(1 row) + +SELECT injection_points_attach('alter-table-phase-3-verify', 'notice'); + injection_points_attach +------------------------- + +(1 row) + +CREATE SCHEMA testgen_inj; +-- Check that the table isn't being rewritten nor scanned during phase 3, +-- even if other objects depend on the column we are changing. +CREATE TABLE testgen_inj.t1 (a INT, b INT NOT NULL); +INSERT INTO testgen_inj.t1 (a, b) VALUES (1, 2); +ALTER TABLE testgen_inj.t1 ADD CONSTRAINT c1 CHECK (b > 0); +NOTICE: notice triggered for injection point alter-table-phase-3-verify +ALTER TABLE testgen_inj.t1 ADD CONSTRAINT c2 CHECK (b = a * 2); +NOTICE: notice triggered for injection point alter-table-phase-3-verify +CREATE INDEX ON testgen_inj.t1 (b); +ALTER TABLE testgen_inj.t1 ALTER b + ADD GENERATED USING CONSTRAINT c2 STORED; +-- we expect to *not* see a "alter-table-phase-3-*" notice here +DROP TABLE testgen_inj.t1; +DROP SCHEMA testgen_inj; +DROP EXTENSION injection_points; diff --git a/src/test/modules/injection_points/meson.build b/src/test/modules/injection_points/meson.build index aff516b901a..2a98a1d5a4d 100644 --- a/src/test/modules/injection_points/meson.build +++ b/src/test/modules/injection_points/meson.build @@ -36,6 +36,7 @@ tests += { 'hashagg', 'reindex_conc', 'vacuum', + 'alter_table', ], 'regress_args': ['--dlpath', meson.project_build_root() / 'src/test/regress'], # The injection points are cluster-wide, so disable installcheck diff --git a/src/test/modules/injection_points/sql/alter_table.sql b/src/test/modules/injection_points/sql/alter_table.sql new file mode 100644 index 00000000000..92393d70f35 --- /dev/null +++ b/src/test/modules/injection_points/sql/alter_table.sql @@ -0,0 +1,24 @@ +-- Tests for ALTER TABLE +CREATE EXTENSION injection_points; + +SELECT injection_points_set_local(); + +SELECT injection_points_attach('alter-table-phase-3-rewrite', 'notice'); +SELECT injection_points_attach('alter-table-phase-3-verify', 'notice'); + +CREATE SCHEMA testgen_inj; + +-- Check that the table isn't being rewritten nor scanned during phase 3, +-- even if other objects depend on the column we are changing. +CREATE TABLE testgen_inj.t1 (a INT, b INT NOT NULL); +INSERT INTO testgen_inj.t1 (a, b) VALUES (1, 2); +ALTER TABLE testgen_inj.t1 ADD CONSTRAINT c1 CHECK (b > 0); +ALTER TABLE testgen_inj.t1 ADD CONSTRAINT c2 CHECK (b = a * 2); +CREATE INDEX ON testgen_inj.t1 (b); +ALTER TABLE testgen_inj.t1 ALTER b + ADD GENERATED USING CONSTRAINT c2 STORED; +-- we expect to *not* see a "alter-table-phase-3-*" notice here +DROP TABLE testgen_inj.t1; +DROP SCHEMA testgen_inj; + +DROP EXTENSION injection_points; 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..535d3ebad5f 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_AddGeneratedStored: + 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 e167a41ce79..b0565c3ef4f 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -4876,3 +4876,466 @@ drop publication pub1; drop schema alter1 cascade; drop schema alter2 cascade; NOTICE: drop cascades to table alter2.t1 +-- Tests for ALTER COLUMN ... ADD GENERATED USING CONSTRAINT name STORED +-- turning a regular column into a stored generated column without a rewrite +create schema tgen; +create table tgen.t1 (a int, b int); +insert into tgen.t1 (a, b) + select x, x * 2 from generate_series(1, 5) x; +begin; +alter table tgen.t1 add constraint chk_gen check (b is not distinct from a * 2); +alter table tgen.t1 alter column b + add generated using constraint chk_gen stored; +\d tgen.t1 + Table "tgen.t1" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+------------------------------------ + a | integer | | | + b | integer | | | generated always as (a * 2) stored +Check constraints: + "chk_gen" CHECK (NOT b IS DISTINCT FROM (a * 2)) + +insert into tgen.t1 (a, b) values (10, 20); +ERROR: cannot insert a non-DEFAULT value into column "b" +DETAIL: Column "b" is a generated column. +rollback; +-- test that we accept IS NOT DISTINCT FROM with the operands swapped, too +begin; +alter table tgen.t1 add constraint chk_gen check (a * 2 is not distinct from b); +alter table tgen.t1 alter column b + add generated using constraint chk_gen stored; +\d tgen.t1 + Table "tgen.t1" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+------------------------------------ + a | integer | | | + b | integer | | | generated always as (a * 2) stored +Check constraints: + "chk_gen" CHECK (NOT (a * 2) IS DISTINCT FROM b) + +insert into tgen.t1 (a, b) values (10, 20); +ERROR: cannot insert a non-DEFAULT value into column "b" +DETAIL: Column "b" is a generated column. +rollback; +-- when the target column is NOT NULL, we also accept = +begin; +alter table tgen.t1 alter b set not null; +alter table tgen.t1 add constraint chk_gen_equal check (b = a * 2); +alter table tgen.t1 alter column b + add generated using constraint chk_gen_equal stored; +\d tgen.t1 + Table "tgen.t1" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+------------------------------------ + a | integer | | | + b | integer | | not null | generated always as (a * 2) stored +Check constraints: + "chk_gen_equal" CHECK (b = (a * 2)) + +rollback; +-- test that we accept = with the operands swapped, too +begin; +alter table tgen.t1 alter b set not null; +alter table tgen.t1 add constraint chk_gen_equal check (a * 2 = b); +alter table tgen.t1 alter column b + add generated using constraint chk_gen_equal stored; +\d tgen.t1 + Table "tgen.t1" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+------------------------------------ + a | integer | | | + b | integer | | not null | generated always as (a * 2) stored +Check constraints: + "chk_gen_equal" CHECK ((a * 2) = b) + +rollback; +-- check that neither the table nor indexes are rewritten +begin; +alter table tgen.t1 + add constraint chk_gen check (b is not distinct from a * 2); +create index t1_a_idx on tgen.t1 (a); +select pg_relation_filenode('tgen.t1') as t1_filenode_before \gset +select pg_relation_filenode('tgen.t1_a_idx') as t1_idx_filenode_before \gset +alter table tgen.t1 alter column b + add generated using constraint chk_gen stored; +select pg_relation_filenode('tgen.t1') as t1_filenode_after \gset +select pg_relation_filenode('tgen.t1_a_idx') as t1_idx_filenode_after \gset +select :t1_filenode_before = :t1_filenode_after as did_skip_table_rewrite, + :t1_idx_filenode_before = :t1_idx_filenode_after as did_skip_idx_rewrite; + did_skip_table_rewrite | did_skip_idx_rewrite +------------------------+---------------------- + t | t +(1 row) + +rollback; +-- test the whole process for adding a stored generated column without +-- long-lived exclusive locks +create table tgen.t2 (a int); +select pg_relation_filenode('tgen.t2') as t2_filenode_before \gset +insert into tgen.t2 select x from generate_series(1, 5) x; +-- test nulls, too +insert into tgen.t2 (a) values (null); +alter table tgen.t2 add column b int; +-- take care of new and updated columns +create function tgen.gen () returns trigger language plpgsql as $$ +begin + new.b = new.a * 2; return new; +end +$$; +create trigger tgen_trig + before insert or update on tgen.t2 + for each row execute function tgen.gen(); +-- add the constraint as not valid: enforced only for new and updated rows +begin; +alter table tgen.t2 + add constraint chk_gen check (b is not distinct from a * 2) not valid; +select locktype, mode from pg_locks + where relation = 'tgen.t2'::regclass and granted; + locktype | mode +----------+--------------------- + relation | AccessExclusiveLock +(1 row) + +commit; +insert into tgen.t2 (a) values (100), (200), (300); +-- backfill existing rows at the appropriate pace +update tgen.t2 set b = a * 2 where b is null; +-- validate: this scans the table, but without an exclusive lock +begin; +alter table tgen.t2 validate constraint chk_gen; +select locktype, mode from pg_locks + where relation = 'tgen.t2'::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 tgen.t2 alter column b + add generated using constraint chk_gen stored; +select locktype, mode from pg_locks +where relation = 'tgen.t2'::regclass and granted; + locktype | mode +----------+--------------------- + relation | AccessExclusiveLock +(1 row) + +commit; +select pg_relation_filenode('tgen.t2') as t2_filenode_after \gset +select :t2_filenode_before = :t2_filenode_after as did_skip_rewrite; + did_skip_rewrite +------------------ + t +(1 row) + +select * from tgen.t2; + 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 +-- installed at this point) +insert into tgen.t2 (a) values (400); +drop trigger tgen_trig on tgen.t2; +drop function tgen.gen(); +insert into tgen.t2 (a) values (500); +\d tgen.t2 + Table "tgen.t2" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+------------------------------------ + a | integer | | | + b | integer | | | generated always as (a * 2) stored +Check constraints: + "chk_gen" CHECK (NOT b IS DISTINCT FROM (a * 2)) + +select * from tgen.t2 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 tgen.t2; +-- test support for partitioned tables +create table tgen.tpart (a int, b int) partition by hash (a); +alter table tgen.tpart + add constraint chk_gen check (b is not distinct from a * 2); +create table tgen.tpart_p1 partition of tgen.tpart + for values with (modulus 2, remainder 0); +create table tgen.tpart_p2 partition of tgen.tpart + for values with (modulus 2, remainder 1); +insert into tgen.tpart (a, b) select x, x * 2 from generate_series(1, 5) x; +-- altering the parent table, recursing +begin; +alter table tgen.tpart alter column b + add generated using constraint chk_gen stored; +-- expected: all the partitions have been changed +\d tgen.tpart + Partitioned table "tgen.tpart" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+------------------------------------ + a | integer | | | + b | integer | | | generated always as (a * 2) stored +Partition key: HASH (a) +Check constraints: + "chk_gen" CHECK (NOT b IS DISTINCT FROM (a * 2)) +Number of partitions: 2 (Use \d+ to list them.) + +\d tgen.tpart_p1 + Table "tgen.tpart_p1" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+------------------------------------ + a | integer | | | + b | integer | | | generated always as (a * 2) stored +Partition of: tgen.tpart FOR VALUES WITH (modulus 2, remainder 0) +Check constraints: + "chk_gen" CHECK (NOT b IS DISTINCT FROM (a * 2)) + +\d tgen.tpart_p2 + Table "tgen.tpart_p2" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+------------------------------------ + a | integer | | | + b | integer | | | generated always as (a * 2) stored +Partition of: tgen.tpart FOR VALUES WITH (modulus 2, remainder 1) +Check constraints: + "chk_gen" CHECK (NOT b IS DISTINCT FROM (a * 2)) + +rollback; +-- altering a single partition is not allowed +alter table tgen.tpart_p1 alter column b + add generated using constraint chk_gen stored; +ERROR: cannot convert column "b" to generated +DETAIL: Converting only part of a partitioning/inheritance hierarchy is not supported. +HINT: Use this command on the root partition/table without specifying ONLY. +-- altering only the parent table is not allowed +alter table only tgen.tpart alter column b + add generated using constraint chk_gen stored; +ERROR: cannot convert column "b" to generated +DETAIL: Converting only part of a partitioning/inheritance hierarchy is not supported. +HINT: Use this command on the root partition/table without specifying ONLY. +drop table tgen.tpart; +-- test support for inheritance and subpartitions +create table tgen.root (a int, b int, c int); +create table tgen.intermediate () inherits (tgen.root); +create table tgen.leaf () inherits (tgen.intermediate); +alter table tgen.tpart + add constraint chk_gen check (b is not distinct from a + b); +ERROR: relation "tgen.tpart" does not exist +-- it's only allowed to change the whole hierarchy at once... +begin; +alter table tgen.root alter column c + add generated using constraint chk_gen stored; +ERROR: cannot convert column "c" to generated +DETAIL: Could not find CHECK constraint "chk_gen". +rollback; +-- ... hence all these should result in an error +alter table only tgen.root alter column c + add generated using constraint chk_gen stored; +ERROR: cannot convert column "c" to generated +DETAIL: Converting only part of a partitioning/inheritance hierarchy is not supported. +HINT: Use this command on the root partition/table without specifying ONLY. +alter table tgen.intermediate alter column c + add generated using constraint chk_gen stored; +ERROR: cannot convert column "c" to generated +DETAIL: Converting only part of a partitioning/inheritance hierarchy is not supported. +HINT: Use this command on the root partition/table without specifying ONLY. +alter table only tgen.intermediate alter column c + add generated using constraint chk_gen stored; +ERROR: cannot convert column "c" to generated +DETAIL: Converting only part of a partitioning/inheritance hierarchy is not supported. +HINT: Use this command on the root partition/table without specifying ONLY. +alter table tgen.leaf alter column c + add generated using constraint chk_gen stored; +ERROR: cannot convert column "c" to generated +DETAIL: Converting only part of a partitioning/inheritance hierarchy is not supported. +HINT: Use this command on the root partition/table without specifying ONLY. +alter table only tgen.leaf alter column c + add generated using constraint chk_gen stored; +ERROR: cannot convert column "c" to generated +DETAIL: Converting only part of a partitioning/inheritance hierarchy is not supported. +HINT: Use this command on the root partition/table without specifying ONLY. +drop table tgen.root cascade; +NOTICE: drop cascades to 2 other objects +DETAIL: drop cascades to table tgen.intermediate +drop cascades to table tgen.leaf +-- tests for invalid invocations +alter table tgen.t1 alter column b + add generated using constraint cdoesnotexist stored; +ERROR: cannot convert column "b" to generated +DETAIL: Could not find CHECK constraint "cdoesnotexist". +-- invalid: only supports STORED. The following are syntax errors. +alter table tgen.t1 alter column b add generated using constraint chk_gen; +ERROR: syntax error at or near ";" +LINE 1: ...en.t1 alter column b add generated using constraint chk_gen; + ^ +alter table tgen.t1 alter column b add generated using constraint chk_gen virtual; +ERROR: syntax error at or near "virtual" +LINE 1: ...ter column b add generated using constraint chk_gen virtual; + ^ +-- invalid: b is already a generated column +begin; +alter table tgen.t1 alter b add generated using constraint chk_gen stored; +ERROR: cannot convert column "b" to generated +DETAIL: Could not find CHECK constraint "chk_gen". +alter table tgen.t1 alter b add generated using constraint chk_gen stored; +ERROR: current transaction is aborted, commands ignored until end of transaction block +rollback; +-- invalid: b is already a generated column +create table tgen.t2 (a int, b int not null generated always as (a * 2) stored); +alter table tgen.t2 add constraint chk_gen check (b = a * 2); +alter table tgen.t2 alter column b add generated using constraint chk_gen stored; +ERROR: cannot convert column "b" to generated +DETAIL: Column "b" of relation "t2" is already a generated column. +drop table tgen.t2; +-- invalid: b is an identity column +create table tgen.t2 (a int, b int generated always as identity); +alter table tgen.t2 alter column b add generated using constraint doesnotexist stored; +ERROR: cannot convert column "b" to generated +DETAIL: Column "b" of relation "t2" is an identity column. +drop table tgen.t2; +create table tgen.t2 (a int, b int generated by default as identity ); +alter table tgen.t2 alter column b add generated using constraint doesnotexist stored; +ERROR: cannot convert column "b" to generated +DETAIL: Column "b" of relation "t2" is an identity column. +drop table tgen.t2; +-- invalid: b is a serial column +create table tgen.t2 (a int, b bigserial); +alter table tgen.t2 add constraint chk_gen check (b is not distinct from (1)); +alter table tgen.t2 alter column b add generated using constraint chk_gen stored; +ERROR: cannot convert column "b" to generated +DETAIL: Column "b" of relation "t2" is a serial column (depends on sequence "sequence tgen.t2_b_seq"). +drop table tgen.t2; +-- invalid: c is referenced by another generated column +create table tgen.t2 (a int, b int generated always as (c + 1), c int); +alter table tgen.t2 add constraint chk_gen check (c is not distinct from (1)); +alter table tgen.t2 alter column c add generated using constraint chk_gen stored; +ERROR: cannot convert column "c" to generated +DETAIL: Column "c" is referenced by generated column "b". +drop table tgen.t2; +-- invalid: c references another generated column +create table tgen.t2 (a int, b int generated always as (a + 1), c int); +alter table tgen.t2 add constraint chk_gen check (c is not distinct from (b + 1)); +alter table tgen.t2 alter column c add generated using constraint chk_gen stored; +ERROR: cannot use generated column "b" in column generation expression +DETAIL: A generated column cannot reference another generated column. +drop table tgen.t2; +-- invalid: b is referenced in a partition key +create table tgen.t2 (a int, b int not null) partition by hash (b); +alter table tgen.t2 add constraint chk_gen check (b = a + 1); +alter table tgen.t2 alter b add generated using constraint chk_gen stored; +ERROR: cannot convert column "b" to generated +DETAIL: Column "b" is referenced in the partition key of relation "t2". +drop table tgen.t2; +create table tgen.t2 (a int, b int) partition by hash (coalesce(b, 123)); +alter table tgen.t2 add constraint chk_gen check (b is not distinct from a + 1); +alter table tgen.t2 alter b add generated using constraint chk_gen stored; +ERROR: cannot convert column "b" to generated +DETAIL: Column "b" is referenced in the partition key of relation "t2". +drop table tgen.t2; +-- 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 tgen, public; +create table t2 (a int, b int) partition by range ((t2)); +alter table tgen.t2 add constraint chk_gen check (b is not distinct from a + 1); +alter table tgen.t2 alter b add generated using constraint chk_gen stored; +ERROR: cannot convert column "b" to generated +DETAIL: Column "b" is referenced in the partition key of relation "t2". +drop table tgen.t2; +create table t2 (a int, b int) partition by range ((t2 is null)); +alter table tgen.t2 add constraint chk_gen check (b is not distinct from a + 1); +alter table tgen.t2 alter b add generated using constraint chk_gen stored; +ERROR: cannot convert column "b" to generated +DETAIL: Column "b" is referenced in the partition key of relation "t2". +drop table tgen.t2; +set search_path to :search_path; +-- fails when the constraint does not exist +alter table tgen.t1 alter column b + add generated using constraint chk_gen_does_not_exist stored; +ERROR: cannot convert column "b" to generated +DETAIL: Could not find CHECK constraint "chk_gen_does_not_exist". +-- fails when the constraint is not valid +begin; +alter table tgen.t1 + add constraint chk_gen check (b is not distinct from a * 2) not valid; +alter table tgen.t1 alter column b + add generated using constraint chk_gen stored; +ERROR: cannot convert column "b" to generated +DETAIL: The constraint "chk_gen" is not valid. +rollback; +-- fails when the constraint is not enforced +begin; +alter table tgen.t1 + add constraint chk_gen check (b is not distinct from a * 2) not enforced; +alter table tgen.t1 alter column b + add generated using constraint chk_gen stored; +ERROR: cannot convert column "b" to generated +DETAIL: The constraint "chk_gen" is not valid. +rollback; +-- fails when the constraint exists but doesn't have the expected shape +-- for a nullable column: +begin; +alter table tgen.t1 add constraint chk_gen check (b = a * 2); +alter table tgen.t1 alter column b add generated using constraint chk_gen stored; +ERROR: cannot convert column "b" to generated +DETAIL: Could not find a valid constraint "chk_gen" CHECK ("b" IS NOT DISTINCT FROM expr). +rollback; +begin; +alter table tgen.t1 add constraint chk_gen check (b >= a * 2); +alter table tgen.t1 alter column b add generated using constraint chk_gen stored; +ERROR: cannot convert column "b" to generated +DETAIL: Could not find a valid constraint "chk_gen" CHECK ("b" IS NOT DISTINCT FROM expr). +rollback; +-- for a not null column: +begin; +alter table tgen.t1 alter b set not null; +alter table tgen.t1 add constraint chk_gen check (b >= a * 2); +alter table tgen.t1 alter column b add generated using constraint chk_gen stored; +ERROR: cannot convert column "b" to generated +DETAIL: Could not find a valid constraint "chk_gen" CHECK ("b" = expr) or CHECK ("b" IS NOT DISTINCT FROM expr). +rollback; +-- fails when the expression does not match the type of the column and is +-- implicitly cast +begin; +truncate tgen.t1; +alter table tgen.t1 add constraint chk_gen check (b is not distinct from (a + random())); +-- the hint should inform about the type cast +alter table tgen.t1 alter column b add generated using constraint chk_gen stored; +ERROR: cannot convert column "b" to generated +DETAIL: Could not find a valid constraint "chk_gen" CHECK ("b" IS NOT DISTINCT FROM expr). +HINT: Ensure that the type of the expression matches the type of the column. +rollback; +create table tgen.t2 (a int, b int); +-- invalid: expr must be immutable +-- (without the cast to int, the expression would return a float and wouldn't +-- match the type of the column b) +alter table tgen.t2 add constraint chk_gen check (b is not distinct from (a + random()::int)); +alter table tgen.t2 alter column b + add generated using constraint chk_gen stored; +ERROR: generation expression is not immutable +drop table tgen.t2; +drop table tgen.t1; +drop schema tgen; diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql index 9f6c2a4bb08..d1b684e8f00 100644 --- a/src/test/regress/sql/alter_table.sql +++ b/src/test/regress/sql/alter_table.sql @@ -3161,3 +3161,300 @@ alter table alter1.t1 set schema alter2; drop publication pub1; drop schema alter1 cascade; drop schema alter2 cascade; + +-- Tests for ALTER COLUMN ... ADD GENERATED USING CONSTRAINT name STORED +-- turning a regular column into a stored generated column without a rewrite +create schema tgen; + +create table tgen.t1 (a int, b int); +insert into tgen.t1 (a, b) + select x, x * 2 from generate_series(1, 5) x; + +begin; +alter table tgen.t1 add constraint chk_gen check (b is not distinct from a * 2); +alter table tgen.t1 alter column b + add generated using constraint chk_gen stored; +\d tgen.t1 +insert into tgen.t1 (a, b) values (10, 20); +rollback; + +-- test that we accept IS NOT DISTINCT FROM with the operands swapped, too +begin; +alter table tgen.t1 add constraint chk_gen check (a * 2 is not distinct from b); +alter table tgen.t1 alter column b + add generated using constraint chk_gen stored; +\d tgen.t1 +insert into tgen.t1 (a, b) values (10, 20); +rollback; + +-- when the target column is NOT NULL, we also accept = +begin; +alter table tgen.t1 alter b set not null; +alter table tgen.t1 add constraint chk_gen_equal check (b = a * 2); +alter table tgen.t1 alter column b + add generated using constraint chk_gen_equal stored; +\d tgen.t1 +rollback; +-- test that we accept = with the operands swapped, too +begin; +alter table tgen.t1 alter b set not null; +alter table tgen.t1 add constraint chk_gen_equal check (a * 2 = b); +alter table tgen.t1 alter column b + add generated using constraint chk_gen_equal stored; +\d tgen.t1 +rollback; + +-- check that neither the table nor indexes are rewritten +begin; +alter table tgen.t1 + add constraint chk_gen check (b is not distinct from a * 2); +create index t1_a_idx on tgen.t1 (a); +select pg_relation_filenode('tgen.t1') as t1_filenode_before \gset +select pg_relation_filenode('tgen.t1_a_idx') as t1_idx_filenode_before \gset +alter table tgen.t1 alter column b + add generated using constraint chk_gen stored; +select pg_relation_filenode('tgen.t1') as t1_filenode_after \gset +select pg_relation_filenode('tgen.t1_a_idx') as t1_idx_filenode_after \gset +select :t1_filenode_before = :t1_filenode_after as did_skip_table_rewrite, + :t1_idx_filenode_before = :t1_idx_filenode_after as did_skip_idx_rewrite; +rollback; + +-- test the whole process for adding a stored generated column without +-- long-lived exclusive locks +create table tgen.t2 (a int); +select pg_relation_filenode('tgen.t2') as t2_filenode_before \gset +insert into tgen.t2 select x from generate_series(1, 5) x; +-- test nulls, too +insert into tgen.t2 (a) values (null); +alter table tgen.t2 add column b int; +-- take care of new and updated columns +create function tgen.gen () returns trigger language plpgsql as $$ +begin + new.b = new.a * 2; return new; +end +$$; +create trigger tgen_trig + before insert or update on tgen.t2 + for each row execute function tgen.gen(); +-- add the constraint as not valid: enforced only for new and updated rows +begin; +alter table tgen.t2 + add constraint chk_gen check (b is not distinct from a * 2) not valid; +select locktype, mode from pg_locks + where relation = 'tgen.t2'::regclass and granted; +commit; +insert into tgen.t2 (a) values (100), (200), (300); +-- backfill existing rows at the appropriate pace +update tgen.t2 set b = a * 2 where b is null; +-- validate: this scans the table, but without an exclusive lock +begin; +alter table tgen.t2 validate constraint chk_gen; +select locktype, mode from pg_locks + where relation = 'tgen.t2'::regclass and granted; +commit; +-- now the schema update, which doesn't need to rewrite the table thanks to +-- the constraint +begin; +alter table tgen.t2 alter column b + add generated using constraint chk_gen stored; +select locktype, mode from pg_locks +where relation = 'tgen.t2'::regclass and granted; +commit; +select pg_relation_filenode('tgen.t2') as t2_filenode_after \gset +select :t2_filenode_before = :t2_filenode_after as did_skip_rewrite; +select * from tgen.t2; +-- verify that it's still possible to insert rows (the trigger is still +-- installed at this point) +insert into tgen.t2 (a) values (400); +drop trigger tgen_trig on tgen.t2; +drop function tgen.gen(); +insert into tgen.t2 (a) values (500); +\d tgen.t2 +select * from tgen.t2 order by a nulls first; +drop table tgen.t2; + +-- test support for partitioned tables +create table tgen.tpart (a int, b int) partition by hash (a); +alter table tgen.tpart + add constraint chk_gen check (b is not distinct from a * 2); +create table tgen.tpart_p1 partition of tgen.tpart + for values with (modulus 2, remainder 0); +create table tgen.tpart_p2 partition of tgen.tpart + for values with (modulus 2, remainder 1); +insert into tgen.tpart (a, b) select x, x * 2 from generate_series(1, 5) x; + +-- altering the parent table, recursing +begin; +alter table tgen.tpart alter column b + add generated using constraint chk_gen stored; +-- expected: all the partitions have been changed +\d tgen.tpart +\d tgen.tpart_p1 +\d tgen.tpart_p2 +rollback; + +-- altering a single partition is not allowed +alter table tgen.tpart_p1 alter column b + add generated using constraint chk_gen stored; + +-- altering only the parent table is not allowed +alter table only tgen.tpart alter column b + add generated using constraint chk_gen stored; + +drop table tgen.tpart; + +-- test support for inheritance and subpartitions +create table tgen.root (a int, b int, c int); +create table tgen.intermediate () inherits (tgen.root); +create table tgen.leaf () inherits (tgen.intermediate); +alter table tgen.tpart + add constraint chk_gen check (b is not distinct from a + b); + +-- it's only allowed to change the whole hierarchy at once... +begin; +alter table tgen.root alter column c + add generated using constraint chk_gen stored; +rollback; + +-- ... hence all these should result in an error +alter table only tgen.root alter column c + add generated using constraint chk_gen stored; +alter table tgen.intermediate alter column c + add generated using constraint chk_gen stored; +alter table only tgen.intermediate alter column c + add generated using constraint chk_gen stored; +alter table tgen.leaf alter column c + add generated using constraint chk_gen stored; +alter table only tgen.leaf alter column c + add generated using constraint chk_gen stored; + +drop table tgen.root cascade; + +-- tests for invalid invocations +alter table tgen.t1 alter column b + add generated using constraint cdoesnotexist stored; + +-- invalid: only supports STORED. The following are syntax errors. +alter table tgen.t1 alter column b add generated using constraint chk_gen; +alter table tgen.t1 alter column b add generated using constraint chk_gen virtual; + +-- invalid: b is already a generated column +begin; +alter table tgen.t1 alter b add generated using constraint chk_gen stored; +alter table tgen.t1 alter b add generated using constraint chk_gen stored; +rollback; + +-- invalid: b is already a generated column +create table tgen.t2 (a int, b int not null generated always as (a * 2) stored); +alter table tgen.t2 add constraint chk_gen check (b = a * 2); +alter table tgen.t2 alter column b add generated using constraint chk_gen stored; +drop table tgen.t2; + +-- invalid: b is an identity column +create table tgen.t2 (a int, b int generated always as identity); +alter table tgen.t2 alter column b add generated using constraint doesnotexist stored; +drop table tgen.t2; +create table tgen.t2 (a int, b int generated by default as identity ); +alter table tgen.t2 alter column b add generated using constraint doesnotexist stored; +drop table tgen.t2; + +-- invalid: b is a serial column +create table tgen.t2 (a int, b bigserial); +alter table tgen.t2 add constraint chk_gen check (b is not distinct from (1)); +alter table tgen.t2 alter column b add generated using constraint chk_gen stored; +drop table tgen.t2; + +-- invalid: c is referenced by another generated column +create table tgen.t2 (a int, b int generated always as (c + 1), c int); +alter table tgen.t2 add constraint chk_gen check (c is not distinct from (1)); +alter table tgen.t2 alter column c add generated using constraint chk_gen stored; +drop table tgen.t2; + +-- invalid: c references another generated column +create table tgen.t2 (a int, b int generated always as (a + 1), c int); +alter table tgen.t2 add constraint chk_gen check (c is not distinct from (b + 1)); +alter table tgen.t2 alter column c add generated using constraint chk_gen stored; +drop table tgen.t2; + +-- invalid: b is referenced in a partition key +create table tgen.t2 (a int, b int not null) partition by hash (b); +alter table tgen.t2 add constraint chk_gen check (b = a + 1); +alter table tgen.t2 alter b add generated using constraint chk_gen stored; +drop table tgen.t2; +create table tgen.t2 (a int, b int) partition by hash (coalesce(b, 123)); +alter table tgen.t2 add constraint chk_gen check (b is not distinct from a + 1); +alter table tgen.t2 alter b add generated using constraint chk_gen stored; +drop table tgen.t2; +-- 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 tgen, public; +create table t2 (a int, b int) partition by range ((t2)); +alter table tgen.t2 add constraint chk_gen check (b is not distinct from a + 1); +alter table tgen.t2 alter b add generated using constraint chk_gen stored; +drop table tgen.t2; +create table t2 (a int, b int) partition by range ((t2 is null)); +alter table tgen.t2 add constraint chk_gen check (b is not distinct from a + 1); +alter table tgen.t2 alter b add generated using constraint chk_gen stored; +drop table tgen.t2; +set search_path to :search_path; + +-- fails when the constraint does not exist +alter table tgen.t1 alter column b + add generated using constraint chk_gen_does_not_exist stored; + +-- fails when the constraint is not valid +begin; +alter table tgen.t1 + add constraint chk_gen check (b is not distinct from a * 2) not valid; +alter table tgen.t1 alter column b + add generated using constraint chk_gen stored; +rollback; + +-- fails when the constraint is not enforced +begin; +alter table tgen.t1 + add constraint chk_gen check (b is not distinct from a * 2) not enforced; +alter table tgen.t1 alter column b + add generated using constraint chk_gen stored; +rollback; + +-- fails when the constraint exists but doesn't have the expected shape +-- for a nullable column: +begin; +alter table tgen.t1 add constraint chk_gen check (b = a * 2); +alter table tgen.t1 alter column b add generated using constraint chk_gen stored; +rollback; +begin; +alter table tgen.t1 add constraint chk_gen check (b >= a * 2); +alter table tgen.t1 alter column b add generated using constraint chk_gen stored; +rollback; + +-- for a not null column: +begin; +alter table tgen.t1 alter b set not null; +alter table tgen.t1 add constraint chk_gen check (b >= a * 2); +alter table tgen.t1 alter column b add generated using constraint chk_gen stored; +rollback; + +-- fails when the expression does not match the type of the column and is +-- implicitly cast +begin; +truncate tgen.t1; +alter table tgen.t1 add constraint chk_gen check (b is not distinct from (a + random())); +-- the hint should inform about the type cast +alter table tgen.t1 alter column b add generated using constraint chk_gen stored; +rollback; + +create table tgen.t2 (a int, b int); +-- invalid: expr must be immutable +-- (without the cast to int, the expression would return a float and wouldn't +-- match the type of the column b) +alter table tgen.t2 add constraint chk_gen check (b is not distinct from (a + random()::int)); +alter table tgen.t2 alter column b + add generated using constraint chk_gen stored; +drop table tgen.t2; + +drop table tgen.t1; +drop schema tgen; diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index 6c366d3a523..263fedc72a7 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -27,6 +27,7 @@ AcquireSampleRowsFunc ActionList ActiveSnapshotElt AddForeignUpdateTargets_function +AddGenConstrError AddrInfo AffixNode AffixNodeData base-commit: 04a20a4a1b02ac12eecde28e441e0a20e5063382 -- 2.47.0
