Thread: Allow COPY from STDIN to absorb all input before throwing an error
When restoring from pg_dump(all), if a problem occurs in a COPY command you're going to get a whole slew of errors, because as soon as COPY detects a problem it will throw an error and psql will immediately switch to trying to process the remaining data that was meant for COPY as if it was psql commands. This is confusing and annoying at best; it could conceivably trash data at worst (picture dumping a table that had SQL commands in it). 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. Of course once it finds a problem it would just throw all the input data away, but when used in the context of a dump file this would remove all the bogus errors that either psql or the backend will generate when trying to process table data as if it was commands. -- Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828
On Tue, 2008-04-08 at 15:26 -0500, Decibel! wrote: > 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. An alternative would be to have the client continue reading (and discarding) COPY input until the end-of-COPY-input sequence is reached, and then switch back into normal input processing mode. -Neil
Decibel! <decibel@decibel.org> writes:
> When restoring from pg_dump(all), if a problem occurs in a COPY
> command you're going to get a whole slew of errors, because as soon
> as COPY detects a problem it will throw an error and psql will
> immediately switch to trying to process the remaining data that was
> meant for COPY as if it was psql commands. This is confusing and
> annoying at best; it could conceivably trash data at worst (picture
> dumping a table that had SQL commands in it).
This is nonsense; it hasn't worked that way since we went to v3
protocol.
What is true is that if the COPY command itself is thoroughly borked,
the backend never tells psql to switch into COPY mode in the first
place.
> 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.
Perhaps we could improve matters by having pg_dump issue \copy instead
of COPY and tweaking psql \copy (when non-interactive) to switch to
COPY-mode even if the backend rejects the command. I seem to recall
though that there was some reason for sticking to the COPY command form.
In the meantime, pg_restore direct to DB is reasonably proof against the
problem anyway ...
regards, tom lane
Tom Lane wrote > Decibel! <decibel@decibel.org> writes: > > When restoring from pg_dump(all), if a problem occurs in a COPY > > command you're going to get a whole slew of errors, because > as soon > > as COPY detects a problem it will throw an error and psql will > > immediately switch to trying to process the remaining data > that was > > meant for COPY as if it was psql commands. This is confusing and > > annoying at best; it could conceivably trash data at worst > (picture > > dumping a table that had SQL commands in it). > > This is nonsense; it hasn't worked that way since we went to v3 > protocol. > > What is true is that if the COPY command itself is thoroughly borked, > the backend never tells psql to switch into COPY mode in the first > place. 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. It is only now that I've found the \set ON_ERROR_STOP command, which I presume would have solved my problem. Regards, Stephen Denne. Disclaimer: At the Datamail Group we value team commitment, respect, achievement, customer focus, and courage. This email with any attachmentsis confidential and may be subject to legal privilege. If it is not intended for you please advise by reply immediately,destroy it and do not copy, disclose or use it in any way. __________________________________________________________________ This email has been scanned by the DMZGlobal BusinessQuality Electronic Messaging Suite. Please see http://www.dmzglobal.com/dmzmessaging.htm for details. __________________________________________________________________
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@decibel.org Give your computer some brain candy! www.distributed.net Team #1828