NotificationsYou must be signed in to change notification settings
Fork6
Star31

Commit9e3f42f

committed

Improve error message when view is not updatable

Avoid using the term "updatable" in confusing ways. Suggest a triggerfirst, before a rule.

1 parent6c5f68e commit9e3f42fCopy full SHA for 9e3f42f

File tree

3 files changed

+26

-26

lines changed

src
- backend
  - executor
    - execMain.c
  - rewrite
    - rewriteHandler.c
- test/regress/expected
  - updatable_views.out

3 files changed

+26

-26

lines changed

`‎src/backend/executor/execMain.c`

Lines changed: 3 additions & 3 deletions

Original file line number	Diff line number	Diff line change
`@@ -975,23 +975,23 @@ CheckValidResultRel(Relation resultRel, CmdType operation)`
`975`	`975`	`(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),`
`976`	`976`	`errmsg("cannot insert into view \"%s\"",`
`977`	`977`	`RelationGetRelationName(resultRel)),`
`978`		`-errhint("Tomakethe view insertable, provide anunconditional ON INSERTDO INSTEAD ruleor anINSTEAD OF INSERTtrigger.")));`
	`978`	`+errhint("Toenable inserting intothe view, provide anINSTEAD OF INSERTtriggeror anunconditional ON INSERTDO INSTEAD rule.")));`
`979`	`979`	`break;`
`980`	`980`	`caseCMD_UPDATE:`
`981`	`981`	`if (!trigDesc\|\| !trigDesc->trig_update_instead_row)`
`982`	`982`	`ereport(ERROR,`
`983`	`983`	`(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),`
`984`	`984`	`errmsg("cannot update view \"%s\"",`
`985`	`985`	`RelationGetRelationName(resultRel)),`
`986`		`-errhint("Tomakethe view updatable, provide anunconditional ON UPDATEDO INSTEAD ruleor anINSTEAD OF UPDATEtrigger.")));`
	`986`	`+errhint("Toenable updatingthe view, provide anINSTEAD OF UPDATEtriggeror anunconditional ON UPDATEDO INSTEAD rule.")));`
`987`	`987`	`break;`
`988`	`988`	`caseCMD_DELETE:`
`989`	`989`	`if (!trigDesc\|\| !trigDesc->trig_delete_instead_row)`
`990`	`990`	`ereport(ERROR,`
`991`	`991`	`(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),`
`992`	`992`	`errmsg("cannot delete from view \"%s\"",`
`993`	`993`	`RelationGetRelationName(resultRel)),`
`994`		`-errhint("Tomakethe view updatable, provide anunconditional ON DELETEDO INSTEAD ruleor anINSTEAD OF DELETEtrigger.")));`
	`994`	`+errhint("Toenable deleting fromthe view, provide anINSTEAD OF DELETEtriggeror anunconditional ON DELETEDO INSTEAD rule.")));`
`995`	`995`	`break;`
`996`	`996`	`default:`
`997`	`997`	`elog(ERROR,"unrecognized CmdType: %d", (int)operation);`

`‎src/backend/rewrite/rewriteHandler.c`

Lines changed: 3 additions & 3 deletions

Original file line number	Diff line number	Diff line change
`@@ -2318,23 +2318,23 @@ rewriteTargetView(Query *parsetree, Relation view)`
`2318`	`2318`	`errmsg("cannot insert into view \"%s\"",`
`2319`	`2319`	`RelationGetRelationName(view)),`
`2320`	`2320`	`errdetail_internal("%s",_(auto_update_detail)),`
`2321`		`-errhint("Tomakethe view insertable, provide anunconditional ON INSERTDO INSTEAD ruleor anINSTEAD OF INSERTtrigger.")));`
	`2321`	`+errhint("Toenable inserting intothe view, provide anINSTEAD OF INSERTtriggeror anunconditional ON INSERTDO INSTEAD rule.")));`
`2322`	`2322`	`break;`
`2323`	`2323`	`caseCMD_UPDATE:`
`2324`	`2324`	`ereport(ERROR,`
`2325`	`2325`	`(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),`
`2326`	`2326`	`errmsg("cannot update view \"%s\"",`
`2327`	`2327`	`RelationGetRelationName(view)),`
`2328`	`2328`	`errdetail_internal("%s",_(auto_update_detail)),`
`2329`		`-errhint("Tomakethe view updatable, provide anunconditional ON UPDATEDO INSTEAD ruleor anINSTEAD OF UPDATEtrigger.")));`
	`2329`	`+errhint("Toenable updatingthe view, provide anINSTEAD OF UPDATEtriggeror anunconditional ON UPDATEDO INSTEAD rule.")));`
