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

On 01/04/2012 01:13 PM, Tom Lane wrote:
> In http://archives.postgresql.org/pgsql-admin/2012-01/msg00008.php
> it's pointed out that recent versions of pg_restore fall over on
> archives made with -Fc --inserts (or --column-inserts), but only when
> restoring direct to database; if you ask for text output it's perfectly
> fine. Investigation shows that the problem is that individual INSERT
> commands are being broken apart at arbitrary buffer boundaries --- you
> don't see any problem in text output, but when the bufferloads are
> submitted as separate PQexec calls, of course bad things happen.
>
> I believe this worked okay up until my patch here:
> http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=6545a901a
> which removed the mini SQL lexer in pg_backup_db.c. I had supposed that
> that had no useful function except to separate COPY data from
> not-COPY-data, but in reality it had another function of ensuring that
> INSERT commands split across zlib bufferload boundaries would get
> reassembled before they are submitted to PQexec.
>
> 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?

cheers

andrew