Re: pg_restore problem

From: "Jules Alberts" <jules(dot)alberts(at)arbodienst-limburg(dot)nl>
To: pgsql-novice(at)postgresql(dot)org
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: pg_restore problem
Date: 2002-08-15 14:40:02
Message-ID: 200208151444.g7FEi5uL001878@artemis.cuci.nl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

On 15 Aug 2002 at 9:22, Tom Lane wrote:
> "Jules Alberts" <jules(dot)alberts(at)arbodienst-limburg(dot)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.

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Gabriel Menini [F.I.S.] 2002-08-15 15:28:40 Accessing thru webmin, don't have admin password
Previous Message Tom Lane 2002-08-15 13:27:02 Re: FATAL 1:Sorry, too many clients