JDBC Connection State Management with SQL Exceptions (esp Postgresql)

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

"Joseph Weinstein" <joe@bea.com> wrote in message
news:3B3277C6.4C9BCA9@bea.com...

John Moore wrote:

.....

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.

What about PreparedConnection pooling?
What is your oppinion on the following code
[design] for such caching within a connection :
( getUsedPstmts() is imaginary method of imaginary
MyConnection interface )

public void returnConnection (Connection con) {
Connection local_con = con;
con = null;
PreparedStatement [] used_pstmt = (MyConnection) local_con.getUsedPstmts()
for (int i =0 ; i < used_con.length ; i++) {
PreparedStatement new_pstmt = used_con[i];
used_con[i] = null;
cached_pstmt_HashMap.put( new_pstmt.getSql(), new_pstmt );
}
... some other cleaning steps....
...set connection as available...
}

AlexV

This is crucial because you don't want retained references
to these objects to allow a 'previous user' to affect anything the next

user

AV wrote:

"Joseph Weinstein" <joe@bea.com> wrote in message
news:3B3277C6.4C9BCA9@bea.com...

John Moore wrote:

.....

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.

What about PreparedConnection pooling?
What is your oppinion on the following code
[design] for such caching within a connection :
( getUsedPstmts() is imaginary method of imaginary
MyConnection interface )

public void returnConnection (Connection con) {
Connection local_con = con;
con = null;
PreparedStatement [] used_pstmt = (MyConnection) local_con.getUsedPstmts()
for (int i =0 ; i < used_con.length ; i++) {
PreparedStatement new_pstmt = used_con[i];
used_con[i] = null;
cached_pstmt_HashMap.put( new_pstmt.getSql(), new_pstmt );
}
... some other cleaning steps....
...set connection as available...
}

AlexV

Hi Alex. I think I understand this... The basis of caching/re-using a PreparedStatment
is via the SQL used to create it, but I see no actual statement-level cleanup here.
You should be clearing any warnings the statement may have accrued. Another example
is that you should do something to cover the possibility some user code called setMaxRows(1)
on the statement. You don't want this condition to remain and silently truncate the results
of any subsequent user... This code also doesn't allow for multiple statements with the
same SQL. There will be some 'utility' statements that might be used at several levels
in a user's stack, and you want to allow for caching multiple identical statements *and*
making sure that no two methods in the same caller stack get the *same* statement,
even if it is the same SQL.

Joe

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

--

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

"Joseph Weinstein" <joe@bea.com> wrote in message
news:3B3277C6.4C9BCA9@bea.com...

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.

Argh... Does this mean that my connection pooler needs to keep track of all
statements and result
sets the user creates. I assume this means I also need to wrap the
statements so that I can
capture the returned result sets by overriding the execute method. Is this
correct?

Do you know of any source out there that implements connection pooling in a
portable manner so I could use it with both Oracle and Postgresql?

You should clear theconnection warnings that accrue.

Okway

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.

It also appears that once a non-autoCommit transaction has sustained an
SQLException, it is
useless until a rollback is done - at least in PostgreSQL. Is this correct?

The following question is still outstanding...

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?

Thanks

John