Thread: updatable/deletable terminology

We have these two error messages:

To make the view updatable, provide an unconditional ON UPDATE DO INSTEAD rule or an INSTEAD OF UPDATE trigger.

and

To make the view updatable, provide an unconditional ON DELETE DO INSTEAD rule or an INSTEAD OF DELETE trigger.

I think it's a bit strange to claim that adding a DELETE rule/trigger
makes a view *updatable*.  I suspect someone thought they would apply
the term "updatable" in an SQL standard sense, but that seems backwards,
because you get to these error conditions exactly because the view as
defined was not Updatable(tm).

Or perhaps "deletable" isn't such a good word here?

Maybe "To enable updates/deletions in the view, ..."?

On 08/07/2013 08:19:03 PM, Peter Eisentraut wrote:
> We have these two error messages:
>
> To make the view updatable, provide an unconditional ON UPDATE DO
> INSTEAD rule or an INSTEAD OF UPDATE trigger.
>
> and
>
> To make the view updatable, provide an unconditional ON DELETE DO
> INSTEAD rule or an INSTEAD OF DELETE trigger.
>
> I think it's a bit strange to claim that adding a DELETE rule/trigger
> makes a view *updatable*.  I suspect someone thought they would apply
> the term "updatable" in an SQL standard sense, but that seems
> backwards,
> because you get to these error conditions exactly because the view as
> defined was not Updatable(tm).

Isn't the problem here that you need a word, instead of "updateable",
to indicate that table content may be changed (insert/update/
delete) through the view?

So...   "to allow the view to influence table content"


Karl <kop@meme.com>
Free Software:  "You don't pay back, you pay forward."                -- Robert A. Heinlein

On Wed, 2013-08-07 at 21:19 -0400, Peter Eisentraut wrote:
> To make the view updatable, provide an unconditional ON DELETE DO
> INSTEAD rule or an INSTEAD OF DELETE trigger.
> 
> I think it's a bit strange to claim that adding a DELETE rule/trigger
> makes a view *updatable*.  I suspect someone thought they would apply
> the term "updatable" in an SQL standard sense, but that seems
> backwards,
> because you get to these error conditions exactly because the view as
> defined was not Updatable(tm). 

After some consideration, I think the best fix here is to revert to the
9.2 wording

"You need an unconditional ON DELETE DO INSTEAD rule or an INSTEAD OF
DELETE trigger."

The addition of the term "updatable" was simply wrong here.

On 13 August 2013 00:01, Peter Eisentraut <peter_e@gmx.net> wrote:
> On Wed, 2013-08-07 at 21:19 -0400, Peter Eisentraut wrote:
>> To make the view updatable, provide an unconditional ON DELETE DO
>> INSTEAD rule or an INSTEAD OF DELETE trigger.
>>
>> I think it's a bit strange to claim that adding a DELETE rule/trigger
>> makes a view *updatable*.  I suspect someone thought they would apply
>> the term "updatable" in an SQL standard sense, but that seems
>> backwards,
>> because you get to these error conditions exactly because the view as
>> defined was not Updatable(tm).
>
> After some consideration, I think the best fix here is to revert to the
> 9.2 wording
>
> "You need an unconditional ON DELETE DO INSTEAD rule or an INSTEAD OF
> DELETE trigger."
>

That's how I had it in the patch I submitted, but perhaps it should be

"You need an INSTEAD OF DELETE trigger or an unconditional ON DELETE
DO INSTEAD rule."

to reflect the fact that the docs now recommend triggers ahead of rules.


> The addition of the term "updatable" was simply wrong here.
>

In the docs we're using "udpatable" as a generic term meaning support
for INSERT, UPDATE and DELETE, and we're not using the terms
"insertable" or "deletable". Also the error detail that immediately
precedes this hint uses the term "updatable". For example:

CREATE VIEW one AS SELECT 1 AS val;
INSERT INTO one VALUES (1);

ERROR:  cannot insert into view "one"
DETAIL:  Views that do not select from a single table or view are not
automatically updatable.
HINT:  To make the view insertable, provide an unconditional ON INSERT
DO INSTEAD rule or an INSTEAD OF INSERT trigger.

so if there is a problem there, it's in the mix of terminology between
the detail and the hint ("updatable" vs "insertable"). But at least in
this case the hint is technically correct -- adding such a rule or
trigger would make the view insertable.

In the UPDATE and DELETE cases, following the hint's suggestion and
adding just an UPDATE rule or trigger, or just a DELETE rule or
trigger, wouldn't actually make the view updatable according to our
current interpretation of the spec, which would require both.

So on balance I think you're right, and it would be better to simply say:

ERROR:  cannot insert into view "one"
DETAIL:  Views that do not select from a single table or view are not
automatically updatable.
HINT:  You need an INSTEAD OF INSERT trigger or an unconditional ON
INSERT DO INSTEAD rule.

Regards,
Dean

On Tue, 2013-08-13 at 09:27 +0100, Dean Rasheed wrote:
> So on balance I think you're right, and it would be better to simply say:
> 
> ERROR:  cannot insert into view "one"
> DETAIL:  Views that do not select from a single table or view are not
> automatically updatable.
> HINT:  You need an INSTEAD OF INSERT trigger or an unconditional ON
> INSERT DO INSTEAD rule.

I made adjustments similar to this.