Re: Serializable transaction restart/re-execute

On Fri, 3 Apr 2015 15:35:14 +0100
Filipe Pina <filipe.pina@impactzero.pt> wrote:

> Hello,
>
> I come from a GTM background and once of the transactional features there are the ?Transaction Restarts?.
>
> Transaction restart is when we have two concurrent processes reading/writing to the same region/table of the
database,the last process to commit will ?see? that the database is not the same as it was when the transaction started
andgoes back to the beginning of the transactional code and re-executes it. 
>
> The closest I found to this in PGSQL is the Serializable transaction isolation mode and it does seem to work well
exceptit simply throws an error (serialization_failure) instead of restarting. 
>
> I?m trying to make use of this exception to implement restartable functions and I have all the examples and
conditionsmentioned here in a question in SO (without any answer so far?): 
>
> http://stackoverflow.com/questions/29372202/postgresql-generic-handler-for-serialization-failure
<http://stackoverflow.com/questions/29372202/postgresql-generic-handler-for-serialization-failure>
>
> So basically I have two questions:
> - the restartable ?wrapper? function never gets its ?DB view? refreshed once it restarts, I assume it?s because of
theoutter transaction (at function level) so it never re-reads the new values and keeps failing with
serialization_failure..Any way to solve this? 
> - the ideal would be to be able to define this at database level so I wouldn?t have to implement wrappers for all
functions..Implementing a ?serialization_failure? generic handler that would simply re-call the function that threw
thatexception (up to a number of tries). Is this possible without going into pgsql source code? 

I suspect that savepoints will accomplish what you want:
http://www.postgresql.org/docs/9.4/static/sql-savepoint.html

--
Bill Moran