It recently came to my attention that, when dumping a single table and
using the --blobs option, pg_dump will dump all BLOBs (not just those
referenced by the table). Similarly, restoring a single table from a backup
will restore all the BLOBs.
This is (at least) non-intuitive.
Unfortunately, the task of restoring a single table with BLOBs is actually
somewhat complex: the OIDs will change, and the table needs to be updated
(which is fine), but it is definitely not clear how to handle other tables
in the database (since the current implementation allows multiple
references to one blob, which is very non-standard). The simple solution of
only updating the OID fields in the restored table seems as unintuitive as
the current situation, and also likely to produce unexpected results.
Also, scanning through the backup file for BLOBs used in the table to be
restored will be slow, to say the least. It can be done in one pass by
building an indexed temporary table with all OID fields from the target
table, but it is definitely getting messy. Especially since Jan was talking
about a decent BLOB implementation RSN.
My inclination at this stage is to disallow --blobs when dumping or
restoring a single table, but I would be interested in hearing from anybody
with a better solution.
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 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 |/