Oliver Jowett <oliver@opencloud.com> writes:
> (2) Run a separate timer thread. Start a timer in Statement.execute()
> before submitting the query to the protocol layer. If the timer expires,
> close the low-level DB connection (from the timer thread) which should
> cause an IOException in the guts of the protocol layer where the query
> executing thread is blocked on network I/O, eventually propagating up as
> a fatal SQLException to the caller.
> I would like to implement (2) but I can see that killing the connection
> on timeout may not be desirable in all cases.
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.
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?
It might be that you need to decouple queries from connections a bit
more, so that a query can fail and "let go" of a connection, while the
connection object has to wait for its query to be cancelled before
returning to the pool of available connections.
regards, tom lane