On 21 September 2015 at 14:38, Simon Slavin <slavins at bigfraud.org> wrote:
> As expected, ALTER TABLE acts like INSERT: it's a change which requires an > exclusive lock. So just as the documentation says, in a normal journal > mode you can't make a change while the database is locked (which it is > during a SELECT), and in WAL mode you can make the change but the > connection doing the SELECT won't see it until it finishes its transaction. > > Which gives the grand conclusion I posted earlier: OP does not have to > worry about this issue. It can never happen. Your schema cannot be > changed on you while you're inside a transaction if you don't intentionally > defeat SQLite's locking mechanism. > This all makes sense, but is it what the OP was asking? I thought gwenn was looking at the normal cached-statement scenario: 1) Statement is prepared 2) Statement is stepped 3) Statement is reset 4) ... time passes ... 5) Statement is stepped 6) Statement is reset 7) ... time passes ... etc. etc. The assertion seems to be that if the return value of sqlite3_column_count is cached at step 2 it will still be valid at step 5 -- unless a schema change happens at step 4 affecting the number of columns that will be returned by a SELECT *. As others have indicated, sqlite3_step may return SQLITE_SCHEMA in this scenario to indicate that the previously prepared statement needs to be recompiled. But a statement prepared by sqlite3_prepare_v2 will transparently recompile itself (up to 50 times by default) instead of returning SQLITE_SCHEMA: https://www.sqlite.org/rescode.html#schema Which I believe has lead to the OP's question "how do I know when this has happened?" One solution suggested by the docs is to rebuild sqlite3 with SQLITE_MAX_SCHEMA_RETRY=0, then handle the SQLITE_SCHEMA error by clearing the cache + manually recompiling the statements. Or maybe using sqlite3_prepare instead of _v2 would do the trick, if no other _v2 features are relied upon. -Rowan
