PostgreSQL query timeout does not stop the JDBC client from reading data from the server

Question

I have a problem in a multi-threaded application using the PostgreSQL JDBC driver. Sometimes it freezes for many hours with various queries to various databases. To stop such hangs, I set the request timeout to 10 minutes, but it does not work from my application, it does not return after this time.

The jstack of such a hanging thread is as follows:

"srv_thead_160621090411" #1560 prio=5 os_prio=0 tid=0x00007f0d24001800 nid=0x150f runnable [0x00007f0c83ffd000] java.lang.Thread.State: RUNNABLE at java.net.SocketInputStream.socketRead0(Native Method) at java.net.SocketInputStream.socketRead(SocketInputStream.java:116) at java.net.SocketInputStream.read(SocketInputStream.java:170) at java.net.SocketInputStream.read(SocketInputStream.java:141) at org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:143) at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:112) at org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:70) at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:283) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1799) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:200) - locked <0x00000005dc2f9f88> (a org.postgresql.core.v3.QueryExecutorImpl) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:424) at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:161) at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:114) 

From the server side, I see that the request (pid 31657) has been running for> 30 seconds, but then the server detects a client disconnect. I think that after 10 minutes the server will receive the command "CANCEL query with PID 32657" from the JDBC driver, but this connection ended after disconnecting:

 2016-06-21 07:05:23.340 CEST [31657]: [1-1] postgres [unknown] 192.168.1.124(58729) dbtest %LOG: duration: 30689.838 ms execute <unnamed>: SELECT MAX(do_nr) + 1 AS numer FROM dokumenty WHERE (do_typ = 'WZ' or upper(do_typ) = upper('WZ_tmp')) AND do_dataw BETWEEN '2016-01-01 00:00:00' AND '2016-12-31 00:00:00' AND do_magazyn = 29 2016-06-21 07:05:23.340 CEST [31657]: [2-1] postgres [unknown] 192.168.1.124(58729) dbtest %LOG: could not receive data from client: Connection reset by peer 2016-06-21 07:05:23.340 CEST [31657]: [3-1] postgres [unknown] 192.168.1.124(58729) dbtest %LOG: unexpected EOF on client connection with an open transaction 2016-06-21 07:09:24.107 CEST [21]: [8151-1] %LOG: checkpoint starting: time 2016-06-21 07:14:52.620 CEST [31703]: [1-1] [unknown] [unknown] 192.168.1.124(58860) [unknown] %LOG: PID 31657 in cancel request did not match any process 

I think this cancellation comes from the JDBC driver, but I'm not sure. The strangest thing for me is that after 10 minutes, the JDBC client still wants to read data from the server. It hangs for several hours until I restarted the entire service.

I think there are 2 problems:

• Something is wrong with network connections: the server detects the disconnect, but the client believes that it is still connected.

• The request timeout does not stop JDBC from reading data from the server.

Is there something I can do to stop a freezing thread that wants to read data from the server?

My environment:

customer:

Java: 1.8.0_92 (Oracle Corporation)

PostgreSQL Parent Driver PostgreSQL 9.4.1208

Server:

PostgreSQL 9.5.3 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit