[BUGS] Bug #533: BLOB (lo type) objects could not be restored

[pgsql-bugs]

Stefan Hadjistoytchev ([EMAIL PROTECTED]) 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


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [BUGS] Bug #532: backend crashes due to segmentation fault

[Tom Lane]

[EMAIL PROTECTED] writes:
> i have PostgreSQL v 7.0.2 , maybe you fixed it in later versions.

Indeed.

regards, tom lane

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [BUGS] Bug #533: BLOB (lo type) objects could not be restored

[Hiroshi Inoue]

[EMAIL PROTECTED] wrote:
> 
> Stefan Hadjistoytchev ([EMAIL PROTECTED]) 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

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly