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

Commitd751ba5

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 parented7bb5c commitd751ba5

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
@@ -1101,10 +1101,10 @@ CheckValidResultRel(ResultRelInfo *resultRelInfo, CmdType operation)
11011101

11021102
/*
11031103
* Okay only if there's a suitable INSTEAD OF trigger. Messages
1104-
* here should match rewriteHandler.c's rewriteTargetView, except
1105-
* that we omit errdetail because we haven't got the information
1106-
* handy (and given that we really shouldn't get here anyway, it's
1107-
* not worth great exertion to get).
1104+
* here should match rewriteHandler.c's rewriteTargetView and
1105+
*RewriteQuery, exceptthat we omit errdetail because we haven't
1106+
*got the informationhandy (and given that we really shouldn't
1107+
*get here anyway, it'snot worth great exertion to get).
11081108
*/
11091109
switch (operation)
11101110
{

‎src/backend/rewrite/rewriteHandler.c

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

36723672
/*
3673-
* If therewere no INSTEADrules, and the target relation is a view
3674-
* without any INSTEAD OF triggers, see if the view can be
3673+
* If therewas nounqualifiedINSTEADrule, and the target relation
3674+
*is a viewwithout any INSTEAD OF triggers, see if the view can be
36753675
* automatically updated. If so, we perform the necessary query
36763676
* transformation here and add the resulting query to the
36773677
* product_queries list, so that it gets recursively rewritten if
36783678
* necessary.
3679+
*
3680+
* If the view cannot be automatically updated, we throw an error here
3681+
* which is OK since the query would fail at runtime anyway. Throwing
3682+
* the error here is preferable to the executor check since we have
3683+
* more detailed information available about why the view isn't
3684+
* updatable.
36793685
*/
3680-
if (!instead&&qual_product==NULL&&
3686+
if (!instead&&
36813687
rt_entry_relation->rd_rel->relkind==RELKIND_VIEW&&
36823688
!view_has_instead_trigger(rt_entry_relation,event))
36833689
{
36843690
/*
3691+
* If there were any qualified INSTEAD rules, don't allow the view
3692+
* to be automatically updated (an unqualified INSTEAD rule or
3693+
* INSTEAD OF trigger is required).
3694+
*
3695+
* The messages here should match execMain.c's CheckValidResultRel
3696+
* and in principle make those checks in executor unnecessary, but
3697+
* we keep them just in case.
3698+
*/
3699+
if (qual_product!=NULL)
3700+
{
3701+
switch (parsetree->commandType)
3702+
{
3703+
caseCMD_INSERT:
3704+
ereport(ERROR,
3705+
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
3706+
errmsg("cannot insert into view \"%s\"",
3707+
RelationGetRelationName(rt_entry_relation)),
3708+
errdetail("Views with conditional DO INSTEAD rules are not automatically updatable."),
3709+
errhint("To enable inserting into the view, provide an INSTEAD OF INSERT trigger or an unconditional ON INSERT DO INSTEAD rule.")));
3710+
break;
3711+
caseCMD_UPDATE:
3712+
ereport(ERROR,
3713+
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
3714+
errmsg("cannot update view \"%s\"",
3715+
RelationGetRelationName(rt_entry_relation)),
3716+
errdetail("Views with conditional DO INSTEAD rules are not automatically updatable."),
3717+
errhint("To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule.")));
3718+
break;
3719+
caseCMD_DELETE:
3720+
ereport(ERROR,
3721+
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
3722+
errmsg("cannot delete from view \"%s\"",
3723+
RelationGetRelationName(rt_entry_relation)),
3724+
errdetail("Views with conditional DO INSTEAD rules are not automatically updatable."),
3725+
errhint("To enable deleting from the view, provide an INSTEAD OF DELETE trigger or an unconditional ON DELETE DO INSTEAD rule.")));
3726+
break;
3727+
default:
3728+
elog(ERROR,"unrecognized CmdType: %d",
3729+
(int)parsetree->commandType);
3730+
break;
3731+
}
3732+
}
3733+
3734+
/*
3735+
* Attempt to rewrite the query to automatically update the view.
36853736
* This throws an error if the view can't be automatically
3686-
* updated, but that's OK since the query would fail at runtime
3687-
* anyway.
3737+
* updated.
36883738
*/
36893739
parsetree=rewriteTargetView(parsetree,rt_entry_relation);
36903740

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

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

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

0 commit comments

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