Re: Proof of concept: auto updatable views [Review of Patch]

On 8 November 2012 17:37, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> writes:
>> If we did nothing here then it would go on to either fire any INSTEAD
>> OF triggers or raise an error if there aren't any. The problem with
>> that is that it makes trigger-updatable views and auto-updatable views
>> inconsistent in their behaviour with qualified INSTEAD rules. I don't
>> think the existing interaction between trigger-updatable views and
>> qualified INSTEAD rules is documented, so perhaps that's something
>> that needs work.
>
> I'm still unhappy about this decision though, and after further thought
> I think I can explain why a bit better: it's actually *not* like the way
> rules work now. The current rule semantics are basically that:
>
> 1. The original query is done only if there are no unconditional INSTEAD
> rules and no conditional INSTEAD rule's condition is true.
>
> 2. Unconditional INSTEAD actions are done, well, unconditionally.
>
> 3. Each conditional INSTEAD action is done if its condition is true.
>
> 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. Which would mean that it happens
> unconditionally, per #2. As submitted, though, the auto-update query
> executes only if there are no unconditional INSTEAD rules *and* no
> conditional INSTEAD rule's condition is true. I do not think this is
> either consistent or useful. It's treating the auto-update replacement
> query as if it were the original, which it is not.
>

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.

Taking a concrete example:

create table foo(a int);
create table bar(a int);
create view foo_v as select * from foo;
create rule foo_r as on insert to foo_v
where new.a < 0 do instead insert into bar values(new.a);

I would expect that to put all positive values into foo, and all
negative values into bar, which is indeed what happens as it stands.

Regards,
Dean