BUG #2662: pg_dump out cannot be retored - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      2662
Logged by:          Harry Hehl
Email address:      harry.hehl@diskstream.com
PostgreSQL version: 8.1.3
Operating system:   Linux
Description:        pg_dump out cannot be retored
Details:

When schemas are used, the output created by pg_dump -Fp cannot be restored.


psql error...

ERROR:  foreign key constraint "accepttaskevent" cannot be implemented
DETAIL:  Key columns "accepttaskevent" and "objectid" are of incompatible
types: myschema1.editrate and myschema1.editrate.

Below is the pg_dump output that was been reduced to reproduce error. If
schemas are not used search path includes public, this restores
successfully.

--
-- PostgreSQL database dump
--

SET client_encoding = 'UTF8';
SET check_function_bodies = false;
SET client_min_messages = warning;

--
-- Name: myschema2; Type: SCHEMA; Schema: -; Owner: postgres
--

CREATE SCHEMA myschema2;


ALTER SCHEMA myschema2 OWNER TO postgres;

--
-- Name: myschema1; Type: SCHEMA; Schema: -; Owner: postgres
--

CREATE SCHEMA myschema1;


ALTER SCHEMA myschema1 OWNER TO postgres;

--
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
--

COMMENT ON SCHEMA public IS 'Standard public schema';


--
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner:
--

CREATE PROCEDURAL LANGUAGE plpgsql;


--
-- Name: plpythonu; Type: PROCEDURAL LANGUAGE; Schema: -; Owner:
--

CREATE PROCEDURAL LANGUAGE plpythonu;


SET search_path = myschema1, pg_catalog;

--
-- Name: editrate_in(cstring); Type: FUNCTION; Schema: myschema1; Owner:
postgres
--

CREATE FUNCTION editrate_in(cstring) RETURNS editrate
    AS 'pgextensions.so', 'editrate_in'
    LANGUAGE c IMMUTABLE STRICT;


ALTER FUNCTION myschema1.editrate_in(cstring) OWNER TO postgres;

--
-- Name: editrate_out(editrate); Type: FUNCTION; Schema: myschema1; Owner:
postgres
--

CREATE FUNCTION editrate_out(editrate) RETURNS cstring
    AS 'pgextensions.so', 'editrate_out'
    LANGUAGE c IMMUTABLE STRICT;


ALTER FUNCTION myschema1.editrate_out(editrate) OWNER TO postgres;

--
-- Name: editrate_recv(internal); Type: FUNCTION; Schema: myschema1; Owner:
postgres
--

CREATE FUNCTION editrate_recv(internal) RETURNS editrate
    AS 'pgextensions.so', 'editrate_recv'
    LANGUAGE c IMMUTABLE STRICT;


ALTER FUNCTION myschema1.editrate_recv(internal) OWNER TO postgres;

--
-- Name: editrate_send(editrate); Type: FUNCTION; Schema: myschema1; Owner:
postgres
--

CREATE FUNCTION editrate_send(editrate) RETURNS bytea
    AS 'pgextensions.so', 'editrate_send'
    LANGUAGE c IMMUTABLE STRICT;


ALTER FUNCTION myschema1.editrate_send(editrate) OWNER TO postgres;

--
-- Name: editrate; Type: TYPE; Schema: myschema1; Owner: postgres
--

CREATE TYPE editrate (
    INTERNALLENGTH = 12,
    INPUT = editrate_in,
    OUTPUT = editrate_out,
    RECEIVE = editrate_recv,
    SEND = editrate_send,
    ALIGNMENT = int4,
    STORAGE = plain
);


ALTER TYPE myschema1.editrate OWNER TO postgres;

SET search_path = myschema2, pg_catalog;


SET search_path = myschema1, pg_catalog;

--
-- Name: editrate_cmp(editrate, editrate); Type: FUNCTION; Schema:
myschema1; Owner: postgres
--

CREATE FUNCTION editrate_cmp(editrate, editrate) RETURNS integer
    AS 'pgextensions.so', 'editrate_cmp'
    LANGUAGE c IMMUTABLE STRICT;


