Обсуждение: savepoints with the same name

I was thinking thinking about the issue asked here, about an error in
a query causing the whole transaction to abort,
http://stackoverflow.com/questions/2741919/can-i-ask-postgresql-to-ignore-errors-within-a-transaction/2745677
which has already bothered so many postgresql users and has been
discussed before (it's certainly not a bug, I know).

I wonder if the suggestion I ("leonbloy") gave, of adding a SAVEPOINT
after each insert  (when doing interactive work)
is reasonable.

In particular, after reading this message
http://archives.postgresql.org/pgsql-general/2009-07/msg00636.php

   "If you savepoint every single insert, you'll wind up begin much
much slower..."

Does that also apply when I issue a SAVEPOINT with the same name? Does
the new savepoint release the previous and create a new
one, or does it "move" the previous? Or it's just that a new one is
created and the name "shadows" the previous, so that there are still N
active savepoints in the transaction, taking up memory/resources ?


Hernán J. González
http://hjg.com.ar/

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


> I was thinking thinking about the issue asked here, about an error in
> a query causing the whole transaction to abort,
...
> I wonder if the suggestion I ("leonbloy") gave, of adding a SAVEPOINT
> after each insert  (when doing interactive work)
> is reasonable.

Not only reasonable, but already implemented inside of psql. Just type:

\set ON_ERROR_ROLLBACK on

inside your psql session, and it will automatically create a savepoint
before each command, and thus allow your transaction to continue
even if you encounter errors.

> "If you savepoint every single insert, you'll wind up begin much
> much slower..."
>
> Does that also apply when I issue a SAVEPOINT with the same name? Does
> the new savepoint release the previous and create a new
> one, or does it "move" the previous? Or it's just that a new one is
> created and the name "shadows" the previous, so that there are still N
> active savepoints in the transaction, taking up memory/resources ?

Yes, savepoints will slow things down, but probably not as much as you
are fearing. Savepoints will nest (or shadow). The ON_ERROR_ROLLBACK
feature creates the savepoint before the query, then either does a
ROLLBACK TO or a RELEASE depending on the success of the query, so there
is no build up of savepoints.

- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201004301245
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkvbCVIACgkQvJuQZxSWSsi4nwCdH8xwQ3RpVlD65I239hs/eAbW
V3oAniaEv2VWFkrrhqDU9HDlCRMv1ROx
=i0P6
-----END PGP SIGNATURE-----