Re: Timeout parameters

On Wed, Mar 13, 2019 at 11:33 PM Tsunakawa, Takayuki
<tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com> wrote:
> I understood hat the example is about an SELECT that returns multiple rows. If so, statement_timeout should handle it, shouldn't it?

Yes. If you want a query timeout, you should use statement_timeout, not this.

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

Hmm. You might be right about the false positive case, although there
might be some exceptions.

As for the false negative case, the only sorta-legitimate scenario I
can think of is: suppose the database server process is running, but
someone has stopped the session connected to your backend by sending
it SIGSTOP or attaching a debugger. Now, the TCP connection is alive,
but the server will not be able to send any data, so statement_timeout
will not fire, nor will any of the network-level stuff.
socket_timeout would. But that's a pretty remote scenario, I think.

Now you might say - what if the server is stopped not because of
SIGSTOP but because of some other reason, like it's waiting for a
lock? Well, in that case, the database server is still functioning,
and you will not want the connection to be terminated if no notifies
are sent during the lock wait but not terminated if they are sent. At
least, I can't see why anyone would want that.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company