Re: Does psql use nested transactions?

"Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:

> This behaviour allows much closer mimicking of Oracle and other RDBMS's
> transactional behaviour

<begin 2c>This is my single biggest pet peeve with Postgres. When I was first
starting it was the single most frustrating violation of the "least surprise"
principle and now that I've been working with it for over a year it's the one
that most continues to interfere with productive work.

With Oracle I found it *extremely* useful on many occasions when doing manual
updates to be able to check out the effects of my statements before committing
them. It also meant I could do several updates or deletes and commit them all
together.

With Postgres I effectively have to work in autocommit mode. Starting over
from scratch every time I make a typo is infeasible. It feels like trying to
type in a C program using "cat". I've done it before but it's not something I
want to repeat often.

In an ideal world I would actually have suggested all drivers would want to
work this way. They could wrap a nested transaction around every statement and
throw an exception if it fails. The application code could catch the exception
and handle it properly without having to manually fiddle with database bits.

That's actually why I mentioned a couple times during the nested transaction
development that it would be best to design with the assumption that there
would be at least one level of nested transaction open at all times.

But in practice I think applications can manage their own transactions
reasonably. It's just users that expect to be able to recover from any
individual statement at any time without advance planning.

--
greg