Also if you have any connections open, the journal may exist, so it doesn't have to constantly open and close it.
On Fri, Oct 28, 2016 at 2:21 AM, Dan Kennedy <danielk1...@gmail.com> wrote: > On 10/28/2016 03:16 AM, David Raymond wrote: > >> I'm playing around with WAL mode here for the first time, along with some >> of the pragmas, and I'm getting some weird results. I was hoping someone >> could let me know if I'm missing something, or if yes, it is indeed weird. >> >> For starters, I'm looking at the journal_size_limit pragma. >> http://www.sqlite.org/pragma.html#pragma_journal_size_limit >> In its description it does say that it works for WAL mode. "To always >> truncate rollback journals and WAL files to their minimum size, set the >> journal_size_limit to zero." So I create a new database, turn on WAL mode, >> set that pragma, create a table, and insert some stuff into it. Then I >> check the file sizes, and the -wal file hasn't shrunk at all. I made sure >> it was large enough to go over the wal_autocheckpoint threshold and it >> didn't shrink. I ran a manual "pragma wal_checkpoint;" and it didn't >> shrink. I tried with specifying passive, full, and restart and it didn't >> shrink. It seems that I can only get it to shrink by doing an explicit >> "pragma wal_checkpoint(truncate);" But if that's the only way to shrink the >> file down, then what's the point of the pragma here? >> >> Or, as is more likely, what obvious thing is my brain missing at the end >> of the day? >> > > In wal mode, the wal file is truncated according to "PRAGMA > journal_size_limit" after the first transaction is written following a > checkpoint. Or, technically, after a writer writes a transaction into the > beginning of the physical file. So: > > sqlite> PRAGMA journal_mode = wal; > wal > sqlite> PRAGMA journal_size_limit = 0; > 0 > sqlite> CREATE TABLE t1(a, b); > sqlite> INSERT INTO t1 VALUES(randomblob(100000), randomblob(100000)); > /* WAL file is now roughly 200KiB */ > sqlite> PRAGMA wal_checkpoint; > 0|52|52 > /* Still roughly 200KiB */ > sqlite> INSERT INTO t1 VALUES(1, 1); > /* Now truncated to 4KiB */ > > This is because the locking scheme only allows writers to modify the wal > file, not checkpointers. > > Dan. > > > _______________________________________________ > 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
