Re: atomic commit; begin for long running transactions , in combination with savepoint.

On 10/13/07, syan tan <kittylitter(at)people(dot)net(dot)au> wrote:
> 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 .

I'm not following your train of thought. It sounds as though you want
to commit data without actually leaving your current transaction, but
what do you need the transaction for?

I don't understand how an atomic COMMIT;BEGIN would help. Consider a
transaction with serializable isolation: your snapshot view of the
data exists exactly as long as your transaction does. A COMMIT
followed by a BEGIN, whether atomic or not, is going to change your
view of the data.

If you want it to do something else, what is that exactly?

> 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.

What is preventing you from doing that now?