Re: pg_restore problem

On 15 Aug 2002 at 9:22, Tom Lane wrote:
> "Jules Alberts" <jules.alberts@arbodienst-limburg.nl> writes:
> > This restores the OIDs of all my tables (as I expected), except the
> > BLOBs, lo_export(241803, '/tmp/foobar') says "ERROR inv_open large
> > object 241803 not found". This is a problem because I save the BLOBs
> > OID as a reference in other tables. Am I doing something wrong or is
> > this a known issue?
>
> pg_restore should fix up OID references to BLOBs ... if they are in
> columns of type OID (or type lo, if you've installed contrib/lo). I
> suspect you stored all your OID references in integer columns?
>
>    regards, tom lane

(thanks for reacting)

Sorry, I wasn't quite clear. The problem isn't that the references get
lost, but that the actual OIDs of the blobs change. Here's an example:

#############################################################
-- bash
createdb test
psql test
-- psql
select lo_import('/usr/share/pixmaps/gimp.png'); -- echoes 243596
-- bash
pg_dump --oids --blobs --format=c --file=test.dump test
dropdb test
createdb test
pg_restore --dbname=test --format=c test.dump
-- psql
select lo_export(243596, '/tmp/gimp.png');
-- ERROR: inv_open: large object 243596 not found
#############################################################

In my database I want to store things like PDF files, images etc. for,
say, a customer called CUST. Also there's a table to link the customers
to any BLOBs related to him called CUST_BLOBS. In this table I create
one row for each BLOB for a customer. A CUST_BLOBS row contains the
customers OID + the BLOBs OID, so I can find all BLOBs of a customer by
selecting blob_oid from CUST_BLOBS where CUST_BLOBS.customer_oid =
customer.oid. This results in 0 or more OIDs of BLOBS. That's the
reason why I need presistent OIDs for BLOBs.

Hope I was a little bit more clear this time, TIA for any tips!

--
Jules Alberts.