Re: [JDBC] Implementing setQueryTimeout()

From: Tom Lane <tgl(at)sss.pgh.pa.us>
Date: Sun Feb 17 2008 - 20:58:23 EST

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

---------------------------(end of broadcast)---------------------------

TIP 7: You can help support the PostgreSQL project by donating at

                
http://www.postgresql.org/about/donate

Received on Sun Feb 17 21:00:26 2008