Thread: Schema + User-Defined Data Type Indexing problems...
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/
Chris Gamache <cgg007@yahoo.com> writes:
> 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.
IIRC, the opclass has to be in a schema that is in your schema search
path to be found by CREATE INDEX by default. If it isn't, you could
specify it explicitly:
CREATE INDEX mt_uuid_idx ON my_schema.my_table USING btree (my_uuid USING my_schema.uuidopclass);
It's possible that we could think of a more convenient behavior for
default opclasses, but I don't want to do something that would foreclose
having similarly-named datatypes in different schemas. You have any
suggestions?
regards, tom lane
--- Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Chris Gamache <cgg007@yahoo.com> writes:
> > 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.
>
> IIRC, the opclass has to be in a schema that is in your schema search
> path to be found by CREATE INDEX by default. If it isn't, you could
> specify it explicitly:
>
> CREATE INDEX mt_uuid_idx
> ON my_schema.my_table USING btree (my_uuid USING my_schema.uuidopclass);
>
> It's possible that we could think of a more convenient behavior for
> default opclasses, but I don't want to do something that would foreclose
> having similarly-named datatypes in different schemas. You have any
> suggestions?
That /is/ important to be able to have similarly named datatypes in different
schemas. I'll give the explicit opclass a go. Indeed, if I place the schema in
my search path the index creation and index scans seem to work perfectly. I had
wanted to have to specify the schema whenever I referenced objects in it
instead of putting it in my search path. I had no concept of exactly how truly
separated schemas are. The only idea that I can think of (and, again, I may be
underestimating the level of separation that needs to exist between schema) is
that object creation could implicitly looks to the current schema for a usable
index/opclass/whatever first before checking the search path. A SELECT could
look first to the schema of the table before checking the search path for a
usable index. Is it even possible to create an index that lives in a different
schema from the table it is indexing?
CG
__________________________________
Do you Yahoo!?
Friends. Fun. Try the all-new Yahoo! Messenger.
http://messenger.yahoo.com/
Chris Gamache <cgg007@yahoo.com> writes:
> Is it even possible to create an index that lives in a different
> schema from the table it is indexing?
It is not --- the index always lives in the same schema as its table.
However, I think that the real issue here is "where is the datatype?".
I'm assuming that you created both the datatype uuid and the opclass
for it in my_schema. So, when working in another schema (with my_schema
not in the search path at all) you'd have had to say
create table foo (my_uuid my_schema.uuid);
and if you then try to make an index you'll have to say
create index fooi on foo (my_uuid my_schema.uuid_ops);
because no default opclass for uuid will be found in the search path.
In practice I'm not sure that this is really a situation that we need to
fret about, because using a datatype that isn't in your search path has
got notational problems that are orders of magnitude worse than this
one. The functions and operators that do something useful with the
datatype would also have to be schema-qualified every time you use them.
This is perhaps tolerable for functions but it's quite unpleasant for
operators :-( You can't writeselect * from foo where my_uuid = 'xxx';
insteadselect * from foo where my_uuid operator(my_schema.=) 'xxx';
Yech. I think you'll end up putting uuid's schema in your search path
before long anyway.
regards, tom lane
--- Tom Lane <tgl@sss.pgh.pa.us> wrote:
> In practice I'm not sure that this is really a situation that we need to
> fret about, because using a datatype that isn't in your search path has
> got notational problems that are orders of magnitude worse than this
> one. The functions and operators that do something useful with the
> datatype would also have to be schema-qualified every time you use them.
> This is perhaps tolerable for functions but it's quite unpleasant for
> operators :-( You can't write
> select * from foo where my_uuid = 'xxx';
> instead
> select * from foo where my_uuid operator(my_schema.=) 'xxx';
> Yech. I think you'll end up putting uuid's schema in your search path
> before long anyway.
Right you are. I guess the moral of the story is that when using custom
datatypes, search_path is a required setting. I guess that is why the "public"
schema should be just that, completely accessable by any user with rights to
the DB. So, is the best-practice for the my_schema tables to reference the
user-defined datatype in the "public" schema?
CREATE TABLE my_schema.foo (uuid public.uniqueidentifier);
__________________________________
Do you Yahoo!?
Friends. Fun. Try the all-new Yahoo! Messenger.
http://messenger.yahoo.com/
Chris Gamache <cgg007@yahoo.com> writes:
> So, is the best-practice for the my_schema tables to reference the
> user-defined datatype in the "public" schema?
Not necessarily, but if you put it somewhere else you'll want to add
the somewhere else to your default search path (probably via ALTER
DATABASE).
regards, tom lane