Setting auto_vacuum=2 doesn't work after setting journal_mode=WAL

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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users