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
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
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.