Re: JDBC Connection State Management with SQL Exceptions (esp Postgresql) - Mailing list pgsql-hackers

John Moore wrote:
> 
> HELP!
> 
> I am converting an app from Oracle to Postgresql and running into a
> significant difference in the behavior of a connection after an SQLException
> has been asserted. I am looking for the "correct" way to deal with the
> issue.
> 
> From a number of experiments, it appears that the only way I can re-use a
> connection after it has asserted an SQLException is to issue a rollback()
> call on the connection.
> 
> I am doing transactional work, with multiple statements and then a commit().
> I am also doing my own connection pooling, so it is important that I be able
> to reliably re-use connections.

Hi. There is a lot of state that can be left with a connection, and a good
pooling system should do a bunch of cleanup on the connection when it is
returned to the pool, so it will be ready for the next user. This would include
closing all statements and result sets that the previous user may have created
but not closed. This is crucial because you don't want retained references
to these objects to allow a 'previous user' to affect anything the next user
does. You should clear theconnection warnings that accrue. You should
roll back any hanging transactional context, by doing a rollback if
autoCommit() is false, and you should then reset the connection to autoCommit(true),
which is the standard condition for a new JDBC connection.
Joe

> 
> My questions:
>    What is the best way (in Postgressql, or even better, in a portable
> manner) to deal with SQLExceptions in a pooled connection environment?
> 
>    If I pull a connection out of my pool, is there any way I can tell if it
> will work?  Should I always do a rollback on it just in case? Will that have
> a performance impact?
> 
> In the case of Postgresql, I cannot find a way to tell if the connection is
> in the state of having had an SQL Exception exerted and no rollback called,
> other than keeping track of it myself! Is there any way to determine that
> connection state other than by doing a test query?
> 
> A non-working trace (that I think should work but doesn't) is below. Note
> that a "Done" means the SQL operation did NOT produce an SQLException
> ------------------------------------ cut
> here --------------------------------------
> 
> ...Drop Table Testtable
> SQL Error (Allowed):java.sql.SQLException: ERROR:  table "testtable" does
> not exist
> 
> ......commit()
> ...Select from TestTable after drop
> SQL Error (Allowed):No results were returned by the query.
> Result set:null
> 
> ...Create Table Testtable
> ......Done
> ...Insert into Testtable
> ......Done
> ...Insert into Testtable
> ......Done
> ......commit()
> ...Insert into Testtable
> SQL Error (Allowed):java.sql.SQLException: ERROR:  Relation 'testtable' does
> not
>  exist
> 
> ......commit()
> ...Select from Testtable
> SQL Error (Allowed):No results were returned by the query.
> Result set:null
> 
> ......commit()
> 
> A working trace (added rollbacks) is here:
> ------------------------------------ cut
> here --------------------------------------
> ...Drop Table Testtable
> ......Done
> ......commit()
> ...Select from TestTable after drop
> SQL Error (Allowed):java.sql.SQLException: ERROR:  Relation 'testtable' does
> not
>  exist
> 
> ......Rollback
> Result set:null
> 
> ...Create Table Testtable
> ......Done
> ...Insert into Testtable
> ......Done
> ...Insert into Testtable
> ......Done
> ......commit()
> ...Insert into BOGUSTABLE
> SQL Error (Allowed):java.sql.SQLException: ERROR:  Relation 'bogustable'
> does no
> t exist
> 
> ......Rollback
> ......commit()
> ...Insert into Testtable
> ......Done
> ......commit()
> ...Select from Testtable
> ......done
> Result set:org.postgresql.jdbc2.ResultSet@653108
> 
> ......commit()
> 
> Thanks in advance
> 
> John Moore
> NOSPAMjohn@NOSPAMtinyvital.com

-- 

PS: Folks: BEA WebLogic is expanding rapidly, with both entry and advanced positions
for people who want to work with Java, XML, SOAP and E-Commerce infrastructure products.
We have jobs at Nashua NH, Liberty Corner NJ, San Francisco and San Jose CA.
Send resumes to joe@bea.com