Re: Timeout parameters

At Thu, 14 Mar 2019 03:33:20 +0000, "Tsunakawa, Takayuki" <tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com> wrote in <0A3221C70F24FB45833433255569204D1FBC4191(at)G01JPEXMBYT05>
> From: Robert Haas [mailto:robertmhaas(at)gmail(dot)com]
> > 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.
>
> I understood hat the example is about an SELECT that returns multiple rows. If so, statement_timeout should handle it, shouldn't it?

If so, in turn the socket_timeout doesn't work as expected? I
understand that what is proposed here is to disconnect after that
time of waiting for *the first tuple* of a query, regardless of
it is a long query or network failure. On of the problems raised
here is the socket_timetout patch doesn't work that way?

In order to at least to make it work as expected, it should arm a
timeout at PQsendQuery and disarm at the first response.

> > 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.
>
> Maybe. But the users aren't often in a situation to modify the application to use libpq asynchronous APIs.

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.

> > 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.
>
> My understanding is that the false positive case doesn't occur,
> because libpq doesn't wait on the socket while the client is
> idle and not communicating SQL request/response. As for the
> false negative case, resetting the timer upon notices or
> notifies receipt is good, because they show that the database
> server is functioning. socket_timeout is not a mechanism to
> precisely limit the duration of query request/response. It is
> kind of a stop-gap, last resort to assure return control within
> reasonable amount of time, rather than minutes or hours.

Doesn't TCP_KEEPALIVE work that way?

statement_timeout works on a live connection, TCP_USER_TIMEOUT
works for an already failed network but if the network fails
after a backend already sent the ack for a query request, it
doesn't work. TCP_KEEPALIVE would work for the case where any
packet doesn't reach each other for a certain time. Is there any
other situations to save?

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center