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