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

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

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

Jens Alfke-2
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Keith Medcalf

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



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Simon Slavin-3
On 22 Feb 2020, at 2:28am, Keith Medcalf <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Keith Medcalf

On Friday, 21 February, 2020 19:36, Simon Slavin <[hidden email]> wrote:

>On 22 Feb 2020, at 2:28am, Keith Medcalf <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

wmertens
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 <[hidden email]> wrote:

>
> On Friday, 21 February, 2020 19:36, Simon Slavin <[hidden email]>
> wrote:
>
> >On 22 Feb 2020, at 2:28am, Keith Medcalf <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users