Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-bugs by date

Next:From: Tom LaneDate: 2011-07-27 03:27:14
Subject: Re: pg_restore silently chokes on object comments/descriptions ending in a backslash
Previous:From: David JohnstonDate: 2011-07-26 23:57:42
Subject: Re: BUG #6131: Query Returning Incorrect Results

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group