X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id E5D0E9DC81F for ; Wed, 8 Feb 2006 22:04:44 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 09631-02 for ; Wed, 8 Feb 2006 22:04:48 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 46F7F9DC813 for ; Wed, 8 Feb 2006 22:04:42 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id k1924kaD022539; Wed, 8 Feb 2006 21:04:46 -0500 (EST) To: pgsql-hackers@postgreSQL.org cc: cgg007@yahoo.com Subject: Schema search for default operator classes (was: [ADMIN] Cross schema Primary Key Indexes problem with datatype in the public schema) In-reply-to: <20060208195933.6993.qmail@web32515.mail.mud.yahoo.com> References: <20060208195933.6993.qmail@web32515.mail.mud.yahoo.com> Comments: In-reply-to CG message dated "Wed, 08 Feb 2006 11:59:33 -0800" Date: Wed, 08 Feb 2006 21:04:46 -0500 Message-ID: <22538.1139450686@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.108 required=5 tests=[AWL=0.108] X-Spam-Score: 0.108 X-Spam-Level: X-Archive-Number: 200602/283 X-Sequence-Number: 79421 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