Re: Problem with jdbc connection behavior

Yes, sorry forgot to include some of the details before. I have tried doing connection rollback and get the same issue. In fact the way our dao is setup for this we commit every individual action, so I should not have to do rollback since the commit will do it implicitly, even so I tried it already.

Currently I have the server at 8.1.5 and jdbc3 driver is 8.2-504, I had to get 504 since the version packaged with the server had a bug with stored procedure return values. It's still in dev, so if I need I can pull a newer server version, but I think the issue is with jdbc.

The testcase code currently looks like this, I have tried rollback already. Since commits happen after each item it didn't change anything. The actual code is on another system on another network that I cannot copy or email from.

********************************************************
//Savepoint sp = connection.setSavepoint(); try {
        rs = stmt.executeQuery("SELECT COUNT(1) FROM TEMP_TABLE");
      fail("TEMP_TABLE already exists cannot create for testcase") } catch(SQLException ex) {
      //Should go here for this testcase
        //connection.rollback(sp);
}
connection.commit();

// Method call here that gets its own connection and creates the table commits and closes

try {
        rs = stmt.executeQuery("SELECT COUNT(1) FROM TEMP_TABLE");
      assertNotNull("No result set from TEMP_TABLE query", rs); } catch(SQLException ex) {
      fail("ERROR querying TEMP_TABLE exception =" + e.getMessage()); } Connection.commit();

// Method call here that gets its own connection and drops the table commits and closes

********************************************************
I can stop in debug at the create table return and verify the table exists in another connection. When I continue execution the e.getMessage on the second execute shows the same original exception. However if I create a new connection right before the second query and use it everything works.

On the other hand, if I create a table with a unique key, insert a record, and then try to insert the same record, a duplicate key exception is caught but then the next execute works fine.

Since my other post I now have noticed a problem with the drop table never returning.
It is a simple: stmt.executeUpdate("DROP TABLE TEMP_TABLE");

I have verified these test cases against Firebird, Sybase, and Oracle with no problems, I am beginning to think my Postgres install is cursed.

Jerel

-----Original Message-----
From: Heikki Linnakangas
Sent: Wednesday, February 14, 2007 10:00 AM
To: Blakely, Jerel (Mission Systems)
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Problem with jdbc connection behavior

Blakely, Jerel (Mission Systems) wrote:
> Any help would be appreciated on this, I am not sure if it is a bug or
> intended behavior. I see no documentation on it.
>
> Some simple exceptions cause the jdbc connection to stop working. It
> does not kill the connection, but any query/update... sent after that
> exception does not execute. The return is always the same exception
> that started the problem. The simple test case that I have to
> reproduce the problem is in a prepared statement sending (select
> count(1) from
> non_existant_tablename) it of course returns an exception stating that
> the table does not exist. If I catch that exception and move on and
> then send another prepared statement (select count(1) from
> a_table_that_exists) or any other command that should work, I only get
> the same original table does not exist exception returned from the
> connection. I can do this testcase against other database servers and
> none seem to have this same issue thus far.

The intended behavior is that on an error, the transaction goes into rollback-only mode, and if you try to do anything else with it you will get a "current transaction is aborted, commands ignored until end of transaction block" error. A rollback or commit (which will rollback instead), should return the connection to a good state.

You shouldn't get the same "table not found" error again and again.
Which version of PostgreSQL and the JDBC driver are you using? Could you send the test program you're using?

> This is just an example testcase that I can easily duplicate the
> problem with, how ever I do not know how many total types of
> exceptions cause this to happen. Unless there is some simple fix for
> the connection it, it looks like I will have to blanket close and
> destroy any connection when I get any type of exception (since I see
> no documented information on this behavior and I have no way of
> knowing what exception will break the connection and which will not)
> and open a new one, which is ridiculous.

If you have a statement in your transaction that you know might fail, you can use savepoints to avoid having to restart the whole transaction:

Savepoint sp = conn.setSavepoint();
try {
        stmt.executeQuery("SELECT 1 FROM table_that_might_not_exist"); } catch(SQLException ex) {
        sp.rollback(sp);
}
stmt.executeQuery("SELECT * FROM table_that_exists"); ...

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com