Re: Timeout parameters

On Wed, Mar 13, 2019 at 10:20 PM Tsunakawa, Takayuki
<tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)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