On Mon, Nov 20, 2023 at 1:12 PM Peter Eisentraut <pe...@eisentraut.org>
wrote:
> On 17.11.23 13:25, Amul Sul wrote:
> > To fix this we should be doing something like ALTER COLUMN TYPE and the
> pass
> > should be AT_PASS_ALTER_TYPE (rename it or invent a new one near to
> that) so
> > that in ATRewriteCatalogs(), we would execute ATPostAlterTypeCleanup().
> >
> > I simply tried that by doing blind copy of code from
> > ATExecAlterColumnType() in
> > 0002 patch. We don't really need to do all the stuff such as re-adding
> > indexes, constraints etc, but I am out of time for today to figure out
> the
> > optimum code and I will be away from work in the first half of the coming
> > week and the week after that. Therefore, I thought of sharing an
> approach to
> > get comments/thoughts on the direction, thanks.
>
> The exact sequencing of this seems to be tricky. It's clear that we
> need to do it earlier than at the end. I also think it should be
> strictly after AT_PASS_ALTER_TYPE so that the new expression can refer
> to the new type of a column. It should also be after AT_PASS_ADD_COL,
> so that the new expression can refer to any newly added column. But
> then it's after AT_PASS_OLD_INDEX and AT_PASS_OLD_CONSTR, is that a
> problem?
>
AT_PASS_ALTER_TYPE and AT_PASS_ADD_COL cannot be together, the ALTER TYPE
cannot see that column, I think we can adopt the same behaviour.
But, we need to have ALTER SET EXPRESSION after the ALTER TYPE since if we
add
the new generated expression for the current type (e.g. int) and we would
alter the type (e.g. text or numeric) then that will be problematic in the
ATRewriteTable() where a new generation expression will generate value for
the
old type but the actual type is something else. Therefore I have added
AT_PASS_SET_EXPRESSION to execute after AT_PASS_ALTER_TYPE.
(It might be an option for the first version of this feature to not
> support altering columns that have constraints on them. But we do need
> to support columns with indexes on them. Does that work ok? Does that
> depend on the relative order of AT_PASS_OLD_INDEX?)
>
I tried to reuse the code by borrowing code from ALTER TYPE, see if that
looks good to you.
But I have concerns, with that code reuse where we drop and re-add the
indexes
and constraints which seems unnecessary for SET EXPRESSION where column
attributes will stay the same. I don't know why ATLER TYPE does that for
index
since finish_heap_swap() anyway does reindexing. We could skip re-adding
index for SET EXPRESSION which would be fine but we could not skip the
re-addition of constraints, since rebuilding constraints for checking might
need a good amount of code copy especially for foreign key constraints.
Please have a look at the attached version, 0001 patch does the code
refactoring, and 0002 is the implementation, using the newly refactored
code to
re-add indexes and constraints for the validation. Added tests for the same.
Regards,
Amul
From e6418c3f36618c517b160ab71895975773d16f6c Mon Sep 17 00:00:00 2001
From: Amul Sul <amul.sul@enterprisedb.com>
Date: Wed, 22 Nov 2023 18:23:56 +0530
Subject: [PATCH v5 1/2] Code refactor: separate function to find all dependent
object on column
Move code from ATExecAlterColumnType() that finds the all the object
that depends on the column to a separate function.
Also, renamed ATPostAlterTypeCleanup() and ATPostAlterTypeParse()
function for the general use.
---
src/backend/commands/tablecmds.c | 474 ++++++++++++++++---------------
1 file changed, 248 insertions(+), 226 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 323d9bf8702..ccc152f54e9 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -557,14 +557,16 @@ static void ATPrepAlterColumnType(List **wqueue,
static bool ATColumnChangeRequiresRewrite(Node *expr, AttrNumber varattno);
static ObjectAddress ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
AlterTableCmd *cmd, LOCKMODE lockmode);
+static void RememberAllDependentForRebuilding(AlteredTableInfo *tab,
+ Relation rel, AttrNumber attnum);
static void RememberConstraintForRebuilding(Oid conoid, AlteredTableInfo *tab);
static void RememberIndexForRebuilding(Oid indoid, AlteredTableInfo *tab);
static void RememberStatisticsForRebuilding(Oid stxoid, AlteredTableInfo *tab);
-static void ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab,
- LOCKMODE lockmode);
-static void ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId,
- char *cmd, List **wqueue, LOCKMODE lockmode,
- bool rewrite);
+static void ATPostAlterColumnCleanup(List **wqueue, AlteredTableInfo *tab,
+ LOCKMODE lockmode);
+static void ATPostAlterColumnParse(Oid oldId, Oid oldRelId, Oid refRelId,
+ char *cmd, List **wqueue, LOCKMODE lockmode,
+ bool rewrite);
static void RebuildConstraintComment(AlteredTableInfo *tab, int pass,
Oid objid, Relation rel, List *domname,
const char *conname);
@@ -5156,7 +5158,7 @@ ATRewriteCatalogs(List **wqueue, LOCKMODE lockmode,
* multiple columns of a table are altered).
*/
if (pass == AT_PASS_ALTER_TYPE)
- ATPostAlterTypeCleanup(wqueue, tab, lockmode);
+ ATPostAlterColumnCleanup(wqueue, tab, lockmode);
if (tab->rel)
{
@@ -13291,15 +13293,225 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
/*
* Find everything that depends on the column (constraints, indexes, etc),
- * and record enough information to let us recreate the objects.
- *
- * The actual recreation does not happen here, but only after we have
- * performed all the individual ALTER TYPE operations. We have to save
- * the info before executing ALTER TYPE, though, else the deparser will
- * get confused.
+ * and record enough information to let us recreate the objects after ALTER
+ * TYPE operations.
+ */
+ RememberAllDependentForRebuilding(tab, rel, attnum);
+
+ /*
+ * Now scan for dependencies of this column on other things. The only
+ * things we should find are the dependency on the column datatype and
+ * possibly a collation dependency. Those can be removed.
*/
depRel = table_open(DependRelationId, RowExclusiveLock);
+ ScanKeyInit(&key[0],
+ Anum_pg_depend_classid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(RelationRelationId));
+ ScanKeyInit(&key[1],
+ Anum_pg_depend_objid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(RelationGetRelid(rel)));
+ ScanKeyInit(&key[2],
+ Anum_pg_depend_objsubid,
+ BTEqualStrategyNumber, F_INT4EQ,
+ Int32GetDatum((int32) attnum));
+
+ scan = systable_beginscan(depRel, DependDependerIndexId, true,
+ NULL, 3, key);
+
+ while (HeapTupleIsValid(depTup = systable_getnext(scan)))
+ {
+ Form_pg_depend foundDep = (Form_pg_depend) GETSTRUCT(depTup);
+ ObjectAddress foundObject;
+
+ foundObject.classId = foundDep->refclassid;
+ foundObject.objectId = foundDep->refobjid;
+ foundObject.objectSubId = foundDep->refobjsubid;
+
+ if (foundDep->deptype != DEPENDENCY_NORMAL)
+ elog(ERROR, "found unexpected dependency type '%c'",
+ foundDep->deptype);
+ if (!(foundDep->refclassid == TypeRelationId &&
+ foundDep->refobjid == attTup->atttypid) &&
+ !(foundDep->refclassid == CollationRelationId &&
+ foundDep->refobjid == attTup->attcollation))
+ elog(ERROR, "found unexpected dependency for column: %s",
+ getObjectDescription(&foundObject, false));
+
+ CatalogTupleDelete(depRel, &depTup->t_self);
+ }
+
+ systable_endscan(scan);
+
+ table_close(depRel, RowExclusiveLock);
+
+ /*
+ * Here we go --- change the recorded column type and collation. (Note
+ * heapTup is a copy of the syscache entry, so okay to scribble on.) First
+ * fix up the missing value if any.
+ */
+ if (attTup->atthasmissing)
+ {
+ Datum missingval;
+ bool missingNull;
+
+ /* if rewrite is true the missing value should already be cleared */
+ Assert(tab->rewrite == 0);
+
+ /* Get the missing value datum */
+ missingval = heap_getattr(heapTup,
+ Anum_pg_attribute_attmissingval,
+ attrelation->rd_att,
+ &missingNull);
+
+ /* if it's a null array there is nothing to do */
+
+ if (!missingNull)
+ {
+ /*
+ * Get the datum out of the array and repack it in a new array
+ * built with the new type data. We assume that since the table
+ * doesn't need rewriting, the actual Datum doesn't need to be
+ * changed, only the array metadata.
+ */
+
+ int one = 1;
+ bool isNull;
+ Datum valuesAtt[Natts_pg_attribute] = {0};
+ bool nullsAtt[Natts_pg_attribute] = {0};
+ bool replacesAtt[Natts_pg_attribute] = {0};
+ HeapTuple newTup;
+
+ missingval = array_get_element(missingval,
+ 1,
+ &one,
+ 0,
+ attTup->attlen,
+ attTup->attbyval,
+ attTup->attalign,
+ &isNull);
+ missingval = PointerGetDatum(construct_array(&missingval,
+ 1,
+ targettype,
+ tform->typlen,
+ tform->typbyval,
+ tform->typalign));
+
+ valuesAtt[Anum_pg_attribute_attmissingval - 1] = missingval;
+ replacesAtt[Anum_pg_attribute_attmissingval - 1] = true;
+ nullsAtt[Anum_pg_attribute_attmissingval - 1] = false;
+
+ newTup = heap_modify_tuple(heapTup, RelationGetDescr(attrelation),
+ valuesAtt, nullsAtt, replacesAtt);
+ heap_freetuple(heapTup);
+ heapTup = newTup;
+ attTup = (Form_pg_attribute) GETSTRUCT(heapTup);
+ }
+ }
+
+ attTup->atttypid = targettype;
+ attTup->atttypmod = targettypmod;
+ attTup->attcollation = targetcollid;
+ if (list_length(typeName->arrayBounds) > PG_INT16_MAX)
+ ereport(ERROR,
+ errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
+ errmsg("too many array dimensions"));
+ attTup->attndims = list_length(typeName->arrayBounds);
+ attTup->attlen = tform->typlen;
+ attTup->attbyval = tform->typbyval;
+ attTup->attalign = tform->typalign;
+ attTup->attstorage = tform->typstorage;
+ attTup->attcompression = InvalidCompressionMethod;
+
+ ReleaseSysCache(typeTuple);
+
+ CatalogTupleUpdate(attrelation, &heapTup->t_self, heapTup);
+
+ table_close(attrelation, RowExclusiveLock);
+
+ /* Install dependencies on new datatype and collation */
+ add_column_datatype_dependency(RelationGetRelid(rel), attnum, targettype);
+ add_column_collation_dependency(RelationGetRelid(rel), attnum, targetcollid);
+
+ /*
+ * Drop any pg_statistic entry for the column, since it's now wrong type
+ */
+ RemoveStatistics(RelationGetRelid(rel), attnum);
+
+ InvokeObjectPostAlterHook(RelationRelationId,
+ RelationGetRelid(rel), attnum);
+
+ /*
+ * Update the default, if present, by brute force --- remove and re-add
+ * the default. Probably unsafe to take shortcuts, since the new version
+ * may well have additional dependencies. (It's okay to do this now,
+ * rather than after other ALTER TYPE commands, since the default won't
+ * depend on other column types.)
+ */
+ if (defaultexpr)
+ {
+ /*
+ * If it's a GENERATED default, drop its dependency records, in
+ * particular its INTERNAL dependency on the column, which would
+ * otherwise cause dependency.c to refuse to perform the deletion.
+ */
+ if (attTup->attgenerated)
+ {
+ Oid attrdefoid = GetAttrDefaultOid(RelationGetRelid(rel), attnum);
+
+ if (!OidIsValid(attrdefoid))
+ elog(ERROR, "could not find attrdef tuple for relation %u attnum %d",
+ RelationGetRelid(rel), attnum);
+ (void) deleteDependencyRecordsFor(AttrDefaultRelationId, attrdefoid, false);
+ }
+
+ /*
+ * Make updates-so-far visible, particularly the new pg_attribute row
+ * which will be updated again.
+ */
+ CommandCounterIncrement();
+
+ /*
+ * We use RESTRICT here for safety, but at present we do not expect
+ * anything to depend on the default.
+ */
+ RemoveAttrDefault(RelationGetRelid(rel), attnum, DROP_RESTRICT, true,
+ true);
+
+ StoreAttrDefault(rel, attnum, defaultexpr, true, false);
+ }
+
+ ObjectAddressSubSet(address, RelationRelationId,
+ RelationGetRelid(rel), attnum);
+
+ /* Cleanup */
+ heap_freetuple(heapTup);
+
+ return address;
+}
+
+/*
+ * Subroutine for ATExecAlterColumnType: Find everything that depends on the
+ * column (constraints, indexes, etc), and record enough information to let us
+ * recreate the objects.
+ *
+ * The actual recreation does not happen here, but only after we have
+ * performed all the individual ALTER TYPE operations. We have to save
+ * the info before executing ALTER TYPE, though, else the deparser will
+ * get confused.
+ */
+static void
+RememberAllDependentForRebuilding(AlteredTableInfo *tab, Relation rel, AttrNumber attnum)
+{
+ Relation depRel;
+ ScanKeyData key[3];
+ SysScanDesc scan;
+ HeapTuple depTup;
+
+ depRel = table_open(DependRelationId, AccessShareLock);
+
ScanKeyInit(&key[0],
Anum_pg_depend_refclassid,
BTEqualStrategyNumber, F_OIDEQ,
@@ -13366,7 +13578,7 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
errmsg("cannot alter type of a column used by a view or rule"),
errdetail("%s depends on column \"%s\"",
getObjectDescription(&foundObject, false),
- colName)));
+ get_attname(RelationGetRelid(rel), attnum, false))));
break;
case OCLASS_TRIGGER:
@@ -13385,7 +13597,7 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
errmsg("cannot alter type of a column used in a trigger definition"),
errdetail("%s depends on column \"%s\"",
getObjectDescription(&foundObject, false),
- colName)));
+ get_attname(RelationGetRelid(rel), attnum, false))));
break;
case OCLASS_POLICY:
@@ -13403,7 +13615,7 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
errmsg("cannot alter type of a column used in a policy definition"),
errdetail("%s depends on column \"%s\"",
getObjectDescription(&foundObject, false),
- colName)));
+ get_attname(RelationGetRelid(rel), attnum, false))));
break;
case OCLASS_DEFAULT:
@@ -13415,9 +13627,9 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
{
/*
* Ignore the column's own default expression, which
- * we will deal with below.
+ * called is supposed to deal with.
*/
- Assert(defaultexpr);
+ Assert(build_column_default(rel, attnum));
}
else
{
@@ -13433,7 +13645,7 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot alter type of a column used by a generated column"),
errdetail("Column \"%s\" is used by generated column \"%s\".",
- colName,
+ get_attname(RelationGetRelid(rel), attnum, false),
get_attname(col.objectId,
col.objectSubId,
false))));
@@ -13501,197 +13713,7 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
}
systable_endscan(scan);
-
- /*
- * Now scan for dependencies of this column on other things. The only
- * things we should find are the dependency on the column datatype and
- * possibly a collation dependency. Those can be removed.
- */
- ScanKeyInit(&key[0],
- Anum_pg_depend_classid,
- BTEqualStrategyNumber, F_OIDEQ,
- ObjectIdGetDatum(RelationRelationId));
- ScanKeyInit(&key[1],
- Anum_pg_depend_objid,
- BTEqualStrategyNumber, F_OIDEQ,
- ObjectIdGetDatum(RelationGetRelid(rel)));
- ScanKeyInit(&key[2],
- Anum_pg_depend_objsubid,
- BTEqualStrategyNumber, F_INT4EQ,
- Int32GetDatum((int32) attnum));
-
- scan = systable_beginscan(depRel, DependDependerIndexId, true,
- NULL, 3, key);
-
- while (HeapTupleIsValid(depTup = systable_getnext(scan)))
- {
- Form_pg_depend foundDep = (Form_pg_depend) GETSTRUCT(depTup);
- ObjectAddress foundObject;
-
- foundObject.classId = foundDep->refclassid;
- foundObject.objectId = foundDep->refobjid;
- foundObject.objectSubId = foundDep->refobjsubid;
-
- if (foundDep->deptype != DEPENDENCY_NORMAL)
- elog(ERROR, "found unexpected dependency type '%c'",
- foundDep->deptype);
- if (!(foundDep->refclassid == TypeRelationId &&
- foundDep->refobjid == attTup->atttypid) &&
- !(foundDep->refclassid == CollationRelationId &&
- foundDep->refobjid == attTup->attcollation))
- elog(ERROR, "found unexpected dependency for column: %s",
- getObjectDescription(&foundObject, false));
-
- CatalogTupleDelete(depRel, &depTup->t_self);
- }
-
- systable_endscan(scan);
-
- table_close(depRel, RowExclusiveLock);
-
- /*
- * Here we go --- change the recorded column type and collation. (Note
- * heapTup is a copy of the syscache entry, so okay to scribble on.) First
- * fix up the missing value if any.
- */
- if (attTup->atthasmissing)
- {
- Datum missingval;
- bool missingNull;
-
- /* if rewrite is true the missing value should already be cleared */
- Assert(tab->rewrite == 0);
-
- /* Get the missing value datum */
- missingval = heap_getattr(heapTup,
- Anum_pg_attribute_attmissingval,
- attrelation->rd_att,
- &missingNull);
-
- /* if it's a null array there is nothing to do */
-
- if (!missingNull)
- {
- /*
- * Get the datum out of the array and repack it in a new array
- * built with the new type data. We assume that since the table
- * doesn't need rewriting, the actual Datum doesn't need to be
- * changed, only the array metadata.
- */
-
- int one = 1;
- bool isNull;
- Datum valuesAtt[Natts_pg_attribute] = {0};
- bool nullsAtt[Natts_pg_attribute] = {0};
- bool replacesAtt[Natts_pg_attribute] = {0};
- HeapTuple newTup;
-
- missingval = array_get_element(missingval,
- 1,
- &one,
- 0,
- attTup->attlen,
- attTup->attbyval,
- attTup->attalign,
- &isNull);
- missingval = PointerGetDatum(construct_array(&missingval,
- 1,
- targettype,
- tform->typlen,
- tform->typbyval,
- tform->typalign));
-
- valuesAtt[Anum_pg_attribute_attmissingval - 1] = missingval;
- replacesAtt[Anum_pg_attribute_attmissingval - 1] = true;
- nullsAtt[Anum_pg_attribute_attmissingval - 1] = false;
-
- newTup = heap_modify_tuple(heapTup, RelationGetDescr(attrelation),
- valuesAtt, nullsAtt, replacesAtt);
- heap_freetuple(heapTup);
- heapTup = newTup;
- attTup = (Form_pg_attribute) GETSTRUCT(heapTup);
- }
- }
-
- attTup->atttypid = targettype;
- attTup->atttypmod = targettypmod;
- attTup->attcollation = targetcollid;
- if (list_length(typeName->arrayBounds) > PG_INT16_MAX)
- ereport(ERROR,
- errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
- errmsg("too many array dimensions"));
- attTup->attndims = list_length(typeName->arrayBounds);
- attTup->attlen = tform->typlen;
- attTup->attbyval = tform->typbyval;
- attTup->attalign = tform->typalign;
- attTup->attstorage = tform->typstorage;
- attTup->attcompression = InvalidCompressionMethod;
-
- ReleaseSysCache(typeTuple);
-
- CatalogTupleUpdate(attrelation, &heapTup->t_self, heapTup);
-
- table_close(attrelation, RowExclusiveLock);
-
- /* Install dependencies on new datatype and collation */
- add_column_datatype_dependency(RelationGetRelid(rel), attnum, targettype);
- add_column_collation_dependency(RelationGetRelid(rel), attnum, targetcollid);
-
- /*
- * Drop any pg_statistic entry for the column, since it's now wrong type
- */
- RemoveStatistics(RelationGetRelid(rel), attnum);
-
- InvokeObjectPostAlterHook(RelationRelationId,
- RelationGetRelid(rel), attnum);
-
- /*
- * Update the default, if present, by brute force --- remove and re-add
- * the default. Probably unsafe to take shortcuts, since the new version
- * may well have additional dependencies. (It's okay to do this now,
- * rather than after other ALTER TYPE commands, since the default won't
- * depend on other column types.)
- */
- if (defaultexpr)
- {
- /*
- * If it's a GENERATED default, drop its dependency records, in
- * particular its INTERNAL dependency on the column, which would
- * otherwise cause dependency.c to refuse to perform the deletion.
- */
- if (attTup->attgenerated)
- {
- Oid attrdefoid = GetAttrDefaultOid(RelationGetRelid(rel), attnum);
-
- if (!OidIsValid(attrdefoid))
- elog(ERROR, "could not find attrdef tuple for relation %u attnum %d",
- RelationGetRelid(rel), attnum);
- (void) deleteDependencyRecordsFor(AttrDefaultRelationId, attrdefoid, false);
- }
-
- /*
- * Make updates-so-far visible, particularly the new pg_attribute row
- * which will be updated again.
- */
- CommandCounterIncrement();
-
- /*
- * We use RESTRICT here for safety, but at present we do not expect
- * anything to depend on the default.
- */
- RemoveAttrDefault(RelationGetRelid(rel), attnum, DROP_RESTRICT, true,
- true);
-
- StoreAttrDefault(rel, attnum, defaultexpr, true, false);
- }
-
- ObjectAddressSubSet(address, RelationRelationId,
- RelationGetRelid(rel), attnum);
-
- /* Cleanup */
- heap_freetuple(heapTup);
-
- return address;
+ table_close(depRel, AccessShareLock);
}
/*
@@ -13753,7 +13775,7 @@ RememberConstraintForRebuilding(Oid conoid, AlteredTableInfo *tab)
/*
* For the index of a constraint, if any, remember if it is used for
* the table's replica identity or if it is a clustered index, so that
- * ATPostAlterTypeCleanup() can queue up commands necessary to restore
+ * ATPostAlterColumnCleanup() can queue up commands necessary to restore
* those properties.
*/
indoid = get_constraint_index(conoid);
@@ -13807,7 +13829,7 @@ RememberIndexForRebuilding(Oid indoid, AlteredTableInfo *tab)
/*
* Remember if this index is used for the table's replica identity
- * or if it is a clustered index, so that ATPostAlterTypeCleanup()
+ * or if it is a clustered index, so that ATPostAlterColumnCleanup()
* can queue up commands necessary to restore those properties.
*/
RememberReplicaIdentityForRebuilding(indoid, tab);
@@ -13850,7 +13872,7 @@ RememberStatisticsForRebuilding(Oid stxoid, AlteredTableInfo *tab)
* queue entries to do those steps later.
*/
static void
-ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode)
+ATPostAlterColumnCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode)
{
ObjectAddress obj;
ObjectAddresses *objects;
@@ -13928,9 +13950,9 @@ ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode)
if (relid != tab->relid && contype == CONSTRAINT_FOREIGN)
LockRelationOid(relid, AccessExclusiveLock);
- ATPostAlterTypeParse(oldId, relid, confrelid,
- (char *) lfirst(def_item),
- wqueue, lockmode, tab->rewrite);
+ ATPostAlterColumnParse(oldId, relid, confrelid,
+ (char *) lfirst(def_item),
+ wqueue, lockmode, tab->rewrite);
}
forboth(oid_item, tab->changedIndexOids,
def_item, tab->changedIndexDefs)
@@ -13939,9 +13961,9 @@ ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode)
Oid relid;
relid = IndexGetRelation(oldId, false);
- ATPostAlterTypeParse(oldId, relid, InvalidOid,
- (char *) lfirst(def_item),
- wqueue, lockmode, tab->rewrite);
+ ATPostAlterColumnParse(oldId, relid, InvalidOid,
+ (char *) lfirst(def_item),
+ wqueue, lockmode, tab->rewrite);
ObjectAddressSet(obj, RelationRelationId, oldId);
add_exact_object_address(&obj, objects);
@@ -13955,9 +13977,9 @@ ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode)
Oid relid;
relid = StatisticsGetRelation(oldId, false);
- ATPostAlterTypeParse(oldId, relid, InvalidOid,
- (char *) lfirst(def_item),
- wqueue, lockmode, tab->rewrite);
+ ATPostAlterColumnParse(oldId, relid, InvalidOid,
+ (char *) lfirst(def_item),
+ wqueue, lockmode, tab->rewrite);
ObjectAddressSet(obj, StatisticExtRelationId, oldId);
add_exact_object_address(&obj, objects);
@@ -14019,8 +14041,8 @@ ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode)
* operator that's not available for the new column type.
*/
static void
-ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, char *cmd,
- List **wqueue, LOCKMODE lockmode, bool rewrite)
+ATPostAlterColumnParse(Oid oldId, Oid oldRelId, Oid refRelId, char *cmd,
+ List **wqueue, LOCKMODE lockmode, bool rewrite)
{
List *raw_parsetree_list;
List *querytree_list;
@@ -14225,7 +14247,7 @@ ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, char *cmd,
}
/*
- * Subroutine for ATPostAlterTypeParse() to recreate any existing comment
+ * Subroutine for ATPostAlterColumnParse() to recreate any existing comment
* for a table or domain constraint that is being rebuilt.
*
* objid is the OID of the constraint.
@@ -14275,7 +14297,7 @@ RebuildConstraintComment(AlteredTableInfo *tab, int pass, Oid objid,
}
/*
- * Subroutine for ATPostAlterTypeParse(). Calls out to CheckIndexCompatible()
+ * Subroutine for ATPostAlterColumnParse(). Calls out to CheckIndexCompatible()
* for the real analysis, then mutates the IndexStmt based on that verdict.
*/
static void
@@ -14300,7 +14322,7 @@ TryReuseIndex(Oid oldId, IndexStmt *stmt)
}
/*
- * Subroutine for ATPostAlterTypeParse().
+ * Subroutine for ATPostAlterColumnParse().
*
* Stash the old P-F equality operator into the Constraint node, for possible
* use by ATAddForeignKeyConstraint() in determining whether revalidation of
--
2.18.0
From e50a46329a74ec0e3050d658607bf943cea4da0c Mon Sep 17 00:00:00 2001
From: Amul Sul <amul.sul@enterprisedb.com>
Date: Wed, 15 Nov 2023 18:04:51 +0530
Subject: [PATCH v5 2/2] Allow to change generated column expression
---
doc/src/sgml/ref/alter_table.sgml | 13 +
src/backend/commands/tablecmds.c | 306 ++++++++++++++----
src/backend/parser/gram.y | 10 +
src/bin/psql/tab-complete.c | 10 +-
src/include/nodes/parsenodes.h | 1 +
.../test_ddl_deparse/test_ddl_deparse.c | 3 +
src/test/regress/expected/generated.out | 214 ++++++++++--
src/test/regress/sql/generated.sql | 49 ++-
8 files changed, 519 insertions(+), 87 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 2c4138e4e9f..c5c0ac6e276 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -46,6 +46,7 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET DEFAULT <replaceable class="parameter">expression</replaceable>
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> DROP DEFAULT
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> { SET | DROP } NOT NULL
+ ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET EXPRESSION AS ( <replaceable class="parameter">expression</replaceable> )
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> DROP EXPRESSION [ IF EXISTS ]
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <replaceable>sequence_options</replaceable> ) ]
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> { SET GENERATED { ALWAYS | BY DEFAULT } | SET <replaceable>sequence_option</replaceable> | RESTART [ [ WITH ] <replaceable class="parameter">restart</replaceable> ] } [...]
@@ -256,6 +257,18 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
+ <varlistentry id="sql-altertable-desc-set-expression">
+ <term><literal>SET EXPRESSION AS</literal></term>
+ <listitem>
+ <para>
+ This form replaces the expression of a generated column. Existing
+ data in the column is rewritten and all the future changes will
+ apply the new
+ generation expression.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="sql-altertable-desc-drop-expression">
<term><literal>DROP EXPRESSION [ IF EXISTS ]</literal></term>
<listitem>
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index ccc152f54e9..3ac54bbe0a3 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -145,17 +145,18 @@ static List *on_commits = NIL;
#define AT_PASS_UNSET -1 /* UNSET will cause ERROR */
#define AT_PASS_DROP 0 /* DROP (all flavors) */
#define AT_PASS_ALTER_TYPE 1 /* ALTER COLUMN TYPE */
-#define AT_PASS_OLD_INDEX 2 /* re-add existing indexes */
-#define AT_PASS_OLD_CONSTR 3 /* re-add existing constraints */
+#define AT_PASS_SET_EXPRESSION 2 /* ALTER SET EXPRESSION */
+#define AT_PASS_OLD_INDEX 3 /* re-add existing indexes */
+#define AT_PASS_OLD_CONSTR 4 /* re-add existing constraints */
/* We could support a RENAME COLUMN pass here, but not currently used */
-#define AT_PASS_ADD_COL 4 /* ADD COLUMN */
-#define AT_PASS_ADD_CONSTR 5 /* ADD constraints (initial examination) */
-#define AT_PASS_COL_ATTRS 6 /* set column attributes, eg NOT NULL */
-#define AT_PASS_ADD_INDEXCONSTR 7 /* ADD index-based constraints */
-#define AT_PASS_ADD_INDEX 8 /* ADD indexes */
-#define AT_PASS_ADD_OTHERCONSTR 9 /* ADD other constraints, defaults */
-#define AT_PASS_MISC 10 /* other stuff */
-#define AT_NUM_PASSES 11
+#define AT_PASS_ADD_COL 5 /* ADD COLUMN */
+#define AT_PASS_ADD_CONSTR 6 /* ADD constraints (initial examination) */
+#define AT_PASS_COL_ATTRS 7 /* set column attributes, eg NOT NULL */
+#define AT_PASS_ADD_INDEXCONSTR 8 /* ADD index-based constraints */
+#define AT_PASS_ADD_INDEX 9 /* ADD indexes */
+#define AT_PASS_ADD_OTHERCONSTR 10 /* ADD other constraints, defaults */
+#define AT_PASS_MISC 11 /* other stuff */
+#define AT_NUM_PASSES 12
typedef struct AlteredTableInfo
{
@@ -455,6 +456,9 @@ static ObjectAddress ATExecAddIdentity(Relation rel, const char *colName,
static ObjectAddress ATExecSetIdentity(Relation rel, const char *colName,
Node *def, LOCKMODE lockmode);
static ObjectAddress ATExecDropIdentity(Relation rel, const char *colName, bool missing_ok, LOCKMODE lockmode);
+static ObjectAddress ATExecSetExpression(AlteredTableInfo *tab, Relation rel,
+ const char *colName, Node *newDefault,
+ LOCKMODE lockmode);
static void ATPrepDropExpression(Relation rel, AlterTableCmd *cmd, bool recurse, bool recursing, LOCKMODE lockmode);
static ObjectAddress ATExecDropExpression(Relation rel, const char *colName, bool missing_ok, LOCKMODE lockmode);
static ObjectAddress ATExecSetStatistics(Relation rel, const char *colName, int16 colNum,
@@ -558,6 +562,7 @@ static bool ATColumnChangeRequiresRewrite(Node *expr, AttrNumber varattno);
static ObjectAddress ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
AlterTableCmd *cmd, LOCKMODE lockmode);
static void RememberAllDependentForRebuilding(AlteredTableInfo *tab,
+ AlterTableType subtype,
Relation rel, AttrNumber attnum);
static void RememberConstraintForRebuilding(Oid conoid, AlteredTableInfo *tab);
static void RememberIndexForRebuilding(Oid indoid, AlteredTableInfo *tab);
@@ -4550,6 +4555,7 @@ AlterTableGetLockLevel(List *cmds)
case AT_AddIdentity:
case AT_DropIdentity:
case AT_SetIdentity:
+ case AT_SetExpression:
case AT_DropExpression:
case AT_SetCompression:
cmd_lockmode = AccessExclusiveLock;
@@ -4851,6 +4857,11 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode, context);
pass = AT_PASS_COL_ATTRS;
break;
+ case AT_SetExpression: /* ALTER COLUMN SET EXPRESSION AS */
+ ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_FOREIGN_TABLE);
+ ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode, context);
+ pass = AT_PASS_SET_EXPRESSION;
+ break;
case AT_DropExpression: /* ALTER COLUMN DROP EXPRESSION */
ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_FOREIGN_TABLE);
ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode, context);
@@ -5153,11 +5164,11 @@ ATRewriteCatalogs(List **wqueue, LOCKMODE lockmode,
lockmode, pass, context);
/*
- * After the ALTER TYPE pass, do cleanup work (this is not done in
- * ATExecAlterColumnType since it should be done only once if
- * multiple columns of a table are altered).
+ * After the ALTER TYPE or ALTER SET EXPRESSION pass, do cleanup
+ * work (this is not done in ATExecAlterColumnType since it should
+ * be done only once if multiple columns of a table are altered).
*/
- if (pass == AT_PASS_ALTER_TYPE)
+ if (pass == AT_PASS_ALTER_TYPE || pass == AT_PASS_SET_EXPRESSION)
ATPostAlterColumnCleanup(wqueue, tab, lockmode);
if (tab->rel)
@@ -5236,6 +5247,9 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab,
case AT_SetAttNotNull: /* set pg_attribute.attnotnull */
address = ATExecSetAttNotNull(wqueue, rel, cmd->name, lockmode);
break;
+ case AT_SetExpression:
+ address = ATExecSetExpression(tab, rel, cmd->name, cmd->def, lockmode);
+ break;
case AT_DropExpression:
address = ATExecDropExpression(rel, cmd->name, cmd->missing_ok, lockmode);
break;
@@ -6363,6 +6377,8 @@ alter_table_type_to_string(AlterTableType cmdtype)
return "ALTER COLUMN ... SET NOT NULL";
case AT_SetAttNotNull:
return NULL; /* not real grammar */
+ case AT_SetExpression:
+ return "ALTER COLUMN ... SET EXPRESSION AS";
case AT_DropExpression:
return "ALTER COLUMN ... DROP EXPRESSION";
case AT_SetStatistics:
@@ -8013,8 +8029,10 @@ ATExecColumnDefault(Relation rel, const char *colName,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("column \"%s\" of relation \"%s\" is a generated column",
colName, RelationGetRelationName(rel)),
- newDefault || TupleDescAttr(tupdesc, attnum - 1)->attgenerated != ATTRIBUTE_GENERATED_STORED ? 0 :
+ TupleDescAttr(tupdesc, attnum - 1)->attgenerated != ATTRIBUTE_GENERATED_STORED ? 0 :
/* translator: %s is an SQL ALTER command */
+ newDefault ? errhint("Use %s instead.",
+ "ALTER TABLE ... ALTER COLUMN ... SET EXPRESSION") :
errhint("Use %s instead.",
"ALTER TABLE ... ALTER COLUMN ... DROP EXPRESSION")));
@@ -8313,6 +8331,121 @@ ATExecDropIdentity(Relation rel, const char *colName, bool missing_ok, LOCKMODE
return address;
}
+/*
+ * ALTER TABLE ALTER COLUMN SET EXPRESSION AS
+ *
+ * Return the address of the affected column.
+ */
+static ObjectAddress
+ATExecSetExpression(AlteredTableInfo *tab, Relation rel, const char *colName,
+ Node *newDefault, LOCKMODE lockmode)
+{
+ HeapTuple tuple;
+ Form_pg_attribute attTup;
+ AttrNumber attnum;
+ Oid attrdefoid;
+ ObjectAddress address;
+ Expr *defval;
+ NewColumnValue *newval;
+ RawColumnDefault *rawEnt;
+
+ tuple = SearchSysCacheAttName(RelationGetRelid(rel), colName);
+ if (!HeapTupleIsValid(tuple))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_COLUMN),
+ errmsg("column \"%s\" of relation \"%s\" does not exist",
+ colName, RelationGetRelationName(rel))));
+
+ attTup = (Form_pg_attribute) GETSTRUCT(tuple);
+ attnum = attTup->attnum;
+
+ if (attnum <= 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot alter system column \"%s\"",
+ colName)));
+
+ if (attTup->attgenerated != ATTRIBUTE_GENERATED_STORED)
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("column \"%s\" of relation \"%s\" is not a stored generated column",
+ colName, RelationGetRelationName(rel))));
+ ReleaseSysCache(tuple);
+
+ /*
+ * Clear all the missing values if we're rewriting the table, since this
+ * renders them pointless.
+ */
+ RelationClearMissing(rel);
+
+ /* make sure we don't conflict with later attribute modifications */
+ CommandCounterIncrement();
+
+ /*
+ * Find everything that depends on the column (constraints, indexes, etc),
+ * and record enough information to let us recreate the objects after
+ * rewrite.
+ */
+ RememberAllDependentForRebuilding(tab, AT_SetExpression, rel, attnum);
+
+ /*
+ * Drop the dependency records of the GENERATED expression, in particular
+ * its INTERNAL dependency on the column, which would otherwise cause
+ * dependency.c to refuse to perform the deletion.
+ */
+ attrdefoid = GetAttrDefaultOid(RelationGetRelid(rel), attnum);
+ if (!OidIsValid(attrdefoid))
+ elog(ERROR, "could not find attrdef tuple for relation %u attnum %d",
+ RelationGetRelid(rel), attnum);
+ (void) deleteDependencyRecordsFor(AttrDefaultRelationId, attrdefoid, false);
+
+ /* Make above changes visible */
+ CommandCounterIncrement();
+
+ /*
+ * Get rid of the GENERATED expression itself. We use RESTRICT here for
+ * safety, but at present we do not expect anything to depend on the
+ * default.
+ */
+ RemoveAttrDefault(RelationGetRelid(rel), attnum, DROP_RESTRICT,
+ false, false);
+
+ /* Prepare to store the new expression, in the catalogs */
+ rawEnt = (RawColumnDefault *) palloc(sizeof(RawColumnDefault));
+ rawEnt->attnum = attnum;
+ rawEnt->raw_default = newDefault;
+ rawEnt->missingMode = false;
+ rawEnt->generated = ATTRIBUTE_GENERATED_STORED;
+
+ /* Store the generated expression */
+ AddRelationNewConstraints(rel, list_make1(rawEnt), NIL,
+ false, true, false, NULL);
+
+ /* Make above new expression visible */
+ CommandCounterIncrement();
+
+ /* Prepare for table rewrite */
+ defval = (Expr *) build_column_default(rel, attnum);
+
+ newval = (NewColumnValue *) palloc0(sizeof(NewColumnValue));
+ newval->attnum = attnum;
+ newval->expr = expression_planner(defval);
+ newval->is_generated = true;
+
+ tab->newvals = lappend(tab->newvals, newval);
+ tab->rewrite |= AT_REWRITE_DEFAULT_VAL;
+
+ /* Drop any pg_statistic entry for the column */
+ RemoveStatistics(RelationGetRelid(rel), attnum);
+
+ InvokeObjectPostAlterHook(RelationRelationId,
+ RelationGetRelid(rel), attnum);
+
+ ObjectAddressSubSet(address, RelationRelationId,
+ RelationGetRelid(rel), attnum);
+ return address;
+}
+
/*
* ALTER TABLE ALTER COLUMN DROP EXPRESSION
*/
@@ -13296,7 +13429,7 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
* and record enough information to let us recreate the objects after ALTER
* TYPE operations.
*/
- RememberAllDependentForRebuilding(tab, rel, attnum);
+ RememberAllDependentForRebuilding(tab, AT_AlterColumnType, rel, attnum);
/*
* Now scan for dependencies of this column on other things. The only
@@ -13493,17 +13626,18 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
}
/*
- * Subroutine for ATExecAlterColumnType: Find everything that depends on the
- * column (constraints, indexes, etc), and record enough information to let us
- * recreate the objects.
+ * Subroutine for ATExecAlterColumnType and ATExecSetExpression: Find everything
+ * that depends on the column (constraints, indexes, etc), and record enough
+ * information to let us recreate the objects.
*
* The actual recreation does not happen here, but only after we have
- * performed all the individual ALTER TYPE operations. We have to save
- * the info before executing ALTER TYPE, though, else the deparser will
- * get confused.
+ * performed all the individual ALTER TYPE/EXPRESSION operations. We have
+ * to save the info before executing ALTER TYPE/EXPRESSION, though, else the
+ * deparser will get confused.
*/
static void
-RememberAllDependentForRebuilding(AlteredTableInfo *tab, Relation rel, AttrNumber attnum)
+RememberAllDependentForRebuilding(AlteredTableInfo *tab, AlterTableType subtype,
+ Relation rel, AttrNumber attnum)
{
Relation depRel;
ScanKeyData key[3];
@@ -13573,12 +13707,22 @@ RememberAllDependentForRebuilding(AlteredTableInfo *tab, Relation rel, AttrNumbe
case OCLASS_REWRITE:
/* XXX someday see if we can cope with revising views */
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("cannot alter type of a column used by a view or rule"),
- errdetail("%s depends on column \"%s\"",
- getObjectDescription(&foundObject, false),
- get_attname(RelationGetRelid(rel), attnum, false))));
+ if (subtype == AT_AlterColumnType)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot alter type of a column used by a view or rule"),
+ errdetail("%s depends on column \"%s\"",
+ getObjectDescription(&foundObject, false),
+ get_attname(RelationGetRelid(rel), attnum, false))));
+ else
+ {
+ Assert(subtype == AT_SetExpression);
+
+ /*
+ * Ignore view dependencies since not altering any column
+ * attribute.
+ */
+ }
break;
case OCLASS_TRIGGER:
@@ -13592,12 +13736,31 @@ RememberAllDependentForRebuilding(AlteredTableInfo *tab, Relation rel, AttrNumbe
* significant amount of new code. Since we can't easily tell
* which case applies, we punt for both. FIXME someday.
*/
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("cannot alter type of a column used in a trigger definition"),
- errdetail("%s depends on column \"%s\"",
- getObjectDescription(&foundObject, false),
- get_attname(RelationGetRelid(rel), attnum, false))));
+ if (subtype == AT_AlterColumnType)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot alter type of a column used in a trigger definition"),
+ errdetail("%s depends on column \"%s\"",
+ getObjectDescription(&foundObject, false),
+ get_attname(RelationGetRelid(rel), attnum, false))));
+ else
+ {
+ Assert(subtype == AT_SetExpression);
+
+ /*
+ * The generated column not allowed to update, but there
+ * could be trigger on it which can have specified as an
+ * update target for this column even though it doesn make
+ * sense. However, the rewrite is an update operation, and
+ * trigger execution not supported at rewrite.
+ */
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot alter generated expression of a column used in a trigger definition"),
+ errdetail("%s depends on column \"%s\"",
+ getObjectDescription(&foundObject, false),
+ get_attname(RelationGetRelid(rel), attnum, false))));
+ }
break;
case OCLASS_POLICY:
@@ -13610,12 +13773,24 @@ RememberAllDependentForRebuilding(AlteredTableInfo *tab, Relation rel, AttrNumbe
* easy enough to remove and recreate the policy; still, FIXME
* someday.
*/
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("cannot alter type of a column used in a policy definition"),
- errdetail("%s depends on column \"%s\"",
- getObjectDescription(&foundObject, false),
- get_attname(RelationGetRelid(rel), attnum, false))));
+ if (subtype == AT_AlterColumnType)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot alter type of a column used in a policy definition"),
+ errdetail("%s depends on column \"%s\"",
+ getObjectDescription(&foundObject, false),
+ get_attname(RelationGetRelid(rel), attnum, false))));
+ else
+ {
+ Assert(subtype == AT_SetExpression);
+
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot alter generated expression of a column used in a policy definition"),
+ errdetail("%s depends on column \"%s\"",
+ getObjectDescription(&foundObject, false),
+ get_attname(RelationGetRelid(rel), attnum, false))));
+ }
break;
case OCLASS_DEFAULT:
@@ -13636,19 +13811,34 @@ RememberAllDependentForRebuilding(AlteredTableInfo *tab, Relation rel, AttrNumbe
/*
* This must be a reference from the expression of a
* generated column elsewhere in the same table.
- * Changing the type of a column that is used by a
- * generated column is not allowed by SQL standard, so
- * just punt for now. It might be doable with some
- * thinking and effort.
+ * Changing the type/generated expression of a column
+ * that is used by a generated column is not allowed by
+ * SQL standard, so just punt for now. It might be
+ * doable with some thinking and effort.
*/
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("cannot alter type of a column used by a generated column"),
- errdetail("Column \"%s\" is used by generated column \"%s\".",
- get_attname(RelationGetRelid(rel), attnum, false),
- get_attname(col.objectId,
- col.objectSubId,
- false))));
+ if (subtype == AT_AlterColumnType)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot alter type of a column used by a generated column"),
+ errdetail("Column \"%s\" is used by generated column \"%s\".",
+ get_attname(RelationGetRelid(rel),
+ attnum, false),
+ get_attname(col.objectId,
+ col.objectSubId,
+ false))));
+ else
+ {
+ Assert(subtype == AT_SetExpression);
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot alter generated expression of a column used by a generated column"),
+ errdetail("Column \"%s\" is used by generated column \"%s\".",
+ get_attname(RelationGetRelid(rel),
+ attnum, false),
+ get_attname(col.objectId,
+ col.objectSubId,
+ false))));
+ }
}
break;
}
@@ -13865,11 +14055,11 @@ RememberStatisticsForRebuilding(Oid stxoid, AlteredTableInfo *tab)
}
/*
- * Cleanup after we've finished all the ALTER TYPE operations for a
- * particular relation. We have to drop and recreate all the indexes
- * and constraints that depend on the altered columns. We do the
- * actual dropping here, but re-creation is managed by adding work
- * queue entries to do those steps later.
+ * Cleanup after we've finished all the ALTER TYPE or ALTER SET EXPRESSION
+ * operations for a particular relation. We have to drop and recreate all the
+ * indexes and constraints that depend on the altered columns. We do the actual
+ * dropping here, but re-creation is managed by adding work queue entries to do
+ * those steps later.
*/
static void
ATPostAlterColumnCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode)
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index c224df4eccc..b3c0e51230f 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -2404,6 +2404,16 @@ alter_table_cmd:
n->name = $3;
$$ = (Node *) n;
}
+ /* ALTER TABLE <name> ALTER [COLUMN] <colname> SET EXPRESSION AS <expr> */
+ | ALTER opt_column ColId SET EXPRESSION AS '(' a_expr ')'
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+
+ n->subtype = AT_SetExpression;
+ n->name = $3;
+ n->def = $8;
+ $$ = (Node *) n;
+ }
/* ALTER TABLE <name> ALTER [COLUMN] <colname> DROP EXPRESSION */
| ALTER opt_column ColId DROP EXPRESSION
{
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 006e10f5d2d..019d55a659d 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -2483,7 +2483,7 @@ psql_completion(const char *text, int start, int end)
/* ALTER TABLE ALTER [COLUMN] <foo> SET */
else if (Matches("ALTER", "TABLE", MatchAny, "ALTER", "COLUMN", MatchAny, "SET") ||
Matches("ALTER", "TABLE", MatchAny, "ALTER", MatchAny, "SET"))
- COMPLETE_WITH("(", "COMPRESSION", "DEFAULT", "GENERATED", "NOT NULL", "STATISTICS", "STORAGE",
+ COMPLETE_WITH("(", "COMPRESSION", "DEFAULT", "EXPRESSION", "GENERATED", "NOT NULL", "STATISTICS", "STORAGE",
/* a subset of ALTER SEQUENCE options */
"INCREMENT", "MINVALUE", "MAXVALUE", "START", "NO", "CACHE", "CYCLE");
/* ALTER TABLE ALTER [COLUMN] <foo> SET ( */
@@ -2494,6 +2494,14 @@ psql_completion(const char *text, int start, int end)
else if (Matches("ALTER", "TABLE", MatchAny, "ALTER", "COLUMN", MatchAny, "SET", "COMPRESSION") ||
Matches("ALTER", "TABLE", MatchAny, "ALTER", MatchAny, "SET", "COMPRESSION"))
COMPLETE_WITH("DEFAULT", "PGLZ", "LZ4");
+ /* ALTER TABLE ALTER [COLUMN] <foo> SET EXPRESSION */
+ else if (Matches("ALTER", "TABLE", MatchAny, "ALTER", "COLUMN", MatchAny, "SET", "EXPRESSION") ||
+ Matches("ALTER", "TABLE", MatchAny, "ALTER", MatchAny, "SET", "EXPRESSION"))
+ COMPLETE_WITH("AS");
+ /* ALTER TABLE ALTER [COLUMN] <foo> SET EXPRESSION AS */
+ else if (Matches("ALTER", "TABLE", MatchAny, "ALTER", "COLUMN", MatchAny, "SET", "EXPRESSION", "AS") ||
+ Matches("ALTER", "TABLE", MatchAny, "ALTER", MatchAny, "SET", "EXPRESSION", "AS"))
+ COMPLETE_WITH("(");
/* ALTER TABLE ALTER [COLUMN] <foo> SET GENERATED */
else if (Matches("ALTER", "TABLE", MatchAny, "ALTER", "COLUMN", MatchAny, "SET", "GENERATED") ||
Matches("ALTER", "TABLE", MatchAny, "ALTER", MatchAny, "SET", "GENERATED"))
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index e494309da8d..cd08391dcb0 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2192,6 +2192,7 @@ typedef enum AlterTableType
AT_DropNotNull, /* alter column drop not null */
AT_SetNotNull, /* alter column set not null */
AT_SetAttNotNull, /* set attnotnull w/o a constraint */
+ AT_SetExpression, /* alter column set expression */
AT_DropExpression, /* alter column drop expression */
AT_SetStatistics, /* alter column set statistics */
AT_SetOptions, /* alter column set ( options ) */
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 0302f79bb71..d34c7719dca 100644
--- a/src/test/modules/test_ddl_deparse/test_ddl_deparse.c
+++ b/src/test/modules/test_ddl_deparse/test_ddl_deparse.c
@@ -132,6 +132,9 @@ get_altertable_subcmdinfo(PG_FUNCTION_ARGS)
case AT_SetAttNotNull:
strtype = "SET ATTNOTNULL";
break;
+ case AT_SetExpression:
+ strtype = "SET EXPRESSION AS";
+ break;
case AT_DropExpression:
strtype = "DROP EXPRESSION";
break;
diff --git a/src/test/regress/expected/generated.out b/src/test/regress/expected/generated.out
index a2f38d0f50a..ffdddcf6fa9 100644
--- a/src/test/regress/expected/generated.out
+++ b/src/test/regress/expected/generated.out
@@ -578,6 +578,9 @@ INSERT INTO gtest20 (a) VALUES (10); -- ok
INSERT INTO gtest20 (a) VALUES (30); -- violates constraint
ERROR: new row for relation "gtest20" violates check constraint "gtest20_b_check"
DETAIL: Failing row contains (30, 60).
+ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS(a * 100); -- violates constraint
+ERROR: check constraint "gtest20_b_check" of relation "gtest20" is violated by some row
+ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS(a * 3); -- ok
CREATE TABLE gtest20a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED);
INSERT INTO gtest20a (a) VALUES (10);
INSERT INTO gtest20a (a) VALUES (30);
@@ -673,6 +676,46 @@ SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
1 | 2
(1 row)
+ALTER TABLE gtest22c ALTER COLUMN b SET EXPRESSION AS(a * 4);
+EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b = 8;
+ QUERY PLAN
+------------------------------------------------
+ Index Scan using gtest22c_pred_idx on gtest22c
+ Filter: (b = 8)
+(2 rows)
+
+SELECT * FROM gtest22c WHERE b = 8;
+ a | b
+---+---
+ 2 | 8
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 12;
+ QUERY PLAN
+------------------------------------------------
+ Index Scan using gtest22c_expr_idx on gtest22c
+ Index Cond: ((b * 3) = 12)
+(2 rows)
+
+SELECT * FROM gtest22c WHERE b * 3 = 12;
+ a | b
+---+---
+ 1 | 4
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
+ QUERY PLAN
+------------------------------------------------
+ Index Scan using gtest22c_pred_idx on gtest22c
+ Index Cond: (a = 1)
+(2 rows)
+
+SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
+ a | b
+---+---
+ 1 | 4
+(1 row)
+
RESET enable_seqscan;
RESET enable_bitmapscan;
-- foreign keys
@@ -698,6 +741,10 @@ INSERT INTO gtest23b VALUES (1); -- ok
INSERT INTO gtest23b VALUES (5); -- error
ERROR: insert or update on table "gtest23b" violates foreign key constraint "gtest23b_b_fkey"
DETAIL: Key (b)=(10) is not present in table "gtest23a".
+ALTER TABLE gtest23b ALTER COLUMN b SET EXPRESSION AS (a * 5); -- error
+ERROR: insert or update on table "gtest23b" violates foreign key constraint "gtest23b_b_fkey"
+DETAIL: Key (b)=(5) is not present in table "gtest23a".
+ALTER TABLE gtest23b ALTER COLUMN b SET EXPRESSION AS (a * 1); -- ok
DROP TABLE gtest23b;
DROP TABLE gtest23a;
CREATE TABLE gtest23p (x int, y int GENERATED ALWAYS AS (x * 2) STORED, PRIMARY KEY (y));
@@ -785,30 +832,119 @@ Partition of: gtest_parent FOR VALUES FROM ('08-01-2016') TO ('09-01-2016')
Partition of: gtest_parent FOR VALUES FROM ('09-01-2016') TO ('10-01-2016')
INSERT INTO gtest_parent (f1, f2) VALUES ('2016-07-15', 1);
-SELECT * FROM gtest_parent;
- f1 | f2 | f3
-------------+----+----
- 07-15-2016 | 1 | 2
-(1 row)
+INSERT INTO gtest_parent (f1, f2) VALUES ('2016-07-15', 2);
+INSERT INTO gtest_parent (f1, f2) VALUES ('2016-08-15', 3);
+SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
+ tableoid | f1 | f2 | f3
+--------------+------------+----+----
+ gtest_child | 07-15-2016 | 1 | 2
+ gtest_child | 07-15-2016 | 2 | 4
+ gtest_child2 | 08-15-2016 | 3 | 66
+(3 rows)
-SELECT * FROM gtest_child;
- f1 | f2 | f3
-------------+----+----
- 07-15-2016 | 1 | 2
-(1 row)
+UPDATE gtest_parent SET f1 = f1 + 60 WHERE f2 = 1;
+SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
+ tableoid | f1 | f2 | f3
+--------------+------------+----+----
+ gtest_child | 07-15-2016 | 2 | 4
+ gtest_child2 | 08-15-2016 | 3 | 66
+ gtest_child3 | 09-13-2016 | 1 | 33
+(3 rows)
-UPDATE gtest_parent SET f1 = f1 + 60;
-SELECT * FROM gtest_parent;
- f1 | f2 | f3
-------------+----+----
- 09-13-2016 | 1 | 33
-(1 row)
+-- alter only parent's and one child's generated expression
+ALTER TABLE ONLY gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 4);
+ALTER TABLE gtest_child ALTER COLUMN f3 SET EXPRESSION AS (f2 * 10);
+\d gtest_parent
+ Partitioned table "public.gtest_parent"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+-------------------------------------
+ f1 | date | | not null |
+ f2 | bigint | | |
+ f3 | bigint | | | generated always as (f2 * 4) stored
+Partition key: RANGE (f1)
+Number of partitions: 3 (Use \d+ to list them.)
-SELECT * FROM gtest_child3;
- f1 | f2 | f3
-------------+----+----
- 09-13-2016 | 1 | 33
-(1 row)
+\d gtest_child
+ Table "public.gtest_child"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+--------------------------------------
+ f1 | date | | not null |
+ f2 | bigint | | |
+ f3 | bigint | | | generated always as (f2 * 10) stored
+Partition of: gtest_parent FOR VALUES FROM ('07-01-2016') TO ('08-01-2016')
+
+\d gtest_child2
+ Table "public.gtest_child2"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+--------------------------------------
+ f1 | date | | not null |
+ f2 | bigint | | |
+ f3 | bigint | | | generated always as (f2 * 22) stored
+Partition of: gtest_parent FOR VALUES FROM ('08-01-2016') TO ('09-01-2016')
+
+\d gtest_child3
+ Table "public.gtest_child3"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+--------------------------------------
+ f1 | date | | not null |
+ f2 | bigint | | |
+ f3 | bigint | | | generated always as (f2 * 33) stored
+Partition of: gtest_parent FOR VALUES FROM ('09-01-2016') TO ('10-01-2016')
+
+SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
+ tableoid | f1 | f2 | f3
+--------------+------------+----+----
+ gtest_child | 07-15-2016 | 2 | 20
+ gtest_child2 | 08-15-2016 | 3 | 66
+ gtest_child3 | 09-13-2016 | 1 | 33
+(3 rows)
+
+-- alter generated expression of a parent and all it's child altogether
+ALTER TABLE gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 2);
+\d gtest_parent
+ Partitioned table "public.gtest_parent"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+-------------------------------------
+ f1 | date | | not null |
+ f2 | bigint | | |
+ f3 | bigint | | | generated always as (f2 * 2) stored
+Partition key: RANGE (f1)
+Number of partitions: 3 (Use \d+ to list them.)
+
+\d gtest_child
+ Table "public.gtest_child"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+-------------------------------------
+ f1 | date | | not null |
+ f2 | bigint | | |
+ f3 | bigint | | | generated always as (f2 * 2) stored
+Partition of: gtest_parent FOR VALUES FROM ('07-01-2016') TO ('08-01-2016')
+
+\d gtest_child2
+ Table "public.gtest_child2"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+-------------------------------------
+ f1 | date | | not null |
+ f2 | bigint | | |
+ f3 | bigint | | | generated always as (f2 * 2) stored
+Partition of: gtest_parent FOR VALUES FROM ('08-01-2016') TO ('09-01-2016')
+
+\d gtest_child3
+ Table "public.gtest_child3"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+-------------------------------------
+ f1 | date | | not null |
+ f2 | bigint | | |
+ f3 | bigint | | | generated always as (f2 * 2) stored
+Partition of: gtest_parent FOR VALUES FROM ('09-01-2016') TO ('10-01-2016')
+
+SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
+ tableoid | f1 | f2 | f3
+--------------+------------+----+----
+ gtest_child | 07-15-2016 | 2 | 4
+ gtest_child2 | 08-15-2016 | 3 | 6
+ gtest_child3 | 09-13-2016 | 1 | 2
+(3 rows)
-- we leave these tables around for purposes of testing dump/reload/upgrade
-- generated columns in partition key (not allowed)
@@ -935,18 +1071,50 @@ CREATE TABLE gtest29 (
b int GENERATED ALWAYS AS (a * 2) STORED
);
INSERT INTO gtest29 (a) VALUES (3), (4);
+SELECT * FROM gtest29;
+ a | b
+---+---
+ 3 | 6
+ 4 | 8
+(2 rows)
+
+\d gtest29
+ Table "public.gtest29"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+------------------------------------
+ a | integer | | |
+ b | integer | | | generated always as (a * 2) stored
+
+ALTER TABLE gtest29 ALTER COLUMN a SET EXPRESSION AS (a * 3); -- error
+ERROR: column "a" of relation "gtest29" is not a stored generated column
ALTER TABLE gtest29 ALTER COLUMN a DROP EXPRESSION; -- error
ERROR: column "a" of relation "gtest29" is not a stored generated column
ALTER TABLE gtest29 ALTER COLUMN a DROP EXPRESSION IF EXISTS; -- notice
NOTICE: column "a" of relation "gtest29" is not a stored generated column, skipping
+-- Change the expression
+ALTER TABLE gtest29 ALTER COLUMN b SET EXPRESSION AS (a * 3);
+SELECT * FROM gtest29;
+ a | b
+---+----
+ 3 | 9
+ 4 | 12
+(2 rows)
+
+\d gtest29
+ Table "public.gtest29"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+------------------------------------
+ a | integer | | |
+ b | integer | | | generated always as (a * 3) stored
+
ALTER TABLE gtest29 ALTER COLUMN b DROP EXPRESSION;
INSERT INTO gtest29 (a) VALUES (5);
INSERT INTO gtest29 (a, b) VALUES (6, 66);
SELECT * FROM gtest29;
a | b
---+----
- 3 | 6
- 4 | 8
+ 3 | 9
+ 4 | 12
5 |
6 | 66
(4 rows)
diff --git a/src/test/regress/sql/generated.sql b/src/test/regress/sql/generated.sql
index 298f6b3aa8b..790a0cdd8fe 100644
--- a/src/test/regress/sql/generated.sql
+++ b/src/test/regress/sql/generated.sql
@@ -293,6 +293,9 @@ CREATE TABLE gtest20 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORE
INSERT INTO gtest20 (a) VALUES (10); -- ok
INSERT INTO gtest20 (a) VALUES (30); -- violates constraint
+ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS(a * 100); -- violates constraint
+ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS(a * 3); -- ok
+
CREATE TABLE gtest20a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED);
INSERT INTO gtest20a (a) VALUES (10);
INSERT INTO gtest20a (a) VALUES (30);
@@ -341,6 +344,14 @@ EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 6;
SELECT * FROM gtest22c WHERE b * 3 = 6;
EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
+
+ALTER TABLE gtest22c ALTER COLUMN b SET EXPRESSION AS(a * 4);
+EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b = 8;
+SELECT * FROM gtest22c WHERE b = 8;
+EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 12;
+SELECT * FROM gtest22c WHERE b * 3 = 12;
+EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
+SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
RESET enable_seqscan;
RESET enable_bitmapscan;
@@ -356,6 +367,8 @@ CREATE TABLE gtest23b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STOR
INSERT INTO gtest23b VALUES (1); -- ok
INSERT INTO gtest23b VALUES (5); -- error
+ALTER TABLE gtest23b ALTER COLUMN b SET EXPRESSION AS (a * 5); -- error
+ALTER TABLE gtest23b ALTER COLUMN b SET EXPRESSION AS (a * 1); -- ok
DROP TABLE gtest23b;
DROP TABLE gtest23a;
@@ -414,11 +427,28 @@ ALTER TABLE gtest_parent ATTACH PARTITION gtest_child3 FOR VALUES FROM ('2016-09
\d gtest_child2
\d gtest_child3
INSERT INTO gtest_parent (f1, f2) VALUES ('2016-07-15', 1);
-SELECT * FROM gtest_parent;
-SELECT * FROM gtest_child;
-UPDATE gtest_parent SET f1 = f1 + 60;
-SELECT * FROM gtest_parent;
-SELECT * FROM gtest_child3;
+INSERT INTO gtest_parent (f1, f2) VALUES ('2016-07-15', 2);
+INSERT INTO gtest_parent (f1, f2) VALUES ('2016-08-15', 3);
+SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
+UPDATE gtest_parent SET f1 = f1 + 60 WHERE f2 = 1;
+SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
+
+-- alter only parent's and one child's generated expression
+ALTER TABLE ONLY gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 4);
+ALTER TABLE gtest_child ALTER COLUMN f3 SET EXPRESSION AS (f2 * 10);
+\d gtest_parent
+\d gtest_child
+\d gtest_child2
+\d gtest_child3
+SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
+
+-- alter generated expression of a parent and all it's child altogether
+ALTER TABLE gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 2);
+\d gtest_parent
+\d gtest_child
+\d gtest_child2
+\d gtest_child3
+SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
-- we leave these tables around for purposes of testing dump/reload/upgrade
-- generated columns in partition key (not allowed)
@@ -473,8 +503,17 @@ CREATE TABLE gtest29 (
b int GENERATED ALWAYS AS (a * 2) STORED
);
INSERT INTO gtest29 (a) VALUES (3), (4);
+SELECT * FROM gtest29;
+\d gtest29
+ALTER TABLE gtest29 ALTER COLUMN a SET EXPRESSION AS (a * 3); -- error
ALTER TABLE gtest29 ALTER COLUMN a DROP EXPRESSION; -- error
ALTER TABLE gtest29 ALTER COLUMN a DROP EXPRESSION IF EXISTS; -- notice
+
+-- Change the expression
+ALTER TABLE gtest29 ALTER COLUMN b SET EXPRESSION AS (a * 3);
+SELECT * FROM gtest29;
+\d gtest29
+
ALTER TABLE gtest29 ALTER COLUMN b DROP EXPRESSION;
INSERT INTO gtest29 (a) VALUES (5);
INSERT INTO gtest29 (a, b) VALUES (6, 66);
--
2.18.0
