Thread: pg_dump output containing CREATE TYPE does not restore with psql

I am using the following commands to dump and then restore a database.

pg_dump -Fp "${PGDATABASE}" > "${BACKUPFILE}"
psql --variable ON_ERROR_STOP=1 -f "${BACKUPFILE}"

The restore fails with:

psql:x:384: ERROR:  foreign key constraint "accepttaskevent" cannot be
implemented
DETAIL:  Key columns "accepttaskevent" and "objectid" are of
incompatible types: public.ds_uuid and public.ds_uuid.

The an pg_dump example output is shown below.  There seems to be a
problem with the search_path.
I have try several experiements with no success.

Does any have an ideas on how to get around this issue?


(from Postgres 8.1.3 on linux.)

--
-- PostgreSQL database dump
--

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

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

CREATE SCHEMA capsa;


ALTER SCHEMA capsa OWNER TO postgres;

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

CREATE SCHEMA capsa_sys;


ALTER SCHEMA capsa_sys 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;

--
-- Name: ds_uuid_in(cstring); Type: FUNCTION; Schema: public; Owner:
postgres
--

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


ALTER FUNCTION public.ds_uuid_in(cstring) OWNER TO postgres;

--
-- Name: ds_uuid_out(ds_uuid); Type: FUNCTION; Schema: public; Owner:
postgres
--

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


ALTER FUNCTION public.ds_uuid_out(ds_uuid) OWNER TO postgres;

--
-- Name: ds_uuid_recv(internal); Type: FUNCTION; Schema: public; Owner:
postgres
--

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


ALTER FUNCTION public.ds_uuid_recv(internal) OWNER TO postgres;

--
-- Name: ds_uuid_send(ds_uuid); Type: FUNCTION; Schema: public; Owner:
postgres
--

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


ALTER FUNCTION public.ds_uuid_send(ds_uuid) OWNER TO postgres;

--
-- Name: ds_uuid; Type: TYPE; Schema: public; Owner: postgres
--

CREATE TYPE ds_uuid (
    INTERNALLENGTH = 16,
    INPUT = ds_uuid_in,
    OUTPUT = ds_uuid_out,
    RECEIVE = ds_uuid_recv,
    SEND = ds_uuid_send,
    ALIGNMENT = int4,
    STORAGE = plain
);


ALTER TYPE public.ds_uuid OWNER TO postgres;

SET search_path = capsa, pg_catalog;

SET search_path = public, pg_catalog;

--
-- Name: ds_uuid_cmp(ds_uuid, ds_uuid); Type: FUNCTION; Schema: public;
Owner: postgres
--

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


ALTER FUNCTION public.ds_uuid_cmp(ds_uuid, ds_uuid) OWNER TO postgres;

--
-- Name: ds_uuid_eq(ds_uuid, ds_uuid); Type: FUNCTION; Schema: public;
Owner: postgres
--

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


ALTER FUNCTION public.ds_uuid_eq(ds_uuid, ds_uuid) OWNER TO postgres;

--
-- Name: ds_uuid_ge(ds_uuid, ds_uuid); Type: FUNCTION; Schema: public;
Owner: postgres
--

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


ALTER FUNCTION public.ds_uuid_ge(ds_uuid, ds_uuid) OWNER TO postgres;

--
-- Name: ds_uuid_gt(ds_uuid, ds_uuid); Type: FUNCTION; Schema: public;
Owner: postgres
--

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


ALTER FUNCTION public.ds_uuid_gt(ds_uuid, ds_uuid) OWNER TO postgres;

--
-- Name: ds_uuid_le(ds_uuid, ds_uuid); Type: FUNCTION; Schema: public;
Owner: postgres
--

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


ALTER FUNCTION public.ds_uuid_le(ds_uuid, ds_uuid) OWNER TO postgres;

--
-- Name: ds_uuid_lt(ds_uuid, ds_uuid); Type: FUNCTION; Schema: public;
Owner: postgres
--

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


ALTER FUNCTION public.ds_uuid_lt(ds_uuid, ds_uuid) OWNER TO postgres;

--
-- Name: ds_uuid_ne(ds_uuid, ds_uuid); Type: FUNCTION; Schema: public;
Owner: postgres
--

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


ALTER FUNCTION public.ds_uuid_ne(ds_uuid, ds_uuid) OWNER TO postgres;

