SQLite Forum

Feature request: expose additional state to identify left-open statements

Feature request: expose additional state to identify left-open statements

(1) By rcombs on 2021-05-19 09:24:29 [link] [source]

I have an application that keeps a pool of database connections open in multi-thread mode, with routines that need a connection being allocated one from the pool, which they return when finished with it. The exact details of the implementation are such that the routine can accidentally return a connection to the pool while a prepared statement derived from it is still alive, which can cause undefined behavior when the statement is operated on later (including to destroy it).

I have some debug-mode code that, when returning a connection to the pool, first validates that it isn't associated with any stale statements (via sqlite3_next_stmt), and fails an assertion it does. The problem I've run into is that several sqlite extensions create persistent prepared statements that aren't finalized until the entire session is closed, so I need to ignore those. Currently I'm doing so by allow-listing their query text, but that's not a very robust solution.

The best way to identify these persistent statements seems to be by the SQLITE_PREPARE_PERSISTENT flag set at their creation, but there doesn't seem to be any way for an application to determine if that flag was set on a statement. Could this be exposed?

Additionally, SQLite currently exposes sqlite3_stmt_busy, which allows a caller to check if a statement hasn't yet been reset or run to completion. It might also be useful to expose whether the statement has been stepped since it was last reset (or created).

(2.1) By Keith Medcalf (kmedcalf) on 2021-05-19 15:49:18 edited from 2.0 in reply to 1 [link] [source]

sqlite3_stmt_busy returns whether or not the statement is busy, that is, in the middle of being executed.

That is, a statement is a row generator function. When sqlite3_stmt_busy returns True that means that the row generator function has been called at least once and has returned an interim result, but has not yet run to completion. (That is, it is holding at least a read lock on the database).

To be more clear, if sqlite3_stmt_busy returns true then the statement has been stepped since it was last reset (or created) and has not been stepped to completion. Once the statement is stepped to completion (or reset) sqlite3_stmt_busy will return false.

In your description, if a statement returns true (is busy) then you can reset the statement. If badly written code (yours or someone else's) is ill-behaved then you should swat them with a clue-by-four and have them use the three seashells more effectively ...

(3) By rcombs on 2021-05-19 19:43:59 in reply to 2.1 [source]

To be clear, I'm not trying to automatically reset statements; I'm trying to determine if statements were left unfinalized (even if they had run to completion) and crash the app on the developer's machine before it can cause problems for users. For this to work, I need to exclude the persistent statements generated internally by extensions like fts, which is why I'm asking for the persistent flag to be exposed. Detecting run-to-completion-but-not-reset statements is less important, but might help diagnose potential cases where internal extension statements are left in an unexpected state.