Re: Serializable transaction restart/re-execute - Mailing list pgsql-general

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

-----Original Message-----
From: Bill Moran [mailto:wmoran@potentialtech.com]
Sent: 3 de abril de 2015 23:07
To: Filipe Pina
Cc: Postgresql General
Subject: Re: [GENERAL] 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 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-s
erialization-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