Thread: Bug #533: BLOB (lo type) objects could not be restored

Stefan Hadjistoytchev (sth@hq.bsbg.net) reports a bug with a severity of 1
The lower the number the more severe it is.

Short Description
BLOB (lo type) objects could not be restored

Long Description
Hi :)

Problem appeared in POSTGRESQL 7.2b3 CVS distribution

After creating a table containing BLOB (lo type) column and filling it in an error occured restoring this table using
"pg_restore":

ERROR:  Unable to identify an operator '=' for types 'oid' and 'lo'
    You will have to retype this query using an explicit cast

Using Postgres 7.1.3 - there is no such error :(, but I need 7.2
  because there are other fixes in it and I need them.

Please, see example:


Sample Code
-- user root
-- in postgres 7.2.b3 directory

./configure
./gmake
./gmake install

chmod -R 777 /usr/local/pgsql

-- user postgres

cd /usr/local/pgsql/bin
./initdb -D /usr/local/pgsql/data

-- ACTION: change access permissions in /usr/local/pgsql/data/pg_hba.conf to allow access

./postmaster -D  /usr/local/pgsql/data -i &
./createdb test
./psql -f test1.sql test

>-- test1.sql contains:
>
>CREATE TYPE lo (
>    internallength=4,  externallength=10,
>    input=int4in, output=int4out,
>    default='',  passedbyvalue
>);
>
>CREATE TABLE "tb_snimki" (
>  "egn" varchar(10) NOT NULL,
>  "img" lo
>--  CONSTRAINT "snimki_pkey" PRIMARY KEY ("egn")
>);

-- ACTION: After this I inserted 1 small BLOB object (10K) in "tb_snimki"
--    from another PC with "egn" = "1234"

./pg_dump -b -Fc test > dump1.bin
./dropdb test
./createdb test

./pg_restore -v -Fc -d test dump1.bin

-- RESULT:
pg_restore: connecting to database for restore
pg_restore: creating TYPE lo
pg_restore: creating TABLE tb_snimki
pg_restore: restoring data for table tb_snimki
pg_restore: restoring data for table BLOBS
pg_restore: connecting to database test as user postgres
pg_restore: creating table for large object cross-references
pg_restore: restored 1 large objects
pg_restore: fixing up large object cross-reference for tb_snimki
pg_restore: fixing large object cross-references for tb_snimki.img
pg_restore: [archiver (db)] error while updating column "img" of table "tb_snimki":
ERROR:  Unable to identify an operator '=' for types 'oid' and 'lo'
    You will have to retype this query using an explicit cast
pg_restore: *** aborted because of error


No file was uploaded with this report

pgsql-bugs@postgresql.org wrote:
> 
> Stefan Hadjistoytchev (sth@hq.bsbg.net) reports a bug with a severity of 1
> The lower the number the more severe it is.
> 
> Short Description
> BLOB (lo type) objects could not be restored
> 
> Long Description
> Hi :)
> 
> Problem appeared in POSTGRESQL 7.2b3 CVS distribution
> 
> After creating a table containing BLOB (lo type) column and filling it in an error occured restoring this table using
"pg_restore":
> 
> ERROR:  Unable to identify an operator '=' for types 'oid' and 'lo'
>         You will have to retype this query using an explicit cast

pg_restore in 7.2 could handle the type lo defined in
contrib/lo but couldn't handle the type lo you created by CREATE TYPE lo (   internallength=4,  externallength=10,
input=int4in,output=int4out,   default='',  passedbyvalue );
 
The type is incomplete and hard to handle in pg_restore.

> Using Postgres 7.1.3 - there is no such error :(,

pg_restore in 7.1.x couldn't restore large objects
of type lo properly though it doesn't cause any error.

One way I can think of is to create the type lo in
contrib/lo in advance of pg_restore and ignore 
the 'create type lo ..' commands in pg_restore but
it seems pretty unnatural for pg_restore.

Comments ?

I've worried about the use of type lo which has
been used only(?) in ODBC. It doesn't seem wrong
because PostgreSQL hasn't provided the proper
BLOB type. However I'm not sure now how to handle
large objects in ODBC in future.

Comments ?

regards,
Hiroshi Inoue