Skip to content

Commit 5d06e99

Browse files
peterepetere
committed
ALTER TABLE command to change generation expression
This adds a new ALTER TABLE subcommand ALTER COLUMN ... SET EXPRESSION that changes the generation expression of a generated column. The syntax is not standard but was adapted from other SQL implementations. This command causes a table rewrite, using the usual ALTER TABLE mechanisms. The implementation is similar to and makes use of some of the infrastructure of the SET DATA TYPE subcommand (for example, rebuilding constraints and indexes afterwards). The new command requires a new pass in AlterTablePass, and the ADD COLUMN pass had to be moved earlier so that combinations of ADD COLUMN and SET EXPRESSION can work. Author: Amul Sul <sulamul@gmail.com> Discussion: https://www.postgresql.org/message-id/flat/CAAJ_b94yyJeGA-5M951_Lr+KfZokOp-2kXicpmEhi5FXhBeTog@mail.gmail.com
1 parent ae69c4f commit 5d06e99

File tree

8 files changed

+460
-80
lines changed

8 files changed

+460
-80
lines changed

‎doc/src/sgml/ref/alter_table.sgml

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -46,6 +46,7 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
4646
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET DEFAULT <replaceable class="parameter">expression</replaceable>
4747
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> DROP DEFAULT
4848
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> { SET | DROP } NOT NULL
49+
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET EXPRESSION AS ( <replaceable class="parameter">expression</replaceable> )
4950
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> DROP EXPRESSION [ IF EXISTS ]
5051
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <replaceable>sequence_options</replaceable> ) ]
5152
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> { SET GENERATED { ALWAYS | BY DEFAULT } | SET <replaceable>sequence_option</replaceable> | RESTART [ [ WITH ] <replaceable class="parameter">restart</replaceable> ] } [...]
@@ -256,6 +257,17 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
256257
</listitem>
257258
</varlistentry>
258259

260+
<varlistentry id="sql-altertable-desc-set-expression">
261+
<term><literal>SET EXPRESSION AS</literal></term>
262+
<listitem>
263+
<para>
264+
This form replaces the expression of a generated column. Existing data
265+
in the column is rewritten and all the future changes will apply the new
266+
generation expression.
267+
</para>
268+
</listitem>
269+
</varlistentry>
270+
259271
<varlistentry id="sql-altertable-desc-drop-expression">
260272
<term><literal>DROP EXPRESSION [ IF EXISTS ]</literal></term>
261273
<listitem>

‎src/backend/commands/tablecmds.c

