Transactions, when should be discarded and rolledback

Question

I'm trying to debug an application (under PostgreSQL) and came across the following error: "current transaction is aborted, commands ignored".

As far as I can understand a "transaction" is just a notion related to the underlying database connection.

If the connection has an auto commit "false", you can execute queries through the same Statement as long as it isn't failing. In which case you should rollback.

If auto commit is "true" then it doesn't matter as long as all your queries are considered atomic.

Using auto commit false, I get the aforementioned error by PostgreSQL even when a simple

select * from foo

fails, which makes me ask, under which SQLException(s) is a "transaction" considered invalid and should be rolled backed or not used for another query?

using MacOS 10.5, Java 1.5.0_16, PostgreSQL 8.3 with JDBC driver 8.1-407.jdbc3

Answer 1

That error means that one of the queries sent in a transaction has failed, so the rest of the queries are ignored until the end of the current transaction (which will automatically be a rollback). To PostgreSQL the transaction has failed, and it will be rolled back in any case after the error with one exception. You have to take appropriate measures, one of

1. discard the statement and start anew.
2. use SAVEPOINTs in the transaction to be able to get back to that point in time and try another path. (This is the exception)

Enable query logging to see which query is the failing one and why.

In any case the exact answer to your question is that any SQLException should mean a rollback happened when the end of transaction command is sent, that is when a COMMIT or ROLLBACK (or END) is issued. This is how it works, if you use savepoints you'll still be bound by the same rules, you'll just be able to get back to where you saved and try something else.

Answer 2

It seems to be a characteristic behaviour of PostgreSQL that is not shared by most other DBMS. In general (outside of PostgreSQL), you can have one operation fail because of an error and then, in the same transaction, can try alternative actions that will succeed, compensating for the error. One example: consider a merging (insert/update) operation. If you try to INSERT the new record but find that it already exists, you can switch to an UPDATE operation that changes the existing record instead. This works fine in all the main DBMS. I'm not certain that it does not work in PostgreSQL, but the descriptions I've seen elsewhere, as well as in this question, suggest that when the attempted INSERT means that any further activity in the transaction is doomed to fail too. Which is at best draconian and at worst 'unusable'.