Implementation of query timeout

[Radosław Smogura-4]

Hello,

I send this message again, because there were problems with my email address,
so it didn't reach mailinglist.

I read a TODO list, and I decided to help a bit. So I've tried to implement
queryTimeout, currently ony for non-batch statements. You can see code, at
http://rsmogura.net/pgsql/pgsql_jdbc_querytimeout.tar.bz2
and analyze, chceck and test it.

Small description how it works.

1. When the query is executed it checks if timeout > 0.
2. If yes it creates TimerTask and add it to the queue, then sends the query
to server.
3. On timeout and if statement hasn't ended cancelStatement is called().

I've used Timer to don't create multiple threads with each statement. The
AbstractJdbc2 have setQueryTimeout method, so DataSources and
PolledConnections can utilze it to set Timer shared for all connections.

The QueryExecutor has new method executeSynchronized (description in API). To
prevent termination on next statement & run statements in current thread, the
lock on QueryExecutor is needed (so no other thread will send other query
until cancelStatement is fully called). This method can be more usefull then
synchronized(...) block and can be used later.

Some new utilities class has been added.

The test code creates non tempolary tables to simulate long-run by creating
data lock on two transactions.

If the JDBC will go to Java 5, some of this code can use new
java.util.concurent API.

Kind regards,
Radek Smogura

--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

[Albe Laurenz *EXTERN*]

Radoslaw Smogura wrote:

> I read a TODO list, and I decided to help a bit. So I've tried to implement
> queryTimeout, currently ony for non-batch statements. You can see code, at
> http://rsmogura.net/pgsql/pgsql_jdbc_querytimeout.tar.bz2
> and analyze, chceck and test it.
>
> Small description how it works.
>
> 1. When the query is executed it checks if timeout > 0.
> 2. If yes it creates TimerTask and add it to the queue, then sends the query
> to server.
> 3. On timeout and if statement hasn't ended cancelStatement is called().

Maybe I am missing something there, but wouldn't it be much easier
to send the following to the server:

SET statement_timeout=<n>

Then all statements that time out throw an exception with SQLSTATE 57014,
and there's no need for separate threads and canceling the query.

Yours,
Lauernz Albe

--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

[Radosław Smogura-4]

Dnia piątek 30 październik 2009 o 08:47:02 napisałeś:

> Radoslaw Smogura wrote:
> > I read a TODO list, and I decided to help a bit. So I've tried to
> > implement queryTimeout, currently ony for non-batch statements. You can
> > see code, at http://rsmogura.net/pgsql/pgsql_jdbc_querytimeout.tar.bz2
> > and analyze, chceck and test it.
> >
> > Small description how it works.
> >
> > 1. When the query is executed it checks if timeout > 0.
> > 2. If yes it creates TimerTask and add it to the queue, then sends the
> > query to server.
> > 3. On timeout and if statement hasn't ended cancelStatement is called().
>
> Maybe I am missing something there, but wouldn't it be much easier
> to send the following to the server:
>
> SET statement_timeout=<n>
>
> Then all statements that time out throw an exception with SQLSTATE 57014,
> and there's no need for separate threads and canceling the query.
>
> Yours,
> Lauernz Albe
>

Hello,
SET statement_timeout only set timeout for statement processing on server, it
not include the time of transfering data, which can be long for BLOBs. I've
found few posts that this is not enaugh solution "SET statement_timeout" from
above reasons. It's way those timeout is implemented in JDBC side.

Kind regards,
Radek Smogura.

--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

[Radosław Smogura-4]

Hi,

Under http://rsmogura.net/pgsql/pgsql_jdbc_querytimeout_v2_20091112.tar.bz2 
you can find newer version of statement timeout, witch is bettered propsal of
solution, but still only from non-batch statements.

Kind regards,
Radek Smogura


--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc