Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group