Lines changed: 186 additions & 49 deletions
Original file line numberDiff line numberDiff line change
@@ -147,10 +147,11 @@ typedef enum AlterTablePass
147147
AT_PASS_UNSET = -1, /* UNSET will cause ERROR */
148148
AT_PASS_DROP, /* DROP (all flavors) */
149149
AT_PASS_ALTER_TYPE, /* ALTER COLUMN TYPE */
150+
AT_PASS_ADD_COL, /* ADD COLUMN */
151+
AT_PASS_SET_EXPRESSION, /* ALTER SET EXPRESSION */
150152
AT_PASS_OLD_INDEX, /* re-add existing indexes */
151153
AT_PASS_OLD_CONSTR, /* re-add existing constraints */
152154
/* We could support a RENAME COLUMN pass here, but not currently used */
153-
AT_PASS_ADD_COL, /* ADD COLUMN */
154155
AT_PASS_ADD_CONSTR, /* ADD constraints (initial examination) */
155156
AT_PASS_COL_ATTRS, /* set column attributes, eg NOT NULL */
156157
AT_PASS_ADD_INDEXCONSTR, /* ADD index-based constraints */
@@ -459,6 +460,8 @@ static ObjectAddress ATExecAddIdentity(Relation rel, const char *colName,
459460
static ObjectAddress ATExecSetIdentity(Relation rel, const char *colName,
460461
Node *def, LOCKMODE lockmode);
461462
static ObjectAddress ATExecDropIdentity(Relation rel, const char *colName, bool missing_ok, LOCKMODE lockmode);
463+
static ObjectAddress ATExecSetExpression(AlteredTableInfo *tab, Relation rel, const char *colName,
464+
Node *newExpr, LOCKMODE lockmode);
462465
static void ATPrepDropExpression(Relation rel, AlterTableCmd *cmd, bool recurse, bool recursing, LOCKMODE lockmode);
463466
static ObjectAddress ATExecDropExpression(Relation rel, const char *colName, bool missing_ok, LOCKMODE lockmode);
464467
static ObjectAddress ATExecSetStatistics(Relation rel, const char *colName, int16 colNum,
@@ -561,7 +564,7 @@ static void ATPrepAlterColumnType(List **wqueue,
561564
static bool ATColumnChangeRequiresRewrite(Node *expr, AttrNumber varattno);
562565
static ObjectAddress ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
563566
AlterTableCmd *cmd, LOCKMODE lockmode);
564-
static void RememberAllDependentForRebuilding(AlteredTableInfo *tab,
567+
static void RememberAllDependentForRebuilding(AlteredTableInfo *tab, AlterTableType subtype,
565568
Relation rel, AttrNumber attnum, const char *colName);
566569
static void RememberConstraintForRebuilding(Oid conoid, AlteredTableInfo *tab);
567570
static void RememberIndexForRebuilding(Oid indoid, AlteredTableInfo *tab);
@@ -4551,6 +4554,7 @@ AlterTableGetLockLevel(List *cmds)
45514554
case AT_AddIdentity:
45524555
case AT_DropIdentity:
45534556
case AT_SetIdentity:
4557+
case AT_SetExpression:
45544558
case AT_DropExpression:
45554559
case AT_SetCompression:
45564560
cmd_lockmode = AccessExclusiveLock;
@@ -4852,6 +4856,11 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
48524856
ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode, context);
48534857
pass = AT_PASS_COL_ATTRS;
48544858
break;
4859+
case AT_SetExpression: /* ALTER COLUMN SET EXPRESSION */
4860+
ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_FOREIGN_TABLE);
4861+
ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode, context);
4862+
pass = AT_PASS_SET_EXPRESSION;
4863+
break;
48554864
case AT_DropExpression: /* ALTER COLUMN DROP EXPRESSION */
48564865
ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_FOREIGN_TABLE);
48574866
ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode, context);
@@ -5153,11 +5162,11 @@ ATRewriteCatalogs(List **wqueue, LOCKMODE lockmode,
51535162
lockmode, pass, context);
51545163

51555164
/*
5156-
* After the ALTER TYPE pass, do cleanup work (this is not done in
5157-
* ATExecAlterColumnType since it should be done only once if
5158-
* multiple columns of a table are altered).
5165+
* After the ALTER TYPE or SET EXPRESSION pass, do cleanup work
5166+
* (this is not done in ATExecAlterColumnType since it should be
5167+
* done only once if multiple columns of a table are altered).
51595168
*/
5160-
if (pass == AT_PASS_ALTER_TYPE)
5169+
if (pass == AT_PASS_ALTER_TYPE || pass == AT_PASS_SET_EXPRESSION)
51615170
ATPostAlterTypeCleanup(wqueue, tab, lockmode);
51625171

51635172
if (tab->rel)
@@ -5236,6 +5245,9 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab,
52365245
case AT_SetAttNotNull: /* set pg_attribute.attnotnull */
52375246
address = ATExecSetAttNotNull(wqueue, rel, cmd->name, lockmode);
52385247
break;
5248+
case AT_SetExpression:
5249+
address = ATExecSetExpression(tab, rel, cmd->name, cmd->def, lockmode);
5250+
break;
52395251
case AT_DropExpression:
52405252
address = ATExecDropExpression(rel, cmd->name, cmd->missing_ok, lockmode);
52415253
break;
@@ -6363,6 +6375,8 @@ alter_table_type_to_string(AlterTableType cmdtype)
63636375
return "ALTER COLUMN ... SET NOT NULL";
63646376
case AT_SetAttNotNull:
63656377
return NULL; /* not real grammar */
6378+
case AT_SetExpression:
6379+
return "ALTER COLUMN ... SET EXPRESSION";
63666380
case AT_DropExpression:
63676381
return "ALTER COLUMN ... DROP EXPRESSION";
63686382
case AT_SetStatistics:
@@ -8013,10 +8027,11 @@ ATExecColumnDefault(Relation rel, const char *colName,
80138027
(errcode(ERRCODE_SYNTAX_ERROR),
80148028
errmsg("column \"%s\" of relation \"%s\" is a generated column",
80158029
colName, RelationGetRelationName(rel)),
8016-
newDefault || TupleDescAttr(tupdesc, attnum - 1)->attgenerated != ATTRIBUTE_GENERATED_STORED ? 0 :
8030+
newDefault ?
80178031
/* translator: %s is an SQL ALTER command */
8018-
errhint("Use %s instead.",
8019-
"ALTER TABLE ... ALTER COLUMN ... DROP EXPRESSION")));
8032+
errhint("Use %s instead.", "ALTER TABLE ... ALTER COLUMN ... SET EXPRESSION") :
8033+
(TupleDescAttr(tupdesc, attnum - 1)->attgenerated == ATTRIBUTE_GENERATED_STORED ?
8034+
errhint("Use %s instead.", "ALTER TABLE ... ALTER COLUMN ... DROP EXPRESSION") : 0)));
80208035

