Statement timeout not working on broken connections with active queries

We have discovered a situation where the statement_timeout is not honored for broken connections. If a connection is in the process of returning results to the client and the connection is severed (for example, network cable on client is unplugged) then the query continues to run on the server even after the statement_timeout is exceeded. The connection will eventually close on its own after about 18-19 minutes and the following log lines will be generated in the postgresql log file:

2006-12-12 04:03:22 LOG: could not send data to client: No route to host
2006-12-12 04:03:22 ERROR: canceling statement due to statement timeout
2006-12-12 04:03:22 LOG: could not send data to client: Broken pipe
2006-12-12 04:03:22 LOG: unexpected EOF on client connection

Our server setup is:
Linux 2.4
Postgresql 8.1.4

Our client setup is:
Windows XP
Java 1.5
postgresql-8.1.jdbc2ee.jar

This behavior appears to be a bug with the statement_timeout. I'd like to know if there is a way to get the connection to close once the statement_timeout is exceeded even if the connection to the client has been severed. I'd also like to know what is causing the connection to close on its own after 18-19 minutes and if this can be adjusted. Any help here would be greatly appreciated.

I tried adjusting the "tcp_keepalives_idle" setting and related settings but this had no affect on the time it took for the connection to close on its own.

I have also tried cancelling the active query via a call to "select pg_cancel_backend(pid)", but this has no affect. I then tried killing the connection by running the command "./pg_ctl kill TERM pid", but this also has no affect (I realize 'kill TERM' isn't considered safe yet, I see it's still on the pg todo list). The connection can be killed with a QUIT signal, but this is not recommended because it causes the database to restart in an unclean way. I'd prefer that the statement_timeout setting simply cancelled the query and the connection was closed without any manual intervention, but does anyone know of a way to manually kill or cancel connections of this sort in a clean manner?

You can duplicate the problem with other clients besides java. For example, you can use PG Admin III following these steps:

1) execute "set statement_timeout = 15000"
2) run a query that will return a large number of rows that will take more than 15 seconds to retrieve
3) a few seconds after you execute the query unplug your network cable
4) wait about 10 seconds
5) plug your network cable back in
6) query the pg_stat_activity view and you will see a non idle connection running your query

Below is the Java code used to duplicate the error. You need to sever your network connection once you see the output "set statement_timeout = ...".

Thanks, Brendan

import java.sql.*;

public class TestStatementTimeout {

private static final String URL = "jdbc:postgresql://hostname/db_name";
private static final String DB_USER = "user";
private static final String DB_PASSWORD = "password";
private static final int STMT_TIMEOUT = 15 * 1000;

public static void main(String[] args) throws Exception {
String sql = "SELECT * FROM table_with_many_rows";
try {
System.out.println("Connecting to " + URL);
Class.forName("org.postgresql.Driver");
Connection conn = java.sql.DriverManager.getConnection(URL, DB_USER, DB_PASSWORD);

Statement stmt = conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,java.sql.ResultSet.CONCUR_READ_ONLY);
stmt.execute("set statement_timeout = " + STMT_TIMEOUT);
System.out.println("set statement_timeout = " + STMT_TIMEOUT);
ResultSet rs=stmt.executeQuery(sql);
System.out.println("executed query");

while (rs.next())
{
System.out.print("column 1 = " + rs.getInt(1) + "\015");
}

System.out.println("Closing Connection");
rs.close(); stmt.close();
conn.close();
}
catch (Exception e) {
e.printStackTrace();
}
}
}