Обсуждение: Schema search for default operator classes (was: [ADMIN] Cross schema Primary Key Indexes problem with datatype in the public schema)

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

On Wed, Feb 08, 2006 at 09:04:46PM -0500, Tom Lane wrote:
> 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

<snip>

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

I'm for. IMHO, if you give someone has access to the type they should
have access to the supporting machinary. Whoever created the type
probably also created the operator class and intended it to be used.
For a comparison, we don't check the schema on looking up type
input/output functions (well, we don't need to because we have the oid,
but the idea is important).

W.R.T. the other option (per column opclass specification), if we ever
do COLLATE users will be allowed to specify it on a per-column basis
anyway. Then specifying opclasses becomes redundant. I've been
seriously neglecting this patch but hope to get back to it soon...

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Tom Lane wrote:

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

How about doing the constrained search first, and revert to the
unconstrained behavior if it doesn't find the desired opclass?

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Tom Lane wrote:
>> 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.

> How about doing the constrained search first, and revert to the
> unconstrained behavior if it doesn't find the desired opclass?

Seems like rather a lot of work to preserve a behavior that (AFAICS)
isn't even documented anywhere.
        regards, tom lane