Re: pg_dump and inserts

Attached is a patch the more clearly handles autocommit in pg_dump. I
had already fixed pg_dump for doing autocommit while dumping, but didn't
handle setting autocommit on restore.

I focused on the initial script file startup, every \\connect,
pg_restore, and pg_dumpall. I think I got them all.

New pg_dump output is:

--
-- PostgreSQL database dump
--

SET autocommit TO 'on';

\connect - postgres

SET autocommit TO 'on';

---------------------------------------------------------------------------

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > I just checked and pg_dump -d _doesn't_ place the INSERT's in a
> > transsaction. Seems it should,
>
> I think this is a bad idea. If one were after speed, one would have
> used the COPY format in the first place. If one uses INSERTs, there
> may be a reason for it --- like, say, wanting each row insertion to
> succeed or fail independently. Put a begin/end around it, and you
> lose that.
>
> > and perhaps add a:
> > SET autocommit TO 'on'
> > as well.
>
> This is probably a good idea, since pg_dump scripts effectively assume
> that anyway.
>
> > Of course, that SET would fail when restoring to prior
> > releases,
>
> Irrelevant; current pg_dump scripts already issue a SET that pre-7.3
> servers won't recognize (search_path). A failed SET is harmless anyway,
> or should be. (What we really need is for someone to fix pg_restore to
> not abort on SQL errors...)
>
> regards, tom lane
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073