Thread: PostgreSQL 8.3 on Debian, Ubuntu

I recall a couple of people asking about when 8.3 would be available for
Debian and Ubuntu.  Here's an update now that some useful packages have
come out this week.

Debian has the new source version available for their some distance in the
future Sid release at http://packages.debian.org/sid/postgresql-8.3 A
related component is their common PostgreSQL package; you can follow
recent QA on getting that compatible with 8.3 at
http://packages.qa.debian.org/p/postgresql-common.html

Binary backports for current releases are now trickling out; see
http://www.nabble.com/Postgresql-8.3-to15290017.html for more information.
That will lead you to unofficial packages at
http://rhonda.deb.at/debian/bpo/postgresql-common/ and
http://rhonda.deb.at/debian/bpo/postgresql-8.3/

Unbuntu will be including 8.3 in their upcoming Hardy Heron release:
http://packages.ubuntu.com/hardy/misc/postgresql-8.3

This has been backported to the current 7.10 by the Unofficial Backports
group:
http://backports.trausch.us/2008/02/11/new-package-backports-postgresql-83-and-dependencies/

The about page their tells how to use their repository to install their
version easily.

Now that the source versions for Debian and Ubuntu are out, a sufficiently
industrious person might do their own backport for an earlier version.
Once upon a time I was able to do that for 8.2 following the instructions
at http://www.debian-administration.org/articles/373 , that may be helpful
if you're interested in starting down that trail.

Note that these are all very unofficial at this point.  You should follow
the appropriate level of due dilligance for your site to confirm these are
reliable sources before installing their packages or trusting their
repositories.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

Greg Smith wrote:
> I recall a couple of people asking about when 8.3 would be available for
> Debian and Ubuntu.  Here's an update now that some useful packages have
> come out this week.

Thanks for the summary, Greg.

Colin

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
--

"Jozsef Szalay" <jszalay@storediq.com> writes:
> 8.1.2 on Linux

> 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);
> ...
> psql:mytable.dump:48: ERROR:  data type public.mytype has no default
> operator class for access method "btree"

Yeah, this is a known bug; it was fixed in ... umm ... 8.1.3 actually.

2006-02-10 14:01  tgl

    * src/: backend/catalog/namespace.c, backend/commands/indexcmds.c,
    backend/utils/cache/typcache.c, include/catalog/namespace.h,
    include/commands/defrem.h (REL8_1_STABLE): Change search for
    default operator classes so that it examines all opclasses
    regardless of the current schema search path.  Since CREATE
    OPERATOR CLASS only allows one default opclass per datatype
    regardless of schemas, this should have minimal impact, and it
    fixes problems with failure to find a desired opclass while
    restoring dump files.  Per discussion at
    http://archives.postgresql.org/pgsql-hackers/2006-02/msg00284.php.
    Remove now-redundant-or-unused code in typcache.c and namespace.c,
    and backpatch as far as 8.0.

            regards, tom lane