Re: Serializable transaction restart/re-execute

Hi Bill, thanks for the quick reply.

I had read about SAVEPOINTs but I also read I cannot use them in PLPGSQL and
should use BEGIN/END blocks and EXCEPTIONs.

Did you check the URL I mentioned? I have the code I used there:

CREATE OR REPLACE FUNCTION myretest() RETURNS integer AS $$
DECLARE
tries integer := 5;
BEGIN
WHILE TRUE LOOP
BEGIN -- nested block for exception
RETURN mytest();
EXCEPTION
WHEN SQLSTATE '40001' THEN
IF tries > 0 THEN
tries := tries - 1;
RAISE NOTICE 'Restart! % left', tries;
ELSE
RAISE EXCEPTION 'NO RESTARTS LEFT';
END IF;
END;
END LOOP;
END
$$
LANGUAGE plpgsql;

But it doesn't work.. Every iteration fails with serialization_failure
probably because the outer transaction is not rolled back and I'm not sure
how to write this in a way I can roll it back and still have control of the
LOOP..

I find it hard to believe that PGSQL has this amazing "serializable"
isolation method but not a standard way to take advantage of it to
automatically "restart" the failed transactions...

On Fri, Apr 3, 2015 at 11:07 PM, Bill Moran <wmoran(at)potentialtech(dot)com>
wrote:

> On Fri, 3 Apr 2015 15:35:14 +0100
> Filipe Pina <filipe(dot)pina(at)impactzero(dot)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 and goes 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 except it 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 conditions mentioned 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 the outter 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 that exception (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
>