80218036
/*
80228037
* Remove any old default for the column. We use RESTRICT here for
@@ -8313,6 +8328,121 @@ ATExecDropIdentity(Relation rel, const char *colName, bool missing_ok, LOCKMODE
83138328
return address;
83148329
}
83158330

8331+
/*
8332+
* ALTER TABLE ALTER COLUMN SET EXPRESSION
8333+
*
8334+
* Return the address of the affected column.
8335+
*/
8336+
static ObjectAddress
8337+
ATExecSetExpression(AlteredTableInfo *tab, Relation rel, const char *colName,
8338+
Node *newExpr, LOCKMODE lockmode)
8339+
{
8340+
HeapTuple tuple;
8341+
Form_pg_attribute attTup;
8342+
AttrNumber attnum;
8343+
Oid attrdefoid;
8344+
ObjectAddress address;
8345+
Expr *defval;
8346+
NewColumnValue *newval;
8347+
RawColumnDefault *rawEnt;
8348+
8349+
tuple = SearchSysCacheAttName(RelationGetRelid(rel), colName);
8350+
if (!HeapTupleIsValid(tuple))
8351+
ereport(ERROR,
8352+
(errcode(ERRCODE_UNDEFINED_COLUMN),
8353+
errmsg("column \"%s\" of relation \"%s\" does not exist",
8354+
colName, RelationGetRelationName(rel))));
8355+
8356+
attTup = (Form_pg_attribute) GETSTRUCT(tuple);
8357+
attnum = attTup->attnum;
8358+
8359+
if (attnum <= 0)
8360+
ereport(ERROR,
8361+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
8362+
errmsg("cannot alter system column \"%s\"",
8363+
colName)));
8364+
8365+
if (attTup->attgenerated != ATTRIBUTE_GENERATED_STORED)
8366+
ereport(ERROR,
8367+
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
8368+
errmsg("column \"%s\" of relation \"%s\" is not a generated column",
8369+
colName, RelationGetRelationName(rel))));
8370+
ReleaseSysCache(tuple);
8371+
8372+
/*
8373+
* Clear all the missing values if we're rewriting the table, since this
8374+
* renders them pointless.
8375+
*/
8376+
RelationClearMissing(rel);
8377+
8378+
/* make sure we don't conflict with later attribute modifications */
8379+
CommandCounterIncrement();
8380+
8381+
/*
8382+
* Find everything that depends on the column (constraints, indexes, etc),
8383+
* and record enough information to let us recreate the objects after
8384+
* rewrite.
8385+
*/
8386+
RememberAllDependentForRebuilding(tab, AT_SetExpression, rel, attnum, colName);
8387+
8388+
/*
8389+
* Drop the dependency records of the GENERATED expression, in particular
8390+
* its INTERNAL dependency on the column, which would otherwise cause
8391+
* dependency.c to refuse to perform the deletion.
8392+
*/
8393+
attrdefoid = GetAttrDefaultOid(RelationGetRelid(rel), attnum);
8394+
if (!OidIsValid(attrdefoid))
8395+
elog(ERROR, "could not find attrdef tuple for relation %u attnum %d",
8396+
RelationGetRelid(rel), attnum);
8397+
(void) deleteDependencyRecordsFor(AttrDefaultRelationId, attrdefoid, false);
8398+
8399+
/* Make above changes visible */
8400+
CommandCounterIncrement();
8401+
8402+
/*
8403+
* Get rid of the GENERATED expression itself. We use RESTRICT here for
8404+
* safety, but at present we do not expect anything to depend on the
8405+
* expression.
8406+
*/
8407+
RemoveAttrDefault(RelationGetRelid(rel), attnum, DROP_RESTRICT,
8408+
false, false);
8409+
8410+
/* Prepare to store the new expression, in the catalogs */
8411+
rawEnt = (RawColumnDefault *) palloc(sizeof(RawColumnDefault));
8412+
rawEnt->attnum = attnum;
8413+
rawEnt->raw_default = newExpr;
8414+
rawEnt->missingMode = false;
8415+
rawEnt->generated = ATTRIBUTE_GENERATED_STORED;
8416+
8417+
/* Store the generated expression */
8418+
AddRelationNewConstraints(rel, list_make1(rawEnt), NIL,
8419+
false, true, false, NULL);
8420+
8421+
/* Make above new expression visible */
8422+
CommandCounterIncrement();
8423+
8424+
/* Prepare for table rewrite */
8425+
defval = (Expr *) build_column_default(rel, attnum);
8426+
8427+
newval = (NewColumnValue *) palloc0(sizeof(NewColumnValue));
8428+
newval->attnum = attnum;
8429+
newval->expr = expression_planner(defval);
8430+
newval->is_generated = true;
8431+
8432+
tab->newvals = lappend(tab->newvals, newval);
8433+
tab->rewrite |= AT_REWRITE_DEFAULT_VAL;
8434+
8435+
/* Drop any pg_statistic entry for the column */
8436+
RemoveStatistics(RelationGetRelid(rel), attnum);
8437+
8438+
InvokeObjectPostAlterHook(RelationRelationId,
8439+
RelationGetRelid(rel), attnum);
8440+
8441+
ObjectAddressSubSet(address, RelationRelationId,
8442+
RelationGetRelid(rel), attnum);
8443+
return address;
8444+
}
8445+
83168446
/*
83178447
* ALTER TABLE ALTER COLUMN DROP EXPRESSION
83188448
*/
@@ -13300,7 +13430,7 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
1330013430
* the info before executing ALTER TYPE, though, else the deparser will
1330113431
* get confused.
1330213432
*/
13303-
RememberAllDependentForRebuilding(tab, rel, attnum, colName);
13433+
RememberAllDependentForRebuilding(tab, AT_AlterColumnType, rel, attnum, colName);
1330413434

