There are three pragma's which affect the "organization" of a newly created 
database.  When a database is to be created these commands must be given BEFORE 
any command which opens or creates the database:

pragma auto_vacuum
pragma encoding
pragma page_size
pragma data_store_directory

The issuance (or preparation) of ANY OTHER COMMAND will cause a new blank 
database to be created using the values of auto_vacuum, encoding, and page_size 
in effect at the time that command is issued (prepared).  The default for 
auto_vacuum is 0 or none.  auto_vacuum can be changed freely between full (1) 
and incremental (2) if the database was created with a non-zero value of 
auto_vacuum (auto_vacuum was not none at the time of database creation).  
page_size can be changed for an existing non-wal mode database by changing the 
page_size and doing a vacuum.  encoding cannot be changed and defaults to utf8.

The reference to "create tables" in the database in the documentation is a 
euphamism for "database file created" since a database will always have at 
least one table in it -- the sqlite_master table -- once the database is 
created, even if no user tables are created.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On
>Behalf Of Jens Alfke
>Sent: Friday, 21 February, 2020 16:24
>To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
>Subject: [sqlite] 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
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to