Thread: contrib/lo and restoring databases

I'm dumping unrestoreable databases when I use the 'lo' type from
contrib/lo:

lo_test=# select version();
                           version
-------------------------------------------------------------
 PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC 2.96

I'm using contrib/lo because the front-end application is MS Access
(boo hiss) and the ODBC driver requires the 'lo' type in order to use
large objects.  However, I can reproduce the problem without involving
Access in any way.

Here's what happens:

issue_track=# \c lo_test;
You are now connected to database lo_test.
lo_test=# create table foo (id serial, object lo);
NOTICE:  CREATE TABLE will create implicit sequence 'foo_id_seq' for SERIAL column 'foo.id'
CREATE TABLE
lo_test=# \lo_import /home/netops/tmp/DNS-posture-1.0.pdf
lo_import 20578
lo_test=# insert into foo (object) values (20578::oid::lo);
INSERT 20579 1
lo_test=# select * from foo;
 id | object
----+--------
  1 | 20578
(1 row)

lo_test=# select distinct loid from pg_largeobject
lo_test-# ;
 loid
-------
 20578
(1 row)

All well and good so far.  I want to make sure I can dump and restore
the database so I do:

bash-2.05a$ pg_dump -Fc -b lo_test >lo_test.dump
bash-2.05a$ createdb -T template0 lo_test2
CREATE DATABASE
bash-2.05a$ pg_restore -v -d lo_test2 < lo_test.dump
pg_restore: connecting to database for restore
pg_restore: creating FUNCTION lo_in (cstring)
pg_restore: NOTICE:  ProcedureCreate: type lo is not yet defined
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 oid (public.lo)
pg_restore: [archiver (db)] could not execute query: ERROR:  parser: parse error at or near "." at character 132
pg_restore: *** aborted because of error

This looks like a bug in pg_dump or pg_restore to me.  I can supply
the dump file if that would be helpful (it's about 100K).

-Doug

Doug McNaught <doug@mcnaught.org> writes:
> pg_restore: creating CAST oid (public.lo)
> pg_restore: [archiver (db)] could not execute query: ERROR:  parser: parse error at or near "." at character 132
> pg_restore: *** aborted because of error

> This looks like a bug in pg_dump or pg_restore to me.

No, I think the command is perfectly reasonable; the problem is the
backend parser isn't prepared for a qualified typename in CREATE CAST.
This is a bug that's fixed in CVS:

2003-02-05 15:16  tgl

    * src/backend/parser/gram.y (REL7_3_STABLE): Allow qualified type
    names in CREATE CAST, DROP CAST.  Also allow the construction
    'SETOF type[]' which for some reason was previously overlooked
    (you'd have to name the array type directly to make it work).

It'll be in 7.3.3.  In the meantime you could possibly grab the updated
gram.y from the CVS server, or just not restore the CAST from the dump.

            regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

> Doug McNaught <doug@mcnaught.org> writes:
> > pg_restore: creating CAST oid (public.lo)
> > pg_restore: [archiver (db)] could not execute query: ERROR:  parser: parse error at or near "." at character 132
> > pg_restore: *** aborted because of error
>
> > This looks like a bug in pg_dump or pg_restore to me.
>
> No, I think the command is perfectly reasonable; the problem is the
> backend parser isn't prepared for a qualified typename in CREATE CAST.
> This is a bug that's fixed in CVS:

Cool, I'll grab the branch tip and give it a shot.  Have to upgrade
Bison too but that won't kill me...

Thanks as always for your help!

-Doug