Description
Currently it is not possible to cancel queries in scalasql. It would be nice to support this feature since cancellation is a big deal in Cats-Effect and generally useful for long running queries. The easiest way to support cancellation transparently would be through the InterruptableException mechanism. However, to cancel a running query, stmt.cancel() needs to be called on the JDBC Statement and this necessarily has to happen on another thread because the primary thread is blocked waiting for the stsmt.executeUpdate(). Starting threads inside scalasql is not very performant and probably against the design principles of this library, so I propose instead to add something like a def transactionCancellable[T](block: DbApi.Txn => T)(implicit ec: ExecutionContext): (Future[T], /*cancel*/ () => Unit) function to DbClient. This will allow users to control where the thread is started through ExecutionContext. The standard blocking def transaction could perhaps be implemented with ExecutionContext.parasitic.