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

On 05/21/2007 04:52:35 PM, Tom Lane wrote:
> "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.

> > 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?

In addition to computing the view and extracting the rows meeting
the WHERE condition, the resultant rows _could_ be put
(by what the parser generates) into a
temporary table (deleted when the command finishes). That
would result in ctid identifiers, but I'm unable to tell if this
is a good idea or not. (It's probably only a good idea if the
answer is "no, it's not ok if triggers get NULL ctid values",
but I'm out out of my depth vis the pg source code and
got tired and figured I'd post this.)

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

If the BEFORE INSERT triggers actually returned rows, and the
INSERT/UPDATE/DELETE code in the executor (execMain.c)
tested for a view and if found did not do the operation
on the heap, then this problem goes away.
That would also get rid of the programming required to produce
a NEW row, because the trigger would produce it. It would also
allow AFTER triggers, for whatever good that would do, and
otherwise make triggers on views just like any other trigger.
(Excepting that it's up to the triggers to actually change
database data because the executor does not do it.)

The RETURNING would be computed in the executor just like always.

To keep the executor from having to do (much) testing, the
parser and executor _could_ add new CmdType-s of
CMD_INSERT_VIEW, CMD_UPDATE_VIEW, and CMD_DELETE_VIEW
to go with corresponding ExecInsertView(), ExecUpdateView(),
and ExecDeleteView() functions. These new functions would
be just like ExecInsert(), etc., but not actually modify the
heap. There'd be a little code duplication, which could
probably be gotten rid of by using macros. There'd be fussing
elsewhere that CMD_INSERT, CMD_UPDATE, and CMD_DELETE appear
to make sure that CMD_INSERT_VIEW, etc., are all recognized.

Karl <kop(at)meme(dot)com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein