Re: Serializable transaction restart/re-execute

Filipe Pina <filipe.pina@impactzero.pt> wrote:

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

Right, serializable transactions provide exactly what you are
looking for as long as you use some framework that starts the
transaction over when it receives an error with a SQLSTATE of 40001
or 40P01.

> 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

Perhaps once we've sorted out the issue here I can post an answer
there for the benefit of anyone finding the SO question.

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

In PostgreSQL a function always runs in the context of a
transaction.  You can't start a new transaction within the context
of a "wrapper" function.  That would require a slightly different
feature, which is commonly called a "stored procedure" -- something
which doesn't exist in PostgreSQL.  Therefore, you need to put the
logic to manage the restart into code which submits the transaction
to the database.  Fortunately, there are many connectors for that
-- Java, perl, python, tcl, ODBC, etc.  There is even a connector
for making a separate connection to a PostgreSQL database within
PostgreSQL procedural language, which might allow you to do
something like what you want:

http://www.postgresql.org/docs/current/static/dblink.html

> - the ideal would be to be able to define this at database level
> so I wouldn’t have to implement wrappers for all functions..

I have seen this done in various "client" frameworks.  Clearly it
is a bad idea to spread this testing around to all locations where
the application is logically dealing with the database, but there
are many good reasons to route all database requests through one
"accessor" method (or at least a very small number of them), and
most frameworks provide a way to deal with this at that layer.
(For example, in Spring you would want to create a transaction
manager using dependency injection.)

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

Yes, but only from the "client" side of a database connection --
although that client code.  That probably belongs in some language
you are using for your application logic, but if you really wanted
to you could use plpgsql and dblink.  It's hard for me to see a
case where that would actually be a good idea, but it is an option.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company