On Wed, Mar 13, 2019 at 10:20 PM Tsunakawa, Takayuki <tsunakawa.ta...@jp.fujitsu.com> wrote: > I think the purpose of socket_timeout is to avoid infinite or unduely long > wait and return response to users, where other existing timeout parameters > wouldn't help. For example, OS's process scheduling or paging/swapping > problems might cause long waits before postgres gets control (e.g. due to > Transparent HugePage (THP)). Within postgres, the unfair lwlock can > unexpected long waits (I experienced dozens of seconds per wait on > ProcArrayLock, which was unbelievable.) Someone may say that those should be > fixed or improved instead of introducing this parameter, but I think it's > good to have this as a stop-gap measure. In other words, we can suggest > setting this parameter when the user asks "How can I return control to the > end user in any situation?"
But that's not what it will do. As long as the server continues to dribble out protocol messages from time to time, the timeout will never fire no matter how much time passes. I saw a system once where every 8kB read took many seconds to complete; such a system could dribble out sequential scan results over an arbitrarily long period of time without ever tripping the timeout. If you really want to return control to the user in any situation, what you can do is use the libpq APIs in asynchronous mode which, barring certain limitations of the current implementation, will actually allow you to maintain control over the connection at all times. I think the use case for a timeout that has both false positives (i.e. it will fire even when there's no problem, as when the connection is legitimately idle) and false negatives (i.e. it will fail to trigger when there is a problem, as when there are periodic notices or notifies from the server connection) is extremely limited if not nonexistent, and I think the potential for users to be confused is really high. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
