Asyncify and sqlite3_interrupt

[twoxfh]

This is a pretty cool implementation of sqlite wasm. I am looking to abort long running queries. I saw your Faq page about asyncify and wondered if I use it and implement sqlite3_interrupt should I be able to achieve aborting long queries? Thanks for any pointers and heads up.

[rhashimoto]

Please use Discussions for topics that are not Issues.

I could be wrong, but I suspect that sqlite3_interrupt will not work for a couple reasons. First, the wa-sqlite builds do not have threading enabled so I'm not certain whether it would be supported in SQLite itself. Second, there are some Emscripten run-time guards that prevent calling an Asyncify function while another Asyncify function is suspended, and that might come into play.

However, if the sqlite3_interrupt function does not work, another possible approach is to write a custom VFS that returns an error code from an I/O operation in order to abort a query. That won't always work - e.g. if the query doesn't need to make any I/O calls - but I expect those cases to be atypical in practice.

[rhashimoto]

I did not know about sqlite3_progress_handler()! This is an alternative way to interrupt a running query. It registers a callback that is called during query execution, approximately every so many database engine ops (you specify how many). If your callback returns a non-zero value, then the query is aborted.

It's not really storage related, but I implemented it in wa-sqlite anyway. You do need to be aware that if you are using a non-Asyncify build (and maybe even if you aren't) then you need a synchronous way to decide whether you want to interrupt (e.g. a fixed maximum elapsed time, or a user action communicated via Atomics/SharedArrayBuffer).