pg_restore, search_path and operator class - Mailing list pgsql-general

Hi,

I've searched the archives for this issue but I could not find an
answer. I apologize if this has been beaten to death already.

Postgresql version:
====================
8.1.2 on Linux

The issue:
===========
I've got a user defined data type that has been defined in the "public"
schema. I use pg_dump to dump a table that has a column of this type:

create myschema.mytable (id public.mytype primary key, name varchar);

pg_dump -U user --schema myschema --table mytable -f mytable.dump mydb

When I try to restore this table with psql

psql -U user -d mydb -f mytable.dump

I get an error

SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
psql:mytable.dump:48: ERROR:  data type public.mytype has no default
operator class for access method "btree"
HINT:  You must specify an operator class for the index or define a
default operator class for the data type.

This error is not correct because "mytype" does have a default operator
for btree:

CREATE OPERATOR CLASS public.mytype_ops_btree DEFAULT FOR TYPE
public.mytype USING btree AS...

I've included the content of the dump file at the bottom of this email.
Note that, at line 11, there is a SET search_path statement, which does
not contain "public". If I change the search_path to include "public"

Set search_path = myschema, public, pg_catalog;

everything works fine. Is there a way to force pg_dump to include
"public"? How should I change my operator classes or data type to make
this work? (Moving the data type to pg_catalog works but we've got a lot
of data out there to migrate.)

Thanks for the help!
Jozsef Szalay

The dump file
==============

--
-- PostgreSQL database dump
--

-- Started on 2008-02-15 21:30:48 UTC

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

SET search_path = myschema, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- TOC entry 2970 (class 1259 OID 69852)
-- Dependencies: 45 422
-- Name: mytable; Type: TABLE; Schema: myschema; Owner: user;
Tablespace:
--

CREATE TABLE mytable (
    id public.mytype NOT NULL,
    name character varying
);


ALTER TABLE myschema.mytable OWNER TO user;

--
-- TOC entry 3300 (class 0 OID 69852)
-- Dependencies: 2970
-- Data for Name: mytable; Type: TABLE DATA; Schema: myschema; Owner:
user
--

COPY mytable (id, name) FROM stdin;
\.


--
-- TOC entry 3299 (class 2606 OID 69858)
-- Dependencies: 2970 2970
-- Name: mytable_pkey; Type: CONSTRAINT; Schema: myschema; Owner: user;
Tablespace:
--

ALTER TABLE ONLY mytable
    ADD CONSTRAINT mytable_pkey PRIMARY KEY (id);


-- Completed on 2008-02-15 21:30:48 UTC

--
-- PostgreSQL database dump complete
--