Re: pg_restore direct to database is broken for --insert dumps

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> On 01/04/2012 01:13 PM, Tom Lane wrote:
>> Not entirely sure what to do about this. We could consider reverting
>> the aforesaid patch and trying to find another way of fixing that code's
>> failure to cope with standard-conforming strings, but I'm not sure that
>> there's a good way to know whether standard_conforming_strings is active
>> here, and anyway that code was ugly as sin and I'd rather not resurrect
>> it. But on the other hand, there are no clear line boundaries in the
>> compressed data, and we can't introduce any without (a) worsening
>> compression and (b) breaking compatibility with existing dump files.
>>
>> Anybody have any bright ideas? I'm fresh out at the moment.

> I pondered this while out on my daily constitutional. The first thing
> that occurred to me is that it would possibly have been better if we'd
> made pg_dump not use a quoting mechanism whose behaviour is dependent on
> a setting (e.g. E'' or dollar quoting). But I guess that's water under
> the bridge.

> Could we detect an appropriate line ending in ahwrite() after it's been
> decompressed and buffer partial lines accordingly?

Not easily: there could be newlines embedded in data strings or SQL
identifiers. I'm not seeing any way around this except to restore the
minimal lexing capability. One thing we could probably do is to
restrict it to be used only when reading table data, and continue to
assume that object creation commands can be emitted as-is. That would
at least get us out of needing to parse dollar-quoted literals, which
aren't used in the INSERT commands. But we'd have to deal with
standard-conforming strings some way. The idea I had about that, since
we have an open database connection at hand, is to check the connected
backend's standard_conforming_strings state via PQparameterStatus. If
it doesn't have the right setting then things are going to fail anyway.

regards, tom lane