Re: COPY into a view; help w. design & patch - Mailing list pgsql-hackers

On 05/21/2007 12:17:38 PM, Jim C. Nasby wrote:
> On Mon, May 21, 2007 at 05:02:29PM +0000, Karl O. Pinc wrote:
> >
> > On 05/21/2007 11:23:57 AM, Jim C. Nasby wrote:
> >
> > >What about adding COPY support to rules? ISTM if you want to copy
> into
> > >a
> > >view you probably want to insert into it as well, so why not use
> the
> > >same mechanism? Presumably a COPY rule would also be faster than a
> > >trigger.
> >
> > I'd say there's no difference between the rule you'd use
> > for COPYing and the rule you'd use for INSERTing,
> > which is why my patch produces an
> > INSERT statement and then proceeds to (attempt
> > to) execute the statement for every row of data
> > to be copied.  If you don't have a rule that allows
> > INSERT into the view you get (the already existing)
> > error with a hint that tells you to make an INSERT
> > rule.
> 
> As Tom mentioned, that's very non-transparent to users.
...

I don't think we understand each other.  (I sure don't
understand the non-transparency comment above.  I thought
Tom said that in regards having to write a special/new
COPY syntax in order to insert into a view, rather than
just using a name of a view instead of a name of a table.)

When I say I write and execute an INSERT statement I mean
that the INSERT statement into the view is executed just as if the user
wrote it -- it is passed through the rule system and turns into
whatever INSERT or other statements the user has
associated with INSERTing into the view.  The INSERT
statement must only be passed through the rule system once,
the resulting prepared statement is executed for every line
of COPY input.  This is exactly what you propose when
you say COPY should go through the rule system, except
that I'm using the INSERT rule to do the COPY rather
than have a separate COPY rule.  Or maybe not, see below.
 You're also
> assuming that converting a COPY to a string of INSERTS (which would
> then
> get pushed through the rule system one-by-one) would be as efficient
> as
> just copying into a table. I don't believe that's the case.

I'm sure it's not.  But (IMO) anybody who's really concerned about
efficency shouldn't be using a view anyhow.  It's easy
enough to run the raw data through a awk script or something
and COPY into the underlying tables.  Views are for making things
easier to do.  It's not necessary for them to be as fast as possible
in all cases.  Again, in my opinion.

> I haven't studied the rule code, but at least for the simple case of
> redirecting a copy into a view to a single table (ie: a single
> statement
> INSTEAD rule that has no where clause) the copy command should be able
> to be changed by the rule so that it's just inserting into a different
> table. The performance should then be the same as if you copied
> directly
> into that table in the first place.

The problem comes when the user writes rules that insert into
mutiple tables, or do other random things.  At that point
you just want to "do what the user asked" when inserting
each row of data into the view because you really don't
know what the rules are going to expand into.

> 
> This doesn't mean that a row-by-row capability (or the ability to have
> COPY generate insert statements) would be bad, but they are not the
> same
> as a simple rewrite of a COPY command (ie: adding COPY support to
> rules).

Are you talking about having a COPY statement rewrite into a bunch
of COPY statments, and then applying the input data to each copy
statement in turn?  That sounds feasible.  There would be a certain
disconnect between such a COPY rule and the rest of the rule
system.  All the other sorts of rules can expand into any kind
of statement.  You could, in theory, have an INSERT rule
that deletes a row from table B for every row inserted into
table A.  (Probably in addition to inserting into table A.)
COPY rules couldn't work that way.  At least not without
all sorts of implimentation complication.  Maybe it doesn't
matter; you could argue that INSERT rules shouldn't do anything
but INSERT, ever.  But that's not enforced now.

It'd also be a little wierd to have an INSERT rule that behaves
differently from a COPY rule, updating different tables or whatever.


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