On 4/12/17, Jens Alfke <j...@mooseyard.com> wrote: > The docs[1] say that: > >> The page_size pragma will only set in the page size if it is issued before >> any other SQL statements that cause I/O against the database file. > > It's not stated explicitly, but I believe the pragma has to be issued before > any other statement _ever_ causes I/O, i.e. it has to be the very first > persistent statement ever used on a new database file. (Except for the > situation regarding VACUUM described later on.) Is this true?
I think the best thing to do is: "PRAGMA page_size=4096; VACUUM;" -- all in a single call to sqlite3_exec(). But only do this when you really need to change the page size as it is expensive. To a first approximation, you never need to change the page size. So why is this important to you? > >> SQL statements that cause I/O against the database file include "CREATE", >> "SELECT", "BEGIN IMMEDIATE", and "PRAGMA journal_mode=WAL". > > I'm guessing that "PRAGMA user_version" also causes I/O. > Yes > > The implication is > that, if you want to set the page_size, you have to do it every time the > database is opened, not just on first-time initialization; because checking > to see if you've initialized the database already would trigger I/O which > will make setting the page_size a no-op. (Unless you use the filesystem to > check that the db file is missing or empty before opening it, I suppose.) > The implication *should* be that you *never* change the page size. SQLite will pick a good page size for you automatically, which works in 99.99% of all cases. In the exceptional case where you do need to adjust the page_size manually, do it exactly once, as if you are running CREATE INDEX. -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
