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
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
