Search Postgresql Archives

Re: Serializable transaction restart/re-execute

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux