Обсуждение: Problem with jdbc connection behavior

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.

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.

Jerel

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

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

Blakely, Jerel (Mission Systems) wrote:
> The testcase code currently looks like this, I have tried rollback
> already. Since commits happen after each item it didn't change anything.
> ...

Hmm. I can't see any apparent reason why that wouldn't work. I'd suggest
setting these parameters in the server's postgresql.conf file to log all
the connections and statements and commits:

log_connections = on
log_disconnections = on
log_statement = 'all'

I'm thinking there's something in your environment inhibiting commits or
or something like that.

> 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");

Can you elaborate? Are you executing the drop table while the test case
is running, or is it just generally not returning?

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

Yeah, I hate it when that happens ;).

Something you should be aware of is that unlike in most other DBMSs, DDL
statements like CREATE/DROP TABLE are transactional in PostgreSQL. If
you CREATE TABLE in one connection, it's not visible to others until commit.

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

I'm working at the moment for transactions and drop tables, I shut down
the server and enabled the logging you suggested and restarted it. I
think this is now the correct behavior I should expect from the server.
When I do a commit after an exception with no savepoint it rolls the
entire transaction back or I can roll back to a savepoint before the
commit, and in either case my jdbc connection continues to work after
that commit. I do not know what caused the jdbc connection to stop
working entirely when I caught that exception before. Also my drop table
is now working the only thing I did between was shutdown the server
enable some more logging and restart it. I am going to have to do some
more testing and hope it was a one time glitch that does not start
happening again. Can you think of anything I could have screwed up in
the server that would reset when cycled?

I know what you mean with ddl in transactions, I already had to tell
some coders they could not manage their own transactions for create and
drop tables. They have to be single item transactions in the dao for
consistency. Sybase at least let me set 'ddl in tran' on, though I am
not sure on the point in that setting, because I can just do a commit
every time rather than turn it off. Oracle does auto commit and does not
even give a warning that the transaction you think you have is now shot.
I prefer the PostgreSQL method.

Jerel



-----Original Message-----
From: Heikki Linnakangas [mailto:hlinnaka@gmail.com] On Behalf Of Heikki
Linnakangas
Sent: Wednesday, February 14, 2007 2:08 PM
To: Blakely, Jerel (Mission Systems)
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Problem with jdbc connection behavior

Blakely, Jerel (Mission Systems) wrote:
> The testcase code currently looks like this, I have tried rollback
> already. Since commits happen after each item it didn't change
anything.
> ...

Hmm. I can't see any apparent reason why that wouldn't work. I'd suggest
setting these parameters in the server's postgresql.conf file to log all
the connections and statements and commits:

log_connections = on
log_disconnections = on
log_statement = 'all'

I'm thinking there's something in your environment inhibiting commits or
or something like that.

> 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");

Can you elaborate? Are you executing the drop table while the test case
is running, or is it just generally not returning?

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

Yeah, I hate it when that happens ;).

Something you should be aware of is that unlike in most other DBMSs, DDL
statements like CREATE/DROP TABLE are transactional in PostgreSQL. If
you CREATE TABLE in one connection, it's not visible to others until
commit.

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