On May 8, 2013, at 1:16 PM, Tom Lane wrote:
> Heikki Linnakangas <hlinnakan...@vmware.com> writes:
>> On 08.05.2013 19:44, Tom Lane wrote:
>>> No there isn't; what you suggest would require FE/BE protocol
>>> extensions, making it several orders of magnitude more work than the
>>> other thing.
>
>> I'd imagine that the flow would go something like this:
>
>> BE FE
>
>> CopyInResponse
>> CopyData
>> CopyData
>> ...
>> CopyDone
>> RowDescription
>> DataRow
>> DataRow
>> CommandComplete
>
> That would require the backend to buffer the entire query response,
> which isn't a great idea. I would expect that such an operation would
> need to interleave CopyData to the backend with DataRow responses. Such
> a thing could possibly be built on COPY_BOTH mode, but it would be a lot
> of work (at both ends) for extremely debatable value.
>
> The general idea of COPY is to load data as fast as possible, so weighing
> it down with processing options seems like a pretty dubious idea even if
> the implementation were easy.
There are cases that I indeed want to load data very quickly, but I want to
perform an operation on it immediately after, e.g. removing bad data that was
immediately added from that copy. For instance, I do have this scenario:
WITH new_data AS (
COPY FROM ...
RETURNING id, field_to_check
)
DELETE FROM table
USING new_data
WHERE
table.id = new_data.id AND
new_data.field_to_check ~* 'bad data';
Now I can take care of that all in one step, and I know I'm only removing
fields I just added. This comes up when I am importing external files from
other sources where I may not necessarily want all of the rows or some of the
rows contain bad data.
This also presumes that COPY works in a CTE, which I'm not sure it does (and I
will do the TIAS test after I hit send on this message).
Jonathan
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
