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(at)postgresql(dot)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