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

Search Discussions

  • Neil Conway at Apr 8, 2008 at 9:17 pm

    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
  • Tom Lane at Apr 8, 2008 at 9:39 pm

    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
  • Stephen Denne at Apr 8, 2008 at 10:11 pm
    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.
    __________________________________________________________________
  • Decibel! at Apr 9, 2008 at 7:30 pm

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedApr 8, '08 at 8:27p
activeApr 9, '08 at 7:30p
posts5
users4
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase