I have a connection to a PostgreSQL DB (using the PostgreSQL JDBC Driver), and I set the network timeout on it (using the setNetworkTimeout method), and there is something weird about it.
When I use the connection for simple queries like select * from table, which takes a lot of time, it works fine (waits for the query to return a result). But when I use the connection for queries which use functions (like select max(a) from table), which also take a lot of time, it throws an exception, as a result of a timeout.
example code:
// Queries which takes more than 5 seconds
String bigQuery = "select * from data.bigtable tb1 inner join data.bigtable tb2 on tb1.a like '%a%'";
String bigQueryWithFunction = "select max(tb1.a) from data.bigtable tb1 inner join data.bigtable tb2 on tb1.a like '%a%'";
// Creating a connection with 5 seconds network timeout
Connection con = source.getConnection();
con.setNetworkTimeout(Executors.newSingleThreadExecutor(), 5000);
con.setAutoCommit(false);
Statement st2 = con.createStatement();
st2.execute(bigQueryWithFunction); // This line DOES throws an exception
st2.execute(bigQuery); // This line DOES NOT throws an exception
(Ignore the logic of the queries.)
Can someone explain to me why it happens?
PostgresSQL streams the result rows to the client as soon as they become available.
In your first query, the first result row will be returned quite soon, even though it takes the query a long time to finish. The JDBC driver collects the results and waits until the query is done, but the network connection won't be idle for any longer time.
The second query takes about as long to complete as the first one, but it cannot return its first (and only) result row until all result rows from the join have been calculated. So there is a long idle time on the network connection, which causes the timeout.