Thread: BUG #1089: pg_restore fails when restoring lo.sql functions from contrib

The following bug has been logged online:

Bug reference:      1089
Logged by:          Joseph Tate

Email address:      jtate@mi-corporation.com

PostgreSQL version: 7.4

Operating system:   Win32/Cygwin

Description:        pg_restore fails when restoring lo.sql functions from
contrib

Details:

I've been having no end to problems with pg_dump/pg_restore.  This stuff
should just work!

Execute the following as a user with createdb privs.
$ createdb test-lo
$ psql -f $CONTRIB/lo.sql -d test-lo
$ pg_dump --format=c --file=/tmp/test-lo.bak test-lo
$ dropdb test-lo
$ pg_restore -vCd template1 /tmp/test-lo.bak

This returns
pg_restore: connecting to database for restore
pg_restore: creating DATABASE test-lo
pg_restore: connecting to new database "test-lo" as user "postgres"
pg_restore: connecting to database "test-lo" as user "postgres"
pg_restore: creating ACL public
pg_restore: creating FUNCTION lo_in(cstring)
pg_restore: NOTICE:  type "lo" is not yet defined
DETAIL:  Creating a shell type definition.
pg_restore: creating FUNCTION lo_out(lo)
pg_restore: NOTICE:  argument type lo is only a shell
pg_restore: creating TYPE lo
pg_restore: creating FUNCTION lo_oid(lo)
pg_restore: creating FUNCTION oid(lo)
pg_restore: creating CAST CAST (public.lo AS oid)
pg_restore: creating FUNCTION lo(oid)
pg_restore: creating CAST CAST (oid AS public.lo)
pg_restore: [archiver (db)] could not execute query: ERROR:  function
lo(oid) does not exist
pg_restore: *** aborted because of error

To me it looks like the function is created two lines above the failure.

This works fine on Linux using 7.3.4.  It also worked on 7.2.x on Cygwin.

PostgreSQL Bugs List wrote:
> This works fine on Linux using 7.3.4.  It also worked on 7.2.x on Cygwin.

FWIW, I can reproduce this problem with CVS HEAD.

-Neil

"PostgreSQL Bugs List" <pgsql-bugs@postgresql.org> writes:
> Description:        pg_restore fails when restoring lo.sql functions from
> contrib

Hmm.  It looks like the code for dumping CREATE CAST fails if the cast
function is from a schema other than pg_catalog.  I'm amazed we did not
notice this when testing nearby fixes.  Anyway, here is the patch
against 7.4.*.

            regards, tom lane

Index: pg_dump.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.355.2.3
diff -c -r1.355.2.3 pg_dump.c
*** pg_dump.c    24 Feb 2004 03:35:45 -0000    1.355.2.3
--- pg_dump.c    2 Mar 2004 21:09:50 -0000
***************
*** 4083,4090 ****
          if (strcmp(castfunc, "0") == 0)
              appendPQExpBuffer(defqry, "WITHOUT FUNCTION");
          else
!             appendPQExpBuffer(defqry, "WITH FUNCTION %s",
!                           format_function_signature(&finfo[fidx], true));

          if (strcmp(castcontext, "a") == 0)
              appendPQExpBuffer(defqry, " AS ASSIGNMENT");
--- 4083,4098 ----
          if (strcmp(castfunc, "0") == 0)
              appendPQExpBuffer(defqry, "WITHOUT FUNCTION");
          else
!         {
!             /*
!              * Always qualify the function name, in case it is not in
!              * pg_catalog schema (format_function_signature won't qualify it).
!              */
!             appendPQExpBuffer(defqry, "WITH FUNCTION %s.",
!                               fmtId(finfo[fidx].pronamespace->nspname));
!             appendPQExpBuffer(defqry, "%s",
!                               format_function_signature(&finfo[fidx], true));
!         }

          if (strcmp(castcontext, "a") == 0)
              appendPQExpBuffer(defqry, " AS ASSIGNMENT");