pg_dump output containing CREATE TYPE does not restore with psql

From: "Harry Hehl" <Harry(dot)Hehl(at)diskstream(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: pg_dump output containing CREATE TYPE does not restore with psql
Date: 2006-09-20 14:17:14
Message-ID: 6AD4F3A63B017C4FB074E2C895AD185482E85F@EXCHSRV.waterloonetworking.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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;

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tomasz Ostrowski 2006-09-20 14:20:46 Re: Strange database corruption with PostgreSQL 7.4.x o
Previous Message Matthias.Pitzl 2006-09-20 14:16:14 Re: Strange database corruption with PostgreSQL 7.4.x o