"Rob Long" <rlong@micropat.com> writes:
>>> Trying to dump and restore a production database with no success.
>>>
>>> pg_restore: [archiver (db)] could not execute query: ERROR: data type
>>> integer has no default operator class for access method "gist"
>>
>> Hm, this is probably a matter of incorrect object restoration order ...
>> could we see the output of "pg_restore -l" for this dump file?
After playing around with this I realize that it's a bug associated with
schemas --- if you have an operator class that's not in pg_catalog, the
opclass name has to be explicitly qualified in pg_dump's output, or it
won't restore correctly. I'm surprised no one reported this before,
since it affects all the contrib operator classes.
Attached is a patch against 7.3.4, in case it helps.
regards, tom lane
Index: src/backend/utils/adt/ruleutils.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/utils/adt/ruleutils.c,v
retrieving revision 1.124.2.1
diff -c -r1.124.2.1 ruleutils.c
*** src/backend/utils/adt/ruleutils.c 8 Jan 2003 22:54:36 -0000 1.124.2.1
--- src/backend/utils/adt/ruleutils.c 2 Oct 2003 22:21:03 -0000
***************
*** 2951,2956 ****
--- 2951,2957 ----
Form_pg_opclass opcrec;
char *opcname;
char *nspname;
+ bool isvisible;
/* Domains use their base type's default opclass */
if (OidIsValid(actual_datatype))
***************
*** 2962,2972 ****
if (!HeapTupleIsValid(ht_opc))
elog(ERROR, "cache lookup failed for opclass %u", opclass);
opcrec = (Form_pg_opclass) GETSTRUCT(ht_opc);
! if (actual_datatype != opcrec->opcintype || !opcrec->opcdefault)
{
/* Okay, we need the opclass name. Do we need to qualify it? */
opcname = NameStr(opcrec->opcname);
! if (OpclassIsVisible(opclass))
appendStringInfo(buf, " %s", quote_identifier(opcname));
else
{
--- 2963,2978 ----
if (!HeapTupleIsValid(ht_opc))
elog(ERROR, "cache lookup failed for opclass %u", opclass);
opcrec = (Form_pg_opclass) GETSTRUCT(ht_opc);
!
! /* Must force use of opclass name if not in search path */
! isvisible = OpclassIsVisible(opclass);
!
! if (actual_datatype != opcrec->opcintype || !opcrec->opcdefault ||
! !isvisible)
{
/* Okay, we need the opclass name. Do we need to qualify it? */
opcname = NameStr(opcrec->opcname);
! if (isvisible)
appendStringInfo(buf, " %s", quote_identifier(opcname));
else
{