From: | Edmund Bacon <ebacon(at)onesystem(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "pgsql-bugs(at)postgresql(dot)org" <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: 8.0 beta1: pg_dump/restore failing |
Date: | 2004-08-16 15:03:48 |
Message-ID: | 4120CCD4.1070202@onesystem.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)onesystem(dot)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(at)onesystem(dot)com>
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2004-08-16 15:09:40 | Re: 8.0 beta1: pg_dump/restore failing |
Previous Message | Tom Lane | 2004-08-16 14:56:44 | Re: Turkish downcasting in PL/pgSQL |