Re: pg_dump: largeobject behavior issues (possible bug)

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> On 04/24/2015 06:41 PM, Tom Lane wrote:
>> Yeah, this was brought up when we added per-large-object metadata; it was
>> obvious that that patch would cause pg_dump to choke on large numbers of
>> large objects. The (perhaps rather lame) argument was that you wouldn't
>> have that many of them.

>> Given that large objects don't have any individual dependencies,
>> one could envision fixing this by replacing the individual large-object
>> DumpableObjects by a single placeholder to participate in the sort phase,
>> and then when it's time to dump that, scan the large objects using a
>> cursor and create/print/delete the information separately for each one.
>> This would likely involve some rather painful refactoring in pg_dump
>> however.

> I think we need to think about this some more, TBH, I'm not convinced
> that the changes made back in 9.0 were well conceived. Having separate
> TOC entries for each LO seems wrong in principle, although I understand
> why it was done.

Perhaps. One advantage of doing it this way is that you can get
pg_restore to extract a single LO from an archive file; though it's
debatable whether that's worth the potential resource-consumption hazards.
Another issue is that restore options such as --no-owner and
--no-privileges would not work for LOs (at least not without messy hacks)
if we go back to a scheme where all the LO information is just SQL
commands inside a single TOC object.

After further thought I realized that if we simply hack pg_dump to emit
the LOs in a streaming fashion, but keep the archive-file representation
the same as it is now, then we haven't really fixed the problem because
pg_restore is still likely to choke when it tries to read the archive's
TOC. So my proposal above isn't enough either.

Perhaps what we need is some sort of "second-level TOC" which is only ever
processed in a streaming fashion, by both pg_dump and pg_restore. This
would not support dependency resolution or re-ordering, but we don't need
those abilities for LOs.

regards, tom lane