Re: Automatic transactions in SELECT

Hello Joseph,

If you can provide some kind of test case to duplicate the problem I
would happy to take a look to see if there is an issue with the pgJDBC.

With that said you have not provided enough information that directly
correlates to any problem with PostgreSQL's JDBC. It seems the functionality
of SquirrelSQL is the issue with leaving transaction hanging.

Sorry but this does not suprise me. The first time I ran that tool it
completely locked its process. Have you ever run FindBugs on it?

Note: I'm the project Manager for the MyJSQLView project so have
somewhat of a bias in the last statemnt.

danap.

Joseph Pravato wrote:
>
>
> 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.