Hi,
Currently, we have an option to drop the expression of stored generated
columns
as:
ALTER [ COLUMN ] column_name DROP EXPRESSION [ IF EXISTS ]
But don't have support to update that expression. The attached patch
provides
that as:
ALTER [ COLUMN ] column_name SET EXPRESSION expression
Note that this form of ALTER is meant to work for the column which is
already
generated. It then changes the generation expression in the catalog and
rewrite
the table, using the existing table rewrite facilities for ALTER TABLE.
Otherwise, an error will be reported.
To keep the code flow simple, I have renamed the existing function that was
in
use for DROP EXPRESSION so that it can be used for SET EXPRESSION as well,
which is a similar design as SET/DROP DEFAULT. I kept this renaming code
changes in a separate patch to minimize the diff in the main patch.
Demo:
-- Create table
CREATE TABLE t1 (x int, y int GENERATED ALWAYS AS (x * 2) STORED);
INSERT INTO t1 VALUES(generate_series(1,3));
-- Check the generated data
SELECT * FROM t1;
x | y
---+---
1 | 2
2 | 4
3 | 6
(3 rows)
-- Alter the expression
ALTER TABLE t1 ALTER COLUMN y SET EXPRESSION (x * 4);
-- Check the new data
SELECT * FROM t1;
x | y
---+----
1 | 4
2 | 8
3 | 12
(3 rows)
Thank you.
--
Regards,
Amul Sul
EDB: http://www.enterprisedb.com
From ef1448f7852000d5b701f9e3c7fe88737670740a Mon Sep 17 00:00:00 2001
From: Amul Sul <amul.sul@enterprisedb.com>
Date: Mon, 31 Jul 2023 15:43:51 +0530
Subject: [PATCH v1 2/2] Allow to change generated column expression
---
doc/src/sgml/ref/alter_table.sgml | 14 +-
src/backend/commands/tablecmds.c | 88 +++++++++----
src/backend/parser/gram.y | 10 ++
src/bin/psql/tab-complete.c | 2 +-
src/test/regress/expected/generated.out | 167 ++++++++++++++++++++----
src/test/regress/sql/generated.sql | 36 ++++-
6 files changed, 262 insertions(+), 55 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index d4d93eeb7c6..1b68dea8d9b 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>
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> ] } [...]
@@ -255,13 +256,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 3b499fc0d8e..df26afe16cb 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -456,7 +456,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);
@@ -5056,7 +5057,8 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab,
ATExecCheckNotNull(tab, 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);
@@ -8015,16 +8017,21 @@ 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),
@@ -8047,7 +8054,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")));
@@ -8058,7 +8065,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;
@@ -8102,16 +8110,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);
@@ -8138,6 +8151,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 b721fc88dee..c5bcd82399f 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> */
+ | ALTER opt_column ColId SET EXPRESSION a_expr
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+
+ n->subtype = AT_ColumnExpression;
+ n->name = $3;
+ n->def = $6;
+ $$ = (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 779fdc90cbc..eac074ffc1f 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", "EXPRESSION", "DEFAULT", "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/test/regress/expected/generated.out b/src/test/regress/expected/generated.out
index f5d802b9d14..5dd4edc084f 100644
--- a/src/test/regress/expected/generated.out
+++ b/src/test/regress/expected/generated.out
@@ -780,30 +780,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);
+ALTER TABLE gtest_child ALTER COLUMN f3 SET EXPRESSION (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;
+ 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);
+\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)
@@ -930,18 +1019,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); -- 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);
+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..e510f77cc33 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);
+ALTER TABLE gtest_child ALTER COLUMN f3 SET EXPRESSION (f2 * 10);
+\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);
+\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); -- 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);
+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 e6227a977c7c56c1d742862c897123d6422a0692 Mon Sep 17 00:00:00 2001
From: Amul Sul <amul.sul@enterprisedb.com>
Date: Mon, 31 Jul 2023 14:05:18 +0530
Subject: [PATCH v1 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 727f1517507..3b499fc0d8e 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -454,8 +454,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,
@@ -4361,7 +4363,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;
@@ -4669,11 +4671,11 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
/* No command-specific prep needed */
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);
@@ -5053,8 +5055,8 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab,
case AT_CheckNotNull: /* check column is already marked NOT NULL */
ATExecCheckNotNull(tab, 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);
@@ -6175,7 +6177,7 @@ alter_table_type_to_string(AlterTableType cmdtype)
return "ALTER COLUMN ... DROP NOT NULL";
case AT_SetNotNull:
return "ALTER COLUMN ... SET NOT NULL";
- case AT_DropExpression:
+ case AT_ColumnExpression:
return "ALTER COLUMN ... DROP EXPRESSION";
case AT_CheckNotNull:
return NULL; /* not real grammar */
@@ -8007,10 +8009,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
@@ -8056,7 +8058,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 15ece871a01..b721fc88dee 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 fe003ded504..a6e4b4e4096 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2215,7 +2215,7 @@ typedef enum AlterTableType
AT_CookedColumnDefault, /* add a pre-cooked column default */
AT_DropNotNull, /* alter column drop not null */
AT_SetNotNull, /* alter column set not null */
- AT_DropExpression, /* alter column drop expression */
+ AT_ColumnExpression, /* alter column drop expression */
AT_CheckNotNull, /* check column is already marked not null */
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 82f937fca4f..1e680bd7f6f 100644
--- a/src/test/modules/test_ddl_deparse/test_ddl_deparse.c
+++ b/src/test/modules/test_ddl_deparse/test_ddl_deparse.c
@@ -129,7 +129,7 @@ get_altertable_subcmdinfo(PG_FUNCTION_ARGS)
case AT_SetNotNull:
strtype = "SET NOT NULL";
break;
- case AT_DropExpression:
+ case AT_ColumnExpression:
strtype = "DROP EXPRESSION";
break;
case AT_CheckNotNull:
--
2.18.0
