I mentioned in
http://archives.postgresql.org/message-id/21837.1248215656@sss.pgh.pa.us
that pg_dump has a bug in its handling of large objects, which is
directly related to the fact that it's still using the deprecated
function PQescapeBytea. The reason PQescapeBytea is deprecated is
that it has to guess at the setting of standard_conforming_strings,
and it is not guessing right in this one case.
I speculated about fixing that by inventing a "PQescapeByteaHex"
function, but that was pure brain fade :-(. The hex format for
bytea still requires one backslash in the value, so you still have
to know standard_conforming_strings to get it right.
AFAICS the only way to make this work correctly and still have libpq
do the work is to create a libpq function that takes an explicit
parameter for the value of standard_conforming_strings to assume.
(We can't pass a PQconn because we don't have one.) That seems a tad
ugly, so I'm thinking we might just as well have pg_dump solve this
problem for itself.
Another issue related to this code is that ideally we'd like it to
use hex format for speed reasons. The problem there is that when
writing text output in pg_dump or pg_restore, we don't know for
sure what server version the output will be loaded into. If we
use hex format then it will be impossible to load the output into
a pre-8.5 server. This wouldn't be the first time we've broken
backwards compatibility of dump output, of course, but it's something
that would be entirely impractical to fix by manual editing of the
dump output. So it seems a bit worse than our average breakage.
If we aren't worried about backwards compatibility then the solution
is easy: have pg_dump emit LO data in the formE'\\x ... hex data here ...'
which is cheap to generate and will work regardless of GUC settings.
If we want it to generate backwards-compatible output then we'll
essentially end up duplicating libpq's PQescapeBytea code in pg_dump.
Thoughts, better ideas?
regards, tom lane