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

Commit220003b

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 parent8720a15 commit220003b

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
@@ -2986,7 +2986,7 @@ relation_is_updatable(Oid reloid,
29862986
*
29872987
* This is used with simply-updatable views to map column-permissions sets for
29882988
* the view columns onto the matching columns in the underlying base relation.
2989-
*The targetlist is expected to be a list of plain Vars of the underlying
2989+
*Relevant entries in the targetlist must be plain Vars of the underlying
29902990
* relation (as per the checks above in view_query_is_auto_updatable).
29912991
*/
29922992
staticBitmapset*
@@ -3186,6 +3186,10 @@ rewriteTargetView(Query *parsetree, Relation view)
31863186
*/
31873187
viewquery=copyObject(get_view_query(view));
31883188

3189+
/* Locate RTE and perminfo describing the view in the outer query */
3190+
view_rte=rt_fetch(parsetree->resultRelation,parsetree->rtable);
3191+
view_perminfo=getRTEPermissionInfo(parsetree->rteperminfos,view_rte);
3192+
31893193
/*
31903194
* Are we doing INSERT/UPDATE, or MERGE containing INSERT/UPDATE? If so,
31913195
* various additional checks on the view columns need to be applied, and
@@ -3225,17 +3229,26 @@ rewriteTargetView(Query *parsetree, Relation view)
32253229

32263230
/*
32273231
* For INSERT/UPDATE (or MERGE containing INSERT/UPDATE) the modified
3228-
* columns must all be updatable. Note that we get the modified columns
3229-
* from the query's targetlist, not from the result RTE's insertedCols
3230-
* and/or updatedCols set, since rewriteTargetListIU may have added
3231-
* additional targetlist entries for view defaults, and these must also be
3232-
* updatable.
3232+
* columns must all be updatable.
32333233
*/
32343234
if (insert_or_update)
32353235
{
3236-
Bitmapset*modified_cols=NULL;
3236+
Bitmapset*modified_cols;
32373237
char*non_updatable_col;
32383238

3239+
/*
3240+
* Compute the set of modified columns as those listed in the result
3241+
* RTE's insertedCols and/or updatedCols sets plus those that are
3242+
* targets of the query's targetlist(s). We must consider the query's
3243+
* targetlist because rewriteTargetListIU may have added additional
3244+
* targetlist entries for view defaults, and these must also be
3245+
* updatable. But rewriteTargetListIU can also remove entries if they
3246+
* are DEFAULT markers and the column's default is NULL, so
3247+
* considering only the targetlist would also be wrong.
3248+
*/
3249+
modified_cols=bms_union(view_perminfo->insertedCols,
3250+
view_perminfo->updatedCols);
3251+
32393252
foreach(lc,parsetree->targetList)
32403253
{
32413254
TargetEntry*tle= (TargetEntry*)lfirst(lc);
@@ -3337,9 +3350,6 @@ rewriteTargetView(Query *parsetree, Relation view)
33373350
}
33383351
}
33393352

3340-
/* Locate RTE describing the view in the outer query */
3341-
view_rte=rt_fetch(parsetree->resultRelation,parsetree->rtable);
3342-
33433353
/*
33443354
* If we get here, view_query_is_auto_updatable() has verified that the
33453355
* view contains a single base relation.
@@ -3434,10 +3444,7 @@ rewriteTargetView(Query *parsetree, Relation view)
34343444
* Note: the original view's RTEPermissionInfo remains in the query's
34353445
* rteperminfos so that the executor still performs appropriate
34363446
* permissions checks for the query caller's use of the view.
3437-
*/
3438-
view_perminfo=getRTEPermissionInfo(parsetree->rteperminfos,view_rte);
3439-
3440-
/*
3447+
*
34413448
* Disregard the perminfo in viewquery->rteperminfos that the base_rte
34423449
* would currently be pointing at, because we'd like it to point now to a
34433450
* 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
@@ -2101,6 +2101,9 @@ DETAIL: View columns that refer to system columns are not updatable.
21012101
INSERT INTO rw_view1 (s, c, a) VALUES (null, null, 1.1); -- should fail
21022102
ERROR: cannot insert into column "s" of view "rw_view1"
21032103
DETAIL: View columns that are not columns of their base relation are not updatable.
2104+
INSERT INTO rw_view1 (s, c, a) VALUES (default, default, 1.1); -- should fail
2105+
ERROR: cannot insert into column "s" of view "rw_view1"
2106+
DETAIL: View columns that are not columns of their base relation are not updatable.
21042107
INSERT INTO rw_view1 (a) VALUES (1.1) RETURNING a, s, c; -- OK
21052108
a | s | c
21062109
-----+-------------------+-------------------

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

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

11121112
INSERT INTO rw_view1VALUES (null,null,1.1,null);-- should fail
11131113
INSERT INTO rw_view1 (s, c, a)VALUES (null,null,1.1);-- should fail
1114+
INSERT INTO rw_view1 (s, c, a)VALUES (default, default,1.1);-- should fail
11141115
INSERT INTO rw_view1 (a)VALUES (1.1) RETURNING a, s, c;-- OK
11151116
UPDATE rw_view1SET s= sWHERE a=1.1;-- should fail
11161117
UPDATE rw_view1SET a=1.05WHERE a=1.1 RETURNING s;-- OK

0 commit comments

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