pg_restore silently chokes on object comments/descriptions ending in a backslash

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

Responses

Browse pgsql-bugs by date

  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