Abort running query #461

Activity

1. lovasoa commented on Jun 19, 2021

   

   lovasoa

   on Jun 19, 2021

   Member

   More actions

   It is not possible to abort a running query in the current sql.js. Your best bet for the moment is to kill the worker.

   You could try to implement that using emscripten's pthread support and sqlite3_interrupt, but this would probably be a lot of work, make the library slower, require you to serve your pages with custom headers, and not work on all browsers...
2. rhashimoto commented on Jun 20, 2021

   

   rhashimoto

   on Jun 20, 2021

   Last edited by rhashimoto

   More actions

   Note that you can interrupt a query between output rows, simply by not continuing to call step() for the next row. The problem is interrupting a query in the middle of a step, e.g. for very large aggregation queries.

   I think there might be one way to do that with the current sql.js, though it is pretty limited. If you use create_function to define a custom SQL function, then throwing an exception from that function looks like it will abort the query that uses it. You would have to construct your query to call the function repeatedly, and the function needs to decide synchronously whether or not to throw. So perhaps technically possible for some narrow use cases.

   The easiest way to implement and use this interrupting function would be to make it just return its argument:

   function interruptible(x) {
     if (checkForInterrupt()) {
       throw new Error('interrupt');
     }
     return x;
   }

   Then just wrap one of the SELECT expressions in the SQL query:

   SELECT SUM(interruptible(x)) FROM my_large_view;

   Alternatively, if you're not opposed to forking sql.js and hacking the SQLite C source, you could find a spot in the SQLite code to insert a call to Javascript and conditionally return an error code (somewhere in the pager might work). Basically a similar approach as above but one that works with any query.

   👍React with 👍2Reacted by Ophir LOJKINE and twoxfh
3. twoxfh commented on Jun 21, 2021

   

   twoxfh

   on Jun 21, 2021

   Last edited by twoxfh

   Author

   More actions

   Thanks @lovasoa and @rhashimoto great insight. I was able to make a sub select as a start time, use time in the main select, calculate the difference in time, then throw the Error which halted the query. Convoluted but worked.

   Thanks, hopefully over time it wont get so convoluted.

   🎉React with 🎉2Reacted by Roy Hashimoto and Ophir LOJKINE
4. twoxfh

   closed this as completedon Jun 21, 2021