On Thu, 2 Oct 2003, Tom Lane wrote:
> Howard Lowndes <lannet@lannet.com.au> writes:
> > On Wed, 1 Oct 2003, Tom Lane wrote:
> >> No, because pg_restore has logic to adjust the references to match the
> >> new BLOB OIDs. If you have a test case where this fails to work, let's
> >> see it ...
>
> > No, I don't have any example, it is an enquiry. What I am reading into
> > the above however is that the loid column in my table should have a
> > CONSTRAINT REFERENCES clause to whereever in the system large objects
> > table. Correct?
>
> No. No doubt if Postgres had had foreign keys when the large-object stuff
> was invented, it would have required such a constraint for LO
> references, but it didn't and it doesn't. The pg_restore code simply
> goes through all "oid" columns (and all "lo" columns if you've installed
> the contrib/lo datatype) and looks for matches to LO OIDs that existed
> in the dumped database. When it finds a match, it replaces that value
> with the new BLOB's OID. Simple, effective, crufty ...
OK, I'm convinced, except for one small, but not insignificant hiccup.
When you dump a database with the BLOBs, even with the -c option, and then
restore that database again with the -c option, you get double the BLOBs.
The original BLOBs are there as are the new copies, and the cross
referenced oids are updated. It looks as if there should be some way of
removing the old BLOB at restore time once the new BLOB is in place. I
don't know the detail of how pg_restore works but it does create a table
solely for the purpose of cross referencing the oids.
This of course means that each dump and subsequent restore doubles up on
the BLOBs and since BLOBs are by nature Large there could be disk space
problems.
--
Howard.
LANNet Computing Associates - Your Linux people <http://www.lannetlinux.com>
------------------------------------------
Flatter government, not fatter government - Get rid of the Australian states.
------------------------------------------
If all economists were laid end to end, they would not reach a conclusion
- George Bernard Shaw
