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

Commit353cd82

Browse files
committed
Make rewriter prevent auto-updates on views with conditional INSTEAD rules.
A view with conditional INSTEAD rules and no unconditional INSTEADrules or INSTEAD OF triggers is not auto-updatable. Previously werelied on a check in the executor to catch this, but that'sproblematic since the planner may fail to properly handle such a queryand thus return a particularly unhelpful error to the user, beforereaching the executor check.Instead, trap this in the rewriter and report the correct error there.Doing so also allows us to include more useful error detail than theexecutor check can provide. This doesn't change the existing behaviourof updatable views; it merely ensures that useful error messages arereported when a view isn't updatable.Per report from Pengzhou Tang, though not adopting that suggested fix.Back-patch to all supported branches.Discussion:https://postgr.es/m/CAG4reAQn+4xB6xHJqWdtE0ve_WqJkdyCV4P=trYr4Kn8_3_PEA@mail.gmail.com
1 parentf9e9525 commit353cd82

File tree

4 files changed

+94
-9
lines changed

4 files changed

+94
-9
lines changed

‎src/backend/executor/execMain.c

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1126,10 +1126,10 @@ CheckValidResultRel(ResultRelInfo *resultRelInfo, CmdType operation)
11261126

11271127
/*
11281128
* Okay only if there's a suitable INSTEAD OF trigger. Messages
1129-
* here should match rewriteHandler.c's rewriteTargetView, except
1130-
* that we omit errdetail because we haven't got the information
1131-
* handy (and given that we really shouldn't get here anyway, it's
1132-
* not worth great exertion to get).
1129+
* here should match rewriteHandler.c's rewriteTargetView and
1130+
*RewriteQuery, exceptthat we omit errdetail because we haven't
1131+
*got the informationhandy (and given that we really shouldn't
1132+
*get here anyway, it'snot worth great exertion to get).
11331133
*/
11341134
switch (operation)
11351135
{

‎src/backend/rewrite/rewriteHandler.c

Lines changed: 55 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -3625,21 +3625,71 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
36253625
}
36263626

