Обсуждение: BUG #4054: Text containing ';' upsets db restores

The following bug has been logged online:

Bug reference:      4054
Logged by:          Martin Gregorie
Email address:      martin@gregorie.org
PostgreSQL version: 8.2.6
Operating system:   Linux (Redhat Fedora 8
Description:        Text containing ';' upsets db restores
Details:

I've been dumping my database using pg_dumpall in its default (fast)
version.

This weekend, after upgrading from Fedora 7 to Fedora 8 I had to restore my
database. Finger trouble & tiredness caused the restore. The database
survived the upgrade but not me. I found that replaying the output from
pg_dumpall into psql caused problems:

1) A VIEW in the database was not dropped. The DROP
   for it was not in the pg_dumpall file). Minor
   point, mentioned only for completeness.
   A manual DROP fixed that.

2) Every table has an int as the first field.
   This number value is flagged as an error for
   the first row in every table.

3) My tables have BYTEA and TEXT fields in them.
   In some rows these field contain HTML text.
   This caused syntax errors during the reload.
   Specifically, "; " and "</" sequences were
   interpreted by psql as syntactic errors wherever
   they occur in textual fields.

4) I was running psql with a -1 option which, rather
   to my surprise, didn't abort the run after the
   first error in the file. Lines that are not flagged
   with errors seem to have loaded OK

The pg_dumpall that made the dumps was from the fully patched version of
Fedora 7 (Postgresql 8.2.5?). The restore was made using psql from version
8.2.6.

The errors were captured in the log file, which is safe from being
overwritten. You're welcome to either the whole thing or to edited
highlights from it.

"Martin Gregorie" <martin@gregorie.org> writes:
> The errors were captured in the log file, which is safe from being
> overwritten. You're welcome to either the whole thing or to edited
> highlights from it.

That's probably fairly useless, but if you have the dump file it would
be interesting to look at that.

Are you sure that the errors aren't all attributable to a CREATE TABLE
statement failing and then the subsequent COPY command failing even to
start?  Usually you need to look first at the first error, since a lot
of the subsequent ones could be cascading failures.

> 4) I was running psql with a -1 option which, rather
>    to my surprise, didn't abort the run after the
>    first error in the file.

psql doesn't have a -1 option, that's only for pg_restore.  (I believe
pg_restore is a bit smarter about the failed-COPY case, as well.)

            regards, tom lane