`2330`	`2330`	`break;`
`2331`	`2331`	`caseCMD_DELETE:`
`2332`	`2332`	`ereport(ERROR,`
`2333`	`2333`	`(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),`
`2334`	`2334`	`errmsg("cannot delete from view \"%s\"",`
`2335`	`2335`	`RelationGetRelationName(view)),`
`2336`	`2336`	`errdetail_internal("%s",_(auto_update_detail)),`
`2337`		`-errhint("Tomakethe view updatable, provide anunconditional ON DELETEDO INSTEAD ruleor anINSTEAD OF DELETEtrigger.")));`
	`2337`	`+errhint("Toenable deleting fromthe view, provide anINSTEAD OF DELETEtriggeror anunconditional ON DELETEDO INSTEAD rule.")));`
`2338`	`2338`	`break;`
`2339`	`2339`	`default:`
`2340`	`2340`	`elog(ERROR,"unrecognized CmdType: %d",`

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

Lines changed: 20 additions & 20 deletions

Original file line number	Diff line number	Diff line change
`@@ -136,83 +136,83 @@ SELECT table_name, column_name, is_updatable`
`136`	`136`	`DELETE FROM ro_view1;`
`137`	`137`	`ERROR: cannot delete from view "ro_view1"`
`138`	`138`	`DETAIL: Views containing DISTINCT are not automatically updatable.`
`139`		`-HINT: Tomakethe view updatable, provide anunconditional ON DELETEDO INSTEAD ruleor anINSTEAD OF DELETEtrigger.`
	`139`	`+HINT: Toenable deleting fromthe view, provide anINSTEAD OF DELETEtriggeror anunconditional ON DELETEDO INSTEAD rule.`
`140`	`140`	`DELETE FROM ro_view2;`
`141`	`141`	`ERROR: cannot delete from view "ro_view2"`
`142`	`142`	`DETAIL: Views containing GROUP BY are not automatically updatable.`
`143`		`-HINT: Tomakethe view updatable, provide anunconditional ON DELETEDO INSTEAD ruleor anINSTEAD OF DELETEtrigger.`
	`143`	`+HINT: Toenable deleting fromthe view, provide anINSTEAD OF DELETEtriggeror anunconditional ON DELETEDO INSTEAD rule.`
`144`	`144`	`DELETE FROM ro_view3;`
`145`	`145`	`ERROR: cannot delete from view "ro_view3"`
`146`	`146`	`DETAIL: Views containing HAVING are not automatically updatable.`
`147`		`-HINT: Tomakethe view updatable, provide anunconditional ON DELETEDO INSTEAD ruleor anINSTEAD OF DELETEtrigger.`
	`147`	`+HINT: Toenable deleting fromthe view, provide anINSTEAD OF DELETEtriggeror anunconditional ON DELETEDO INSTEAD rule.`
`148`	`148`	`DELETE FROM ro_view4;`
`149`	`149`	`ERROR: cannot delete from view "ro_view4"`
`150`	`150`	`DETAIL: Views that return columns that are not columns of their base relation are not automatically updatable.`
`151`		`-HINT: Tomakethe view updatable, provide anunconditional ON DELETEDO INSTEAD ruleor anINSTEAD OF DELETEtrigger.`
	`151`	`+HINT: Toenable deleting fromthe view, provide anINSTEAD OF DELETEtriggeror anunconditional ON DELETEDO INSTEAD rule.`
`152`	`152`	`DELETE FROM ro_view5;`
`153`	`153`	`ERROR: cannot delete from view "ro_view5"`
`154`	`154`	`DETAIL: Views that return columns that are not columns of their base relation are not automatically updatable.`
`155`		`-HINT: Tomakethe view updatable, provide anunconditional ON DELETEDO INSTEAD ruleor anINSTEAD OF DELETEtrigger.`
	`155`	`+HINT: Toenable deleting fromthe view, provide anINSTEAD OF DELETEtriggeror anunconditional ON DELETEDO INSTEAD rule.`
`156`	`156`	`DELETE FROM ro_view6;`
`157`	`157`	`ERROR: cannot delete from view "ro_view6"`
`158`	`158`	`DETAIL: Views containing UNION, INTERSECT, or EXCEPT are not automatically updatable.`
`159`		`-HINT: Tomakethe view updatable, provide anunconditional ON DELETEDO INSTEAD ruleor anINSTEAD OF DELETEtrigger.`
	`159`	`+HINT: Toenable deleting fromthe view, provide anINSTEAD OF DELETEtriggeror anunconditional ON DELETEDO INSTEAD rule.`
