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

Commitfd958bb

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 parent34e9dce commitfd958bb

File tree

3 files changed

+25
-14
lines changed

3 files changed

+25
-14
lines changed

‎src/backend/rewrite/rewriteHandler.c

Lines changed: 21 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -2963,7 +2963,7 @@ relation_is_updatable(Oid reloid,
29632963
*
29642964
* This is used with simply-updatable views to map column-permissions sets for
29652965
* the view columns onto the matching columns in the underlying base relation.
2966-
*The targetlist is expected to be a list of plain Vars of the underlying
2966+
*Relevant entries in the targetlist must be plain Vars of the underlying
29672967
* relation (as per the checks above in view_query_is_auto_updatable).
29682968
*/
29692969
staticBitmapset*
@@ -3063,6 +3063,10 @@ rewriteTargetView(Query *parsetree, Relation view)
30633063
*/
30643064
viewquery=copyObject(get_view_query(view));
30653065

3066+
/* Locate RTE and perminfo describing the view in the outer query */
3067+
view_rte=rt_fetch(parsetree->resultRelation,parsetree->rtable);
3068+
view_perminfo=getRTEPermissionInfo(parsetree->rteperminfos,view_rte);
3069+
30663070
/* The view must be updatable, else fail */
30673071
auto_update_detail=
30683072
view_query_is_auto_updatable(viewquery,
@@ -3105,17 +3109,26 @@ rewriteTargetView(Query *parsetree, Relation view)
31053109
}
31063110

31073111
/*
3108-
* For INSERT/UPDATE the modified columns must all be updatable. Note that
3109-
* we get the modified columns from the query's targetlist, not from the
3110-
* result RTE's insertedCols and/or updatedCols set, since
3111-
* rewriteTargetListIU may have added additional targetlist entries for
3112-
* view defaults, and these must also be updatable.
3112+
* For INSERT/UPDATE the modified columns must all be updatable.
31133113
*/
31143114
if (parsetree->commandType!=CMD_DELETE)
31153115
{
3116-
Bitmapset*modified_cols=NULL;
3116+
Bitmapset*modified_cols;
31173117
char*non_updatable_col;
31183118

3119+
/*
3120+
* Compute the set of modified columns as those listed in the result
3121+
* RTE's insertedCols and/or updatedCols sets plus those that are
3122+
* targets of the query's targetlist(s). We must consider the query's
3123+
* targetlist because rewriteTargetListIU may have added additional
3124+
* targetlist entries for view defaults, and these must also be
3125+
* updatable. But rewriteTargetListIU can also remove entries if they
3126+
* are DEFAULT markers and the column's default is NULL, so
3127+
* considering only the targetlist would also be wrong.
3128+
*/
3129+
modified_cols=bms_union(view_perminfo->insertedCols,
3130+
view_perminfo->updatedCols);
3131+
31193132
foreach(lc,parsetree->targetList)
31203133
{
31213134
TargetEntry*tle= (TargetEntry*)lfirst(lc);
@@ -3173,9 +3186,6 @@ rewriteTargetView(Query *parsetree, Relation view)
31733186
}
31743187
}
31753188

3176-
/* Locate RTE describing the view in the outer query */
3177-
view_rte=rt_fetch(parsetree->resultRelation,parsetree->rtable);
3178-
31793189
/*
31803190
* If we get here, view_query_is_auto_updatable() has verified that the
31813191
* view contains a single base relation.
@@ -3270,10 +3280,7 @@ rewriteTargetView(Query *parsetree, Relation view)
32703280
* Note: the original view's RTEPermissionInfo remains in the query's
32713281
* rteperminfos so that the executor still performs appropriate
32723282
* permissions checks for the query caller's use of the view.
3273-
*/
3274-
view_perminfo=getRTEPermissionInfo(parsetree->rteperminfos,view_rte);
3275-
3276-
/*
3283+
*
32773284
* Disregard the perminfo in viewquery->rteperminfos that the base_rte
32783285
* would currently be pointing at, because we'd like it to point now to a
32793286
* new one that will be filled below. Must set perminfoindex to 0 to not

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

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1598,6 +1598,9 @@ DETAIL: View columns that refer to system columns are not updatable.
15981598
INSERT INTO rw_view1 (s, c, a) VALUES (null, null, 1.1); -- should fail
15991599
ERROR: cannot insert into column "s" of view "rw_view1"
16001600
DETAIL: View columns that are not columns of their base relation are not updatable.
1601+
INSERT INTO rw_view1 (s, c, a) VALUES (default, default, 1.1); -- should fail
1602+
ERROR: cannot insert into column "s" of view "rw_view1"
1603+
DETAIL: View columns that are not columns of their base relation are not updatable.
16011604
INSERT INTO rw_view1 (a) VALUES (1.1) RETURNING a, s, c; -- OK
16021605
a | s | c
16031606
-----+-------------------+-------------------

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

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

846846
INSERT INTO rw_view1VALUES (null,null,1.1,null);-- should fail
847847
INSERT INTO rw_view1 (s, c, a)VALUES (null,null,1.1);-- should fail
848+
INSERT INTO rw_view1 (s, c, a)VALUES (default, default,1.1);-- should fail
848849
INSERT INTO rw_view1 (a)VALUES (1.1) RETURNING a, s, c;-- OK
849850
UPDATE rw_view1SET s= sWHERE a=1.1;-- should fail
850851
UPDATE rw_view1SET a=1.05WHERE a=1.1 RETURNING s;-- OK

0 commit comments

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