pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
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)
Date: Wed, 08 Feb 2006 21:04:46 -0500
Message-ID: <22538.1139450686@sss.pgh.pa.us> (raw)
In-Reply-To: <20060208195933.6993.qmail@web32515.mail.mud.yahoo.com>
References: <20060208195933.6993.qmail@web32515.mail.mud.yahoo.com>

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



view thread (4+ messages)  latest in thread

Message-ID: <22538.1139450686@sss.pgh.pa.us>
Permalink:  ../22538.1139450686@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/22538.1139450686@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: tgl@sss.pgh.pa.us, pgsql-hackers@postgreSQL.org, cgg007@yahoo.com
  Subject: Re: Schema search for default operator classes (was: [ADMIN] Cross schema Primary Key Indexes problem with datatype in the public schema)
  In-Reply-To: <22538.1139450686@sss.pgh.pa.us>
  References: <20060208195933.6993.qmail@web32515.mail.mud.yahoo.com> <22538.1139450686@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox