How to set a driver-side query execution timeout

[congoamz]

Hi, is there a way to set a driver-side query execution timeout? I noticed that the Postgres JDBC driver (pgjdbc) supports setting a driver-side socket/network timeout but as far as I can tell the psycopg driver does not. This is a problem for us because if the server becomes unresponsive, any queries being executed will hang indefinitely. Is there a suggested solution for this problem?

[dvarrazzo]

No, we don't currently have a client query timeout. What you can do is to set a server timeout by using statement_timeout or to run a concurrent thread/task to run conn.cancel() after a timeout. Both these methods, however, require the server to be somewhat responsive.

I honestly don't know what an unresponsive server is. Is this a real use case? With postgres?

If we implemented a client timeout that unilaterally decides to interrupt the query, we should close the connection anyway, because we wouldn't be able to exit the ACTIVE state and, arguably, if the server is considered non responsive, there is not much point to send it another query.

[congoamz]

@dvarrazzo Thanks for your response. We are using Amazon Aurora Postgres. Although database outages are rare, it is possible that the database availability zone may suddenly fail or a network misconfiguration could cause a database instance to become unavailable. In the case of these spontaneous failures I believe the server may be unresponsive. In our test suite we are using a module called toxiproxy to simulate an unresponsive server and are running into the issue where queries executed against that server hang indefinitely. In this scenario statement_timeout or conn.cancel() will not work since you mentioned they require the server to be somewhat responsive. Do you have any recommendations to solve this?

Yep, that's what the pgjdbc driver does if the socket timeout is enabled and has been reached - it closes the connection and throws an exception indicating that there was a socket timeout.

[dvarrazzo]

I have run a test with a proxy disconnecting on my side and have different results, the connection is interrupted. The test is at 674afa2.

I am not sure that the effect you are seeing in your test are a correct reproduction of what may happen in AWS when a disconnection happen. I think you are testing for something different, and stricter.

I think something you can try is to set the tcp socket keepalive using the socket module. conn.fileno() will return the socket fd, which you may use with socket.fromfd() and use setsockopt() to configure the TCP keepalive parameters. Mind that the connection socket is in non-blocking mode, the fromfd() function says that it expects the socket to be in blocking mode, I don't know what the implications are.

[congoamz]

@dvarrazzo Since its hanging with toxiproxy but not with your Proxy test class, it sounds like toxiproxy tests for stricter conditions than your Proxy. The problem is, we don't know whether a real-world AWS failure could result in the behavior we are seeing using toxiproxy or not. Can you explain why you think toxiproxy is not a correct reproduction of what may happen in a real-world scenario? If the server spontaneously dies (using a bizarre example, lets say the network cable gets unplugged from the server rack), wouldn't it be completely unresponsive similar to how toxiproxy is completely unresponsive?

[dvarrazzo]

I don't have much to explain. You have your hypothesis that a certain failure will happen (will it?) and that a certain test is an adequate simulation for this scenario (is it?). Me not having certainties about these hypotheses doesn't require an explanation.

As you surely understand, I cannot work for free to develop features for commercial products such as AWS and Aurora Postgres. Surely cables can be unplugged and can disconnect free-software databases too, but I have other development priorities for this project at the moment (see the issues currently in the works). If you are interested in the development of such feature it should be under a professional consultancy. Of course you are welcome to provide a MR.

[congoamz]

Makes sense, thanks for the response, I was asking in case you had info that I didn't about how database failures might behave. Sounds like there isn't really a good way to know for sure that our scenario occurs in the real world.