SQLite Forum

Anton Dyachenko
Hi, yesterday I have encountered a very strange issue with sqlite, it is hard for me to isolate the issue to a small reproducible scenario so I will start with a written description and maybe somebody can reveal something related to the issue:

* my app uses lots of connections from many threads
* we have unit tests that perform lots of migrations and check relevant properties of the resulting schema
* migrations are always run as one of the first things we do with our dbs (after checking the version and setting some pragmas)
* up to yesterday unit test didn't fail
* yesterday I have changed which connection unit test should use, most likely previously unit test used the same connection that performs migrations, but now it is another one (but I am not sure about this)
* one of our unit test creating a db with very old schema and then performs a bunch of migrations
* in the initial schema we had a view with the name say XXX, but after massive migrations, we removed this view and created a table with the same name XXX
* unit test checks that this new table XXX has a unique constraint on two columns by trying to insert the same pair of values twice and expecting the second attempt should fail with constraint violation
* yesterday this test starts failing on the FIRST insertion saying that XXX is a view
* I guessed that sqlite caches schema for each connection in the memory and didn't update it after migrations, so my next guess was that VACUUM operation (as one of the most global change from sqlite point of view) should force the cached schema to be updated, and that fixes my unit test
* our users may have very large dbs and calling vacuum after each migration is a real performance penalty especially we are going to release a new version of our app that has MASSIVE schema change in a serial of migrations that would multiple vacuum calls on 10x
* of course, opening my db in db browser shows the most recent schema and all queries from my unit test work as expected. I checked it specifically during debug session just before performing the first insertion from my app, in db browser everything is fine.
* I work on Windows and repro the issue on windows, but our app is cross-platform and works on Mac and Linux as well, however, I don't know if the issue is present on other platforms
* one more note, that might be related to the issue, my app uses custom vfs(based on 5.12 qt) that we know has issues with flushing/syncing data to disk as a trade-off for performance but likely this is not related in this case due to db browser has the recent schema

sqlite should update cached schema on all connections automatically or specify in the documentation how and when exactly I as a developer should do it manually. Having vacuum as a fix is likely not acceptable due to perf penalty (except we can't find a better option) and I would like to understand if this is a programmer error or bug inside sqlite.