From: | Julian Mehnle <julian(at)mehnle(dot)net> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | pg_restore silently chokes on object comments/descriptions ending in a backslash |
Date: | 2011-07-27 00:42:18 |
Message-ID: | 201107270042.22427.julian@mehnle.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
I observe the following issue on PostgreSQL 9.0.4 on at least the
following platforms:
* FreeBSD 6.3 (amd64)
`uname -a`:
FreeBSD <hostname> 6.3-STABLE FreeBSD 6.3-STABLE #1: Fri May 30 18:11:47 PDT 2008
root@<hostname>:/data/obj/data/home/<username>/symbols/builddir_amd64/usr/src/sys/MESSAGING_GATEWAY.amd64_INSTALL amd64
* Mac OS X 10.6.8 (i386)
`uname -a`:
Darwin joule 10.8.0 Darwin Kernel Version 10.8.0: Tue Jun 7 16:33:36 PDT 2011; root:xnu-1504.15.3~1/RELEASE_I386 i386 i386
* semi-current Debian testing (amd64)
`uname -a`:
Linux gray 2.6.30-2-amd64 #1 SMP Mon Dec 7 05:21:45 UTC 2009 x86_64 GNU/Linux
If the comment/description of a database object (table, function, etc.)
ends in a backslash (which generally works fine otherwise), then
pg_restore is unable to completely restore a custom-format dump of the
schema. pg_restore does not complain, but silently(!) stops issuing DDL
statements to the server starting with the first "COMMENT ON …" statement
that would have set an object comment/description ending in a backslash.
Reproduce as follows:
$ createdb test0
$ createdb test1
$ psql -c "CREATE TABLE bar (); COMMENT ON TABLE bar IS 'bar\\';" test0
COMMENT
$ psql -c "CREATE TABLE foo (); COMMENT ON TABLE foo IS 'foo';" test0
COMMENT
$ pg_dump --format custom --file test0.pg_dump --schema-only test0
$ pg_restore -d test1 test0.pg_dump
$ psql -c '\dt+' test0
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+--------+---------+-------------
public | bar | table | julian | 0 bytes | bar\
public | foo | table | julian | 0 bytes | foo
(2 rows)
$ psql -c '\dt+' test1
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+--------+---------+-------------
public | bar | table | julian | 0 bytes |
(1 row)
This also happens with PostgreSQL 8.4.
To demonstrate that this is not an academic issue, these are a few
functions I have defined, and their comments:
List of functions
-[ RECORD 1 ]-------+------------------------------------------------------------------------
Schema | public
Name | escape_are
... : ...
Description | escape advanced regexp (ARE) special characters: .*+?|[](){}^$\
-[ RECORD 2 ]-------+------------------------------------------------------------------------
Schema | public
Name | escape_control
... : ...
Description | escape control characters: \a\b\t\n\v\f\r\e\\
-[ RECORD 3 ]-------+------------------------------------------------------------------------
Schema | public
Name | escape_like
... : ...
Description | escape LIKE pattern special characters: %_\
I have worked around the issue by appending a space character to each of
those function descriptions. What makes the problem really bad is that it
silently renders your custom-format database dumps (which pg_dump creates
just fine) useless, which you notice only after you do a restore (without
an error being thrown) and your restored database being incomplete.
-Julian
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2011-07-27 03:27:14 | Re: pg_restore silently chokes on object comments/descriptions ending in a backslash |
Previous Message | David Johnston | 2011-07-26 23:57:42 | Re: BUG #6131: Query Returning Incorrect Results |