1330513435
/*
1330613436
* Now scan for dependencies of this column on other things. The only
@@ -13497,18 +13627,21 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
1349713627
}
1349813628

1349913629
/*
13500-
* Subroutine for ATExecAlterColumnType: Find everything that depends on the
13501-
* column (constraints, indexes, etc), and record enough information to let us
13502-
* recreate the objects.
13630+
* Subroutine for ATExecAlterColumnType and ATExecSetExpression: Find everything
13631+
* that depends on the column (constraints, indexes, etc), and record enough
13632+
* information to let us recreate the objects.
1350313633
*/
1350413634
static void
13505-
RememberAllDependentForRebuilding(AlteredTableInfo *tab, Relation rel, AttrNumber attnum, const char *colName)
13635+
RememberAllDependentForRebuilding(AlteredTableInfo *tab, AlterTableType subtype,
13636+
Relation rel, AttrNumber attnum, const char *colName)
1350613637
{
1350713638
Relation depRel;
1350813639
ScanKeyData key[3];
1350913640
SysScanDesc scan;
1351013641
HeapTuple depTup;
1351113642

13643+
Assert(subtype == AT_AlterColumnType || subtype == AT_SetExpression);
13644+
1351213645
depRel = table_open(DependRelationId, RowExclusiveLock);
1351313646

1351413647
ScanKeyInit(&key[0],
@@ -13572,12 +13705,13 @@ RememberAllDependentForRebuilding(AlteredTableInfo *tab, Relation rel, AttrNumbe
1357213705

1357313706
case OCLASS_REWRITE:
1357413707
/* XXX someday see if we can cope with revising views */
13575-
ereport(ERROR,
13576-
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
13577-
errmsg("cannot alter type of a column used by a view or rule"),
13578-
errdetail("%s depends on column \"%s\"",
13579-
getObjectDescription(&foundObject, false),
13580-
colName)));
13708+
if (subtype == AT_AlterColumnType)
13709+
ereport(ERROR,
13710+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
13711+
errmsg("cannot alter type of a column used by a view or rule"),
13712+
errdetail("%s depends on column \"%s\"",
13713+
getObjectDescription(&foundObject, false),
13714+
colName)));
1358113715
break;
1358213716

1358313717
case OCLASS_TRIGGER:
@@ -13591,12 +13725,13 @@ RememberAllDependentForRebuilding(AlteredTableInfo *tab, Relation rel, AttrNumbe
1359113725
* significant amount of new code. Since we can't easily tell
1359213726
* which case applies, we punt for both. FIXME someday.
1359313727
*/
13594-
ereport(ERROR,
13595-
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
13596-
errmsg("cannot alter type of a column used in a trigger definition"),
13597-
errdetail("%s depends on column \"%s\"",
13598-
getObjectDescription(&foundObject, false),
13599-
colName)));
13728+
if (subtype == AT_AlterColumnType)
13729+
ereport(ERROR,
13730+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
13731+
errmsg("cannot alter type of a column used in a trigger definition"),
13732+
errdetail("%s depends on column \"%s\"",
13733+
getObjectDescription(&foundObject, false),
13734+
colName)));
1360013735
break;
1360113736

