Query cancellation/timeout support

[penberg]

If a query takes a long time, applications may want to cancel them either manually or via a timeout mechanism. We should be able to use the sqlite3_progress_handler() to implement this where SQLite periodically checks if it's OK to keep running. We would have to track the execution time of individual queries.

Depends on #122 for infrastructure on query timing.

[haaawk]

Is this fixed @penberg or is there anything left for @levydsa to do?

[penberg]

@haaawk I only implemented brute force canceling all ongoing operations. We still need to do timeouts, for example.

[haaawk]

How are we going to allow cancelation of a single queries @penberg? by making sure there's at most a single operation at each connection so that we can be sure that when we interrupt that connection we cancel a particular query?

[haaawk]

Here's the idea @levydsa @LucioFranco and me have about doing this (based on https://github.com/tursodatabase/libsql-js/blob/main/src/database.rs):

1. We add running_queries_count and running_cancellable_query to Database possibly atomics
2. Every time js_exec_sync or js_exec_async run they increase running_queries_count and every time they finish they decrease it
3. Both js_exec_sync or js_exec_async check that running_cancellable_query is false and if not they error out saying that there can't be any other query while a query with timeout is running
4. We add js_exec_sync_with_timeout and js_exec_async_with_timeout which set running_cancellable_query to true when starting and to false after they finish
5. Both new methods check that running_cancellable_query is false and that running_queries_count is 0 or they error out saying that query with timeout can't run in parallel to other queries
6. Both new methods spawn a timer that if run calls interrupt on the connection and sets running_cancellable_query to false. We need to make sure that the timer is cancelled when the query finishes successfully.

This way we make sure there's at most one cancellable query on the connection and we can safely interrupt it.

[penberg]

@haaawk I am not sure I understand the proposal. How will you interrupt a query that is running within the SQLite VDBE? See the Database.interrupt() test case in integration-tests/tests/async.test.js, for example, which is essentially an infinite loop within VDBE.

[penberg]

I still think (ab)using the progress handler is better here and just document the limitation that it only works for a single query at a time.

[haaawk]

@haaawk I am not sure I understand the proposal. How will you interrupt a query that is running within the SQLite VDBE? See the Database.interrupt() test case in integration-tests/tests/async.test.js, for example, which is essentially an infinite loop within VDBE.

I would implement it in https://github.com/tursodatabase/libsql-js/blob/main/src/database.rs in js_exec_async_with_timeout by spawning a timeout that calls conn.interrupt. Wouldn't it work?