Thread

setQueryTimeout in JDBC driver seems unimplemented

Andriy Rozeluk <arozeluk@home.com> — 2000-12-14T00:02:44Z

Hello!

I've been working with PostgreSQL using Java and I've just noticed a
peculiar thing. At the moment, I'm assuming that this is unimplemented,
but it should probably be looked at.

Specifically, the interface javax.sql.Statement defines a method called
setQueryTimeout which is, of course, used to abort a long-running query
(basically a search on a very large table, on a field that isn't
indexed, which was my test case) if it runs past a certain limit (#
seconds). Unfortunately, calling this method had absolutely no effect on
the operation - I had a test query which runs several hours
(theoretically) that never ended up dying.

Even worse, I aborted the Java application I used to test this behaviour
and the query was still executing on the server - a dangerous behaviour
I think. For the purpose of web serving it means that if a server ever
gets bogged down then it has to wait for ALL of the queries to finish
before it can right itself again, rather than just killing off the ones
that have been running too long.

It also means that there's no chance of me being able to report back to
the user that the server is bogged down, because control is not returned
to the application until the query has completed. If there's 100
long-running queries, the server will be bogged down until they're all
done, whether or not I care if they return any data.

I was only able to cancel the query (the one that still ran after my
application had been killed), by logging into the database server and
killing the process (noticing that it was taking up about 80% of the CPU
- cool!). So if my server gets /.'d, the queries (if I get enough of
them at once) could take hours, or even days to complete without my
intervention. Even worse, a user is likely to hit "reload" on their
browser a few times before they give up, effectively creating a few more
minutes' worth of queries.

I would strongly suggest that someone take a look at this. I hope
PostgreSQL supports killing a running query, because this is a critical
issue for me (and probably anyone who writes web-based or otherwise
connection-based apps). My suggestions, then, are:

1. implement the setQueryTimeout() method in the PostgreSQL JDBC driver
2. if not already implemented, please do the same to the cancel(). If #1
isn't easy to do, at least it'll give me the opportunity to have a
'watcher' thread to kill the query.
    I'm guessing that you need this to do #1 anyway.
3. have something call the Statement's cancel() method if/when the
statement is getting garbage collected ( destroy(), I think ). This
would kill the query if I kill the server or application which initiated
it.

I thank you all for your hard work - the PostgreSQL server is quite
excellent and I'm looking forward to using it more! (I'm nearing
complete conversion from MS-SQL)
-Andriy Rozeluk