--
-- Name: ds_uuid_new(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION ds_uuid_new() RETURNS ds_uuid
    AS 'pgextensions.so', 'ds_uuid_new'
    LANGUAGE c;


ALTER FUNCTION public.ds_uuid_new() OWNER TO postgres;

--
-- Name: ds_uuid_null(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION ds_uuid_null() RETURNS ds_uuid
    AS 'pgextensions.so', 'ds_uuid_null'
    LANGUAGE c IMMUTABLE;


ALTER FUNCTION public.ds_uuid_null() OWNER TO postgres;

--
-- Name: ds_uuid_textin(text); Type: FUNCTION; Schema: public; Owner:
postgres
--

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


ALTER FUNCTION public.ds_uuid_textin(text) OWNER TO postgres;

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

CREATE OPERATOR < (
    PROCEDURE = ds_uuid_lt,
    LEFTARG = ds_uuid,
    RIGHTARG = ds_uuid,
    COMMUTATOR = >,
    NEGATOR = >=,
    RESTRICT = scalarltsel,
    JOIN = scalarltjoinsel
);


ALTER OPERATOR public.< (ds_uuid, ds_uuid) OWNER TO postgres;


CREATE OPERATOR <= (
    PROCEDURE = ds_uuid_le,
    LEFTARG = ds_uuid,
    RIGHTARG = ds_uuid,
    COMMUTATOR = >=,
    NEGATOR = >,
    RESTRICT = scalarltsel,
    JOIN = scalarltjoinsel
);


ALTER OPERATOR public.<= (ds_uuid, ds_uuid) OWNER TO postgres;

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

CREATE OPERATOR <> (
    PROCEDURE = ds_uuid_ne,
    LEFTARG = ds_uuid,
    RIGHTARG = ds_uuid,
    COMMUTATOR = <>,
    NEGATOR = =,
    RESTRICT = neqsel,
    JOIN = neqjoinsel
);


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


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

CREATE OPERATOR = (
    PROCEDURE = ds_uuid_eq,
    LEFTARG = ds_uuid,
    RIGHTARG = ds_uuid,
    COMMUTATOR = =,
    NEGATOR = <>,
    RESTRICT = eqsel,
    JOIN = eqjoinsel
);


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

--

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

CREATE OPERATOR > (
    PROCEDURE = ds_uuid_gt,
    LEFTARG = ds_uuid,
    RIGHTARG = ds_uuid,
    COMMUTATOR = <,
    NEGATOR = <=,
    RESTRICT = scalargtsel,
    JOIN = scalargtjoinsel
);


ALTER OPERATOR public.> (ds_uuid, ds_uuid) OWNER TO postgres;

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


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

CREATE OPERATOR >= (
    PROCEDURE = ds_uuid_ge,
    LEFTARG = ds_uuid,
    RIGHTARG = ds_uuid,
    COMMUTATOR = <=,
    NEGATOR = <,
    RESTRICT = scalargtsel,
    JOIN = scalargtjoinsel
);


ALTER OPERATOR public.>= (ds_uuid, ds_uuid) OWNER TO postgres;

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

--

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

CREATE OPERATOR CLASS ds_uuid_ops
    DEFAULT FOR TYPE ds_uuid USING btree AS
    OPERATOR 1 <(ds_uuid,ds_uuid) ,
    OPERATOR 2 <=(ds_uuid,ds_uuid) ,
    OPERATOR 3 =(ds_uuid,ds_uuid) ,
    OPERATOR 4 >=(ds_uuid,ds_uuid) ,
    OPERATOR 5 >(ds_uuid,ds_uuid) ,
    FUNCTION 1 ds_uuid_cmp(ds_uuid,ds_uuid);


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

SET search_path = capsa_sys, pg_catalog;

CREATE TABLE master (
    objectid public.ds_uuid NOT NULL
);

--
-- Name: master_pkey; Type: CONSTRAINT; Schema: capsa_sys; Owner:
postgres; Tablespace:
--

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

ALTER TABLE capsa_sys.master OWNER TO postgres;

SET search_path = capsa, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: omeventtask; Type: TABLE; Schema: capsa; Owner: postgres;
Tablespace:
--

CREATE TABLE omeventtask (
    accepttaskevent public.ds_uuid NOT NULL
);


SET search_path = capsa, pg_catalog;

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

"Harry Hehl" <Harry.Hehl@diskstream.com> writes:
> The restore fails with:

> psql:x:384: ERROR:  foreign key constraint "accepttaskevent" cannot be
> implemented
> DETAIL:  Key columns "accepttaskevent" and "objectid" are of
> incompatible types: public.ds_uuid and public.ds_uuid.

This seems to be a variant of the problem discussed here:
http://archives.postgresql.org/pgsql-hackers/2006-02/msg00284.php

I thought we had fixed it in 8.1.3, but apparently there's another
problem behind that one :-(.  Your best short-term answer may be to
create the uuid type and its operators in the pg_catalog schema.

            regards, tom lane

I wrote:
> "Harry Hehl" <Harry.Hehl@diskstream.com> writes:
>> The restore fails with:

>> psql:x:384: ERROR:  foreign key constraint "accepttaskevent" cannot be
>> implemented
>> DETAIL:  Key columns "accepttaskevent" and "objectid" are of
>> incompatible types: public.ds_uuid and public.ds_uuid.

> This seems to be a variant of the problem discussed here:
> http://archives.postgresql.org/pgsql-hackers/2006-02/msg00284.php

Actually, it's exactly the problem discussed in this thread:
http://archives.postgresql.org/pgsql-hackers/2006-02/msg00960.php

We had more or less arrived at a consensus about what to do about it,
but nothing ever actually got done, and I fear it's too late now for
8.2 :-(

            regards, tom lane