Schema + User-Defined Data Type Indexing problems...

From: Chris Gamache <cgg007(at)yahoo(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Schema + User-Defined Data Type Indexing problems...
Date: 2004-06-09 16:41:03
Message-ID: 20040609164103.36003.qmail@web13811.mail.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

PostgreSQL 7.4.2 -- All vacuumed and analyzed.

I inserted the uniqueidentifier datatype into a new schema that I'm working on
by changing the search_path to "my_schema" in the contrib SQL. It effectively
created the datatype within the schema, all of its functions, operators, and
operator classes. To move the data from the public schema into the new
"my_schema" I had to create an assignment cast public.uniqueidentifier to
my_schema.uniqueidentifier. I was profiling queries and I couldn't figure out
why PostgreSQL wasn't using indexes. I'm having a heck of a time, and it seems
like in my thrashing about to find a solution to this problem I have ruined the
uniqueidentifier datatype in the schema...

CREATE INDEX mt_uuid_idx
ON my_schema.my_table USING btree (my_uuid);

ERROR: data type my_schema.uniqueidentifier 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.

I can look at the operator classes and see that there is an operator class for
btree for my_schema.uniqueidentifier.

I must be doing something wrong with my schema set-up to have this much trouble
with it. If this is the norm for complexity when using schema, I'm not sure it
is worth the effort to impliment. Other PostgreSQL users are comfortable with
the schema implimentation... I _must_ be doing something wrong. The bottom line
for my problem is that searches that should be using indexes in the schema
aren't. Please help me find out what's going on.

CG



__________________________________
Do you Yahoo!?
Friends. Fun. Try the all-new Yahoo! Messenger.
http://messenger.yahoo.com/

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Jodi Kanter 2004-06-09 17:30:40 search and replace
Previous Message Richard Huxton 2004-06-09 09:01:46 Re: Triggers