Thread

1. Allow COPY from STDIN to absorb all input before throwing an error

   Decibel! <decibel@decibel.org> — 2008-04-08T20:26:24Z

   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
   
   
   

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

   Neil Conway <neilc@samurai.com> — 2008-04-08T20:50:20Z

   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
   
   
   
   

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

   Tom Lane <tgl@sss.pgh.pa.us> — 2008-04-08T21:39:48Z

   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
   
   

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

   Stephen Denne <stephen.denne@datamail.co.nz> — 2008-04-08T22:10:29Z

   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 attachments is 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 Business Quality
                 Electronic Messaging Suite.
   Please see http://www.dmzglobal.com/dmzmessaging.htm for details.
   __________________________________________________________________
   
   
   
   

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

   Decibel! <decibel@decibel.org> — 2008-04-09T19:29:34Z

   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