Re: COPY into a view; help w. design & patch

"Karl O. Pinc" <kop(at)meme(dot)com> writes:
> On 05/19/2007 12:41:47 PM, Tom Lane wrote:
>> There's been previous discussion of allowing BEFORE INSERT triggers
>> on views, so long as the triggers always return NULL to suppress
>> the actual insertion attempt (ie, we'd move the "can't insert into
>> view" test out of the rewriter and put it downstream of trigger firing
>> in the executor). So far no one's figured out how to make that idea
>> work for UPDATE/DELETE, but maybe you could argue that even if it
>> only worked for INSERT it'd be a useful feature. It'd certainly solve
>> the problem for COPY.

> Disclaimer: At least some of that discussion was my fault.

> I'd be happy to go in that direction, but arguing for
> having BEFORE INSERT triggers work for
> INSERT and not on UPDATE and DELETE seems tough. It's a
> little like driving blind in one eye, you can see _partly_
> where you're going but not all the way to the end of the road.
> I'd be afraid it'd be a bad design decision that would
> artificially constrain later work.

Well, as I noted earlier, making triggers work for views seems like
it'd be more useful than adding more dependencies on the current
rule system.

> The problem with UPDATE and DELETE on BEFORE
> triggers is coming up with an OLD row.

No, that's the easy part, since as you note you can just compute the
view and extract the rows meeting the WHERE condition. And in the
UPDATE case you need to compute a NEW row with the updated values
substituted, but that seems just a small matter of programming as well.
The issues I see are:

* Existing triggers expect to see a ctid identifier for each updated
or deleted row. Is it OK to just say that you don't get that in a
trigger for a view?

* What about INSERT/UPDATE/DELETE RETURNING? The current definition of
triggers gives them no way to specify what is computed for RETURNING.

> The big problem that came up revolves around what
> to do if the view does not contain any/enough
> primary keys.

I don't think that's a problem we need to solve. If a user wants to
update his views it's up to him to define them in a way that gives
the trigger enough information to do its job. This is the SQL spec's
approach (no pkey -> view not updatable), and I don't think we need
to invent weird new concepts to do better.

regards, tom lane