From: | Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Peter Eisentraut <peter_e(at)gmx(dot)net>, Amit kapila <amit(dot)kapila(at)huawei(dot)com>, "robertmhaas(at)gmail(dot)com" <robertmhaas(at)gmail(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Proof of concept: auto updatable views [Review of Patch] |
Date: | 2012-11-08 15:22:29 |
Message-ID: | CAEZATCVVHHb+aahDvvJ3jhZRsUyQCVGuttmuimOsoD9PdazhHw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 8 November 2012 14:38, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
> On 8 November 2012 08:33, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2012-11-08 16:05:28 | Re: Further pg_upgrade analysis for many tables |
Previous Message | Alvaro Herrera | 2012-11-08 15:18:07 | Re: Proposal for Allow postgresql.conf values to be changed via SQL |