Here is the rebase version for the latest master head(673a17e3120).
I haven't done any other changes related to the ON UPDATE trigger since that
seems non-trivial; need a bit of work to add trigger support in
ATRewriteTable().
Also, I am not sure yet, if we were doing these changes, and the correct
direction
for that.
Regards,
Amul
From 0b6ca9d74ecb7debfe02af340843fa80c937684f Mon Sep 17 00:00:00 2001
From: Amul Sul <amul.sul@enterprisedb.com>
Date: Mon, 9 Oct 2023 12:00:04 +0530
Subject: [PATCH v2 2/2] Allow to change generated column expression
---
doc/src/sgml/ref/alter_table.sgml | 14 +-
src/backend/commands/tablecmds.c | 91 +++++++++----
src/backend/parser/gram.y | 10 ++
src/bin/psql/tab-complete.c | 2 +-
src/include/nodes/parsenodes.h | 2 +-
src/test/regress/expected/generated.out | 167 ++++++++++++++++++++----
src/test/regress/sql/generated.sql | 36 ++++-
7 files changed, 265 insertions(+), 57 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 2c4138e4e9f..84bf8fa6ef3 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 <replaceable class="parameter">expression</replaceable> STORED
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,13 +257,18 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
- <varlistentry id="sql-altertable-desc-drop-expression">
+ <varlistentry id="sql-altertable-desc-set-drop-expression">
<term><literal>DROP EXPRESSION [ IF EXISTS ]</literal></term>
<listitem>
<para>
- This form turns a stored generated column into a normal base column.
- Existing data in the columns is retained, but future changes will no
- longer apply the generation expression.
+ The <literal>SET</literal> form replaces stored generated value for a
+ column. Existing data in the columns is rewritten and all the future
+ changes will apply the new generation expression.
+ </para>
+ <para>
+ The <literal>DROP</literal> form turns a stored generated column into a
+ normal base column. Existing data in the columns is retained, but future
+ changes will no longer apply the generation expression.
</para>
<para>
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index e56f3af8e84..2f1d7d3531d 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -457,7 +457,8 @@ static ObjectAddress ATExecSetIdentity(Relation rel, const char *colName,
static ObjectAddress ATExecDropIdentity(Relation rel, const char *colName, bool missing_ok, LOCKMODE lockmode);
static void ATPrepColumnExpression(Relation rel, AlterTableCmd *cmd,
bool recurse, bool recursing, LOCKMODE lockmode);
-static ObjectAddress ATExecColumnExpression(Relation rel, const char *colName,
+static ObjectAddress ATExecColumnExpression(AlteredTableInfo *tab, Relation rel,
+ const char *colName, Node *newDefault,
bool missing_ok, LOCKMODE lockmode);
static ObjectAddress ATExecSetStatistics(Relation rel, const char *colName, int16 colNum,
Node *newValue, LOCKMODE lockmode);
@@ -4851,7 +4852,7 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode, context);
pass = AT_PASS_COL_ATTRS;
break;
- case AT_ColumnExpression: /* ALTER COLUMN EXPRESSION */
+ case AT_ColumnExpression: /* ALTER COLUMN SET/DROP EXPRESSION */
ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_FOREIGN_TABLE);
ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode, context);
ATPrepColumnExpression(rel, cmd, recurse, recursing, lockmode);
@@ -5237,7 +5238,8 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab,
address = ATExecSetAttNotNull(wqueue, rel, cmd->name, lockmode);
break;
case AT_ColumnExpression:
- address = ATExecColumnExpression(rel, cmd->name, cmd->missing_ok, lockmode);
+ address = ATExecColumnExpression(tab, rel, cmd->name, cmd->def,
+ cmd->missing_ok, lockmode);
break;
case AT_SetStatistics: /* ALTER COLUMN SET STATISTICS */
address = ATExecSetStatistics(rel, cmd->name, cmd->num, cmd->def, lockmode);
@@ -8314,16 +8316,22 @@ static void
ATPrepColumnExpression(Relation rel, AlterTableCmd *cmd, bool recurse, bool recursing, LOCKMODE lockmode)
{
/*
- * Reject ONLY if there are child tables. We could implement this, but it
- * is a bit complicated. GENERATED clauses must be attached to the column
- * definition and cannot be added later like DEFAULT, so if a child table
- * has a generation expression that the parent does not have, the child
- * column will necessarily be an attislocal column. So to implement ONLY
- * here, we'd need extra code to update attislocal of the direct child
- * tables, somewhat similar to how DROP COLUMN does it, so that the
- * resulting state can be properly dumped and restored.
+ * Only SET EXPRESSION would be having new expression for the replacement.
*/
- if (!recurse &&
+ bool isdrop = (cmd->def == NULL);
+
+ /*
+ * Reject ALTER TABLE ONLY ... DROP EXPRESSION if there are child tables.
+ * We could implement this, but it is a bit complicated. GENERATED
+ * clauses must be attached to the column definition and cannot be added
+ * later like DEFAULT, so if a child table has a generation expression
+ * that the parent does not have, the child column will necessarily be an
+ * attislocal column. So to implement ONLY here, we'd need extra code to
+ * update attislocal of the direct child tables, somewhat similar to how
+ * DROP COLUMN does it, so that the resulting state can be properly dumped
+ * and restored.
+ */
+ if (!recurse && isdrop &&
find_inheritance_children(RelationGetRelid(rel), lockmode))
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
@@ -8346,7 +8354,7 @@ ATPrepColumnExpression(Relation rel, AlterTableCmd *cmd, bool recurse, bool recu
attTup = (Form_pg_attribute) GETSTRUCT(tuple);
- if (attTup->attinhcount > 0)
+ if (attTup->attinhcount > 0 && isdrop)
ereport(ERROR,
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
errmsg("cannot drop generation expression from inherited column")));
@@ -8357,7 +8365,8 @@ ATPrepColumnExpression(Relation rel, AlterTableCmd *cmd, bool recurse, bool recu
* Return the address of the affected column.
*/
static ObjectAddress
-ATExecColumnExpression(Relation rel, const char *colName, bool missing_ok, LOCKMODE lockmode)
+ATExecColumnExpression(AlteredTableInfo *tab, Relation rel, const char *colName,
+ Node *newDefault, bool missing_ok, LOCKMODE lockmode)
{
HeapTuple tuple;
Form_pg_attribute attTup;
@@ -8401,16 +8410,21 @@ ATExecColumnExpression(Relation rel, const char *colName, bool missing_ok, LOCKM
}
}
- /*
- * Mark the column as no longer generated. (The atthasdef flag needs to
- * get cleared too, but RemoveAttrDefault will handle that.)
- */
- attTup->attgenerated = '\0';
- CatalogTupleUpdate(attrelation, &tuple->t_self, tuple);
+ /* DROP EXPRESSION */
+ if (newDefault == NULL)
+ {
+ /*
+ * Mark the column as no longer generated. (The atthasdef flag needs
+ * to get cleared too, but RemoveAttrDefault will handle that.)
+ */
+ attTup->attgenerated = '\0';
+ CatalogTupleUpdate(attrelation, &tuple->t_self, tuple);
+
+ InvokeObjectPostAlterHook(RelationRelationId,
+ RelationGetRelid(rel),
+ attnum);
+ }
- InvokeObjectPostAlterHook(RelationRelationId,
- RelationGetRelid(rel),
- attnum);
heap_freetuple(tuple);
table_close(attrelation, RowExclusiveLock);
@@ -8437,6 +8451,37 @@ ATExecColumnExpression(Relation rel, const char *colName, bool missing_ok, LOCKM
RemoveAttrDefault(RelationGetRelid(rel), attnum, DROP_RESTRICT,
false, false);
+ /* SET EXPRESSION */
+ if (newDefault)
+ {
+ Expr *defval;
+ NewColumnValue *newval;
+ RawColumnDefault *rawEnt;
+
+ /* Prepare to store the 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 EXPRESSION */
+ AddRelationNewConstraints(rel, list_make1(rawEnt), NIL,
+ false, true, false, NULL);
+ 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;
+ }
+
ObjectAddressSubSet(address, RelationRelationId,
RelationGetRelid(rel), attnum);
return address;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 3de8a774d10..dceb52119d7 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 <expr> STORED */
+ | ALTER opt_column ColId SET EXPRESSION '(' a_expr ')' STORED
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+
+ n->subtype = AT_ColumnExpression;
+ n->name = $3;
+ n->def = $7;
+ $$ = (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 93742fc6ac9..d94c4e3e9ea 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 ( */
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 332fc86ee02..8867f3d88a5 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2216,7 +2216,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_ColumnExpression, /* alter column drop expression */
+ AT_ColumnExpression, /* alter column set/drop expression */
AT_SetStatistics, /* alter column set statistics */
AT_SetOptions, /* alter column set ( options ) */
AT_ResetOptions, /* alter column reset ( options ) */
diff --git a/src/test/regress/expected/generated.out b/src/test/regress/expected/generated.out
index dc97ed3fe01..521b5fa1e2e 100644
--- a/src/test/regress/expected/generated.out
+++ b/src/test/regress/expected/generated.out
@@ -782,30 +782,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;
+ 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;
+ 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 (f2 * 4) STORED;
+ALTER TABLE gtest_child ALTER COLUMN f3 SET EXPRESSION (f2 * 10) STORED;
+\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;
+ 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 (f2 * 2) STORED;
+\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;
+ 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)
@@ -932,18 +1021,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 (a * 3) STORED; -- 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 (a * 3) STORED;
+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 8ddecf0cc38..f7af399b8cc 100644
--- a/src/test/regress/sql/generated.sql
+++ b/src/test/regress/sql/generated.sql
@@ -411,11 +411,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;
+UPDATE gtest_parent SET f1 = f1 + 60 WHERE f2 = 1;
+SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1;
+
+-- alter only parent's and one child's generated expression
+ALTER TABLE ONLY gtest_parent ALTER COLUMN f3 SET EXPRESSION (f2 * 4) STORED;
+ALTER TABLE gtest_child ALTER COLUMN f3 SET EXPRESSION (f2 * 10) STORED;
+\d gtest_parent
+\d gtest_child
+\d gtest_child2
+\d gtest_child3
+SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1;
+
+-- alter generated expression of a parent and all it's child altogether
+ALTER TABLE gtest_parent ALTER COLUMN f3 SET EXPRESSION (f2 * 2) STORED;
+\d gtest_parent
+\d gtest_child
+\d gtest_child2
+\d gtest_child3
+SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1;
-- we leave these tables around for purposes of testing dump/reload/upgrade
-- generated columns in partition key (not allowed)
@@ -470,8 +487,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 (a * 3) STORED; -- 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 (a * 3) STORED;
+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
From f7b628789edd64227ed0310f40d7d6a55ab9143d Mon Sep 17 00:00:00 2001
From: Amul Sul <amul.sul@enterprisedb.com>
Date: Mon, 9 Oct 2023 10:41:27 +0530
Subject: [PATCH v2 1/2] Prerequisite changes: rename functions & enum.
Note: this patch is to minimize diff from the next patch, not ment to
commit separatly.
---
src/backend/commands/tablecmds.c | 26 ++++++++++---------
src/backend/parser/gram.y | 4 +--
src/include/nodes/parsenodes.h | 2 +-
.../test_ddl_deparse/test_ddl_deparse.c | 2 +-
4 files changed, 18 insertions(+), 16 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 416a98e7cef..e56f3af8e84 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -455,8 +455,10 @@ 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 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 void ATPrepColumnExpression(Relation rel, AlterTableCmd *cmd,
+ bool recurse, bool recursing, LOCKMODE lockmode);
+static ObjectAddress ATExecColumnExpression(Relation rel, const char *colName,
+ bool missing_ok, LOCKMODE lockmode);
static ObjectAddress ATExecSetStatistics(Relation rel, const char *colName, int16 colNum,
Node *newValue, LOCKMODE lockmode);
static ObjectAddress ATExecSetOptions(Relation rel, const char *colName,
@@ -4548,7 +4550,7 @@ AlterTableGetLockLevel(List *cmds)
case AT_AddIdentity:
case AT_DropIdentity:
case AT_SetIdentity:
- case AT_DropExpression:
+ case AT_ColumnExpression:
case AT_SetCompression:
cmd_lockmode = AccessExclusiveLock;
break;
@@ -4849,11 +4851,11 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode, context);
pass = AT_PASS_COL_ATTRS;
break;
- case AT_DropExpression: /* ALTER COLUMN DROP EXPRESSION */
+ case AT_ColumnExpression: /* ALTER COLUMN EXPRESSION */
ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_FOREIGN_TABLE);
ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode, context);
- ATPrepDropExpression(rel, cmd, recurse, recursing, lockmode);
- pass = AT_PASS_DROP;
+ ATPrepColumnExpression(rel, cmd, recurse, recursing, lockmode);
+ pass = cmd->def ? AT_PASS_ADD_OTHERCONSTR : AT_PASS_DROP;
break;
case AT_SetStatistics: /* ALTER COLUMN SET STATISTICS */
ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_MATVIEW | ATT_INDEX | ATT_PARTITIONED_INDEX | ATT_FOREIGN_TABLE);
@@ -5234,8 +5236,8 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab,
case AT_SetAttNotNull: /* set pg_attribute.attnotnull */
address = ATExecSetAttNotNull(wqueue, rel, cmd->name, lockmode);
break;
- case AT_DropExpression:
- address = ATExecDropExpression(rel, cmd->name, cmd->missing_ok, lockmode);
+ case AT_ColumnExpression:
+ address = ATExecColumnExpression(rel, cmd->name, cmd->missing_ok, lockmode);
break;
case AT_SetStatistics: /* ALTER COLUMN SET STATISTICS */
address = ATExecSetStatistics(rel, cmd->name, cmd->num, cmd->def, lockmode);
@@ -6361,7 +6363,7 @@ alter_table_type_to_string(AlterTableType cmdtype)
return "ALTER COLUMN ... SET NOT NULL";
case AT_SetAttNotNull:
return NULL; /* not real grammar */
- case AT_DropExpression:
+ case AT_ColumnExpression:
return "ALTER COLUMN ... DROP EXPRESSION";
case AT_SetStatistics:
return "ALTER COLUMN ... SET STATISTICS";
@@ -8306,10 +8308,10 @@ ATExecDropIdentity(Relation rel, const char *colName, bool missing_ok, LOCKMODE
}
/*
- * ALTER TABLE ALTER COLUMN DROP EXPRESSION
+ * ALTER TABLE ALTER COLUMN DROP/SET EXPRESSION
*/
static void
-ATPrepDropExpression(Relation rel, AlterTableCmd *cmd, bool recurse, bool recursing, LOCKMODE lockmode)
+ATPrepColumnExpression(Relation rel, AlterTableCmd *cmd, bool recurse, bool recursing, LOCKMODE lockmode)
{
/*
* Reject ONLY if there are child tables. We could implement this, but it
@@ -8355,7 +8357,7 @@ ATPrepDropExpression(Relation rel, AlterTableCmd *cmd, bool recurse, bool recurs
* Return the address of the affected column.
*/
static ObjectAddress
-ATExecDropExpression(Relation rel, const char *colName, bool missing_ok, LOCKMODE lockmode)
+ATExecColumnExpression(Relation rel, const char *colName, bool missing_ok, LOCKMODE lockmode)
{
HeapTuple tuple;
Form_pg_attribute attTup;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index c224df4eccc..3de8a774d10 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -2409,7 +2409,7 @@ alter_table_cmd:
{
AlterTableCmd *n = makeNode(AlterTableCmd);
- n->subtype = AT_DropExpression;
+ n->subtype = AT_ColumnExpression;
n->name = $3;
$$ = (Node *) n;
}
@@ -2418,7 +2418,7 @@ alter_table_cmd:
{
AlterTableCmd *n = makeNode(AlterTableCmd);
- n->subtype = AT_DropExpression;
+ n->subtype = AT_ColumnExpression;
n->name = $3;
n->missing_ok = true;
$$ = (Node *) n;
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index f637937cd20..332fc86ee02 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2216,7 +2216,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_DropExpression, /* alter column drop expression */
+ AT_ColumnExpression, /* alter column drop expression */
AT_SetStatistics, /* alter column set statistics */
AT_SetOptions, /* alter column set ( options ) */
AT_ResetOptions, /* alter column reset ( 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..421e93ea209 100644
--- a/src/test/modules/test_ddl_deparse/test_ddl_deparse.c
+++ b/src/test/modules/test_ddl_deparse/test_ddl_deparse.c
@@ -132,7 +132,7 @@ get_altertable_subcmdinfo(PG_FUNCTION_ARGS)
case AT_SetAttNotNull:
strtype = "SET ATTNOTNULL";
break;
- case AT_DropExpression:
+ case AT_ColumnExpression:
strtype = "DROP EXPRESSION";
break;
case AT_SetStatistics:
--
2.18.0
