pg_restore error checking

From: Stephen Frost <sfrost(at)snowman(dot)net>
To: pgsql-bugs(at)postgresql(dot)org
Subject: pg_restore error checking
Date: 2006-01-20 14:49:57
Message-ID: 20060120144957.GG6026@ns.snowman.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-patches

Greetings,

pg_restore doesn't handle errors very well. While attempting to
upgraded a database from 8.0 to 8.1 there were some errors creating
some tables because 8.0 had PostGIS installed (along with some tables)
and 8.1 did not. This also meant that attempts to load the tables
which had failed to be created didn't work. The bad part about this
is that instead of skipping to the end of the data from the COPY
command, pg_restore seems to have thought that the contents of the
table was an SQL command and read in the whole table and I think then
got very confused. Here's the process list from this:

postgres 6692 2.7 0.7 78380 48524 pts/3 S+ Jan19 32:26 /usr/lib/postgresql/8.1/bin/pg_dump -h /var/run/postgresql -p 5432 -Fc tsf
postgres 6693 1.2 51.7 3198716 3169240 pts/3 S+ Jan19 15:06 /usr/lib/postgresql/8.1/bin/pg_restore -h /var/run/postgresql -p 5433 -d template1 -C
postgres 6694 10.8 0.9 96780 59316 ? S Jan19 126:40 postgres: postgres tsf [local] COPY
postgres 6782 3.4 0.3 52364 24092 ? S Jan19 40:12 postgres: postgres tsf [local] idle

As you can see, pg_restore is using up 3G or so, which seems likely to
be the size of one of the PostGIS-based tables. The 8.0 COPY seems to
think it's still going (perhaps it's blocking because pg_restore is
trying to do something, but it's not actually using any CPU time),
while the 8.1 connection is idle (waiting for pg_restore to give it
something to do).

It seems like pg_restore really should be able to handle COPY errors
correctly by skipping to the end of the COPY data segment when the
initial COPY command comes back as an error. If it's not too invasive
of a fix, I really think it should be included in the next point
release of 8.1.

Thanks!

Stephen

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2006-01-20 15:40:28 Re: pg_restore error checking
Previous Message Steven Mooij 2006-01-20 10:17:51 Re: BUG #2168: 45.000.000 records too much?

Browse pgsql-patches by date

  From Date Subject
Next Message Tom Lane 2006-01-20 15:40:28 Re: pg_restore error checking
Previous Message Rocco Altier 2006-01-20 03:34:44 Re: [HACKERS] pgxs/windows