Re: Implementing setQueryTimeout() - Mailing list pgsql-jdbc

From Oliver Jowett
Subject Re: Implementing setQueryTimeout()
Date
Msg-id 47BA2A6E.6050907@opencloud.com
Whole thread Raw
In response to Re: Implementing setQueryTimeout()  (Gregory Stark <stark@enterprisedb.com>)
Responses Re: Implementing setQueryTimeout()  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-jdbc
Gregory Stark wrote:

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

It is not about triggering failover in my scenario, it is about limiting
the time a thread can spend blocked waiting on a query.

The scenario is call processing where access to the DB is not critical -
you can do something acceptable without DB access. However, what you do
*not* have the luxury of doing is hanging around for 5 minutes waiting
for a connection to time out before you make a decision about what to do
with the call! Instead you want a rapid error, mark the DB as down, and
future queries proceed as if the DB has failed until a separate check
process manages to run a test query against the DB successfully.

There is a secondary problem that you do not have an unbounded number of
threads and connections to do your processing on, so tying up threads
and connections blocking on a slowly dying TCP connection is a real
problem - under any real load you will exhaust your thread pool (or
connection pool) in a matter of seconds. Then everything stops. This is bad.

I can implement something in my local copy of the driver to support
this, but it'd be nice if I can do something more generally useful too..

I'd argue that "close connection on network timeout if setQueryTimeout()
is set" is better than "do absolutely nothing special if
setQueryTimeout() is set", anyway. If you don't want queries being
aborted because they are taking too long to complete.. don't set a query
timeout!

-O


pgsql-jdbc by date:

Previous
From: Dave Cramer
Date:
Subject: Re: Getting "This ResultSet is closed" exceptions
Next
From: Jan de Visser
Date:
Subject: Re: Getting "This ResultSet is closed" exceptions