Thread: Schema search for default operator classes (was: [ADMIN] Cross schema Primary Key Indexes problem with datatype in the public schema)
Schema search for default operator classes (was: [ADMIN] Cross schema Primary Key Indexes problem with datatype in the public schema)
From
Tom Lane
Date:
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
Re: Schema search for default operator classes (was: [ADMIN] Cross schema Primary Key Indexes problem with datatype in the public schema)
From
Martijn van Oosterhout
Date:
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.
Re: Schema search for default operator classes (was: [ADMIN] Cross schema Primary Key Indexes problem with datatype in the public schema)
From
Alvaro Herrera
Date:
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
Re: Schema search for default operator classes (was: [ADMIN] Cross schema Primary Key Indexes problem with datatype in the public schema)
From
Tom Lane
Date:
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