Re: pg_dump insert transactions

Tom Lane wrote:
> Kris Jurka <books(at)ejurka(dot)com> writes:
> > This patch makes pg_dump wrap insert dumps (-d, -D) in a transaction to
> > speed the restore process.
>
> What's the point of this, compared to Simon's recent patch adding an
> option to wrap the whole output in one big transaction? An intermediate
> level doesn't seem very interesting ... and lord knows pg_dump has an
> unreasonable number of options already.
>
> > It also adds an option to disable these
> > transactions for doing things like copying only newly inserted values from
> > one database to another (assuming old ones will fail on an unique
> > constraint).
>
> I would think that would be the main use-case for still using -d/-D at
> all (instead of COPY), so this seems of dubious usefulness, and
> certainly not what should be the default.

I have added a patch to document that pg_dump -d/-D throw an error on
invalid data, but allow other INSERTs to continue.

Also, we do have this TODO item:

o %Have pg_dump use multi-statement transactions for INSERT dumps

It always bothered me that INSERT did not have the same behavior as
non-INSERT, but if we document the difference, and people find it
useful, we should just keep it. TODO item removed.

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +