pg_dump and LOs (another proposal)

Having now flirted with recreating BLOBs (and even DBs) with matching OIDs,
I find myself thinking it may be 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:

To Dump
-------

Dump all LOs by looking in pg_class for relkind='l'. 

Don't bother cross-referencing with actual table entries, since we are
trying to do a backup rather than a consistency check. 

The dump will consist of the LO and it's original OID.


To Load
-------

Create a temporary table, lo_xref, with appropriate indexes

Reload the LOs, storing old & new oid in lo_xref.

Now, disable triggers and sequentially search through all tables that have
one or more oid columns: for each oid column, see if the column value is in
lo_xref, if it is, update it with the new value.

For large databases, this system will rely heavily on lo_xref, so my main
worries are:

1. How are temp tables stored? (eg. if in memory this is a problem -
Dec/Rdb stores temp tables in memory).

2. Are there any limitation on indexes of temp tables (I seem to be able to
create them at least - Dec/Rdb won't even let you do that).


----------------------------------------------------------------
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   |/