SQLite Forum

Cancellation in custom SQLiteFunction

Cancellation in custom SQLiteFunction

(1) By Oliver Ehrenm├╝ller (greuelpirat) on 2023-01-10 11:52:14 [link] [source]

We have implemented a custom SQLiteFunction in C# (using 1.0.116) that can take some time.

Now we want to be able to cancel it, but I found now way. Is there a way get an event when Connection.Cancel() is invoked?

Thank you

(2) By ddevienne on 2023-01-10 13:38:13 in reply to 1 [link] [source]

I'm afraid that's not possible, but I'm no expert.

The only API in SQLite to cancel a running statement is to interrupt it.

But that's likely implemented via regularly checking for an interrupted flag when running the VDBE code of that query, in between those VDBE instructions. But calling a custom (scalar) function is likely a single instruction, and it's running outside SQLite's control, which is waiting for it to return. In other words, an interrupt point is possible between instructions, but not inside a single VDBE instruction.

Unless there was an SQLite API such as sqlite3_was_interrupted() that user-code implementing a (long-running) custom function could call / poll explicitly, and return early if set. I'm not aware of such an API. Thus I don't think what you are asking is possible.

Sometimes threads can be interrupted, but typically not cleanly, and that wouldn't an SQLite mechanism. Signals also can also interrupt things, but often only temporarily, and again that's not an SQLite mechanism.

(3.2) By mistachkin on 2023-01-11 21:04:07 edited from 3.1 in reply to 1 [link] [source]

Thanks for the request.  I'll be adding this for the next release of
System.Data.SQLite, which is

EDIT #1: Now included in trunk check-in https://system.data.sqlite.org/index.html/info/6ea27254da9256b2.

EDIT #2: Support has been added to System.Data.SQLite for the new
         core library API sqlite3_is_interrupted, when available.

(4) By ddevienne on 2023-01-10 15:43:50 in reply to 3.0 [link] [source]

Can you please explain how Joe? Is this .NET specific? Or can a long-running C custom SQL function be interrupted too?

(5) By mistachkin on 2023-01-10 15:50:08 in reply to 4 [link] [source]

Yes, it is somewhat specific to how System.Data.SQLite handles its event

(6) By David Jones (vman59) on 2023-01-10 16:59:38 in reply to 2 [link] [source]

There should to be an sqlite3_is_interrupted(sqlite3_context*) function that heavy weight user functions/modules can call to check whether the application has called sqlite3_interrupt().

(7) By Keith Medcalf (kmedcalf) on 2023-01-10 17:40:36 in reply to 4 [link] [source]

To do the same thing with the C API an API function (one that has access to SQLite3 internals) would need to accept the sqlite_context*, find the connection, and return the interrupt flag associated with the underlying connection.

Not complicated, but it would have to be an internal function (API).

(8) By anonymous on 2023-01-10 19:51:05 in reply to 6 [link] [source]

Something like sqlite3_is_interrupted is something that I wanted too, some time ago; I think it is useful for some things (including any functions/virtual-tables that access the internet, or that make a lot of slow calculations). However, it has not been added yet.

(9) By Oliver Ehrenm├╝ller (greuelpirat) on 2023-01-11 04:30:52 in reply to 3.1 [link] [source]

Thank you! I will check it out when it is available! :-)

(10) By Richard Hipp (drh) on 2023-01-11 10:48:10 in reply to 8 [link] [source]

The sqlite3_is_interrupted() interface is now available on the latest trunk check-in. Please try it out and report back any problems you find.

(11) By ddevienne on 2023-01-11 12:21:05 in reply to 10 [link] [source]

Thanks! Here is the commit, for reference.

(12) By David Jones (vman59) on 2023-01-12 22:05:52 in reply to 10 [source]

The function operates as intended. I'm trying to incorporate it into a virtual table and make sure I can cleanly unwind an aborted operation (obviously highly dependent upon the vtab implementation).