Thread: AW: Re: pg_dump and LOs (another proposal)

 
> At 11:09 5/07/00 -0400, Tom Lane wrote:
> >Philip Warner <pjw@rhyme.com.au> writes:
> >> Having now flirted with recreating BLOBs (and even DBs) 
> with matching OIDs,
> >> I find myself thinking it's a waste of effort for the 
> moment. A modified
> >> version of the system used by Pavel Janik in pg_dumplo may 
> be substantially
> >> more reliable than my previous proposal:
> >
> >I like this a lot better than trying to restore the original 
> OIDs.  For
> >one thing, the restore-original-OIDs idea cannot be made to 
> work if what
> >we want to do is load additional tables into an existing database.
> >
> 
> The thing that bugs me about this if for 30,000 rows, I do 
> 30,000 updates
> after the restore. It seems *really* inefficient, not to mention slow.
> 
> I'll also have to modify pg_restore to talk to the database 
> directly (for
> lo import). As a result I will probably send the entire 
> script directly
> from withing pg_restore. Do you know if comment parsing 
> ('--') is done in
> the backend, or psql?

Strictly speaking you are absolutely safe if you only do one update 
with the max oid from the 30,000 rows before you start creating the lo's.
Don't know if you know that beforehand though.

If you only know afterwards then you have to guarantee that no other 
connection to this db (actually postmaster if you need the oid's site
unique)
does anything while you insert the lo's and then update to max oid.

Andreas

At 09:52 6/07/00 +0200, Zeugswetter Andreas SB wrote:
>> 
>> I'll also have to modify pg_restore to talk to the database 
>> directly (for
>> lo import). As a result I will probably send the entire 
>> script directly
>> from withing pg_restore. Do you know if comment parsing 
>> ('--') is done in
>> the backend, or psql?
>
>Strictly speaking you are absolutely safe if you only do one update 
>with the max oid from the 30,000 rows before you start creating the lo's.
>Don't know if you know that beforehand though.
>
>If you only know afterwards then you have to guarantee that no other 
>connection to this db (actually postmaster if you need the oid's site
>unique)
>does anything while you insert the lo's and then update to max oid.
>

You may be confusing the two proposed techniques, the current flavour of
the minute is to restore the BLOBs using lo_craete to get a new oid; write
an entry in a table indicating what the old & new are, then when the table
data is loaded, update all oid fields that refer to oids in the xref table.
It's pretty nasty, but it has the big advantage of being as vanilla as
possible. It's also pretty close to what pg_dump_lo does.






----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.C.N. 008 659 498)             |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|                                |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/