Filipe Pina <filipe.pina@xxxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