ALTER FUNCTION myschema1.editrate_cmp(editrate, editrate) OWNER TO
postgres;

--
-- Name: editrate_eq(editrate, editrate); Type: FUNCTION; Schema: myschema1;
Owner: postgres
--

CREATE FUNCTION editrate_eq(editrate, editrate) RETURNS boolean
    AS 'pgextensions.so', 'editrate_eq'
    LANGUAGE c IMMUTABLE STRICT;


ALTER FUNCTION myschema1.editrate_eq(editrate, editrate) OWNER TO postgres;

--
-- Name: editrate_ne(editrate, editrate); Type: FUNCTION; Schema: myschema1;
Owner: postgres
--

CREATE FUNCTION editrate_ne(editrate, editrate) RETURNS boolean
    AS 'pgextensions.so', 'editrate_ne'
    LANGUAGE c IMMUTABLE STRICT;


ALTER FUNCTION myschema1.editrate_ne(editrate, editrate) OWNER TO postgres;

--
-- Name: editrate_textin(text); Type: FUNCTION; Schema: myschema1; Owner:
postgres
--

CREATE FUNCTION editrate_textin(text) RETURNS editrate
    AS 'pgextensions.so', 'editrate_textin'
    LANGUAGE c IMMUTABLE STRICT;


ALTER FUNCTION myschema1.editrate_textin(text) OWNER TO postgres;


SET search_path = public, pg_catalog;

--
-- Name: <>; Type: OPERATOR; Schema: public; Owner: postgres
--

CREATE OPERATOR <> (
    PROCEDURE = myschema1.editrate_ne,
    LEFTARG = myschema1.editrate,
    RIGHTARG = myschema1.editrate,
    COMMUTATOR = <>,
    NEGATOR = =,
    RESTRICT = scalarltsel,
    JOIN = scalarltjoinsel
);


ALTER OPERATOR public.<> (myschema1.editrate, myschema1.editrate) OWNER TO
postgres;


--
-- Name: =; Type: OPERATOR; Schema: public; Owner: postgres
--

CREATE OPERATOR = (
    PROCEDURE = myschema1.editrate_eq,
    LEFTARG = myschema1.editrate,
    RIGHTARG = myschema1.editrate,
    COMMUTATOR = =,
    NEGATOR = <>,
    RESTRICT = scalarltsel,
    JOIN = scalarltjoinsel
);


ALTER OPERATOR public.= (myschema1.editrate, myschema1.editrate) OWNER TO
postgres;



--
-- Name: myschema1_editrate_ops; Type: OPERATOR CLASS; Schema: public;
Owner: postgres
--

CREATE OPERATOR CLASS myschema1_editrate_ops
    DEFAULT FOR TYPE myschema1.editrate USING btree AS
    OPERATOR 1 =(myschema1.editrate,myschema1.editrate) ,
    FUNCTION 1
myschema1.editrate_cmp(myschema1.editrate,myschema1.editrate);


ALTER OPERATOR CLASS public.myschema1_editrate_ops USING btree OWNER TO
postgres;


SET search_path = myschema2, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

CREATE TABLE omarchivereviewtask2 (
    accepttaskevent myschema1.editrate
);

ALTER TABLE myschema2.omarchivereviewtask2 OWNER TO postgres;

SET search_path = myschema1, pg_catalog;

CREATE TABLE master (
    objectid editrate NOT NULL
);


ALTER TABLE myschema1.master OWNER TO postgres;

ALTER TABLE ONLY master
    ADD CONSTRAINT master_pkey PRIMARY KEY (objectid);

--
-- Name: schemainfo; Type: TABLE; Schema: myschema1; Owner: postgres;
Tablespace:
--


SET search_path = myschema2, pg_catalog;

ALTER TABLE ONLY omarchivereviewtask2
    ADD CONSTRAINT accepttaskevent FOREIGN KEY (accepttaskevent) REFERENCES
myschema1.master(objectid) DEFERRABLE INITIALLY DEFERRED;