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

The following bug has been logged online:

Bug reference: 4123
Logged by: Amit Mujawar
Email address: amit(dot)mujawar(at)gmail(dot)com
PostgreSQL version: 8.1
Operating system: Windows XP
Description: Statement.setQueryTimeout does not work with Postgres
Java Driver
Details:

I am using PostgreSQL through JDBC
PostgreSQL 8.1, Driver - org.postgresql.Driver 8.1-408.jdbc3

When I set Statement.setQueryTimeout, the timeout value does not show any
effect on actual timeout...The query blocks for a specific time always [may
be configured by another global variable - statement_timeout? not sure]

I suspect there is a problem with JDBC driver implementation for
setQueryTimeout API.

On Tue, 22 Apr 2008, Amit Mujawar wrote:

>
> The following bug has been logged online:
>
> Bug reference: 4123
> PostgreSQL version: 8.1
> Description: Statement.setQueryTimeout does not work with Postgres
> Java Driver
> Details:
>
> I am using PostgreSQL through JDBC
> PostgreSQL 8.1, Driver - org.postgresql.Driver 8.1-408.jdbc3
>
> I suspect there is a problem with JDBC driver implementation for
> setQueryTimeout API.
>

setQueryTimeout is not implemented at all. Newer drivers (8.3+) will
throw an exception telling you that if you try to call setQueryTimeout
while older drivers silently accept the value and do nothing.

Kris Jurka

On Apr 22, 12:05 pm, amit(dot)muja(dot)(dot)(dot)(at)gmail(dot)com ("Amit Mujawar") wrote:
> The following bug has been logged online:
>
> Bug reference:      4123
> Logged by:          Amit Mujawar
> Email address:      amit(dot)muja(dot)(dot)(dot)(at)gmail(dot)com
> PostgreSQL version: 8.1
> Operating system:   Windows XP
> Description:        Statement.setQueryTimeout does not work with Postgres
> Java Driver
> Details:
>
> I am using PostgreSQL through JDBC
> PostgreSQL – 8.1, Driver - org.postgresql.Driver 8.1-408.jdbc3
>
> When I set Statement.setQueryTimeout, the timeout value does not show any
> effect on actual timeout...The query blocks for a specific time always [may
> be configured by another global variable - statement_timeout? not sure]
>
> I suspect there is a problem with JDBC driver implementation for
> setQueryTimeout API.
>

It is in the TODO list of the driver to be implemented. Actually the
TODO list for Postgres JDBC It is a good place to see what the
features you cannot use :-)

I am setting the timeout by the acquisition of the connection by the
pool (in ConnectionCustomizer in C3P0 pooling library) like that:

PreparedStatement s = null;
ResultSet rs = null;
try {
s = c.prepareStatement("SELECT set_config('statement_timeout', ?,
false);" );
s.setInt(1, 35000);
rs = s.executeQuery();
if ( rs.next() ) {
String newTimeout = rs.getString(1);
if ( logger.isInfoEnabled() ) {
logger.info("STATEMENT_TIMEOUT set to '" + newTimeout + "' (" +
parentDataSourceIdentityToken + ")");
}
} else {
if ( logger.isErrorEnabled() ) {
logger.error("STATEMENT_TIMEOUT could not be set! (" +
parentDataSourceIdentityToken + ")");
}
}
} catch (SQLException e) {
if ( logger.isErrorEnabled() ) {
logger.error("STATEMENT_TIMEOUT could not be set! (" +
parentDataSourceIdentityToken + ")", e);
}
} finally {
if ( rs != null ) rs.close();
if ( s != null ) s.close();
}

if you want to do it before you start some transaction, you can bring
this code into a function like Utils.setStatementTimeout(Connection c,
boolean isTransactionLocal) and call it after
Connection.setAutoCommit(false);

On Apr 22, 5:53 pm, bo(dot)(dot)(dot)(at)ejurka(dot)com (Kris Jurka) wrote:
> On Tue, 22 Apr 2008, Amit Mujawar wrote:
>
> > The following bug has been logged online:
>
> > Bug reference:      4123
> > PostgreSQL version: 8.1
> > Description:        Statement.setQueryTimeout does not work with Postgres
> > Java Driver
> > Details:
>
> > I am using PostgreSQL through JDBC
> > PostgreSQL – 8.1, Driver - org.postgresql.Driver 8.1-408.jdbc3
>
> > I suspect there is a problem with JDBC driver implementation for
> > setQueryTimeout API.
>
> setQueryTimeout is not implemented at all.  Newer drivers (8.3+) will
> throw an exception telling you that if you try to call setQueryTimeout
> while older drivers silently accept the value and do nothing.
>
> Kris Jurka
>
> --
> 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 -

Hi Kris,

wanted to ask you that all the time, but never had found time to do
that.

Is it possible to implement the setStatementTimeout() as somethig
like:

s = c.prepareStatement("SELECT set_config('statement_timeout',
<neededTimeoutInMilliseconds>, false);" );
s.executeQuery();
c.commit();

With best regards,

-- Valentine Gogichashvili

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.

Kris Jurka

Ok, understood... that could not be so easy anyway :) I supposed that
it should be something, that lays in the JDBC specs...

Regards,

-- Valentine

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

>>> On Fri, Apr 25, 2008 at 11:59 AM, in message
<e9a628e9-fa81-44a8-a49b-7b9efc235875(at)s50g2000hsb(dot)googlegroups(dot)com>,
valgog
<valgog(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

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