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...
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@xxxxxxxxxxxxxxxxx> wrote:
On Fri, 3 Apr 2015 15:35:14 +0100
Filipe Pina <filipe.pina@xxxxxxxxxxxxx> 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
