Schema search for default operator classes (was: [ADMIN] Cross schema Primary Key Indexes problem with datatype in the public schema) - Mailing list pgsql-hackers

http://archives.postgresql.org/pgsql-admin/2006-02/msg00084.php
reports a problem with default btree operator classes that are
not in pg_catalog: you can create a UNIQUE or PRIMARY KEY constraint
that depends on such an opclass, but then when you pg_dump and
try to reload, you get something like

pg_restore: [archiver (db)] could not execute query: ERROR:  data type
public.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.   Command was: ALTER TABLE ONLY table_1   ADD CONSTRAINT table_1_luuid_pkey PRIMARY
KEY(luuid);
 

The problem is that pg_dump sets up a restrictive search path during the
restore, basically just the schema of the object being restored (plus
the implicit reference to pg_catalog).  There are good reasons for that
behavior and I'm disinclined to mess with it --- but meanwhile,
GetDefaultOpClass only looks at operator classes that are in the current
search path.  So if the desired opclass is not in pg_catalog and also
not in the same schema as the table being restored, you lose.

Given that we only allow one default opclass for a datatype regardless
of schema (see DefineOpClass), it's not really necessary for
GetDefaultOpClass to restrict its search.  I can think of some corner
cases involving multiple binary-compatible-datatype matches where the
restriction might give a unique answer when an unrestricted search would
not, but I kinda doubt this would ever arise in practice.

The only other solution I can see is to extend the ADD CONSTRAINT syntax
to allow explicit specification of an opclass for each column.  This
might be a good thing to do in itself, but it looks like a new feature
to me, rather than something we could reasonably apply as a bug fix.
It would certainly be a much larger code change (affecting both pg_dump
and the backend) than changing the behavior of GetDefaultOpClass.  And
it'd not fix the problem for existing dump files, either.

So I'm leaning towards removing the search-path dependency of
GetDefaultOpClass.  Comments?
        regards, tom lane


pgsql-hackers by date:

Previous
From: Kris Jurka
Date:
Subject: Re: Upcoming re-releases
Next
From: Stephen Frost
Date:
Subject: Re: Upcoming re-releases