SQLite Forum

Anton Dyachenko
Login
I see the point, I will try it soon. Although it should help for each connection individually I would like to have a mass fix. I mean this approach requires each connection to run one of the reread schema queries, but what I am really looking for is something like a vacuum (but much more performant) that will force all opened connections to reread schema on the next statement execution.

Now I know when to call reread schema, immediately after migration, but I don't have a list of all opened connections (some of them can be created bypassing the pool), even more, if I would have it some connections could be used by other threads at the time. So I can set a flag in my connection wrapper (over sqlite3) and then run a reread query on the next attempt to prepare a statement but this is not so simple and error-prone due to my app caches some statements (most of those queries that don't have user-provided params and fires quite often), for multiple executions. So I would need to retire all of the prepared statements as well.

Now my app uses journal mode == there is the exclusive mutable statement (migration), so I don't worry about some other read statements that can run simultaneously (this is not possible), but we have plans to start using WAL where this is possible, and it is not clear for me what is the proper handling of this case. Should already running statements be interrupted or not? Interruption is "correct" but not always reasonable due to change in the schema can be quite often compatible with running statements so there is no point interrupting them. On the other hand, I, as a developer, don't have the facilities to say sqlite that it needs to recompile a statement other than interrupt and finalize it.

It is easy for sqlite to monitor all create, alter, and drop queries and do proper handling of such cases, so from my point of view this is clearly should be done internally in sqlite as it has much more options to do it properly so I believe this is a sqlite bug however not very important for most of the users.

Anyway, thank you for your help.