1360213737
case OCLASS_POLICY:
@@ -13609,12 +13744,13 @@ RememberAllDependentForRebuilding(AlteredTableInfo *tab, Relation rel, AttrNumbe
1360913744
* easy enough to remove and recreate the policy; still, FIXME
1361013745
* someday.
1361113746
*/
13612-
ereport(ERROR,
13613-
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
13614-
errmsg("cannot alter type of a column used in a policy definition"),
13615-
errdetail("%s depends on column \"%s\"",
13616-
getObjectDescription(&foundObject, false),
13617-
colName)));
13747+
if (subtype == AT_AlterColumnType)
13748+
ereport(ERROR,
13749+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
13750+
errmsg("cannot alter type of a column used in a policy definition"),
13751+
errdetail("%s depends on column \"%s\"",
13752+
getObjectDescription(&foundObject, false),
13753+
colName)));
1361813754
break;
1361913755

1362013756
case OCLASS_DEFAULT:
@@ -13634,19 +13770,20 @@ RememberAllDependentForRebuilding(AlteredTableInfo *tab, Relation rel, AttrNumbe
1363413770
/*
1363513771
* This must be a reference from the expression of a
1363613772
* generated column elsewhere in the same table.
13637-
* Changing the type of a column that is used by a
13638-
* generated column is not allowed by SQL standard, so
13639-
* just punt for now. It might be doable with some
13640-
* thinking and effort.
13773+
* Changing the type/generated expression of a column
13774+
* that is used by a generated column is not allowed
13775+
* by SQL standard, so just punt for now. It might be
13776+
* doable with some thinking and effort.
1364113777
*/
13642-
ereport(ERROR,
13643-
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
13644-
errmsg("cannot alter type of a column used by a generated column"),
13645-
errdetail("Column \"%s\" is used by generated column \"%s\".",
13646-
colName,
13647-
get_attname(col.objectId,
13648-
col.objectSubId,
13649-
false))));
13778+
if (subtype == AT_AlterColumnType)
13779+
ereport(ERROR,
13780+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
13781+
errmsg("cannot alter type of a column used by a generated column"),
13782+
errdetail("Column \"%s\" is used by generated column \"%s\".",
13783+
colName,
13784+
get_attname(col.objectId,
13785+
col.objectSubId,
13786+
false))));
1365013787
}
1365113788
break;
1365213789
}
@@ -13863,11 +14000,11 @@ RememberStatisticsForRebuilding(Oid stxoid, AlteredTableInfo *tab)
1386314000
}
1386414001

1386514002
/*
13866-
* Cleanup after we've finished all the ALTER TYPE operations for a
13867-
* particular relation. We have to drop and recreate all the indexes
13868-
* and constraints that depend on the altered columns. We do the
13869-
* actual dropping here, but re-creation is managed by adding work
13870-
* queue entries to do those steps later.
14003+
* Cleanup after we've finished all the ALTER TYPE or SET EXPRESSION
14004+
* operations for a particular relation. We have to drop and recreate all the
14005+
* indexes and constraints that depend on the altered columns. We do the
14006+
* actual dropping here, but re-creation is managed by adding work queue
14007+
* entries to do those steps later.
1387114008
*/
1387214009
static void
1387314010
ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode)

0 commit comments

Comments
 (0)