We use SquirrelSQL to talk to our databases with the Postgres 9.2-1002 JDBC driver. We've been having issues with transactions mostly related to syntax issues.
Example:
select firstname from contact limit 10;
Will throw this error: Error: ERROR: column "firstname" does not exist Position: 8 SQLState: 42703 ErrorCode: 0
But, if we fix the column name:
select first_name from contact limit 10;
We would get this strange error: Error: ERROR: current transaction is aborted, commands ignored until
end of transaction block SQLState: 25P02 ErrorCode: 0
Over the past few weeks, our team has been looking into this issue where our application would just stop and hang. For the past couple months
we've been dealing with this, our solution was to go to everybody's SquirrelSQL sessions and hit rollback. This fixes the issue every time.
Usually, users' who's session caused the hang couldn't remember what they were doing, so we still don't know what exactly is causing the issue.
We did however figure out what the root cause is, as you can see above. Every statement in the JDBC driver is ran inside of a transaction. This
is not a SquirrelSQL problem since a simple test program that only uses the JDBC driver gives the same error. Yet when I run it in on the
command line (psql) it has no issues.
What gets particularly confusing with this issue is when your query is wrong in any way, you are required to rollback the transaction. Worse
yet, you have to re-run any prior statements in the transaction if you have any errors. The easiest solution is to turn auto-commit on and hope
that you never seriously screw up data. But, we are not going to, nor allowed to do that.
Did I miss anything, maybe a setting somewhere in the JDBC driver that can fix this issue? It makes it extremely difficult to run ad-hoc
queries since at anytime there is a possibility that some queries stop responding.
Please note, one of the recommendations was to switch sql query tools, turn on auto-commit, or to use save points. We would rather find a
solution with the JDBC driver instead of switching tools as we have been using SquirrelSQL for over a year with various databases.
We are currently looking into the use of save points, but as of now, any assistance is appreciated.