Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit461f479

Browse files
committed
Correctly check updatability of columns targeted by INSERT...DEFAULT.
If a view has some updatable and some non-updatable columns, we failedto verify updatability of any columns for which an INSERT or UPDATEon the view explicitly specifies a DEFAULT item (unless the view hasa declared default for that column, which is rare anyway, and onewould almost certainly not write one for a non-updatable column).This would lead to an unexpected "attribute number N not found inview targetlist" error rather than the intended error.Per bug #18546 from Alexander Lakhin. This bug is old, so back-patchto all supported branches.Discussion:https://postgr.es/m/18546-84a292e759a9361d@postgresql.org
1 parentc5321e9 commit461f479

File tree

3 files changed

+23
-10
lines changed

3 files changed

+23
-10
lines changed

‎src/backend/rewrite/rewriteHandler.c‎

Lines changed: 19 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -2914,7 +2914,7 @@ relation_is_updatable(Oid reloid,
29142914
*
29152915
* This is used with simply-updatable views to map column-permissions sets for
29162916
* the view columns onto the matching columns in the underlying base relation.
2917-
*The targetlist is expected to be a list of plain Vars of the underlying
2917+
*Relevant entries in the targetlist must be plain Vars of the underlying
29182918
* relation (as per the checks above in view_query_is_auto_updatable).
29192919
*/
29202920
staticBitmapset*
@@ -3011,6 +3011,9 @@ rewriteTargetView(Query *parsetree, Relation view)
30113011
*/
30123012
viewquery=copyObject(get_view_query(view));
30133013

3014+
/* Locate RTE describing the view in the outer query */
3015+
view_rte=rt_fetch(parsetree->resultRelation,parsetree->rtable);
3016+
30143017
/* The view must be updatable, else fail */
30153018
auto_update_detail=
30163019
view_query_is_auto_updatable(viewquery,
@@ -3053,17 +3056,26 @@ rewriteTargetView(Query *parsetree, Relation view)
30533056
}
30543057

30553058
/*
3056-
* For INSERT/UPDATE the modified columns must all be updatable. Note that
3057-
* we get the modified columns from the query's targetlist, not from the
3058-
* result RTE's insertedCols and/or updatedCols set, since
3059-
* rewriteTargetListIU may have added additional targetlist entries for
3060-
* view defaults, and these must also be updatable.
3059+
* For INSERT/UPDATE the modified columns must all be updatable.
30613060
*/
30623061
if (parsetree->commandType!=CMD_DELETE)
30633062
{
3064-
Bitmapset*modified_cols=NULL;
3063+
Bitmapset*modified_cols;
30653064
char*non_updatable_col;
30663065

3066+
/*
3067+
* Compute the set of modified columns as those listed in the result
3068+
* RTE's insertedCols and/or updatedCols sets plus those that are
3069+
* targets of the query's targetlist(s). We must consider the query's
3070+
* targetlist because rewriteTargetListIU may have added additional
3071+
* targetlist entries for view defaults, and these must also be
3072+
* updatable. But rewriteTargetListIU can also remove entries if they
3073+
* are DEFAULT markers and the column's default is NULL, so
3074+
* considering only the targetlist would also be wrong.
3075+
*/
3076+
modified_cols=bms_union(view_rte->insertedCols,
3077+
view_rte->updatedCols);
3078+
30673079
foreach(lc,parsetree->targetList)
30683080
{
30693081
TargetEntry*tle= (TargetEntry*)lfirst(lc);
@@ -3121,9 +3133,6 @@ rewriteTargetView(Query *parsetree, Relation view)
31213133
}
31223134
}
31233135

3124-
/* Locate RTE describing the view in the outer query */
3125-
view_rte=rt_fetch(parsetree->resultRelation,parsetree->rtable);
3126-
31273136
/*
31283137
* If we get here, view_query_is_auto_updatable() has verified that the
31293138
* view contains a single base relation.

‎src/test/regress/expected/updatable_views.out‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1361,6 +1361,9 @@ DETAIL: View columns that refer to system columns are not updatable.
13611361
INSERT INTO rw_view1 (s, c, a) VALUES (null, null, 1.1); -- should fail
13621362
ERROR: cannot insert into column "s" of view "rw_view1"
13631363
DETAIL: View columns that are not columns of their base relation are not updatable.
1364+
INSERT INTO rw_view1 (s, c, a) VALUES (default, default, 1.1); -- should fail
1365+
ERROR: cannot insert into column "s" of view "rw_view1"
1366+
DETAIL: View columns that are not columns of their base relation are not updatable.
13641367
INSERT INTO rw_view1 (a) VALUES (1.1) RETURNING a, s, c; -- OK
13651368
a | s | c
13661369
-----+-------------------+-------------------

‎src/test/regress/sql/updatable_views.sql‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -665,6 +665,7 @@ CREATE VIEW rw_view1 AS
665665

666666
INSERT INTO rw_view1VALUES (null,null,1.1,null);-- should fail
667667
INSERT INTO rw_view1 (s, c, a)VALUES (null,null,1.1);-- should fail
668+
INSERT INTO rw_view1 (s, c, a)VALUES (default, default,1.1);-- should fail
668669
INSERT INTO rw_view1 (a)VALUES (1.1) RETURNING a, s, c;-- OK
669670
UPDATE rw_view1SET s= sWHERE a=1.1;-- should fail
670671
UPDATE rw_view1SET a=1.05WHERE a=1.1 RETURNING s;-- OK

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp