Re: Proof of concept: auto updatable views [Review of Patch] - Mailing list pgsql-hackers

On 8 November 2012 21:13, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Dean Rasheed <dean.a.rasheed@gmail.com> writes:
>> create table bar(a int);
>> create view bar_v as select * from bar;
>> create rule bar_r as on insert to bar_v where new.a < 0 do instead nothing;
>> insert into bar_v values(-1),(1);
>> select * from bar_v;
>>  a
>> ---
>>  1
>> (1 row)
>
>> Having that put both -1 and 1 into bar seems completely wrong to me.
>
> Right now, what you get from that is
>
> ERROR:  cannot insert into view "bar_v"
> HINT:  You need an unconditional ON INSERT DO INSTEAD rule or an INSTEAD OF INSERT trigger.
>
> and (modulo the contents of the HINT) I think that's still what you
> should get.  If the user has got some DO INSTEAD rules we should not be
> second-guessing what should happen.
>

You say it's second-guessing what should happen, but in every example
I've been able to think of, it does exactly what I would expect, and
exactly what already happens for a table or a trigger-updatable view.

Clearly though, what I expect/find surprising is at odds with what you
expect/find surprising. If I think about it, I would summarise my
expectations something like this:
   Given 2 identical tables table1 and table2, and view view2 defined   as "select * from table2", I would expect view2
tobehave identically   to table1 for all operations supported by both tables and views.
 

In particular, given any set of rules defined on table1, if the
matching set of rules is defined on view2, I would expect all queries
on view2 to behave the same as the matching queries on table1.

>> This also seems like a much more plausible case where users might have
>> done something like this with a trigger-updatable view, so I don't
>> think the backwards-compatibility argument can be ignored.
>
> I think the most reasonable backwards-compatibility argument is that we
> shouldn't change the behavior if there are either INSTEAD rules or
> INSTEAD triggers.  Otherwise we may be disturbing carefully constructed
> behavior (and no, I don't buy that "throw an error" couldn't be what the
> user intended).
>

The current behaviour, if there is only a conditional instead rule, is
to throw an error whether or not that condition is satisfied. It's
hard to imagine that's an error the user intended.

However, given the niche nature of conditional instead rules, it
doesn't seem so bad to say that auto-updatable views don't support
them at the moment, so long as backwards compatibility is maintained
in the table and trigger-updatable view cases. So I think the current
behaviour to maintain is, for a relation with only a conditional
instead rule:

if the relation is a table:   if the condition is satisfied: fire the rule action   else: modify the table
else if the relation is a view with triggers:   if the condition is satisfied: fire the rule action   else: modify the
viewusing the triggers
 
else:   throw an error unconditionally

That's backwards compatible and easy to document - views with
conditional instead rules are not auto-updatable. If anyone cared
enough about it, or could come up with a realistic use case, we could
always add support for that case in the future.

Regards,
Dean