`160`	`160`	`UPDATE ro_view7 SET a=a+1;`
`161`	`161`	`ERROR: cannot update view "ro_view7"`
`162`	`162`	`DETAIL: Views containing WITH are not automatically updatable.`
`163`		`-HINT: Tomakethe view updatable, provide anunconditional ON UPDATEDO INSTEAD ruleor anINSTEAD OF UPDATEtrigger.`
	`163`	`+HINT: Toenable updatingthe view, provide anINSTEAD OF UPDATEtriggeror anunconditional ON UPDATEDO INSTEAD rule.`
`164`	`164`	`UPDATE ro_view8 SET a=a+1;`
`165`	`165`	`ERROR: cannot update view "ro_view8"`
`166`	`166`	`DETAIL: Views containing LIMIT or OFFSET are not automatically updatable.`
`167`		`-HINT: Tomakethe view updatable, provide anunconditional ON UPDATEDO INSTEAD ruleor anINSTEAD OF UPDATEtrigger.`
	`167`	`+HINT: Toenable updatingthe view, provide anINSTEAD OF UPDATEtriggeror anunconditional ON UPDATEDO INSTEAD rule.`
`168`	`168`	`UPDATE ro_view9 SET a=a+1;`
`169`	`169`	`ERROR: cannot update view "ro_view9"`
`170`	`170`	`DETAIL: Views containing LIMIT or OFFSET are not automatically updatable.`
`171`		`-HINT: Tomakethe view updatable, provide anunconditional ON UPDATEDO INSTEAD ruleor anINSTEAD OF UPDATEtrigger.`
	`171`	`+HINT: Toenable updatingthe view, provide anINSTEAD OF UPDATEtriggeror anunconditional ON UPDATEDO INSTEAD rule.`
`172`	`172`	`UPDATE ro_view10 SET a=a+1;`
`173`	`173`	`ERROR: cannot update view "ro_view10"`
`174`	`174`	`DETAIL: Views that do not select from a single table or view are not automatically updatable.`
`175`		`-HINT: Tomakethe view updatable, provide anunconditional ON UPDATEDO INSTEAD ruleor anINSTEAD OF UPDATEtrigger.`
	`175`	`+HINT: Toenable updatingthe view, provide anINSTEAD OF UPDATEtriggeror anunconditional ON UPDATEDO INSTEAD rule.`
`176`	`176`	`UPDATE ro_view11 SET a=a+1;`
`177`	`177`	`ERROR: cannot update view "ro_view11"`
`178`	`178`	`DETAIL: Views that do not select from a single table or view are not automatically updatable.`
`179`		`-HINT: Tomakethe view updatable, provide anunconditional ON UPDATEDO INSTEAD ruleor anINSTEAD OF UPDATEtrigger.`
	`179`	`+HINT: Toenable updatingthe view, provide anINSTEAD OF UPDATEtriggeror anunconditional ON UPDATEDO INSTEAD rule.`
`180`	`180`	`UPDATE ro_view12 SET a=a+1;`
`181`	`181`	`ERROR: cannot update view "ro_view12"`
`182`	`182`	`DETAIL: Views that do not select from a single table or view are not automatically updatable.`
`183`		`-HINT: Tomakethe view updatable, provide anunconditional ON UPDATEDO INSTEAD ruleor anINSTEAD OF UPDATEtrigger.`
	`183`	`+HINT: Toenable updatingthe view, provide anINSTEAD OF UPDATEtriggeror anunconditional ON UPDATEDO INSTEAD rule.`
`184`	`184`	`INSERT INTO ro_view13 VALUES (3, 'Row 3');`
`185`	`185`	`ERROR: cannot insert into view "ro_view13"`
`186`	`186`	`DETAIL: Views that do not select from a single table or view are not automatically updatable.`
`187`		`-HINT: Tomakethe view insertable, provide anunconditional ON INSERTDO INSTEAD ruleor anINSTEAD OF INSERTtrigger.`
	`187`	`+HINT: Toenable inserting intothe view, provide anINSTEAD OF INSERTtriggeror anunconditional ON INSERTDO INSTEAD rule.`
