Re: pg_restore COPY error handling

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Andrew Dunstan wrote:
> >> I assume you are referring to this post:
> >> http://archives.postgresql.org/pgsql-bugs/2006-01/msg00188.php
>
> > OK, that helps.  The solution is to "not do that", meaning install
> > postgis before the restore or something.  Anyway, adding the patch seems
> > like too large a risk for a minor release, especially since you are the
> > first person to complain about it that I remember.
>
> I don't think you should see this as something specific to PostGIS.
> If I interpret Stephen's report properly, any sort of failure during
> COPY IN would lead to undesirable behavior in pg_restore.

I'm reasonably confident this is exactly the case.

> This is not super surprising because the original design approach for
> pg_restore was "bomb out on any sort of difficulty whatsoever".  That
> was justly complained about, and now it will try to keep going after
> SQL-command errors ... but it sounds like the COPY-data processing
> part didn't get fixed properly.

Exactly so.  Possibly because it appears to be non-trivial to detect
when it was a *COPY* command which failed (to differentiate it from some
other command).  What I did was check for '<whitespace>COPY'.  I'd be
happy to change that if anyone has a better suggestion though.  There
might be a way to pass that information down into the pg_archive_db but
I don't think it's available at that level currently and I didn't see
any way to get the answer from libpq about what *kind* of command
failed.

> I take no position on whether Stephen's patch is any good --- I haven't
> looked at it --- but if he's correct about the problem then I agree it's
> a bug fix.  Before deciding whether it deserves to be back-patched, we
> at least ought to look closely enough to characterize the situations in
> which pg_restore will fail.

I have some level of confidence that this is the only case along these
lines because it's the only case where pg_restore isn't dealing with SQL
commands but with a dataset which has to be handled in a special way.
pg_restore checks if the command sent was a successful COPY command and
then treats everything after it in a special way; it doesn't do that for
any other commands...

    Thanks,

        Stephen