We have about 100 database servers distributed statewide. We are
planning to migrate to PostgreSQL, and to that end, we have three of our
larger databases (hundreds of GB each) currently running PostgreSQL in
the distributed mix. (We started with databases containing redundant
data, to minimize risk in the early stages.) Our software has been
written with every effort to maintain portability, with the ANSI / ISO
standards as our guide.
Some database products, including the one which has been our mainstay
for years, treat the JDBC response as a stream. This is an allowed
option under the JDBC spec, and has its pros and cons. One thing we
have found, using this product, is that it is beneficial to cancel a
Statement before closing the ResultSet for that Statement if an
exception occurs. (If you don't do this you can wait for a while while
the ResultSet.close() method reads and discards all rows.)
All of this is to introduce a pattern which we have found very useful,
from which we are reluctant to move. Simplified, it goes:
Statement stmt = null;
ResultSet rs = null;
try
{
stmt = conn.createStatement();
rs = stmt.executeQuery("<qry>");
while (rs.next())
{
<process the row>
}
rs.close();
rs = null;
stmt.close();
stmt = null;
}
finally
{
if (stmt != null)
{
try
{
stmt.cancel();
}
catch (Exception e)
{
// ignore
}
}
if (rs != null)
{
try
{
rs.close();
}
catch (Exception e)
{
// ignore
}
rs = null;
}
if (stmt != null)
{
try
{
stmt.close();
}
catch (Exception e)
{
// ignore
}
stmt = null;
}
}
The problem is that even after the exception comes out of this code, is
caught, and the transaction is rolled back -- we are still often able to
start another statement which is running by the time the server gets
around to interrupting the related back end process. Obviously, having
the cancel of one statement actually interrupt the processing of a
subsequent statement violates the popular principle of "least surprising
result".
Since the place we've been hitting this involves a race condition on a
single client thread, there is an obvious simple-minded solution.
Attached is a patch representing that solution, which actually solves
the problem where we've been having. It clearly isn't ready for
prime-time, though, since it fails to deal with a result set which uses
a cursor which is canceled from the thread which started the execution.
I'm posting at this point because I'm not sure how best to identify that
condition, or how best to handle it.
Discussion welcome.
-Kevin