JDBC Connection State Management with SQL Exceptions (esp Postgresql)

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.

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
notexist

......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
notexist

......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