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

On 8 November 2012 19:29, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Dean Rasheed <dean.a.rasheed@gmail.com> writes:
>> On 8 November 2012 17:37, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> I believe that the right way to think about the auto-update
>>> transformation is that it should act like a supplied-by-default
>>> unconditional INSTEAD rule.
>
>> But if you treat the auto-update transformation as a
>> supplied-by-default unconditional INSTEAD rule, and the user defines
>> their own conditional INSTEAD rule, if the condition is true it would
>> execute both the conditional rule action and the auto-update action,
>> making it an ALSO rule rather than the INSTEAD rule the user
>> specified.
>
> Well, that's how things work if you specify both a conditional and an
> unconditional INSTEAD action, so I don't find this so surprising.
>

To me, it's very surprising, so I must be thinking about it
differently. I think that I'm really expecting auto-updatable views to
behave like tables, so I keep coming back to the question "what would
happen if you did that to a table?".

Taking another concrete example, I could use a conditional DO INSTEAD
NOTHING rule on a table to prevent certain values from being inserted:

create table foo(a int);
create rule foo_r as on insert to foo where new.a < 0 do instead nothing;
insert into foo values(-1),(1);
select * from foo;a
---1
(1 row)

So I would expect the same behaviour from an auto-updatable view:

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.
I could live with it raising a "you need an unconditional instead
rule" error, but that makes the auto-update view seem a bit
half-baked.

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.

Regards,
Dean


> What you're arguing for would make some sense if the auto-update feature
> could be seen as something that acts ahead of, and independently of,
> INSTEAD rules and triggers.  But it can't be treated that way: in
> particular, the fact that it doesn't fire when there's an INSTEAD
> trigger pretty much breaks the fiction that it's an independent
> feature.  I would rather be able to explain its interaction with rules
> by saying "it's a default implementation of an INSTEAD rule" than by
> saying "well, it has these weird interactions with INSTEAD rules, which
> are different for conditional and unconditional INSTEAD rules".
>
> Or we could go back to what I suggested to start with, which is that the
> auto-update transformation doesn't fire if there are *either*
> conditional or unconditional INSTEAD rules.  That still seems like the
> best way if you want an arms-length definition of behavior; it means we
> can explain the interaction with INSTEAD rules exactly the same as the
> interaction with INSTEAD triggers, ie, having one prevents the
> transformation from being used.
>
>                         regards, tom lane