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

On 8 November 2012 14:38, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
> On 8 November 2012 08:33, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
>> OK, yes I think we do need to be throwing the error at runtime rather
>> than at plan time. That's pretty easy if we just keep the current
>> error message...
>
> Oh wait, that's nonsense (not enough caffeine). The rewrite code needs
> to know whether there are INSTEAD OF triggers before it decides
> whether it's going to substitute the base relation. The fundamental
> problem is that the plans with and without triggers are completely
> different, and there's no way the executor is going to notice the
> addition of triggers if they weren't there when the query was
> rewritten and planned.
>

In fact doesn't the existing plan invalidation mechanism already
protect us from this? Consider for example:

create table foo(a int);
create view foo_v as select a+1 as a from foo;
create function foo_trig_fn() returns trigger as
  $$ begin insert into foo values(new.a-1); return new; end $$
  language plpgsql;
create trigger foo_trig instead of insert on foo_v
  for each row execute procedure foo_trig_fn();

Then I can do:

prepare f(int) as insert into foo_v values($1);
PREPARE
execute f(1);
INSERT 0 1
drop trigger foo_trig on foo_v;
DROP TRIGGER
execute f(2);
ERROR:  cannot insert into view "foo_v"
DETAIL:  Views with columns that are not simple references to columns
in the base relation are not updatable.
HINT:  You need an unconditional ON INSERT DO INSTEAD rule or an
INSTEAD OF INSERT trigger.
create trigger foo_trig instead of insert on foo_v
  for each row execute procedure foo_trig_fn();
CREATE TRIGGER
execute f(3);
INSERT 0 1

Regards,
Dean