A small add-on to the generated columns feature: Add an ALTER TABLE
subcommand for dropping the generated property from a column, per SQL
standard.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From 8aa4710e1fdda3c605c1dd0c839f3cc7ff0b3918 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Mon, 9 Dec 2019 08:41:43 +0100
Subject: [PATCH] ALTER TABLE ... ALTER COLUMN ... DROP EXPRESSION
Add an ALTER TABLE subcommand for dropping the generated property from
a column, per SQL standard.
---
doc/src/sgml/ref/alter_table.sgml | 18 ++++++
src/backend/catalog/sql_features.txt | 2 +-
src/backend/commands/tablecmds.c | 82 ++++++++++++++++++++++++-
src/backend/parser/gram.y | 20 +++++-
src/bin/psql/tab-complete.c | 2 +-
src/include/nodes/parsenodes.h | 1 +
src/include/parser/kwlist.h | 1 +
src/test/regress/expected/generated.out | 72 ++++++++++++++++++++++
src/test/regress/sql/generated.sql | 28 +++++++++
9 files changed, 222 insertions(+), 4 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml
b/doc/src/sgml/ref/alter_table.sgml
index 8403c797e2..4bf449587c 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -46,6 +46,7 @@
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>
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> ] } [...]
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable>
DROP IDENTITY [ IF EXISTS ]
@@ -241,6 +242,23 @@ <title>Description</title>
</listitem>
</varlistentry>
+ <varlistentry>
+ <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.
+ </para>
+
+ <para>
+ If <literal>DROP EXPRESSION IF EXISTS</literal> is specified and the
+ column is not a stored generated column, no error is thrown. In this
+ case a notice is issued instead.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>ADD GENERATED { ALWAYS | BY DEFAULT } AS
IDENTITY</literal></term>
<term><literal>SET GENERATED { ALWAYS | BY DEFAULT }</literal></term>
diff --git a/src/backend/catalog/sql_features.txt
b/src/backend/catalog/sql_features.txt
index ab3e381cff..9f840ddfd2 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -252,7 +252,7 @@ F381 Extended schema manipulation 03 ALTER
TABLE statement: DROP CONSTRAINT clau
F382 Alter column data type YES
F383 Set column not null clause YES
F384 Drop identity property clause YES
-F385 Drop column generation expression clause NO
+F385 Drop column generation expression clause YES
F386 Set identity column generation clause YES
F391 Long identifiers YES
F392 Unicode escapes in identifiers YES
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 5440eb9015..0820c661da 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -386,6 +386,7 @@ 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 ATExecDropExpression(Relation rel, const char *colName,
bool missing_ok, LOCKMODE lockmode);
static void ATPrepSetStatistics(Relation rel, const char *colName, int16
colNum,
Node *newValue,
LOCKMODE lockmode);
static ObjectAddress ATExecSetStatistics(Relation rel, const char *colName,
int16 colNum,
@@ -3673,6 +3674,7 @@ AlterTableGetLockLevel(List *cmds)
case AT_AddIdentity:
case AT_DropIdentity:
case AT_SetIdentity:
+ case AT_DropExpression:
cmd_lockmode = AccessExclusiveLock;
break;
@@ -3947,6 +3949,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 */
+ ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE);
+ ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode);
+ pass = AT_PASS_DROP;
+ break;
case AT_SetStatistics: /* ALTER COLUMN SET STATISTICS */
ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode);
/* Performs own permission checks */
@@ -4266,6 +4273,9 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation
rel,
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);
+ break;
case AT_SetStatistics: /* ALTER COLUMN SET STATISTICS */
address = ATExecSetStatistics(rel, cmd->name, cmd->num,
cmd->def, lockmode);
break;
@@ -6432,7 +6442,9 @@ ATExecColumnDefault(Relation rel, const char *colName,
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("column \"%s\" of relation \"%s\" is a
generated column",
- colName,
RelationGetRelationName(rel))));
+ colName,
RelationGetRelationName(rel)),
+ newDefault || TupleDescAttr(tupdesc, attnum -
1)->attgenerated != ATTRIBUTE_GENERATED_STORED ? 0 :
+ errhint("Use ALTER TABLE ... ALTER COLUMN ...
DROP EXPRESSION instead.")));
/*
* Remove any old default for the column. We use RESTRICT here for
@@ -6700,6 +6712,74 @@ ATExecDropIdentity(Relation rel, const char *colName,
bool missing_ok, LOCKMODE
return address;
}
+/*
+ * ALTER TABLE ALTER COLUMN DROP EXPRESSION
+ *
+ * Return the address of the affected column.
+ */
+static ObjectAddress
+ATExecDropExpression(Relation rel, const char *colName, bool missing_ok,
LOCKMODE lockmode)
+{
+ HeapTuple tuple;
+ Form_pg_attribute attTup;
+ AttrNumber attnum;
+ Relation attrelation;
+ ObjectAddress address;
+
+ attrelation = table_open(AttributeRelationId, RowExclusiveLock);
+ tuple = SearchSysCacheCopyAttName(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)
+ {
+ if (!missing_ok)
+ ereport(ERROR,
+
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("column \"%s\" of relation
\"%s\" is not a stored generated column",
+ colName,
RelationGetRelationName(rel))));
+ else
+ {
+ ereport(NOTICE,
+ (errmsg("column \"%s\" of relation
\"%s\" is not a stored generated column, skipping",
+ colName,
RelationGetRelationName(rel))));
+ heap_freetuple(tuple);
+ table_close(attrelation, RowExclusiveLock);
+ return InvalidObjectAddress;
+ }
+ }
+
+ attTup->attgenerated = '\0';
+ CatalogTupleUpdate(attrelation, &tuple->t_self, tuple);
+
+ InvokeObjectPostAlterHook(RelationRelationId,
+ RelationGetRelid(rel),
+ attTup->attnum);
+ ObjectAddressSubSet(address, RelationRelationId,
+ RelationGetRelid(rel), attnum);
+ heap_freetuple(tuple);
+
+ table_close(attrelation, RowExclusiveLock);
+
+ CommandCounterIncrement();
+
+ RemoveAttrDefault(RelationGetRelid(rel), attnum, DROP_RESTRICT, false,
false);
+
+ return address;
+}
+
/*
* ALTER TABLE ALTER COLUMN SET STATISTICS
*/
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index c5086846de..25e1aa66d9 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -636,7 +636,7 @@ static Node *makeRecursiveViewSelect(char *relname, List
*aliases, Node *query);
DOUBLE_P DROP
EACH ELSE ENABLE_P ENCODING ENCRYPTED END_P ENUM_P ESCAPE EVENT EXCEPT
- EXCLUDE EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN
+ EXCLUDE EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXPRESSION
EXTENSION EXTERNAL EXTRACT
FALSE_P FAMILY FETCH FILTER FIRST_P FLOAT_P FOLLOWING FOR
@@ -2126,6 +2126,23 @@ alter_table_cmd:
n->name = $3;
$$ = (Node *)n;
}
+ /* ALTER TABLE <name> ALTER [COLUMN] <colname> DROP
EXPRESSION */
+ | ALTER opt_column ColId DROP EXPRESSION
+ {
+ AlterTableCmd *n =
makeNode(AlterTableCmd);
+ n->subtype = AT_DropExpression;
+ n->name = $3;
+ $$ = (Node *)n;
+ }
+ /* ALTER TABLE <name> ALTER [COLUMN] <colname> DROP
EXPRESSION IF EXISTS */
+ | ALTER opt_column ColId DROP EXPRESSION IF_P EXISTS
+ {
+ AlterTableCmd *n =
makeNode(AlterTableCmd);
+ n->subtype = AT_DropExpression;
+ n->name = $3;
+ n->missing_ok = true;
+ $$ = (Node *)n;
+ }
/* ALTER TABLE <name> ALTER [COLUMN] <colname> SET
STATISTICS <SignedIconst> */
| ALTER opt_column ColId SET STATISTICS SignedIconst
{
@@ -15193,6 +15210,7 @@ unreserved_keyword:
| EXCLUSIVE
| EXECUTE
| EXPLAIN
+ | EXPRESSION
| EXTENSION
| EXTERNAL
| FAMILY
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index df26826993..d258e25dc3 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -2007,7 +2007,7 @@ psql_completion(const char *text, int start, int end)
/* ALTER TABLE ALTER [COLUMN] <foo> DROP */
else if (Matches("ALTER", "TABLE", MatchAny, "ALTER", "COLUMN",
MatchAny, "DROP") ||
Matches("ALTER", "TABLE", MatchAny, "ALTER", MatchAny,
"DROP"))
- COMPLETE_WITH("DEFAULT", "IDENTITY", "NOT NULL");
+ COMPLETE_WITH("DEFAULT", "EXPRESSION", "IDENTITY", "NOT NULL");
else if (Matches("ALTER", "TABLE", MatchAny, "CLUSTER"))
COMPLETE_WITH("ON");
else if (Matches("ALTER", "TABLE", MatchAny, "CLUSTER", "ON"))
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index ff626cbe61..b7aaab69dc 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1764,6 +1764,7 @@ typedef enum AlterTableType
AT_ColumnDefault, /* alter column default */
AT_DropNotNull, /* alter column drop not null */
AT_SetNotNull, /* alter column set not null */
+ AT_DropExpression, /* 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/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 00ace8425e..5bf0c13dca 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -155,6 +155,7 @@ PG_KEYWORD("exclusive", EXCLUSIVE, UNRESERVED_KEYWORD)
PG_KEYWORD("execute", EXECUTE, UNRESERVED_KEYWORD)
PG_KEYWORD("exists", EXISTS, COL_NAME_KEYWORD)
PG_KEYWORD("explain", EXPLAIN, UNRESERVED_KEYWORD)
+PG_KEYWORD("expression", EXPRESSION, UNRESERVED_KEYWORD)
PG_KEYWORD("extension", EXTENSION, UNRESERVED_KEYWORD)
PG_KEYWORD("external", EXTERNAL, UNRESERVED_KEYWORD)
PG_KEYWORD("extract", EXTRACT, COL_NAME_KEYWORD)
diff --git a/src/test/regress/expected/generated.out
b/src/test/regress/expected/generated.out
index f62c93f468..0de480d2b8 100644
--- a/src/test/regress/expected/generated.out
+++ b/src/test/regress/expected/generated.out
@@ -621,6 +621,7 @@ ALTER TABLE gtest27 ALTER COLUMN b TYPE boolean USING b <>
0; -- error
ERROR: generation expression for column "b" cannot be cast automatically to
type boolean
ALTER TABLE gtest27 ALTER COLUMN b DROP DEFAULT; -- error
ERROR: column "b" of relation "gtest27" is a generated column
+HINT: Use ALTER TABLE ... ALTER COLUMN ... DROP EXPRESSION instead.
\d gtest27
Table "public.gtest27"
Column | Type | Collation | Nullable | Default
@@ -628,6 +629,77 @@ ERROR: column "b" of relation "gtest27" is a generated
column
a | integer | | |
b | numeric | | | generated always as ((a * 2)) stored
+-- ALTER TABLE ... ALTER COLUMN ... DROP EXPRESSION
+ALTER TABLE gtest27 ALTER COLUMN a DROP EXPRESSION; -- error
+ERROR: column "a" of relation "gtest27" is not a stored generated column
+ALTER TABLE gtest27 ALTER COLUMN a DROP EXPRESSION IF EXISTS; -- notice
+NOTICE: column "a" of relation "gtest27" is not a stored generated column,
skipping
+ALTER TABLE gtest27 ALTER COLUMN b DROP EXPRESSION;
+INSERT INTO gtest27 (a) VALUES (5);
+INSERT INTO gtest27 (a, b) VALUES (6, 66);
+SELECT * FROM gtest27;
+ a | b
+---+----
+ 3 | 6
+ 4 | 8
+ 5 |
+ 6 | 66
+(4 rows)
+
+\d gtest27
+ Table "public.gtest27"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+ b | numeric | | |
+
+-- with inheritance
+CREATE TABLE gtest29 (
+ a int,
+ b int GENERATED ALWAYS AS (a * 2) STORED
+);
+CREATE TABLE gtest29_1 () INHERITS (gtest29);
+ALTER TABLE gtest29 ALTER COLUMN b DROP EXPRESSION;
+\d gtest29
+ Table "public.gtest29"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+ b | integer | | |
+Number of child tables: 1 (Use \d+ to list them.)
+
+\d gtest29_1
+ Table "public.gtest29_1"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+ b | integer | | |
+Inherits: gtest29
+
+DROP TABLE gtest29 CASCADE;
+NOTICE: drop cascades to table gtest29_1
+CREATE TABLE gtest29 (
+ a int,
+ b int GENERATED ALWAYS AS (a * 2) STORED
+);
+CREATE TABLE gtest29_1 () INHERITS (gtest29);
+ALTER TABLE ONLY gtest29 ALTER COLUMN b DROP EXPRESSION;
+\d gtest29
+ Table "public.gtest29"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+ b | integer | | |
+Number of child tables: 1 (Use \d+ to list them.)
+
+\d gtest29_1
+ Table "public.gtest29_1"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+------------------------------------
+ a | integer | | |
+ b | integer | | | generated always as (a * 2) stored
+Inherits: gtest29
+
-- triggers
CREATE TABLE gtest26 (
a int PRIMARY KEY,
diff --git a/src/test/regress/sql/generated.sql
b/src/test/regress/sql/generated.sql
index 6a56ae260f..12f84458f2 100644
--- a/src/test/regress/sql/generated.sql
+++ b/src/test/regress/sql/generated.sql
@@ -332,6 +332,34 @@ CREATE TABLE gtest27 (
ALTER TABLE gtest27 ALTER COLUMN b DROP DEFAULT; -- error
\d gtest27
+-- ALTER TABLE ... ALTER COLUMN ... DROP EXPRESSION
+ALTER TABLE gtest27 ALTER COLUMN a DROP EXPRESSION; -- error
+ALTER TABLE gtest27 ALTER COLUMN a DROP EXPRESSION IF EXISTS; -- notice
+ALTER TABLE gtest27 ALTER COLUMN b DROP EXPRESSION;
+INSERT INTO gtest27 (a) VALUES (5);
+INSERT INTO gtest27 (a, b) VALUES (6, 66);
+SELECT * FROM gtest27;
+\d gtest27
+
+-- with inheritance
+CREATE TABLE gtest29 (
+ a int,
+ b int GENERATED ALWAYS AS (a * 2) STORED
+);
+CREATE TABLE gtest29_1 () INHERITS (gtest29);
+ALTER TABLE gtest29 ALTER COLUMN b DROP EXPRESSION;
+\d gtest29
+\d gtest29_1
+DROP TABLE gtest29 CASCADE;
+CREATE TABLE gtest29 (
+ a int,
+ b int GENERATED ALWAYS AS (a * 2) STORED
+);
+CREATE TABLE gtest29_1 () INHERITS (gtest29);
+ALTER TABLE ONLY gtest29 ALTER COLUMN b DROP EXPRESSION;
+\d gtest29
+\d gtest29_1
+
-- triggers
CREATE TABLE gtest26 (
a int PRIMARY KEY,
--
2.24.0
