Thread: 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
transactionsmostly 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.
Forthe past couple months 
> we've been dealing with this, our solution was to go to everybody's SquirrelSQL sessions and hit rollback. This fixes
theissue every time. 
> Usually, users' who's session caused the hang couldn't remember what they were doing, so we still don't know what
exactlyis 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
insideof a transaction. This 
> is not a SquirrelSQL problem since a simple test program that only uses the JDBC driver gives the same error. Yet
whenI 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
thetransaction. Worse 
> yet, you have to re-run any prior statements in the transaction if you have any errors. The easiest solution is to
turnauto-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
difficultto 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
wouldrather find a 
> solution with the JDBC driver instead of switching tools as we have been using SquirrelSQL for over a year with
variousdatabases. 
> We are currently looking into the use of save points, but as of now, any assistance is appreciated.

Joseph Pravato <joseph.pravato@nomagic.com> wrote:

> We use SquirrelSQL to talk to our databases with the Postgres
> 9.2-1002 JDBC driver.

In my previous job the DBAs had an outright ban on using
SquirrelSQL against production or shared development or test
databases because of its bad transactional behavior.  We used
several other tools, none of which caused the same problems.

> What gets particularly confusing with this issue is when your
> query is wrong in any way, you are required to rollback the
> transaction.

This is viewed as a feature since otherwise, in the absence of
careful exception handling, you might commit a transaction after an
important step failed, possibly resulting in data loss.

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

I recommend using savepoints, or a tool which can automatically use
them:

http://www.postgresql.org/docs/9.2/interactive/sql-savepoint.html

FWIW, psql has an option to turn on automatic savepoint usage via
the ON_ERROR_ROLLBACK option.  Use that with care, though, because
if you use it with a script like this, you can regret it:

BEGIN;
CREATE TABLE new_copy [...] ;
INSERT INTO new_copy SELECT * FROM prod_dat;
DROP TABLE prod_data;
ALTER TABLE new_copy RENAME TO prod_data;
COMMIT;

Note the typo in INSERT/SELECT.  With the default behavior, the
script leaves you in the same state you started.  Ignoring errors,
you lose the contents of that table.  That tends not be a problem
with interactive commands, as long as copy/paste of multiple
commands is not used, but it can be a problem with scripts or
copy/paste.

> Please note, one of the recommendations was to switch sql query
> tools, turn on auto-commit, or to use save points.

When I had to do ad hoc data fix queries, I always used
transactions.  If I wasn't sure of my syntax, I tried it on a small
test database first, tried an EXPLAIN of the statement on a
different connection to validate syntax, or used a savepoint.

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

Due to the desire of members of the community not to lose data due
to a script file typo, any suggestion that a transaction continue
to process subsequent statements after an error without a
subtransaction is a non-starter.  Maybe you can change the
SquirrelSQL source code to have a feature for this, like psql does?

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

After review I do not see a problem with the way pgJDBC handles transactions
appropriately during the failure in your test case. As indicated by others
replies this is an architicture decision to properly handle failure.

Basically as indicated you can continue to use SquirrelSQL and modify the
code to handle the behavior or convice the project to do so. Or move on
to another application or your own to handle the behavior you desire.

Your code to properly handle the exception and continue processing. I'm
sure this is not what may be pertinent to your situation but demostrates
how an application could possibly handle the situation.

danap.

try
       {
          conn.setAutoCommit(false);

          Statement st1 = conn.createStatement();
          String sqlStatementString = "create table example ( " +
                               "       name varchar(255), " +
                               "       primary key(name) " +
                               ")";
          System.out.println(sqlStatementString);
          st1.execute(sqlStatementString);
          st1.close();
          conn.commit();

          try
          {
             Statement query = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
             sqlStatementString = "select nam from example";
             System.out.println(sqlStatementString);
             query.executeQuery(sqlStatementString);
             query.close();
          }
          catch(Exception e)
          {
             System.out.println("Exception Inner Try");
             conn.rollback(); // Added
             e.printStackTrace();
          }

          Statement query2 = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
          sqlStatementString = "select name from example";
          System.out.println(sqlStatementString);
          query2.executeQuery(sqlStatementString);
          query2.close();
          conn.commit();
          //conn.rollback(); << Change

          Statement st3 = conn.createStatement();
          sqlStatementString = "drop table example";
          System.out.println(sqlStatementString);
          st3.execute(sqlStatementString);
          st3.close();
          conn.commit();

          System.out.println("Finished.");

          conn.setAutoCommit(true); // Return autocommit
       }
       catch (Exception sqle)
       {
          System.out.println("Exception Outer Try");
          System.out.println("Exeception" + sqle);
          // Roll back.
          try
          {
             conn.rollback();
          }
          catch (Exception ee)
          {
             System.out.println("Failed to Rollback");
          }
       }

Joseph Pravato wrote:
>
>> 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.
> Thank you for the response, we have gone ahead and provided a Java test case for you.
>
> Basically we have run the queries outside of SquirrelSQL and we still happen to get the same error when we directly
accessthe database 
> through the JDBC driver. At this time we do not believe that this is a SquirrelSQL problem since we get this
transactionerror directly through 
> java.
>
> http://pastebin.com/vg8J0kZi
>
> Your input would be appreciated.
>

Joseph Pravato, 28.02.2013 23:50:
> That's sad to hear, unfortunately if that is the case we're probably going to have to do what you
>recommended by switching applications or adding save points in by hand.

I answered a similar question of yours on the novice list (regarding the VACUUM/autocommit problem)

For my SQL tool (SQL Workbench/J) each statement that is run interactively is guarded with a savepoint. Upon an error,
thetool automatically rolls back to the savepoint, leaving the transaction "intact" and letting you continue without
themanual need to rollback (possibly losing changes you did before that). 

If you want to look at it, it's here: http://www.sql-workbench.net

Regards
Thomas

On 3/1/2013 2:14 AM, Thomas Kellerer wrote:
> Joseph Pravato, 28.02.2013 23:50:
>> That's sad to hear, unfortunately if that is the case we're probably
>> going to have to do what you
>> recommended by switching applications or adding save points in by hand.
>
> I answered a similar question of yours on the novice list (regarding
> the VACUUM/autocommit problem)
>
> For my SQL tool (SQL Workbench/J) each statement that is run
> interactively is guarded with a savepoint. Upon an error, the tool
> automatically rolls back to the savepoint, leaving the transaction
> "intact" and letting you continue without the manual need to rollback
> (possibly losing changes you did before that).
>
> If you want to look at it, it's here: http://www.sql-workbench.net
>
> Regards
> Thomas
I apologize for the confusion, we ended up moving our question regarding
this transaction issue out of the pgsql-novice thread since this was a
separate issue. In the process we forgot to respond to you and I would
like to apologize, we are actually in the process of looking over your
tool now as well as looking into save points.

We appreciate your assistance!