RE: Timeout parameters
> Oops, unfortunately, PQcancel() does not follow any timeout parameters... It uses a blocking socket. > Also, I still don't think it's a good idea to request cancellation. socket_timeout should be sufficiently longer than the usually expected query execution duration. And long-running queries should be handled bystatement_timeout which indicates the maximum tolerable query execution duration. > For example, if the usually expected query execution time is 100 ms, statement_timeout can be set to 3 seconds and socket_timeout to 5 seconds. Based on your comment it seems to me that 'socket_timeout' should be connected with statement_timeout. I mean that end-user should wait statement_timeout + 'socket_timeout' for returning control. It looks much more safer for me. Best regards, Mikalai Keida
RE: Timeout parameters
Hello Takayuki-san, > Yes, so I think it would be necessary to describe how to set socket_timeout with relation to other timeout parameters -- socket_timeout > statement_timeout, emphasizing that socket_timeout is not for canceling long-running queries but for returning control to the client. For me it does not look enough. If you would like to implement the 'socket_timeout' parameter, could you pay attention on the Fabien Coelho comment? > The fact that no answer data is received may mean that it takes time to > compute the result, so cancelling seems appropriate to me, rather than > severing the connection and starting a new one immediately, leaving the > server loaded with its query. This comment is very important to pay attention on. Let's imagine once more: 1. end-user makes a query 2. the query has not completed within this timeout due to any reason: the query is too 'heavy', some problem with OS happened, PostgreSQL server terminated 3. if we simple close connection, the first case is negative by mistake. To avoid such a situation I think it would be better to call a PQcancel(). In case of failure PQcancel() terminates in 'socket_timeout'. So, control to the end-user in such a failure situation will be returned in 2 * 'socket_timeout' interval. It is much better than hanging forever in some specific cases. Moreover, such solution will not lead to the overloading of PostgreSQL server by abnormally ignored 'heavy' queries results by end-users. What do you think about it? Best regards, Mikalai Keida
RE: Timeout parameters
Hello, Takayuki. > > > For example, OS issues such as abnormally (buggy) slow process scheduling > > or paging/swapping that prevent control from being passed to postgres. Or, > > abnormally long waits on lwlocks in postgres. statement_timeout doesn't > > take effect while waiting on a lwlock. I have experienced both. And, any > > other issues in the server that we haven't experienced and not predictable. > > > > For me all mentioned by Takayuki Tsunakawa problems looks like a lack of > > design of end-user application or configuration of DB server. It is not > > a problem of PostgreSQL. > Certainly, my cases could be avoided by the OS and database configuration. But we cannot say all such problems can be avoided by configuration in advance. The point is to provide a method to get ready for any situation. Do you mind me asking you whether you have thought that solving your problem can lead to the problem in the other user applications? Let's imagine a possible problem: 1. end-user sets 'socket_timeout' only for current session 2. 'socket_timeout' is much shorter than 'keep_alive', 'tcp_user_timeout' and 'statement_timeout' 3. end-user invokes a 'heavy' query which is not be possible to process by PostgreSQL server within 'socket_timeout' 4. if the query fails due to timeout, terminate the session and repeat it As the query is not cancelled, PostgreSQL server will process it till completion or 'statement_timeout' expiration. In such a case PostgreSQL server can be overloaded by an 'unreliable' user/users and other end-users will not be able to work with PostgreSQL server as they expected. There are a number of timeout parameters which can lead to the terminating a query: 1. keep_alive and tcp_user_timeout terminate a query because of network problems 2. statement_timeout terminates a query because the time for processing this query by PostgreSQL server expired. These timeouts are absolutely different and do not interact with each other. Suggested 'socket_timeout' can replace all mentioned by me timeout parameters because of willing of 'selfish' end-user. 'SocketTimeout' in JDBC was added to cope with network problems when keep_alive and tcp_user_timeout were not implemented. Implementation of the 'socket_timeout' looks like adding a bug into the libpq. I think that the problems you faced, relate to the other issues in PostgreSQL. Best regards, Mikalai Keida
RE: Timeout parameters
Hello, all. The main subject of discussion in this thread relates to the 'socket_timeout'. As I understand there is no any hesitation about applying TCP_USER_TIMEOUT into the PostgreSQL. We have been waiting for applying TCP_USER_TIMEOUT in PostgreSQL for about 6 moths. Fabien, I was wondering whether you can apply TCP_USER_TIMEOUT patch and continue discussion about 'socket_timeout'? > I can't imagine that in the cases where other than applications > cannot be rebuild for some reasons. (E.g. the source code has > been lost or the source code no longer be built on modern > environment..) > If an application is designed to have such a requirement, mustn't > it have implemented that by itself by any means? For example, an > application on JDBC can be designed to kill a querying thread > that takes longer than threshold. Kyotaro-san, I am afraid you are mistaken in your statement about JDBC. JDBC is an another level of abstraction which provides only an universal Java interface to interact with different databases. There is the same ODBC driver which provides an universal C interface to interact with different databases. So, the 'socket_timeout' seems to be a part of functionality of ODBC driver. libpq provides two interfaces: pqWait() which waits for a socket state forever and psTimedWait() which waits for a socket state for an appropriate timeout. This functionality seems to be enough. I agree with Robert Haas that this parameter can make a mess in the head of PostgreSQL user because it is very difficult to understand the case when each timeout parameter, which is provided by PostgreSQL, works. > For example, OS issues such as abnormally (buggy) slow process scheduling or paging/swapping that prevent control from being passed to postgres. Or, abnormally long waits on lwlocks in postgres. statement_timeout doesn't take effect while waiting on a lwlock. I have experienced both. And, any other issues in the server that we haven't experienced and not predictable. For me all mentioned by Takayuki Tsunakawa problems looks like a lack of design of end-user application or configuration of DB server. It is not a problem of PostgreSQL. Best regards, Mikalai Keida
RE: Timeout parameters
Hello Nagaura-san. Thank you for your response. The main idea of my comment was to avoid handling logical errors ( "client-side timeout") in advance to the detection of network problems Therefore, I suggested setting "client-side timeout" greater of equal to the TCP_USER_TIMEOUT or note about it in the documentation. > In conclusion, you think this feature should process something e.g., sending canceling request. Don't you? > If yes, is it hard for you to accept my thought as follows? > 1. The "end-user" I mentioned is application/system user. > 2. Such end-users don't want to wait responses from system or application for whatever reason. > 3. End-users don't care where the failure of the system is when it occurs. > 4. They just don't want to wait long. > 5. If I made the server processing something when this parameter is triggered, they would wait for the time it takes to process it. > 6. Therefore it is not preferable to make servers processing something in this feature. I see what you mean and I agree with you. > > Such a situation looks to be covered by TCP_USER_TIMEOUT and keep_alive > > mechanisms. > i.e., you mean that it can't be happened to occur OS hang with network still alive. > Don't you? I'm afraid to be mistaken but I think that when network is alive it means that OS does not hang. I think that it is a responsibility of the OS kernel to manage network connections. Keep_alive mechanism checks a network state by exchanging data between client's and server's OS kernels. I think the same procedure is used in TCP_USER_TIMEOUT mechanism. So, when keep_alive and TCP_USER_TIMEOUT say that network works correct, it means that kernels of client and server do not hang. On the other hand it does not guarantee that PostgreSQL server is not hang. "client-side timeout" should handle this problem when network problem was not detected. Best regards, Mikalai Keida
RE: Timeout parameters
Hello Ryohei-san, I understand the main aim of your suggestion that a client application has to do a lot of work except making quires to the database. I agree with you that "client-side timeout" has to be integrated into the PostgreSQL server and libpq. I'm with Fabien that "client-side timeout" seems unsafe. Also I agree with Fabien that quire can take much time to be processed by the PosgtreSQL server and it is a normal behavior. There is possible that performance of the PostgreSQL server machine can be low temporary or continuously, especially during upgrading procedure. I think it is important to add some more information into the description of this parameter which informs end-users that this parameter has to be used very carefully because it can impact as on the client application as on the server. > You mentioned about when a SQL query is heavy, but I want to talk about when OS hang. > If OS hang occurs, the cost of cancel request processing is so high. Such a situation looks to be covered by TCP_USER_TIMEOUT and keep_alive mechanisms. May be it is better to warn in documentation or prohibit in the source code to set "client-side timeout" less than TCP_USER_TIMEOUT to avoid handling "possible" logical problems ahead to the network problems. Keep in mind that "client-side timeout" can abort a connection which uses UNIX-domain sockets too. What do you think about it? Best regards, Mikalai Keida
RE: Timeout parameters
Hello, all. > tcp_socket_timeout (integer) > > Terminate and restart any session that has been idle for more than > the specified number of milliseconds to prevent client from infinite > waiting for server due to dead connection. This can be used both as > a brute force global query timeout and detecting network problems. > A value of zero (the default) turns this off. I am not very familiar with the PostgreSQL source code. Nevertheless, the main idea of this parameter is clear for me - closing a connection when the PostgreSQL server does not response due to any reason. However, I have not found in the discussion a reference that this parameter can be applied to the TCP as well as to the UNIX-domain sockets. Moreover, this parameter works out of communication layer. When we consider TCP communication, the failover is covered by keep_alive and tpc_user_timeout parameters. According to it, we should not use 'tcp' prefix in this parameter name, 'socket' sub string is not suitable too. 'timeout' is OK. This parameter works on the client side. So the word 'client' is a good candidate for using in this parameter name. This parameter affects only when we send a 'query' to the pg server. Based on it, we can build a name for this parameter 'client_query_timeout'. The suggested explanation of this parameter does not follow the aim of integrating this parameter: client_query_timeout Specifies the number of seconds to prevent client from infinite waiting for server acknowledge to the sent query due to dead connection. This can be used both as a force global query timeout and network problems detector. A value of zero (the default) turns this off. Best regards, Mikalai Keida
