Thread: BUG #7838: pg_dump major bug

The following bug has been logged on the website:

Bug reference:      7838
Logged by:          Jan Mate
Email address:      jan.mate@inf-it.com
PostgreSQL version: 9.2.2
Operating system:   Debian GNU/Linux
Description:        =


Today I tried to upgrade from 9.1.7 to 9.2.2 by using:

pg_upgradecluster 9.1 main

The upgrade process failed on one of my VIEWS with syntax error (after I
removed that VIEW, the upgrade process worked well - also there was no
problem with re-creation of the VIEW after the upgrade).

I created a very simple example to reproduce the problem (looks like it is a
pg_dump problem). Here are the links to files used in my example:

- my original SQL queries
http://www.inf-it.com/fixes/postgres-bugreport/test.sql

- result from the pg_dump
http://www.inf-it.com/fixes/postgres-bugreport/test_dump.sql

Steps to reproduce the problem:
$ createdb test
$ psql test < test.sql
$ psql test -c 'SELECT * FROM export_data;'
      number   |                     org                      |             =

 name
  =

------------+----------------------------------------------+---------------=
-------------------
    5555551212 | Starfleet or Springfield Nuclear Power Plant | Simpson
Homer or Picard Jean-Luc
    (1 row)
$ pg_dump test > test_dump.sql
$ dropdb test
$ createdb test
$ psql test < test_dump.sql
   SET
   SET
   SET
   SET
   SET
   CREATE EXTENSION
   COMMENT
   SET
   SET
   SET
   CREATE TABLE
   ALTER TABLE
   ERROR:  syntax error at or near "["
   LINE 2: ...i'::text) regexp_matches(regexp_matches) LIMIT 1))[1],
'[\\]...
                                                                ^
   ERROR:  relation "public.export_data" does not exist
   REVOKE
   REVOKE
   GRANT
   GRANT

jan.mate@inf-it.com writes:
> I created a very simple example to reproduce the problem (looks like it is a
> pg_dump problem). Here are the links to files used in my example:
> http://www.inf-it.com/fixes/postgres-bugreport/test.sql

Huh.  That's not pg_dump's fault --- it's a grammar bug.  The server
should accept something like

    select ((select array[1,2]))[1];

but it does not.  I see that you dodged the grammar bug in your view
definition by inserting a useless cast:

    select ((select array[1,2])::int[])[1];

but you shouldn't have had to do that.  The useless cast is omitted when
the view definition is regurgitated (indeed it's not there in the
internal form at all), and then the parser fails on that version of the
query text.

This seems to be easy to fix with one more grammar production to cover
the case of "c_expr ::= select_with_parens indirection".  But I'm
surprised we've not heard complaints before, because this area of the
grammar has been like that for a very long time.

Will fix, thanks for the report!

            regards, tom lane