I just discovered that none of our databases are auto-vacuuming, even though we
set auto_vacuum to 'incremental' when a database is created, and periodically
call 'pragma incremental_vacuum'. If I go into the CLI, open a database and run
"pragma auto_vacuum", it returns 0.
After some experimentation I've discovered that (at least in 3.28) `PRAGMA
auto_vacuum=incremental` has to be done as the very first thing after creating
a database, even before `PRAGMA journal_mode=WAL`. I know it's documented that
auto_vacuum has to be enabled before creating any tables, but the docs say
nothing about ordering wrt other pragmas!
To be precise, this is what we currently do after creating a new database,
which does not work:
sqlite3_exec(db, "PRAGMA journal_mode=WAL; "
"PRAGMA auto_vacuum=incremental; " // ⟵ will have no
effect
"BEGIN; "
"CREATE TABLE …….. ; "
"PRAGMA user_version=302; "
"END;");
If I swap the first two statements, it does work:
sqlite3_exec(db, "PRAGMA auto_vacuum=incremental; " // ⟵ will take effect
"PRAGMA journal_mode=WAL; "
"BEGIN; "
"CREATE TABLE …….. ; "
"PRAGMA user_version=302; "
"END;");
Is this expected? If so, the docs for auto_vacuum should be updated.
—Jens
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
