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 <kgri...@ymail.com> wrote: > Filipe Pina <filipe.p...@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 >
