Re: Serializable transaction restart/re-execute

Hi Kevin, thank you very much for reply.

We plan to have a middleware/gateway in our full solution so we could have
the restart logic there but that would only apply to external interface
calls.

We plan to have a few "backend processes" that we want to run directly in
pgsql and those would not have "restarts"..

dblink does sound like a decent option/workaround but I'm guessing
everything points toward focusing on locks instead of relying on some hacky
serializable failure restart implementation..

If you post this reply in the SO post I found quite helpful and insightful
and I'll definitely accept it as answer. If you have the time to elaborate
on a working example using dblink it would definitely by a nice bonus :)

Thank you once again

On Mon, Apr 6, 2015 at 3:22 PM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:

> Filipe Pina <filipe(dot)pina(at)impactzero(dot)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
>