Thread: savepoints in 8.3.7 or whatever...

 we have an app thats doing massive amounts of inserts, batched in
transactions, multiple concurrent connections (tuned for optimal
throughput, usually around 1 thread per cpu core plus a couple more).
occasionally a transaction gets duplicated, and that causes a constraint
violation which causes the whole transaction to abort unless we wrap
each insert in a savepoint.

my developers are asking me if there are limits as to how many
savepoints can be active, etc.   they have run into various such limits
in oracle.

John R Pierce wrote:
> we have an app thats doing massive amounts of inserts, batched in
> transactions, multiple concurrent connections (tuned for optimal
> throughput, usually around 1 thread per cpu core plus a couple more).
> occasionally a transaction gets duplicated, and that causes a constraint
> violation which causes the whole transaction to abort unless we wrap
> each insert in a savepoint.
>
> my developers are asking me if there are limits as to how many
> savepoints can be active, etc.   they have run into various such limits
> in oracle.

There's a hard limit of 2^32 savepoints in a transaction, but you'll
probably run into limits before that due to memory constraints (I think
each savepoint will use at least 8kB).  Anyway I suggest you do RELEASE
SAVEPOINT after each insert to ensure resources are released as best as
possible.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Wed, Jul 15, 2009 at 12:05 PM, John R Pierce<pierce@hogranch.com> wrote:
> we have an app thats doing massive amounts of inserts, batched in
> transactions, multiple concurrent connections (tuned for optimal throughput,
> usually around 1 thread per cpu core plus a couple more).   occasionally a
> transaction gets duplicated, and that causes a constraint violation which
> causes the whole transaction to abort unless we wrap each insert in a
> savepoint.
>
> my developers are asking me if there are limits as to how many savepoints
> can be active, etc.   they have run into various such limits in oracle.

To add to what Alvaro said, savepoints are't free.  If you savepoint
every single insert, you'll wind up begin much much slower than if you
can insert a few dozen to a few hundred at a time.  So, you might be
better off bunching up a few hundred inserts at a time, and when one
of the batches of 100 fail then fall back to breaking it into pieces
and pushing them in until you find the one that doesn't work and spit
it out into a log.  It's more work on the app end, but by putting a
hundred or so inserts together you get much better performance than
savepointing each one.