Re: BUG #4123: Statement.setQueryTimeout does not work with Postgres Java Driver

On Apr 25, 11:38 pm, Kevin(dot)Gritt(dot)(dot)(dot)(at)wicourts(dot)gov ("Kevin Grittner")
wrote:
> >>> On Fri, Apr 25, 2008 at 11:59 AM, in message
>
> <e9a628e9-fa81-44a8-a49b-7b9efc235(dot)(dot)(dot)(at)s50g2000hsb(dot)googlegroups(dot)com>,
> valgog
>
>
>
>
>
> <val(dot)(dot)(dot)(at)gmail(dot)com> wrote:
> > On Apr 24, 12:28 pm, bo(dot)(dot)(dot)(at)ejurka(dot)com (Kris Jurka) wrote:
> >> On Wed, 23 Apr 2008, valgog wrote:
> >> > Is it possible to implement the setStatementTimeout() as somethig
> >> > like:
>
> >> > s = c.prepareStatement("SELECT set_config('statement_timeout',
> >> > <neededTimeoutInMilliseconds>, false);" );
> >> > s.executeQuery();
> >> > c.commit();
>
> >> Not really.  This sets a global timeout for all queries while the
> JDBC API
> >> specifies that it is per-Statement.  Also this only protects against
> long
> >> running queries.  Recently there was some discussion on the JDBC
> list
> >> about soft vs hard timeouts and it seemed the conclusion was that
> people
> >> wanted setQueryTimeout to protect against things like the network
> >> connection dropping that statement_timeout can't do.
>
> >> In many cases statement_timeout is an adequate substitute for
> >> setQueryTimeout, but not in the general case that the JDBC driver
> must
> >> implement.
>
> > Ok, understood...
>
> It's not too hard to create a monitor thread which issues a
> Statement.cancel after the appropriate interval.  We have that option
> built into our framework; if you route all your SQL requests through
> some such layer you could do it there.  I assume that the only reason
> it hasn't been implemented in the JDBC driver for PostgreSQL is that
> there seems to be a reluctance to create any threads in the driver,
> but rather to use the thread of the requester.  Is that a hard and
> fast rule?
>
> -Kevin
>
> --
> Sent via pgsql-bugs mailing list (pgsql-b(dot)(dot)(dot)(at)postgresql(dot)org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-bugs- Hide quoted text -
>
> - Show quoted text -

It probably depends on the timeout you care about :) In our case,
network problems are practically impossible. What is really important
for us, that some long running queue do not create a snowball of long
running and waiting queues bringing the DB server down with 3 digit
load on the server machine. So the only reasonable way to do it was to
set a 'fuse' like STATEMENT_TIMEOUT for the whole server (it is also
possible to set it only for the session of some particular user role)
and fine tune some of the timeouts for complex queries from JDBC, in
some special cases. And it is easier to implement anyway, then a
monitoring thread... especially if you are using third party
connection pooling. :)

Thanks for the tip anyway, as I was not really considering such a
thread at all... Actually such a thread should be probably implemented
not by the JDBC driver itself, but by the connection pooling
libraris... there are normally several monitoring threads there
anyway.

With best regards,

-- Valentine