Add cancelQuery() from BaseConnection to PGConnection

[davidtinker]

I am using this method to get background threads running queries to stop e.g. when the result of the query is no longer needed. I know you can cancel at a statement level but that requires control over the code running the queries and introduces unnecessary coupling. Keeping track of the connection being used by the background operation is much simpler.

Original Stackoverflow POST

[vlsi]

The sad thing with connection.cancel() is you never know what gets cancelled.
For instance, the query you did want to cancel might pass trough, the connection might be reused for another transaction, and then cancel is executed and it might blow an innocent operation (<-- I feel like sequence of tenses is off by a large margin there, and I hope it is still understandable)

Java API does have "nuke the connection" option known as Connection#abort(...).

Connection#cancel() might sound like a safer thing, however it does bother me that it provides no way to limit the damage.

I think we need to have some scoping, so Connection#cancel() is not a random "cancel whatever you happen to cancel at random time", but something like "cancel task X, and ignore the cancel request if task X terminated before cancel request started processing".

For instance:

// Thread 1
Connection con = ...;
Globals.cancelTicket = con.getCancelTicket();
doLongOperation();
Globals.cancelTicket.complete(); // marks the ticket as completed, so the cancel from a concurrent thread would be ignored


// Thread2
Globals.cancelTicket.cancel(); // Cancels the operation. In case the operation has completed earlier, the cancel is just ignored

The interesting thing is Java 9 does have Connection#beginRequest() and Connection#endRequest(): https://docs.oracle.com/javase/9/docs/api/java/sql/Connection.html#beginRequest-- , but I don't see how it enables us to associate cancel with a specific "request".

@davidtinker , any thoughts?

[vlsi]

Just in case: the alternative approach could be to make "socket processing" interruptible.

For instance, socket.read could be replaced with timed read, and it might verify Thread.interrupted state from time to time, so it could bail out in case interrupt is detected.

[davidtinker]

I solved the "cancel whatever is randomly running" problem by ensuring that only the code that acquires and releases the connection does the cancelling. So once the connection has been released it won't get cancelled.

[davecramer]

fixed in #1157