PRAGMA journal_size_limit prevents -wal growth or only size outside a transaction?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

PRAGMA journal_size_limit prevents -wal growth or only size outside a transaction?

Dan Kennedy-4
> The docs are a little unclear =>
> https://sqlite.org/pragma.html#pragma_journal_size_limit
> I need to disable autocheckpoint@close (for other reasons) so I'm looking for
> ways to fence the -wal file. If I want to bound a database's -wal file to <=1MB
> when I'm not in a transaction is it just PRAGMA journal_size_limit=1048576;?
> Does this affect -wal growth *during* a transaction, i.e. if I BEGIN; ...INSERT
> lots...; COMMIT will I successfully reach COMMIT? The journal_size_limit
> doesn't affect *growth* of the -wal, right? Just 'at rest' size  (i.e. outside
> any transaction)?

>
> Is this the functional equivalent?
> int limit = ...get current value via sqlite3_exec(db, "PRAGMA
> journal_size_limit;")...
> sqlite3_exec(db, "PRAGMA journal_size_limit=0;")
> sqlite3_wal_checkpoint_v2(db, NULL, SQLITE_CHECKPOINT_PASSIVE, &size, &chkpt);
> sqlite3_exec(db, "PRAGMA journal_size_limit=" + limit + ";")


It's close, but not quite equivalent.

The checkpoint operation does not consider the journal_size_limit
setting in wal mode. Instead, it is applied when a writer writes a
transaction into the very start of the wal file.

So, say you manage to checkpoint the entire wal file (because there are
no old readers preventing this). The next connection to write to the
database will write its transaction into the start of the wal file,
overwriting old content. When the writer commits, it checks if the wal
file on disk is larger than the configured "PRAGMA journal_size_limit".
If it is, the writer truncates it - either to the configured limit or to
the smallest possible size without truncating away part of the
transaction that was just written.

In other words - "PRAGMA journal_size_limit" is applied by the next
COMMIT after a successful checkpoint, not by the checkpoint itself.

Dan.




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users