--PmR16eBfJVO7r8Yq
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

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/p=
ostgresql/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/p=
ostgresql/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 =
=20
postgres 6782 3.4 0.3 52364 24092 ? S Jan19 40:12 postgres: =
postgres tsf [local] idle =
=20

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

--PmR16eBfJVO7r8Yq
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (GNU/Linux)

iD8DBQFD0PiVrzgMPqB3kigRAiPvAJ4juCl0xsiRxIv5IGK7gg 6YtJ6fTACfcMOw
o/Fq6ucbPDPB8MgTAqoykXg=
=mcdk
-----END PGP SIGNATURE-----

--PmR16eBfJVO7r8Yq--

Stephen Frost <sfrost (AT) snowman (DOT) net> writes:
Send a patch ;-)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly