I was wondering whether there could be an atomic commit;begin command
for postgresql, in order to open up a transaction at the beginning of
a unit of work in a client session, so that client applications don't
have to duplicate work with having optimistic locking and version id
fields in their table rows. savepoint doesn't actually commit writes
in a transaction upto the time savepoint is called, but it's useful
for detecting conflicts, whilst allowing work to continue ( e.g.
with timeout set) . the atomic commit;begin wouldn't be necessary
except a client could crash before the end of the unit of work,
and work done upto that point would be lost in the transaction.
the atomic commit;begin is so that clients can use postgresql's
mechanisms for detecting concurrency read/write conflicts by
issuing savepoints before each write, instead of the current need
to begin;select for update xxx, client_versionid (or xmin) ; ( client
checks version id hasn't changed against version id stored when last
selected for read); update; commit .
set autocommit to on , wouldn't cut it would it, because between
writes there is no transaction , so savepoints couldn't be used
for conflict checking.
Also, if the transaction is in read committed mode, then if
a write failed ,and a rollback to savepoint was done, you could
do select again ,get the new value, inform the client, and if
the user elected to go ahead, overwrite with their new value,
it would work the second time, because one has read the committed
select.