SQLite queries are uninterruptible

[chris-martin]

I think that when a SQLite backend is being accessed, queries are not killable by async exceptions (because, generally, FFI is not interruptible). This means that e.g. WAI timeout middleware is ineffective for a sqlite-backed web server.

SQLite provides a means of interrupting a query:

void sqlite3_interrupt(sqlite3*);
int sqlite3_is_interrupted(sqlite3*);

and some precedent of using this in Haskell can be find in direct-sqlite:

interrupt :: Database -> IO ()
interruptibly :: Database -> IO a -> IO a

... where I believe all three of these are equivalent:

• Database in Database.SQLite3 in the direct-sqlite package
• Connection in Database.Sqlite.Internal in the persistent-sqlite package
• sqlite3* in the SQLite C library

I'm about to attempt to mimic what interruptibly does to make long-running queries killable in my own application. I thought I should also inquire here:

• Does this explanation and solution all make sense?
• Would there be any interest in incorporating this solution into the persistent-sqlite package? I think running queries interruptibly should be the default behavior. I would be happy to submit a pull request.

I am not sure if there is an obvious place to insert a solution, but at the very least the persistent-sqlite package could offer documentation on the issue and some utilities for making the interrupt call.

[chris-martin]

Suggestion from @pbrisbin to add an analogue to the Database.Persist.Sql function

runSqlPoolWithExtensibleHooks :: forall backend m a.
  (MonadUnliftIO m, BackendCompatible SqlBackend backend)
  => ReaderT backend m a
  -> Pool backend
  -> Maybe IsolationLevel
  -> SqlPoolHooks m backend
  -> m a

to Database.Persist.Sqlite, which I imagine would look something like:

runSqlPoolInterruptibleWithExtensibleHooks :: forall m a.
  MonadUnliftIO m
  => ReaderT (RawSqlite SqlBackend) m a
  -> Pool (RawSqlite SqlBackend)
  -> Maybe IsolationLevel
  -> SqlPoolHooks m (RawSqlite SqlBackend)
  -> m a