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

[gontard]

Hello.
I am trying to achieve that with Doobie, and it seems impossible for now. Has anyone found a workaround?

[TalkingFoxMid]

Conversation and possible fix are there #2077

[jatcwang]

#2079 and #2088 should address cancellation for the vast majority of the cases.

[TalkingFoxMid]

@jatcwang Hello! How soon we could see these fixes in a new RC version ?

[jatcwang]

@TalkingFoxMid Hey sorry releasing it currently blocked because the Sonatype password (API token) needs to be regenerated but that's something only Rob can do. I've reached out a couple of days ago and he said he'll look into it.
Worst case we'll have to do the typelevel org migration from 1.0-RC5's commit, then cherrypick all PRs over.

[armanbilge]

Worst case we'll have to do the typelevel org migration from 1.0-RC5's commit, then cherrypick all PRs over.

We don't need to cherry-pick, when we migrate we'll just move the entire repository over including PRs. Actually if we do this it will fix publishing, since the Typelevel publishing credentials now have permissions for org.tpolecat as well. So that would be my recommendation.

[jatcwang]

Oh nice @armanbilge I didn't know that typelevel org can publish to org.tpolecat. Let's do that then!

[TalkingFoxMid]

@jatcwang Hello! Sorry but I don't fully understand what the further plan is for rolling out version 1.0-RC6. Please explain what is the next step

[jatcwang]

Hi @TalkingFoxMid sorry for the delay. I just merged the final PR that I think needs to go into 1.0-RC6.
Can you give 1.0-b5b234b-SNAPSHOT a try and see if you hit any issues? (You might need to add resolvers ++ Opts.resolver.sonatypeOssSnapshots in SBT settings as it's a snapshot release).

If you don't find any big issues, I'll tag 1.0-RC6 tomorrow.

[jatcwang]

@TalkingFoxMid 1.0-RC6 has been released. Thanks for your help!