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

On 05/19/2007 12:41:47 PM, Tom Lane wrote:
> "Karl O. Pinc" <kop(at)meme(dot)com> writes:
> > I don't really want to do this. I really want my users
> > to be able to use the COPY statement without worrying
> > about whether they are copying into a table or a view.
>
> But ... but ... the proposed feature entirely fails to achieve that.
> Copying into an explicit INSERT statement isn't necessarily a bad
> idea,
> but surely it's not transparent in that way.

Sorry to be confusing. The first sentence in my email
starting this thread was "I'm attempting a patch
that would allow the COPY
command to copy into a view." That's what the
patch does.

The part of the first email that talks
about copying into an INSERT statement was my
attempt to summarize the brief discussion that
occurred on IRC when I went there to talk about
copying into a view. As well as the design related
thoughts I've had since.

If you could COPY into an INSERT statement then
you could have COPYing into a view be syntactic
sugar for COPYing into a INSERT statement that
inserts into the view. Then it'd be transparent.
(And that's how my patch actually works now,
it comes up with an INSERT statement and
prepares it and (tries to) execute it in a portal.)

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

Recalling the discussion, plus perhaps a solution:

The problem with UPDATE and DELETE on BEFORE
triggers is coming up with an OLD row. IIRC it is
possible to take the SELECT associated with the view,
add to it the additional WHERE conditions specified
by the UPDATE and the DELETE, and run the resulting
query to come up with the OLD row(s).

The steps would be something like:

Take the raw parse tree of the UPDATE/DELETE WHERE and stick it
into the raw parse tree of a:
"SELECT * FROM viewname"

Send the result through the rule system and run it.

For each row returned do the UPDATE or DELETE, which
would involve calling the BEFORE INSERT trigger
with the row as OLD.

Check that the BEFORE INSERT returns NULL.

The big problem that came up revolves around what
to do if the view does not contain any/enough
primary keys. That would make the BEFORE INSERT
trigger pretty useless when it comes to updating
the underlying tables. I am willing to argue that
the solution to this need not be implemented right
away, along with the rest of the BEFORE INSERT
on views, because there's clear utility when the
views _do_ reveal primary keys. And, perhaps more
importantly, I think there's at least one clean way
to solve the problem.

The trick would be to allow the definition of
the view to "hide" particular columns
in the normal use of the view, but show them in the
OLD rows of the BEFORE INSERT trigger. This would
make primary keys or other data available to the
trigger, without having to reveal them to the
regular users of the view. A syntax like:

CREATE VIEW viewname ( [ BEFORE INSERT ] columnname, ... )
AS query

If "BEFORE INSERT" shows up before "columnname" then
the columnname is not visible in the view, but is visible
in the OLD row of the BEFORE INSERT trigger code.
I'd imagine this means keeping two different query trees
for the view, one with and one without the hidden columns.

Another trouble would be if the view had multiple
columns with the same name, as gotten from
the underlying tables. The solution there is simple;
don't allow BEFORE INSERT triggers on views unless
the columns of the view have unique names.

To keep things sane it'd probably also be a good
idea to have BEFORE INSERT triggers on views
be mutually exclusive with having INSERT/UPDATE/DELETE
rules on them. But I've not thought that through.

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