Re: pg_restore fails - postgres 7.3.4 - Mailing list pgsql-admin

Tom,

Will this be fixed in 7.4?

Thanks for the support.

Rob

----- Original Message -----
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Thursday, October 2, 2003 6:28 pm
Subject: Re: [ADMIN] pg_restore fails - postgres 7.3.4

> "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
          {

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend