Re: [JDBC] Implementing setQueryTimeout()

"Oliver Jowett" <oliver@opencloud.com> writes:

> Tom Lane wrote:
>
>> That seems pretty darn horrid, actually. If the reason for the slow
>> response is server overload, this technique will make things rapidly
>> *worse*. In the first place it does nothing to prevent the server from
>> continuing to compute the too-slow query (and perhaps even committing
>> it). In the second place, having to establish a new connection will eat
>> a lot of cycles you really don't want to waste. In the third place,
>> once you do establish a new connection it will be competing for cycles
>> with the still-running query in the original backend. Iterate a few
>> times and you'll have a self-inflicted denial of service.
>
> Except for the problem of the query continuing to run, these problems seem to
> be common to anything that throws an exception to the client on timeout. The
> client is going to have to give up on that query regardless of how we actually
> implement the timeout, so the server is doing extra "useless" work anyway. If
> the surrounding logic is not smart enough to throttle itself, then you're hosed
> either way.
>
>> I agree with having a timer thread, I think, just not with what you want
>> to do when the timer fires. Can't you do something like sending a query
>> cancel request when you time out?
>
> I could do that, but if the problem is actually that the server or network has
> died it will not help things (the cancel is just going to fail.. eventually).

I think you have to tackle this as two problems. The usual case is going to be a long query which you want to cancel. Cancelling is normally quick and you can report an error with the query just as if the database had encountered some other error.

The problem of a broken network connection or down server is another case. For most users without a failover server I think triggering an error in this case would actually do more harm than good. Even with a failover in my experience you really want a manual or out-of-band mechanism to trigger failover lest you get false positives or double-failures.

> (2) there is no simple way to nondestructively interrupt a blocking I/O call
> deep in the protocol code; and a rewrite to allow this (a) is a lot of work and
> (b) would probably require that we drop support for older Java versions.

Ouch. That's frightening. I'm not sure there's any reasonable way to implement a statement timeout without some way to interrupt the read it's blocking on. Unless there some kind of select(2) equivalent that can allow you to block only for a limited amount of time and then regain control?

-- 
  Gregory Stark
  EnterpriseDB          
http://www.enterprisedb.com
  Ask me about EnterpriseDB's On-Demand Production Tuning

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match