pg_dump insert transactions

Kris Jurka <books@ejurka.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.

regards, tom lane

Tom Lane wrote:

Kris Jurka <books@ejurka.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. +