Re: Does psql use nested transactions?

On Wed, Aug 18, 2004 at 09:23:44AM +1000, Philip Warner wrote:
> At 07:10 AM 18/08/2004, Tom Lane wrote:
> >I have not had an opportunity to benchmark it but I fear a savepoint may
> >cost near as much as a full xact in practice.
>
> Out of curiosity, does this mean that using a savepoint per statement will
> be no worse than using psql in autocommit mode?

I think it would be slightly better. A transaction-commit XLog entry
requires fsync, whereas there is no XLog entry for savepoint release.
As Tom points out, however, savepoint rollback may be more expensive
than main transaction rollback due to retail releasing of some
resources. But in the case of a long transaction and query-long
savepoints, then retail-releasing may have little work to do and be not
that much more expensive than wholesale releasing.

OTOH, pg_clog and pg_subtrans entries are not fsync()'ed, so it's
mostly write() traffic which should be cached at the kernel level.

Of course, we should really measure it to be sure ...

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
La web junta la gente porque no importa que clase de mutante sexual seas,
tienes millones de posibles parejas. Pon "buscar gente que tengan sexo con
ciervos incendiándose", y el computador dirá "especifique el tipo de ciervo"
(Jason Alexander)