pg_restore silently chokes on object comments/descriptions ending in a backslash - Mailing list 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:4=
7 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 P=
DT 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 =E2=80=A6" sta=
tement
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=20
    $ psql -c '\dt+' test0
                       List of relations
     Schema | Name | Type  | Owner  |  Size   | Description=20
    --------+------+-------+--------+---------+-------------
     public | bar  | table | julian | 0 bytes | bar\
     public | foo  | table | julian | 0 bytes | foo
    (2 rows)
=20=20=20=20
    $ psql -c '\dt+' test1
                       List of relations
     Schema | Name | Type  | Owner  |  Size   | Description=20
    --------+------+-------+--------+---------+-------------
     public | bar  | table | julian | 0 bytes |=20
    (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