The docs are a little unclear =>
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
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.


sqlite-users mailing list

Reply via email to