Cancel the DB side query when doobie execution is cancelled

[jatcwang]

If we run a long running query but then cancel it (e.g. application-side timeout or IO.race), we might want to cancel the DB side query by calling PreparedStatement#cancel(). (Simply calling close() which we currently do does not seem to cancel the query on the DB-side)

e.g. https://discord.com/channels/632277896739946517/632727524434247691/1151507294664786021

Will need some more thought on whether this is universally desirable, and if not we'll need to make this configurable somehow (e.g. opt-in or opt-out)

[armanbilge]

if not we'll need to make this configurable somehow (e.g. opt-in or opt-out)

Repeating my comment from Discord: a user can always opt-out of cancelation by adding .uncancelable.

[AlekseyKovalevsky]

Hello! Can I as a user customise transactor somehow from the outside to get the behaviour mentioned? Just not to wait for this feature to be implemented.

[jatcwang]

@AlekseyKovalevsky In plain Java/JDBC, what needs to be called is PreparedStatement#cancel, but this has to be done from another thread (since JDBC operations are blocking). Therefore in order to do the same in Doobie I think we'll need to fork a fiber to cancel the preparedstatement when a cancellation is signaled from the caller of .transact.

[armanbilge]

Actually this is a perfect usecase for the cancelable combinator in Cats Effect.

https://github.com/typelevel/cats-effect/blob/85915dd87382fe5e50099f724d64ecc7e35d8051/kernel/shared/src/main/scala/cats/effect/kernel/GenSpawn.scala#L219-L269

https://typelevel.org/cats-effect/docs/faq#how-do-i-cancel-or-timeout-delay-or-blocking

https://github.com/typelevel/fs2/blob/41521428769dda218863e2d26c322a48c64ba1c9/io/jvm/src/main/scala/fs2/io/net/unixsocket/UnixSocketsPlatform.scala#L103-L104