Re: Allow COPY from STDIN to absorb all input before throwing an error

On Apr 8, 2008, at 5:10 PM, Stephen Denne wrote:
> I had an annoying experience with COPY within psql yesterday.
> I had a dump of just three tables, which I wanted to investigate. I
> tried loading them into an empty database, using psql's \i command.
> The table creation failed as dependent tables/sequences where absent.
> The copy command failed as the tables did not exist.
> The data intended as the input to the copy statement resulted in a
> large number of error messages.
>
>>> My idea to avoid this situation is to add an option to COPY that
>>> tells it not to throw an error until it runs out of input data.
>>
>> This will not solve the problem, since again it only works if the
>> COPY
>> command gets to execution

I brought this up because of a very similar problem a coworker ran
into. He did a pg_dumpall and tried to restore it into an existing
cluster. One of the tables already existed and didn't have the same
columns, so the copy command ran and then failed. And then all hell
broke lose. :) This was on 8.1, which AFAIK is using the v3 protocol,
so it's still an issue.

I can see that there would be a problem if you wrapped the dump into
a transaction and something up-stream of the copy failed... I'm not
sure on a good way to handle that, perhaps other than switching to
\COPY.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828