36273627
/*
3628-
* If therewere no INSTEADrules, and the target relation is a view
3629-
* without any INSTEAD OF triggers, see if the view can be
3628+
* If therewas nounqualifiedINSTEADrule, and the target relation
3629+
*is a viewwithout any INSTEAD OF triggers, see if the view can be
36303630
* automatically updated. If so, we perform the necessary query
36313631
* transformation here and add the resulting query to the
36323632
* product_queries list, so that it gets recursively rewritten if
36333633
* necessary.
3634+
*
3635+
* If the view cannot be automatically updated, we throw an error here
3636+
* which is OK since the query would fail at runtime anyway. Throwing
3637+
* the error here is preferable to the executor check since we have
3638+
* more detailed information available about why the view isn't
3639+
* updatable.
36343640
*/
3635-
if (!instead&&qual_product==NULL&&
3641+
if (!instead&&
36363642
rt_entry_relation->rd_rel->relkind==RELKIND_VIEW&&
36373643
!view_has_instead_trigger(rt_entry_relation,event))
36383644
{
36393645
/*
3646+
* If there were any qualified INSTEAD rules, don't allow the view
3647+
* to be automatically updated (an unqualified INSTEAD rule or
3648+
* INSTEAD OF trigger is required).
3649+
*
3650+
* The messages here should match execMain.c's CheckValidResultRel
3651+
* and in principle make those checks in executor unnecessary, but
3652+
* we keep them just in case.
3653+
*/
3654+
if (qual_product!=NULL)
3655+
{
3656+
switch (parsetree->commandType)
3657+
{
3658+
caseCMD_INSERT:
3659+
ereport(ERROR,
3660+
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
3661+
errmsg("cannot insert into view \"%s\"",
3662+
RelationGetRelationName(rt_entry_relation)),
3663+
errdetail("Views with conditional DO INSTEAD rules are not automatically updatable."),
3664+
errhint("To enable inserting into the view, provide an INSTEAD OF INSERT trigger or an unconditional ON INSERT DO INSTEAD rule.")));
3665+
break;
3666+
caseCMD_UPDATE:
3667+
ereport(ERROR,
3668+
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
3669+
errmsg("cannot update view \"%s\"",
3670+
RelationGetRelationName(rt_entry_relation)),
3671+
errdetail("Views with conditional DO INSTEAD rules are not automatically updatable."),
3672+
errhint("To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule.")));
3673+
break;
3674+
caseCMD_DELETE:
3675+
ereport(ERROR,
3676+
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
3677+
errmsg("cannot delete from view \"%s\"",
3678+
RelationGetRelationName(rt_entry_relation)),
3679+
errdetail("Views with conditional DO INSTEAD rules are not automatically updatable."),
3680+
errhint("To enable deleting from the view, provide an INSTEAD OF DELETE trigger or an unconditional ON DELETE DO INSTEAD rule.")));
3681+
break;
3682+
default:
3683+
elog(ERROR,"unrecognized CmdType: %d",
3684+
(int)parsetree->commandType);
3685+
break;
3686+
}
3687+
}
3688+
3689+
/*
3690+
* Attempt to rewrite the query to automatically update the view.
36403691
* This throws an error if the view can't be automatically
3641-
* updated, but that's OK since the query would fail at runtime
3642-
* anyway.
3692+
* updated.
36433693
*/
36443694
parsetree=rewriteTargetView(parsetree,rt_entry_relation);
36453695

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

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -328,6 +328,27 @@ UPDATE ro_view20 SET b=upper(b);
328328
ERROR: cannot update view "ro_view20"
329329
DETAIL: Views that return set-returning functions are not automatically updatable.
330330
HINT: To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule.
331+
-- A view with a conditional INSTEAD rule but no unconditional INSTEAD rules
332+
-- or INSTEAD OF triggers should be non-updatable and generate useful error
333+
-- messages with appropriate detail
334+
CREATE RULE rw_view16_ins_rule AS ON INSERT TO rw_view16
335+
WHERE NEW.a > 0 DO INSTEAD INSERT INTO base_tbl VALUES (NEW.a, NEW.b);
336+
CREATE RULE rw_view16_upd_rule AS ON UPDATE TO rw_view16
337+
WHERE OLD.a > 0 DO INSTEAD UPDATE base_tbl SET b=NEW.b WHERE a=OLD.a;
338+
CREATE RULE rw_view16_del_rule AS ON DELETE TO rw_view16
339+
WHERE OLD.a > 0 DO INSTEAD DELETE FROM base_tbl WHERE a=OLD.a;
340+
INSERT INTO rw_view16 (a, b) VALUES (3, 'Row 3'); -- should fail
341+
ERROR: cannot insert into view "rw_view16"
342+
DETAIL: Views with conditional DO INSTEAD rules are not automatically updatable.
343+
HINT: To enable inserting into the view, provide an INSTEAD OF INSERT trigger or an unconditional ON INSERT DO INSTEAD rule.
344+
UPDATE rw_view16 SET b='ROW 2' WHERE a=2; -- should fail
345+
ERROR: cannot update view "rw_view16"
346+
DETAIL: Views with conditional DO INSTEAD rules are not automatically updatable.
347+
HINT: To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule.
348+
DELETE FROM rw_view16 WHERE a=2; -- should fail
349+
ERROR: cannot delete from view "rw_view16"
350+
DETAIL: Views with conditional DO INSTEAD rules are not automatically updatable.
351+
HINT: To enable deleting from the view, provide an INSTEAD OF DELETE trigger or an unconditional ON DELETE DO INSTEAD rule.
331352
DROP TABLE base_tbl CASCADE;
332353
NOTICE: drop cascades to 16 other objects
333354
DETAIL: drop cascades to view ro_view1

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

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -98,6 +98,20 @@ DELETE FROM ro_view18;
9898
UPDATE ro_view19SET last_value=1000;
9999
UPDATE ro_view20SET b=upper(b);
100100

101+
-- A view with a conditional INSTEAD rule but no unconditional INSTEAD rules
102+
-- or INSTEAD OF triggers should be non-updatable and generate useful error
103+
-- messages with appropriate detail
104+
CREATERULErw_view16_ins_ruleASON INSERT TO rw_view16
105+
WHERENEW.a>0 DO INSTEADINSERT INTO base_tblVALUES (NEW.a,NEW.b);
106+
CREATERULErw_view16_upd_ruleASONUPDATE TO rw_view16
107+
WHEREOLD.a>0 DO INSTEADUPDATE base_tblSET b=NEW.bWHERE a=OLD.a;
108+
CREATERULErw_view16_del_ruleASON DELETE TO rw_view16
109+
WHEREOLD.a>0 DO INSTEADDELETEFROM base_tblWHERE a=OLD.a;
110+
111+
INSERT INTO rw_view16 (a, b)VALUES (3,'Row 3');-- should fail
112+
UPDATE rw_view16SET b='ROW 2'WHERE a=2;-- should fail
113+
DELETEFROM rw_view16WHERE a=2;-- should fail
114+
101115
DROPTABLE base_tbl CASCADE;
102116
DROPVIEW ro_view10, ro_view12, ro_view18;
103117
DROPSEQUENCE seq CASCADE;

0 commit comments

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