sqlite3_progress_handler for query timeouts

[zisismaras]

Hello,

I am looking for a way to interrupt/timeout "long" running queries. I followed the discussion on #568 and it seems(?) sqlite3_progress_handler is the best way to go since sqlite3_interrupt will be difficult to work with in a synchronous setting and killing and restarting the whole processing thread might be too expensive to be usable.

For example to cancel a query longer than 1s i was thinking of something like this:

//actual API can vary, just an example
const start = Date.now();
db.progressHandler(function() {
  if (Date.now() > start + 1000) {
    return 1;
  } else {
    return 0;
  }
});
//run query
//unset handler

Do you think something like this could work?
If so, would it be possible to add it (for a fee)?

Thanks a lot for your work!

[Prinzhorn]

restarting the whole processing thread might be too expensive to be usable.

We are talking about multiple orders of magnitude here. If you kill a long running query after 1s timeout (that has been tightly consuming CPU and I/O), what is a nano second or even if it's a millisecond it takes to create a new thread compared to the 1000ms of timeout?

Anyway, I think this could be a useful addition. But I wouldn't attach it to Database, from an architectural point of view it would make much more sense to attach this to the Statement. And since only a single Statement can execute at any given time (thanks to the synchronous architecture), the following API might make sense: add an optional callback argument to each of run, get and all. If the callback exists, it will become sqlite3_progress_handler. If not, sqlite3_progress_handler will be set to NULL. This will also take care of cleaning the progress handler up when the next statement runs. Same for db.exec I guess.

This means you can use sqlite3_progress_handler exactly where and when you need it and not worry about global state (e.g. your unset handler comment).

[Prinzhorn]

Also please don't use Date.now() for any critical tasks. It's not a monotonic clock. Leap seconds and NTP exist.

https://stackoverflow.com/questions/46964779/monotonically-increasing-time-in-node-js

Also why didn't you comment on #568 directly? We already concluded that sqlite3_progress_handler might be the way to go.

Also I can't speak for Joshua 😄

[zisismaras]

Thanks for your input (and the Date advice!)

Just throwing an extra idea, maybe a hooks namespace in case you want to support more hooks in the future like commit/rollback/update/authorizer.

Sorry about the duplicate issue, you are right. I can close it and move the discussion there if you'd like.

Thanks again

[JoshuaWise]

Duplicate of #568 🤷‍♂️