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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]>
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
