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

(1) By rcombs on 2021-05-19 09:24:29

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]

`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 [link]

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.