Re: [sqlite] Setting auto_vacuum=2 doesn't work after setting journal_mode=WAL
I do the exact same pragmas as Jens, and also in this order. I was even convinced that it was working, so I'll need to double check. The documentation might be correct if you know what to look for, but from current experience it's not obvious. Wout. On Sat., Feb. 22, 2020, 4:02 a.m. Keith Medcalf wrote: > > On Friday, 21 February, 2020 19:36, Simon Slavin > wrote: > > >On 22 Feb 2020, at 2:28am, Keith Medcalf wrote: > > >> 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 problem is, Jens points out, that this is not documented. The > >documentation doesn't distinguish between those four PRAGMAs and other > >PRAGMAs which stop those four from working. > > That is not entirely true. All of them say that they change the format of > a database and only work if the database does not yet exist, although in > various variant wording. Perhaps the wording needs to be more clear like > it is for the "encoding" pragma which is very clear in stating that the > attempt to change the encoding of an existing database will have no effect > and therefore this command must be given before the database file is > created in order to have effect. > > In the case of the auto_vacuum and page_size pragma's, however, they DO > have effect on a currently existing open database in particular > circumstances. > > However if you want them to have effect for a newly created database you > need to issue them before the database is created. > > >I've previously suggested that the PRAGMAs should be divided into > >categories. Perhaps this should be a new category: those which do not > >create a database but have to be done before anything that creates the > >database. > > Well, that would be only one pragma, encoding. Whether on not the same > applies to any other pragma (page_size, auto_vacuum) depends on the intent > of the issuer of the command. If they are expected to affect a database > which has not yet been created, then obviously they must be issued before > the database is created. If they are intended to affect the database after > it is created then they should be issued after the database is created. If > they are issued after the database is created they are subject to the > limitations of the operation of those commands on already existant > databases. > > Perhaps the pragma encoding, pragma auto_vacuum and pragma page_size > simply need to say that if one wants the change to apply to a "newly > created" database these commands must be given first, before any other > command. > > -- > The fact that there's a Highway to Hell but only a Stairway to Heaven says > a lot about anticipated traffic volume. > > > > > ___ > 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
Re: [sqlite] Setting auto_vacuum=2 doesn't work after setting journal_mode=WAL
On Friday, 21 February, 2020 19:36, Simon Slavin wrote: >On 22 Feb 2020, at 2:28am, Keith Medcalf wrote: >> 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 problem is, Jens points out, that this is not documented. The >documentation doesn't distinguish between those four PRAGMAs and other >PRAGMAs which stop those four from working. That is not entirely true. All of them say that they change the format of a database and only work if the database does not yet exist, although in various variant wording. Perhaps the wording needs to be more clear like it is for the "encoding" pragma which is very clear in stating that the attempt to change the encoding of an existing database will have no effect and therefore this command must be given before the database file is created in order to have effect. In the case of the auto_vacuum and page_size pragma's, however, they DO have effect on a currently existing open database in particular circumstances. However if you want them to have effect for a newly created database you need to issue them before the database is created. >I've previously suggested that the PRAGMAs should be divided into >categories. Perhaps this should be a new category: those which do not >create a database but have to be done before anything that creates the >database. Well, that would be only one pragma, encoding. Whether on not the same applies to any other pragma (page_size, auto_vacuum) depends on the intent of the issuer of the command. If they are expected to affect a database which has not yet been created, then obviously they must be issued before the database is created. If they are intended to affect the database after it is created then they should be issued after the database is created. If they are issued after the database is created they are subject to the limitations of the operation of those commands on already existant databases. Perhaps the pragma encoding, pragma auto_vacuum and pragma page_size simply need to say that if one wants the change to apply to a "newly created" database these commands must be given first, before any other command. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Setting auto_vacuum=2 doesn't work after setting journal_mode=WAL
On 22 Feb 2020, at 2:28am, Keith Medcalf wrote: > 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 problem is, Jens points out, that this is not documented. The documentation doesn't distinguish between those four PRAGMAs and other PRAGMAs which stop those four from working. I've previously suggested that the PRAGMAs should be divided into categories. Perhaps this should be a new category: those which do not create a database but have to be done before anything that creates the database. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Setting auto_vacuum=2 doesn't work after setting journal_mode=WAL
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 On >Behalf Of Jens Alfke >Sent: Friday, 21 February, 2020 16:24 >To: SQLite mailing list >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
[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
