disallow ALTER VIEW SET DEFAULT when the corresponding base relation column is a generated column
hi.
CREATE TABLE gtest1 (a int, b int GENERATED ALWAYS AS (a * 2) STORED);
CREATE VIEW gtest1v AS SELECT * FROM gtest1;
ALTER VIEW gtest1v ALTER COLUMN b SET DEFAULT 100;
INSERT INTO gtest1v VALUES (8, DEFAULT) returning *;
ERROR: cannot insert a non-DEFAULT value into column "b"
DETAIL: Column "b" is a generated column.
we can make
ALTER VIEW gtest1v ALTER COLUMN b SET DEFAULT 100;
error out,
then
INSERT INTO gtest1v VALUES (8, DEFAULT) returning *;
will work just fine.
obviously,
INSERT INTO gtest1v VALUES (8, 1) returning *;
will fail.
we can do this by in ATExecColumnDefault,
checking if
* gtest1v is updatable view or not
* column b is an updatable column or not
* column b on view corresponding base relation's column is a generated
column or not.
if all these conditions meet then, we error out saying
``cannot alter column \"%s\" on updateable view ``.
what do you think?
Attachments:
v1-0001-disallow-set-default-when-baserel-column-is-generated.patchtext/x-patch; charset=US-ASCII; name=v1-0001-disallow-set-default-when-baserel-column-is-generated.patchDownload
From 8e973bcd093ce25a5728f10aa9e73eb838406758 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Fri, 11 Apr 2025 15:41:10 +0800
Subject: [PATCH v1 1/1] disallow set default when baserel column is generated
disallow change updatable view column default expression when the corresponding
base column is generated column.
discussion: https://postgr.es/m/
---
src/backend/commands/tablecmds.c | 54 +++++++++++++++++++
src/backend/rewrite/rewriteHandler.c | 2 +-
src/include/rewrite/rewriteHandler.h | 4 ++
.../regress/expected/generated_stored.out | 29 +++++-----
.../regress/expected/generated_virtual.out | 29 +++++-----
src/test/regress/sql/generated_stored.sql | 6 +--
src/test/regress/sql/generated_virtual.sql | 6 +--
7 files changed, 95 insertions(+), 35 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 686f1850cab..5548b629a0c 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -81,6 +81,7 @@
#include "parser/parse_relation.h"
#include "parser/parse_type.h"
#include "parser/parse_utilcmd.h"
+#include "parser/parsetree.h"
#include "parser/parser.h"
#include "partitioning/partbounds.h"
#include "partitioning/partdesc.h"
@@ -8156,6 +8157,59 @@ ATExecColumnDefault(Relation rel, const char *colName,
(TupleDescAttr(tupdesc, attnum - 1)->attgenerated == ATTRIBUTE_GENERATED_STORED ?
errhint("Use %s instead.", "ALTER TABLE ... ALTER COLUMN ... DROP EXPRESSION") : 0)));
+
+ /* Check if this is an automatically updatable view */
+ if (rel->rd_rel->relkind == RELKIND_VIEW && newDefault != NULL)
+ {
+ Query *viewquery = get_view_query(rel);
+
+ if (view_query_is_auto_updatable(viewquery, true) == NULL)
+ {
+ Bitmapset *set_col = NULL;
+
+ set_col = bms_add_member(set_col,
+ attnum - FirstLowInvalidHeapAttributeNumber);
+
+ if (view_cols_are_auto_updatable(viewquery, set_col, NULL, NULL) == NULL)
+ {
+ RangeTblRef *rtr;
+ RangeTblEntry *base_rte;
+ Relation base_rel;
+ TupleDesc rel_tupdesc;
+ TargetEntry *tle;
+ AttrNumber attno;
+
+ rtr = (RangeTblRef *) linitial(viewquery->jointree->fromlist);
+ base_rte = rt_fetch(rtr->rtindex, viewquery->rtable);
+ Assert(base_rte->rtekind == RTE_RELATION);
+
+ base_rel = table_open(base_rte->relid, AccessShareLock);
+ rel_tupdesc = RelationGetDescr(base_rel);
+
+
+ tle = (TargetEntry *) list_nth(viewquery->targetList, attnum - 1);
+ Assert(!tle->resjunk);
+ Assert(IsA(tle->expr, Var));
+
+ attno = ((Var *) tle->expr)->varattno;
+
+ if (TupleDescAttr(rel_tupdesc, attno - 1)->attgenerated)
+ {
+ Form_pg_attribute att = TupleDescAttr(tupdesc, attno - 1);
+
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot alter column \"%s\" default expression on view \"%s\"",
+ colName, RelationGetRelationName(rel)),
+ errdetail("Column \"%s\" on base relation \"%s\" is a generated column",
+ NameStr(att->attname),
+ RelationGetRelationName(base_rel)));
+ }
+ table_close(base_rel, AccessShareLock);
+ }
+ }
+ }
+
/*
* Remove any old default for the column. We use RESTRICT here for
* safety, but at present we do not expect anything to depend on the
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index f0bce5f9ed9..5a3a9ed0c94 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -2776,7 +2776,7 @@ view_query_is_auto_updatable(Query *viewquery, bool check_cols)
* We do not check whether the referenced columns of the base relation are
* updatable.
*/
-static const char *
+const char *
view_cols_are_auto_updatable(Query *viewquery,
Bitmapset *required_cols,
Bitmapset **updatable_cols,
diff --git a/src/include/rewrite/rewriteHandler.h b/src/include/rewrite/rewriteHandler.h
index 99cab1a3bfa..6a4cb14d150 100644
--- a/src/include/rewrite/rewriteHandler.h
+++ b/src/include/rewrite/rewriteHandler.h
@@ -29,6 +29,10 @@ extern bool view_has_instead_trigger(Relation view, CmdType event,
List *mergeActionList);
extern const char *view_query_is_auto_updatable(Query *viewquery,
bool check_cols);
+extern const char *view_cols_are_auto_updatable(Query *viewquery,
+ Bitmapset *required_cols,
+ Bitmapset **updatable_cols,
+ char **non_updatable_col);
extern int relation_is_updatable(Oid reloid,
List *outer_reloids,
bool include_triggers,
diff --git a/src/test/regress/expected/generated_stored.out b/src/test/regress/expected/generated_stored.out
index ffa844ca903..4b8f242357a 100644
--- a/src/test/regress/expected/generated_stored.out
+++ b/src/test/regress/expected/generated_stored.out
@@ -286,21 +286,22 @@ INSERT INTO gtest1v VALUES (6, 66), (7, DEFAULT); -- error
ERROR: cannot insert a non-DEFAULT value into column "b"
DETAIL: Column "b" is a generated column.
INSERT INTO gtest1v VALUES (6, DEFAULT), (7, DEFAULT); -- ok
-ALTER VIEW gtest1v ALTER COLUMN b SET DEFAULT 100;
-INSERT INTO gtest1v VALUES (8, DEFAULT); -- error
-ERROR: cannot insert a non-DEFAULT value into column "b"
-DETAIL: Column "b" is a generated column.
-INSERT INTO gtest1v VALUES (8, DEFAULT), (9, DEFAULT); -- error
-ERROR: cannot insert a non-DEFAULT value into column "b"
-DETAIL: Column "b" is a generated column.
+ALTER VIEW gtest1v ALTER COLUMN b SET DEFAULT 100; --error
+ERROR: cannot alter column "b" default expression on view "gtest1v"
+DETAIL: Column "b" on base relation "gtest1" is a generated column
+INSERT INTO gtest1v VALUES (8, DEFAULT); -- ok
+INSERT INTO gtest1v VALUES (9, DEFAULT), (10, DEFAULT); -- ok
SELECT * FROM gtest1v;
- a | b
----+----
- 3 | 6
- 5 | 10
- 6 | 12
- 7 | 14
-(4 rows)
+ a | b
+----+----
+ 3 | 6
+ 5 | 10
+ 6 | 12
+ 7 | 14
+ 8 | 16
+ 9 | 18
+ 10 | 20
+(7 rows)
DELETE FROM gtest1v WHERE a >= 5;
DROP VIEW gtest1v;
diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out
index 26bbe1e9c31..1fd07d53f22 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -280,21 +280,22 @@ INSERT INTO gtest1v VALUES (6, 66), (7, DEFAULT); -- error
ERROR: cannot insert a non-DEFAULT value into column "b"
DETAIL: Column "b" is a generated column.
INSERT INTO gtest1v VALUES (6, DEFAULT), (7, DEFAULT); -- ok
-ALTER VIEW gtest1v ALTER COLUMN b SET DEFAULT 100;
-INSERT INTO gtest1v VALUES (8, DEFAULT); -- error
-ERROR: cannot insert a non-DEFAULT value into column "b"
-DETAIL: Column "b" is a generated column.
-INSERT INTO gtest1v VALUES (8, DEFAULT), (9, DEFAULT); -- error
-ERROR: cannot insert a non-DEFAULT value into column "b"
-DETAIL: Column "b" is a generated column.
+ALTER VIEW gtest1v ALTER COLUMN b SET DEFAULT 100; --error
+ERROR: cannot alter column "b" default expression on view "gtest1v"
+DETAIL: Column "b" on base relation "gtest1" is a generated column
+INSERT INTO gtest1v VALUES (8, DEFAULT); -- ok
+INSERT INTO gtest1v VALUES (9, DEFAULT), (10, DEFAULT); -- ok
SELECT * FROM gtest1v;
- a | b
----+----
- 3 | 6
- 5 | 10
- 6 | 12
- 7 | 14
-(4 rows)
+ a | b
+----+----
+ 3 | 6
+ 5 | 10
+ 6 | 12
+ 7 | 14
+ 8 | 16
+ 9 | 18
+ 10 | 20
+(7 rows)
DELETE FROM gtest1v WHERE a >= 5;
DROP VIEW gtest1v;
diff --git a/src/test/regress/sql/generated_stored.sql b/src/test/regress/sql/generated_stored.sql
index ba8ae62dea0..6ee9db2df8f 100644
--- a/src/test/regress/sql/generated_stored.sql
+++ b/src/test/regress/sql/generated_stored.sql
@@ -123,9 +123,9 @@ INSERT INTO gtest1v VALUES (6, DEFAULT), (7, 77); -- error
INSERT INTO gtest1v VALUES (6, 66), (7, DEFAULT); -- error
INSERT INTO gtest1v VALUES (6, DEFAULT), (7, DEFAULT); -- ok
-ALTER VIEW gtest1v ALTER COLUMN b SET DEFAULT 100;
-INSERT INTO gtest1v VALUES (8, DEFAULT); -- error
-INSERT INTO gtest1v VALUES (8, DEFAULT), (9, DEFAULT); -- error
+ALTER VIEW gtest1v ALTER COLUMN b SET DEFAULT 100; --error
+INSERT INTO gtest1v VALUES (8, DEFAULT); -- ok
+INSERT INTO gtest1v VALUES (9, DEFAULT), (10, DEFAULT); -- ok
SELECT * FROM gtest1v;
DELETE FROM gtest1v WHERE a >= 5;
diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql
index 13cfbd76859..d095beb1201 100644
--- a/src/test/regress/sql/generated_virtual.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -123,9 +123,9 @@ INSERT INTO gtest1v VALUES (6, DEFAULT), (7, 77); -- error
INSERT INTO gtest1v VALUES (6, 66), (7, DEFAULT); -- error
INSERT INTO gtest1v VALUES (6, DEFAULT), (7, DEFAULT); -- ok
-ALTER VIEW gtest1v ALTER COLUMN b SET DEFAULT 100;
-INSERT INTO gtest1v VALUES (8, DEFAULT); -- error
-INSERT INTO gtest1v VALUES (8, DEFAULT), (9, DEFAULT); -- error
+ALTER VIEW gtest1v ALTER COLUMN b SET DEFAULT 100; --error
+INSERT INTO gtest1v VALUES (8, DEFAULT); -- ok
+INSERT INTO gtest1v VALUES (9, DEFAULT), (10, DEFAULT); -- ok
SELECT * FROM gtest1v;
DELETE FROM gtest1v WHERE a >= 5;
--
2.34.1
jian he <jian.universality@gmail.com> writes:
CREATE TABLE gtest1 (a int, b int GENERATED ALWAYS AS (a * 2) STORED);
CREATE VIEW gtest1v AS SELECT * FROM gtest1;
ALTER VIEW gtest1v ALTER COLUMN b SET DEFAULT 100;
INSERT INTO gtest1v VALUES (8, DEFAULT) returning *;
ERROR: cannot insert a non-DEFAULT value into column "b"
DETAIL: Column "b" is a generated column.
we can make
ALTER VIEW gtest1v ALTER COLUMN b SET DEFAULT 100;
error out,
This is not an improvement over having the error happen at run time.
(1) What if the state of the underlying column changes between the
ALTER VIEW and the INSERT? Either you have rejected something
that could have worked, or in the other direction you're going to get
the run-time error anyway.
(2) I don't see anything wrong or surprising about the run-time
error anyway, thus I fail to see that this is an improvement,
even aside from (1).
regards, tom lane
On Friday, April 11, 2025, Tom Lane <tgl@sss.pgh.pa.us> wrote:
jian he <jian.universality@gmail.com> writes:
CREATE TABLE gtest1 (a int, b int GENERATED ALWAYS AS (a * 2) STORED);
CREATE VIEW gtest1v AS SELECT * FROM gtest1;
ALTER VIEW gtest1v ALTER COLUMN b SET DEFAULT 100;INSERT INTO gtest1v VALUES (8, DEFAULT) returning *;
ERROR: cannot insert a non-DEFAULT value into column "b"
DETAIL: Column "b" is a generated column.we can make
ALTER VIEW gtest1v ALTER COLUMN b SET DEFAULT 100;
error out,This is not an improvement over having the error happen at run time.
(1) What if the state of the underlying column changes between the
ALTER VIEW and the INSERT? Either you have rejected something
that could have worked, or in the other direction you're going to get
the run-time error anyway.
I concur. The view is only loosely coupled to the base relation, via the
rewrite rule which is applied at runtime. Putting checks in place that
strongly couples the two relations adds a coupling burden that we are
better off avoiding.
David J.