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

Commit9695305

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 parentb82791c commit9695305

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

3087+
/* Locate RTE describing the view in the outer query */
3088+
view_rte=rt_fetch(parsetree->resultRelation,parsetree->rtable);
3089+
30873090
/* The view must be updatable, else fail */
30883091
auto_update_detail=
30893092
view_query_is_auto_updatable(viewquery,
@@ -3126,17 +3129,26 @@ rewriteTargetView(Query *parsetree, Relation view)
31263129
}
31273130

31283131
/*
3129-
* For INSERT/UPDATE the modified columns must all be updatable. Note that
3130-
* we get the modified columns from the query's targetlist, not from the
3131-
* result RTE's insertedCols and/or updatedCols set, since
3132-
* rewriteTargetListIU may have added additional targetlist entries for
3133-
* view defaults, and these must also be updatable.
3132+
* For INSERT/UPDATE the modified columns must all be updatable.
31343133
*/
31353134
if (parsetree->commandType!=CMD_DELETE)
31363135
{
3137-
Bitmapset*modified_cols=NULL;
3136+
Bitmapset*modified_cols;
31383137
char*non_updatable_col;
31393138

3139+
/*
3140+
* Compute the set of modified columns as those listed in the result
3141+
* RTE's insertedCols and/or updatedCols sets plus those that are
3142+
* targets of the query's targetlist(s). We must consider the query's
3143+
* targetlist because rewriteTargetListIU may have added additional
3144+
* targetlist entries for view defaults, and these must also be
3145+
* updatable. But rewriteTargetListIU can also remove entries if they
3146+
* are DEFAULT markers and the column's default is NULL, so
3147+
* considering only the targetlist would also be wrong.
3148+
*/
3149+
modified_cols=bms_union(view_rte->insertedCols,
3150+
view_rte->updatedCols);
3151+
31403152
foreach(lc,parsetree->targetList)
31413153
{
31423154
TargetEntry*tle= (TargetEntry*)lfirst(lc);
@@ -3194,9 +3206,6 @@ rewriteTargetView(Query *parsetree, Relation view)
31943206
}
31953207
}
31963208

3197-
/* Locate RTE describing the view in the outer query */
3198-
view_rte=rt_fetch(parsetree->resultRelation,parsetree->rtable);
3199-
32003209
/*
32013210
* If we get here, view_query_is_auto_updatable() has verified that the
32023211
* 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
@@ -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