Обсуждение: pg_dump, psql load problem

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

hey all,
one of our applications has the following view:
- --
- -- TOC Entry ID 85 (OID 27519291)
- --
- -- Name: inusecountrycode Type: VIEW Owner: postgres
- --

CREATE VIEW "inusecountrycode" as SELECT DISTINCT
countryname.seviscountrycodeid AS countrycode FROM countryname,
sevisrecordworking WHERE (countryname.seviscountrycodeid =
sevisrecordworking.permanentresidencecountrycode) ORDER BY
countryname.seviscountrycodeid UNION SELECT DISTINCT
countryname.seviscountrycodeid AS countrycode FROM countryname,
sevisrecordofficial WHERE (countryname.seviscountrycodeid =
sevisrecordofficial.permanentresidencecountrycode) ORDER BY
countryname.seviscountrycodeid;

that is what gets dumped via pg_dump.  however if we take that statement
and paste it into psql (as one long line) or through command line (ie
psql dbname < inusecountrycode.schema) we get an error:
'ERROR:  parser: parse error at or near "SELECT"'

if i manually change the definition to put parenthesis around each
select statement, then psql has no problem.  also, if i break the
statement up into individual lines and paste it into psql, i will not
get the error.

vcsdev=# select version();
~                            version
- ---------------------------------------------------------------
~ PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.95.4
(1 row)

note - we have not yet tested this application over to 7.3 so i can't
test it there.

has anyone seen anything similar?
- --
Tom Jenkins
devIS - Development Infostructure
http://www.devis.com


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.0-nr2 (Windows XP)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE/MVttV7Yk9/McDYURAvUZAKCJ+SrH03FmJgI28FWKMsaxmqbjZACcCHU+
wjPqCk0ktVz2Q9y7L/Dqpww=
=luSU
-----END PGP SIGNATURE-----

Tom Jenkins <tjenkins@devis.com> writes:
> that is what gets dumped via pg_dump.  however if we take that statement
> and paste it into psql (as one long line) or through command line (ie
> psql dbname < inusecountrycode.schema) we get an error:
> 'ERROR:  parser: parse error at or near "SELECT"'

> if i manually change the definition to put parenthesis around each
> select statement, then psql has no problem.

Yeah.  This is a known bug in PG 7.2.1 (in the backend, actually, not
in pg_dump).  [checks CVS logs...]  It's fixed as of 7.2.2:

2002-06-15 14:38  tgl

    * src/backend/utils/adt/ruleutils.c (REL7_2_STABLE): Back-patch 7.3
    fix to fully parenthesize UNION/EXCEPT/INTERSECT queries in
    ruleutils output.  The previous partial parenthesization was a hack
    to get around grammar restrictions that have since been fixed; and
    as Sam O'Connor pointed out, there were cases where it didn't work.

You really should be running 7.2.4 in any case --- there are some nasty
bugs fixed in the later 7.2 releases.

            regards, tom lane