timeout implementation issues

I have been talking with Bruce Momjian about implementing query
timeouts in the JDBC driver. As things stand, you can call
setQueryTimeout() or getQueryTimeout(), but a slow query will never
actually timeout, even if a timeout is set. The result of a timeout
should be a SQLException.

Bruce feels that this should be implemented in the backend: set an
alarm() in the backend on transaction start, then call the query
cancel() code if the alarm() goes off, and reset the alam if the query
finishes before the timeout.

I am concerned that this method does not provide a means of triggering
the SQLException in the driver. For an example, look at how cancel is
implemented (org.postgresql.Connection::cancelQuery()): we create a
new PG_Stream and send some integers to it which represent the cancel
request. Then we close the PG_Stream. There is no point at which we
receive any notification from the backend that the query has been
cancelled.

I looked in postmaster.c, processCancelRequest() to see what the
backend does. A SIGINT is sent to the backend when the cancel request
is successfully fulfilled, but nothing seems to be sent to the
interface.

One possibility is that the driver might just notice that the connection
has closed, and throw an Exception then. javax.sql.PooledConnection has an
addConnectionEventListener() method; we could add a
ConnectionEventListener there which would throw an Exception when the
connection closes.

In practice, this may or may not be a good idea. The place to get hold
of a PooledConnection seems to be in XAConnectionImpl (I am not sure
how the driver would actually request the relevant XAConnectionImpl
object, but I am sure I could figure that out). The thing is that this
class only allows one ConnectionEventListener to be set, so if we set
it, the user would be out of luck if he wanted to add his own
listener.

My proposal, then, is that the Java driver should submit the
transaction request; wait for the timeout; if it goes off, submit a
cancel request; and then throw a SQLException. We would not handle
this in the backend at all.

Bruce agreed that this was a good point to ask what the rest of the
hackers list thought. Any input?

Thanks,
Jessica