Re: Implementing setQueryTimeout()

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Oliver Jowett <oliver(at)opencloud(dot)com>
Cc: PostgreSQL - JDBC <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: Implementing setQueryTimeout()
Date: 2008-02-18 01:58:23
Message-ID: 5350.1203299903@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

Oliver Jowett <oliver(at)opencloud(dot)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

In response to

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Oliver Jowett 2008-02-18 02:19:08 Re: Implementing setQueryTimeout()
Previous Message Oliver Jowett 2008-02-18 01:40:16 Implementing setQueryTimeout()