Re: 8.0 beta1: pg_dump/restore failing - Mailing list pgsql-bugs

Yes. My appologies for the poor bug report.

I have tried this on RedHat 9, gcc 3.2.2 x86 and HP-UX 10.20 gcc 3.2.3
pa-risc

The problem is that pg_restore is not correctly recognizing the ending
$$ quotes on functions:  Note that in the pg_restore text output at the
bottom of the message, the closing $$ quotes are there, but pg_restore
using the custom format (or tar format) doesn't recognize them.  Doing a
strings -a on test.dump also shows the closing $$ quotes.

e.g.

# -- start with a fresh database:

$ createdb test
CREATE DATABASE

# -- do a restore
$ pg_restore --format=c --dbname=test test.dump
pg_restore: [archiver (db)] could not execute query: ERROR:
unterminated dollar
-quoted string at or near "$$begin return 1;" at character 115
pg_restore: WARNING:  there is no transaction in progress
pg_restore: [archiver (db)] could not execute query: ERROR:
unterminated dollar
-quoted string at or near "$$
     LANGUAGE plpgsql;" at character 1
pg_restore: [archiver (db)] could not execute query: ERROR:  function
public.foo
() does not exist
--
-- PostgreSQL database dump complete
--

WARNING, errors ignored on restore: 3

# -- try again with fresh database, but use psql and output from pg_dump

$ dropdb test
DROP DATABASE
$ createdb test
CREATE DATABASE

$ pg_restore test.dump | psql test -f -
SET
SET
COMMENT
SET
CREATE FUNCTION
ALTER FUNCTION
CREATE FUNCTION
ALTER FUNCTION
CREATE LANGUAGE
CREATE FUNCTION
ALTER FUNCTION
REVOKE
REVOKE
GRANT
GRANT
$

#### -- and here is the pg_restore output

$ pg_restore test.dump
--
-- PostgreSQL database dump
--

[ ### snip creating language plpgsql, etc ### ]

--
-- Name: foo(); Type: FUNCTION; Schema: public; Owner: ebacon
--

CREATE FUNCTION foo() RETURNS integer
     AS $$begin return 1; end;$$
     LANGUAGE plpgsql;


ALTER FUNCTION public.foo() OWNER TO ebacon;

--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;


--
-- PostgreSQL database dump complete
--

$

Tom Lane wrote:
> Edmund Bacon <ebacon@onesystem.com> writes:
>
>>pg_dump/restore in 8.0beta1 are not working well with formats other
>>thans plain text:
>
>
> What seems to be broken is --clean mode, because it drops and fails to
> restore the public schema.  I can see no reason that it wouldn't
> work exactly the same regardless of dump format though.  Are you
> sure you didn't get confused because things were already broken
> once the destination DB's public schema was gone?
>
>             regards, tom lane

--
Edmund Bacon <ebacon@onesystem.com>