Yes, we realize this is a problem and are working on a solution for beta2.
--------------------------------------------------------------------------- Edmund Bacon wrote: > 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]) > -- Bruce Momjian | http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
