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

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

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).
--
Jim Nasby decibel(at)decibel(dot)org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)