Re: 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