Error handling problems when using PostgreSQL

[Nicklas Nordborg]

I have discovered an annoying "feature" in PostgreSQL that causes a lot of trouble for us when it comes to error handling in list pages. In some cases the user selection of columns, sort order, filters, etc. on a list page may result in an incorrect SQL statement being executed against the database. We have fixed several of those issues by trying to validate as much as possible before executing the query, but the last line of defense has been to catch the error, display it to the user and then continue with the rest of the page as if nothing has happened.

PostgreSQL behaves differently than MySQL when an SQL statement has generated an error. It will refuse to execute any other SQL statements until the transaction has been rolledback. The problem with this is that the "continue with the rest of the page as if nothing has happened" approach no longer works since the rest of the page usually means that additional queries are executed. There is nothing wrong with those queries but the PostgreSQL driver still generates an exception. This time, the code is aborted and the user is presented with a 'half-done' page containing only half the menu and nothing else. Error messages are written to the log file.

NOTE! It may be difficult to trigger this since we have weeded out most of the situations were this can happen. I discovered this now while implementing new stuff related to #1616.

To get PostgreSQL to work as MySQL, we must set a transaction SAVEPOINT before executing the list query and then rollback to that savepoint in case of an error. Then, it should be possible to continue with the original transaction as if nothing happened. It is probably fairly easy to do this in dynamic queries (since we are executing the SQL outside of Hibernate). I don't know if we need it for HQL queries. So far I have not been able to trigger an error from the SQL execution.

[Nicklas Nordborg]

(In [5881]) Fixes #1651: Error handling problems when using PostgreSQL

Adds the possibitlity to use "failSafe" query execution. This can only be enabled when we do our own SQL execution and not via Hibernate. The "failSafe" option should be enabled when the gui must be able to continue in order to render a useful html page. I have enabled this option on the spot data list page, but there may be other places that need it as well (eg. experiment explorer).