Re: [sqlite] Using sqlite3_interrupt with a timeout
On 31 Dec 2018, at 10:44pm, Jesse Rittner wrote: > Simon Slavin-3 wrote > >> it can be long only if you're a bad SQL programmer > > To be fair, the query in question might not get run frequently enough to > warrant the overhead of maintaining an index. Fair point. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using sqlite3_interrupt with a timeout
Simon Slavin-3 wrote > it can be long only if you're a bad SQL programmer To be fair, the query in question might not get run frequently enough to warrant the overhead of maintaining an index. -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using sqlite3_interrupt with a timeout
On 31 Dec 2018, at 9:10pm, Jesse Rittner wrote: > Simon Slavin-3 wrote > >> You can calculate an "end time" yourself, check it each time you're ready to >> call sqlite3_step(), and jump straight to sqlite3_finalize() if your time is >> up. > > I'm not familiar with the inner workings of sqlite3_step, but if that itself > takes time, then I'd like to interrupt the call prematurely. [The following is simplified for clarity.] The first call to sqlite3_step() can take time if there's no good index for your operation. It may have to construct its own temporary index. Of course, creation of a temporary index will happen only if you haven't thought through your clauses (WHERE, GROUP BY, ORDER BY, Santa) and created a good index that deals with them all. If you have a 100 Gig table with no indexes suited to your clauses, creating a temporary index might take a minute or two. But you really only have yourself to blame. Subsequent calls to sqlite3_step() rely on the preparation having been done. They just step through an index (permanent or temporary) which already exists. Such calls are very fast. Since the one possibly-long operation happens at the very beginning of the command, and it can be long only if you're a bad SQL programmer, it's unlikely that your timeout will happen during it. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using sqlite3_interrupt with a timeout
>I don't think the interrupt call will actually terminate a step that >is actually being processed, but only mark that no more steps should >happen. In other words, I don't think SQLite is spending time >checking a flag to stop in the middle of processing a step to allow the >processing to terminate early without a result. Actually it does. The callback and the interrupt flags are checked at the bottom of each loop. I haven't looked through the source to see if the sorter is interruptible or not though ... and the Vdbe halt code will "clean up" from the interruption (as if it were an error). --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using sqlite3_interrupt with a timeout
On 12/31/18 4:10 PM, Jesse Rittner wrote: > Simon Slavin-3 wrote >> You can calculate an "end time" yourself, check it each time you're ready >> to call sqlite3_step(), and jump straight to sqlite3_finalize() if your >> time is up. > I'm not familiar with the inner workings of sqlite3_step, but if that itself > takes time, then I'd like to interrupt the call prematurely. > I don't think the interrupt call will actually terminate a step that is actually being processed, but only mark that no more steps should happen. In other words, I don't think SQLite is spending time checking a flag to stop in the middle of processing a step to allow the processing to terminate early without a result. -- Richard Damon ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using sqlite3_interrupt with a timeout
That is, of course (I forgot the stmt argument to sqlite3_stmt_busy in all the whizing around): def interrupt_function(db, stmt, timeout, whizround) while whizround and !sqlite3_stmt_busy(stmt) /* whizround waiting for statement to start */ sleep(0.001) sleep(timeout) /* wait for our timeout */ if sqlite3_stmt_busy(stmt) /* if statement is running */ sqlite3_interrupt(db) /* interrupt it */ def run_query_with_timeout(db, query, timeout, whizround) stmt = prepare(db, query) create_thread A interrupt_function(db, stmt, timeout, whizround) while sqlite3_step(stmt) == SQLITE_ROW ... process the row ... cancel_thread A join_thread A /* make sure the thread is ended */ sqlite3_finalize(stmt) --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using sqlite3_interrupt with a timeout
On Monday, 31 December, 2018 13:48, Simon Slavin wrote: >On 31 Dec 2018, at 8:18pm, Jesse Rittner wrote: >> I'm trying to write a function to run a query with a timeout. If >> the timeout expires, the query must stop execution within a "reasonable" >> amount >> of time. >There is no rule that you must continue to call sqlite3_step() until >it runs out of rows. You can calculate an "end time" yourself, check >it each time you're ready to call sqlite3_step(), and jump straight >to sqlite3_finalize() if your time is up. >No threads, no interrupts, no clearup of hooks. This will not work for queries which do not return rows immediately. The query may be trying to calculate the meaning of life and take quite a while before returning its result (and the query writer may not have had the benefit of reading The Guide and knowing that the answer is 42 and writing the query as SELECT 42; and instead resorted to brute force methods taking many aeons to compute). --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using sqlite3_interrupt with a timeout
On Monday, 31 December, 2018 13:19, Jesse Rittner wrote: >Keith Medcalf wrote >> What are you trying to accomplish? Perhaps what you really want is >> a progress callback? > I'm trying to write a function to run a query with a timeout. If the > timeout expires, the query must stop execution within a "reasonable" amount > of time. > To use a progress callback, it sounds like I'd have to choose a small > enough N and poll some kind of "timed out" flag, which sounds undesirable. > If instead of sqlite3_interrupt, we had sqlite3_begin_interrupt and > sqlite3_end_interrupt, that would meet my needs a lot better. Then I > would > just do the following: > 1. Have thread 1 call sqlite3_step. > 2. Have thread 2 sleep for whatever timeout, then call > sqlite3_begin_interrupt. > 3. Once sqlite3_step returns, have thread 1 signal thread 2. > 4. Have thread 2 call sqlite3_end_interrupt. > Then there would never be a race condition because the interrupt > remains in effect even while there are no running statements. > Alternatively, having some sort of object to pass into sqlite3_step > would also work, as then I could just call some sort of cancel method on > that object and have it interrupt that call only. If the timeout is so short that you need to make sure that a query is actively running, then simply wait for it it be running if necessary: def interrupt_function(db, stmt, timeout, whizround) while whizround and !sqlite3_stmt_busy /* whizround waiting for statement to start */ sleep(0.001) sleep(timeout) /* wait for our timeout */ if sqlite3_stmt_busy(stmt) /* if statement is running */ sqlite3_interrupt(db) /* interrupt it */ def run_query_with_timeout(db, query, timeout, whizround) stmt = prepare(db, query) create_thread A interrupt_function(db, stmt, timeout, whizround) while sqlite3_step(stmt) == SQLITE_ROW ... process the row ... cancel_thread A join_thread A /* make sure the thread is ended */ sqlite3_finalize(stmt) --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using sqlite3_interrupt with a timeout
Simon Slavin-3 wrote > You can calculate an "end time" yourself, check it each time you're ready > to call sqlite3_step(), and jump straight to sqlite3_finalize() if your > time is up. I'm not familiar with the inner workings of sqlite3_step, but if that itself takes time, then I'd like to interrupt the call prematurely. -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using sqlite3_interrupt with a timeout
On 31 Dec 2018, at 8:18pm, Jesse Rittner wrote: > I'm trying to write a function to run a query with a timeout. If the timeout > expires, the query must stop execution within a "reasonable" amount of time. There is no rule that you must continue to call sqlite3_step() until it runs out of rows. You can calculate an "end time" yourself, check it each time you're ready to call sqlite3_step(), and jump straight to sqlite3_finalize() if your time is up. No threads, no interrupts, no clearup of hooks. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using sqlite3_interrupt with a timeout
Keith Medcalf wrote > What are you trying to accomplish? Perhaps what you really want is a > progress callback? I'm trying to write a function to run a query with a timeout. If the timeout expires, the query must stop execution within a "reasonable" amount of time. To use a progress callback, it sounds like I'd have to choose a small enough N and poll some kind of "timed out" flag, which sounds undesirable. If instead of sqlite3_interrupt, we had sqlite3_begin_interrupt and sqlite3_end_interrupt, that would meet my needs a lot better. Then I would just do the following: 1. Have thread 1 call sqlite3_step. 2. Have thread 2 sleep for whatever timeout, then call sqlite3_begin_interrupt. 3. Once sqlite3_step returns, have thread 1 signal thread 2. 4. Have thread 2 call sqlite3_end_interrupt. Then there would never be a race condition because the interrupt remains in effect even while there are no running statements. Alternatively, having some sort of object to pass into sqlite3_step would also work, as then I could just call some sort of cancel method on that object and have it interrupt that call only. -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using sqlite3_interrupt with a timeout
>To be clear, is it sqlite3_step returning SQLITE_DONE that marks it >as "not running", or calling sqlite3_reset/sqlite3_finalize? Well, "running" means that execution has commenced (the first call to sqlite3_step has been made on the statement) and the execution has not yet completed. Execution has completed when either (a) sqlite3_step returns SQLITE_DONE or SQLITE_INTERRUPT or (b) you "reset" the program by calling sqlite3_reset on the statement. In other words the flow is like this: START: set RUNNING to 1 LOOP: do stuff yield a row by returning SQLITE_ROW CARRYON: done? no -> LOOP END: set RUNNING to 0 say we are done by returning SQLITE_DONE >Also, is there any way to mark a statement as "running" other than >calling sqlite_step on it? Otherwise, it sounds like I'll have to wait until >after sqlite3_step gets called the first time to spawn the thread. What are you trying to accomplish? Perhaps what you really want is a progress callback? https://sqlite.org/c3ref/progress_handler.html --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using sqlite3_interrupt with a timeout
To be clear, is it sqlite3_step returning SQLITE_DONE that marks it as "not running", or calling sqlite3_reset/sqlite3_finalize? Also, is there any way to mark a statement as "running" other than calling sqlite_step on it? Otherwise, it sounds like I'll have to wait until after sqlite3_step gets called the first time to spawn the thread. -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using sqlite3_interrupt with a timeout
"RUNNING" means that the statement is running. When you call sqlite3_prepare, SQLite3 generates a PROGRAM which will "yield" the results of the execution of that SQL statement. It looks something like this: START: ... do stuff ... LOOP: ... do stuff ... return a result row (SQLITE_ROW) CARRYON: are we done? no? Go to LOOP yes? Go to END END ... do some cleanup ... return that there are no more rows (SQLITE_DONE) and reset the program When you call sqlite3_step FOR THE FIRST TIME, the program commences "RUNNING" at START. Periodically it may "return" (yield) a result row to you by returning the SQLITE_ROW result code. The program is still running (unless you call sqlite3_reset or sqlite3_finalize on the program). You do your stuff and then call sqlite3_step again to CARRYON execution of the program. It will do some more stuff and return to you again. Lather, Rinse, Repeat. Eventually there will be no more work to be done and when you CARRYON (sqlite3_step) there will not be anything more to do and the program will branch to END, clean itself up, and return to you the SQLITE_DONE return code. After this, the program statement is no longer "RUNNING". At any time the program is in the "RUNNING" state (that is, subsequent to the first call to sqlite3_step and before sqlite3_step returns SQLITE_DONE) you may call sqlite3_interrupt to interrupt the execution of the program. This will cause sqlite3_step to return SQLITE_INTERRUPT rather that SQLITE_ROW or SQLITE_DONE. You can then call sqlite3_reset and/or sqlite3_finalize to "clean up" the interrupted program. Since you can sqlite3_prepare more than one statement (program) on a connection at a time, and you can be executing more than one statement on the same connection at the "same time", it is possible for there to be multiple statements (programs) associated with the connection (which is what you interrupt) in the "RUNNING" state at the same time (that is, you have called sqlite3_step on the statement but have not yet received an SQLITE_DONE return). [NB: Multiple statements may be RUNNING on the same connection at the same time, but only one may be executing at a time] A typical use for sqlite3_interrupt is to attach it to a "STOP" button in your hooey-gooey so that you can "stop" execution of an operation or attach it to a signal (such as the BREAK key) for the same purpose in a non-hooey-gooey console application, or perhaps to SIGHUP or SIGINT or somesuch so that you can stop a runaway operation. sqlite3_interrupt does not "interrupt" the execution of a "step", it interrupts the execution of the stream of steps that are required to execute the statement which has been prepared. You can call sqlite3_interrupt at any time from anywhere (with a few restrictions). FOr example, maybe you want to do this for some reason: sqlite3_prepare() while sqlite3_step() == SQLITE_ROW: get column 47 ... do a bunch of stuff ... if column 47 is "Uh Oh Lets Stop" then sqlite3_interrupt() sqlite3_reset() sqlite3_finalize() --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Jesse Rittner >Sent: Monday, 31 December, 2018 09:03 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] Using sqlite3_interrupt with a timeout > >Consider the following pseudo-code. > >void interrupt_timeout(sqlite3* db, int timeout) { >sleep(timeout); >sqlite3_interrupt(db); >} > >int main() { >sqlite3* db = sqlite3_open_v2(...); >sqlite3_stmt* stmt = sqlite3_prepare_v2(db, ...); > >... > >pthread_create(interrupt_timeout, db, timeout); > >int rv = sqlite3_step(stmt); > >... >} > >(I know this doesn't work properly if sqlite3_step doesn't time out, >but it >suffices for this example.) > >For the purposes of this example, let's suppose that the call to >sqlite3_step takes a while. According to the docs, "New SQL >statements that >are started after the running statement count reaches zero are not >effected >by the sqlite3_interrupt()." But for very small timeouts, it's >possible that >sqlite3_interrupt gets executed /before/ sqlite3_step ever gets >called, in >which case sqlite3_step runs to completion no matter how long it >takes. > >Am I missing something? Is there another way to leverage >sqlite3_interrupt >that doesn't have this race condition? What exactly is meant by >"running >statements"? Is it statements that are in the middle of a call to >sqlite3_step? Statements that have been stepped, but not yet reset? >Something else? > > > >-- >Sent from: http://sqlite.1065341.n5.nabble.com/ >___ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Using sqlite3_interrupt with a timeout
Consider the following pseudo-code. void interrupt_timeout(sqlite3* db, int timeout) { sleep(timeout); sqlite3_interrupt(db); } int main() { sqlite3* db = sqlite3_open_v2(...); sqlite3_stmt* stmt = sqlite3_prepare_v2(db, ...); ... pthread_create(interrupt_timeout, db, timeout); int rv = sqlite3_step(stmt); ... } (I know this doesn't work properly if sqlite3_step doesn't time out, but it suffices for this example.) For the purposes of this example, let's suppose that the call to sqlite3_step takes a while. According to the docs, "New SQL statements that are started after the running statement count reaches zero are not effected by the sqlite3_interrupt()." But for very small timeouts, it's possible that sqlite3_interrupt gets executed /before/ sqlite3_step ever gets called, in which case sqlite3_step runs to completion no matter how long it takes. Am I missing something? Is there another way to leverage sqlite3_interrupt that doesn't have this race condition? What exactly is meant by "running statements"? Is it statements that are in the middle of a call to sqlite3_step? Statements that have been stepped, but not yet reset? Something else? -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Thread-Safety of Multi-Thread Connection
On 12/31/18, Jesse Rittner wrote: > I have some questions about multi-thread (as opposed to serialized, see > https://www.sqlite.org/threadsafe.html) connections. > > 1. If I have two multi-thread connections that are using a shared cache, is > it safe to use those two connections concurrently with each other? > 2. Same as (1), but what if it's an in-memory database? > 3. Is it safe to use sqlite3_interrupt (from a different thread) on a > multi-thread connection? I don't see how else sqlite3_interrupt could be > used, so I'm not sure if it's a serialized-only feature. Yes to all of the above. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Thread-Safety of Multi-Thread Connection
I have some questions about multi-thread (as opposed to serialized, see https://www.sqlite.org/threadsafe.html) connections. 1. If I have two multi-thread connections that are using a shared cache, is it safe to use those two connections concurrently with each other? 2. Same as (1), but what if it's an in-memory database? 3. Is it safe to use sqlite3_interrupt (from a different thread) on a multi-thread connection? I don't see how else sqlite3_interrupt could be used, so I'm not sure if it's a serialized-only feature. Thanks! -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Shared Cache vs. Serialized
Keith Medcalf wrote > See also Write Ahead Logging journal mode (WAL) where in the case you > specified B's operation will succeed even if it is updating a table being > read by connection A. Oh, good to know! For anyone who stumbles across this thread in the future, note that this requires that you be using separate private caches between connections A and B. With a shared cache, you still get SQLITE_LOCKED even with write-ahead logging. -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in 3.25.2 (RECURSIVE CTE + window function)
On 12/30/2018 02:41 PM, Nicolas Roy-Renaud wrote: I've been dealing with occasional segfaults on one of my applications when running a similar query, except even on SQLite 3.26, the safeguard wouldn't trigger. Running the SQL query specified in the bug report description from the tracker (https://www.sqlite.org/src/tktview?name=e8275b415a) now yields either "Error: cannot use window functions in recursive queries" or "Error: recursive reference in a subquery: q" if I try to nest it. Below is the query in question, modified to work as a self-contained test case, but I have not managed to figure out how to reduce it further. The query comes from a Markov chain project. With the data contained in the CTE for chain, this query should print "hello" and "world" 10 times, but crashes after 3. Alternatively, it could also not run at all and simply return an error, as seems to be the expected behavior for thee queries since SQLite 3.25. Thanks for reporting this. No fix yet, but a further reduction here: https://sqlite.org/src/tktview/d0866b26f83e9c55e30d I think this probably should work (not return the error message). Although technically there are window functions within a recursive sub-query, they don't operate on the recursively generated dataset (they're in a subquery) so I don't think the restriction applies. Dan. WITH RECURSIVE chain(link1, link2, n) AS (VALUES ('hello', 'world', 1), ('world', 'hello', 1)), markov(last_word, current_word, random_const) AS ( VALUES('hello', 'world', ABS(random()) / CAST(0x7FFF AS real)) UNION ALL SELECT markov.current_word, ( SELECT link2 FROM ( SELECT link1, link2, n, SUM(n) OVER (PARTITION BY link1 ROWS UNBOUNDED PRECEDING) AS rank, SUM(n) OVER (PARTITION BY link1) * markov.random_const AS roll FROM chain WHERE link1 = markov.current_word ) t WHERE roll <= rank LIMIT 1 ) AS next_word, ABS(random()) / CAST(0x7FFF AS real) AS random_const FROM markov WHERE current_word <> ' ' ) SELECT last_word FROM markov LIMIT 10; I've had no issue running that same query on PostgreSQL, and I have in fact had it run to completion multiple times on SQLite 3.26 as well, with very large datasets. -- Nicolas Roy-Renaud Richard Hipp Thu, 27 Sep 2018 06:13:36 -0700 Thanks for the report and test case. Now fixed on trunk and on branch-3.25. On 9/25/18, Щекин Ярослав wrote: > Hello. > > Here's the self-contained test case: > > WITH t(id, parent) AS ( > SELECT CAST(1 AS INT), CAST(NULL AS INT) > UNION ALL > SELECT 2, NULL > UNION ALL > SELECT 3, 1 > UNION ALL > SELECT 4, 1 > UNION ALL > SELECT 5, 2 > UNION ALL > SELECT 6, 2 > ), q AS ( > SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.id) AS rn > FROM t > WHERE parent IS NULL > UNION ALL > SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.id) AS rn > FROM q > JOIN t > ON t.parent = q.id > ) > SELECT * > FROM q; > > Results in segmentation fault. > (I also wanted to thank [Arfrever] (in #sqlite IRC) for testing / > confirmation.) > > -- > WBR, Yaroslav Schekin. > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users