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();
}
}
}
