Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://github.com/psycopg/psycopg/discussions/676 below:

How to set a driver-side query execution timeout · psycopg/psycopg · Discussion #676 · GitHub

• Pricing

Oct 31, 2023 · 2 comments · 4 replies

-

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?

Beta Was this translation helpful? Give feedback.

You must be logged in to vote

-

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.

Beta Was this translation helpful? Give feedback.

You must be logged in to vote

0 replies

-

@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.

Beta Was this translation helpful? Give feedback.

You must be logged in to vote

4 replies

-

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.

Beta Was this translation helpful? Give feedback.

-

@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?

Beta Was this translation helpful? Give feedback.

-

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.

Beta Was this translation helpful? Give feedback.

-

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.

Beta Was this translation helpful? Give feedback.

Heading

Bold

Italic

Quote

Code

Link

Numbered list

Unordered list

Task list

Attach files

Mention

Reference

You can’t perform that action at this time.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4