`188`	`188`	`INSERT INTO ro_view14 VALUES (null);`
`189`	`189`	`ERROR: cannot insert into view "ro_view14"`
`190`	`190`	`DETAIL: Views that return system columns are not automatically updatable.`
`191`		`-HINT: Tomakethe view insertable, provide anunconditional ON INSERTDO INSTEAD ruleor anINSTEAD OF INSERTtrigger.`
	`191`	`+HINT: Toenable inserting intothe view, provide anINSTEAD OF INSERTtriggeror anunconditional ON INSERTDO INSTEAD rule.`
`192`	`192`	`INSERT INTO ro_view15 VALUES (3, 'ROW 3');`
`193`	`193`	`ERROR: cannot insert into view "ro_view15"`
`194`	`194`	`DETAIL: Views that return columns that are not columns of their base relation are not automatically updatable.`
`195`		`-HINT: Tomakethe view insertable, provide anunconditional ON INSERTDO INSTEAD ruleor anINSTEAD OF INSERTtrigger.`
	`195`	`+HINT: Toenable inserting intothe view, provide anINSTEAD OF INSERTtriggeror anunconditional ON INSERTDO INSTEAD rule.`
`196`	`196`	`INSERT INTO ro_view16 VALUES (3, 'Row 3', 3);`
`197`	`197`	`ERROR: cannot insert into view "ro_view16"`
`198`	`198`	`DETAIL: Views that return the same column more than once are not automatically updatable.`
`199`		`-HINT: Tomakethe view insertable, provide anunconditional ON INSERTDO INSTEAD ruleor anINSTEAD OF INSERTtrigger.`
	`199`	`+HINT: Toenable inserting intothe view, provide anINSTEAD OF INSERTtriggeror anunconditional ON INSERTDO INSTEAD rule.`
`200`	`200`	`INSERT INTO ro_view17 VALUES (3, 'ROW 3');`
`201`	`201`	`ERROR: cannot insert into view "ro_view1"`
`202`	`202`	`DETAIL: Views containing DISTINCT are not automatically updatable.`
`203`		`-HINT: Tomakethe view insertable, provide anunconditional ON INSERTDO INSTEAD ruleor anINSTEAD OF INSERTtrigger.`
	`203`	`+HINT: Toenable inserting intothe view, provide anINSTEAD OF INSERTtriggeror anunconditional ON INSERTDO INSTEAD rule.`
`204`	`204`	`INSERT INTO ro_view18 VALUES (3, 'ROW 3');`
`205`	`205`	`ERROR: cannot insert into view "ro_view18"`
`206`	`206`	`DETAIL: Security-barrier views are not automatically updatable.`
`207`		`-HINT: Tomakethe view insertable, provide anunconditional ON INSERTDO INSTEAD ruleor anINSTEAD OF INSERTtrigger.`
	`207`	`+HINT: Toenable inserting intothe view, provide anINSTEAD OF INSERTtriggeror anunconditional ON INSERTDO INSTEAD rule.`
`208`	`208`	`DELETE FROM ro_view19;`
`209`	`209`	`ERROR: cannot delete from view "ro_view19"`
`210`	`210`	`DETAIL: Views that do not select from a single table or view are not automatically updatable.`
`211`		`-HINT: Tomakethe view updatable, provide anunconditional ON DELETEDO INSTEAD ruleor anINSTEAD OF DELETEtrigger.`
	`211`	`+HINT: Toenable deleting fromthe view, provide anINSTEAD OF DELETEtriggeror anunconditional ON DELETEDO INSTEAD rule.`
`212`	`212`	`UPDATE ro_view20 SET max_value=1000;`
`213`	`213`	`ERROR: cannot update view "ro_view20"`
`214`	`214`	`DETAIL: Views that do not select from a single table or view are not automatically updatable.`
`215`		`-HINT: Tomakethe view updatable, provide anunconditional ON UPDATEDO INSTEAD ruleor anINSTEAD OF UPDATEtrigger.`
	`215`	`+HINT: Toenable updatingthe view, provide anINSTEAD OF UPDATEtriggeror anunconditional ON UPDATEDO INSTEAD rule.`
`216`	`216`	`DROP TABLE base_tbl CASCADE;`
`217`	`217`	`NOTICE: drop cascades to 16 other objects`
`218`	`218`	`DETAIL: drop cascades to view ro_view1`

0 commit comments

Comments

(0)

Movatterモバイル変換

Navigation Menu

Search code, repositories, users, issues, pull requests...

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Commit9e3f42f

File tree

3 files changed

3 files changed

`‎src/backend/executor/execMain.c`

`‎src/backend/rewrite/rewriteHandler.c`

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

0 commit comments