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?
From 8e973bcd093ce25a5728f10aa9e73eb838406758 Mon Sep 17 00:00:00 2001 From: jian he <jian.universal...@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
