The journal_size_limit in WAL mode is for when the WAL file resets (everything 
checkpointed successfully). It doesn't limit transaction size in any way. It's 
simply "when everything has checkpointed: cut the file back to at most this 
size" rather than the normal mode of: "wal file size will never shrink, just be 
recycled", (similar to how the database file size doesn't shrink when you 
delete records).

Can't help you on the C code part though, sorry.

-----Original Message-----
From: sqlite-users [] On 
Behalf Of Howard Kapustein
Sent: Tuesday, September 05, 2017 1:07 PM
Subject: [sqlite] PRAGMA journal_size_limit prevents -wal growth or only size 
outside a transaction?

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)?

As for why... I'm good with -wal growth while I do work but in some cases I've 
got too much work coming and going w/o any idle period for autocheckpoint@close 
to kick in. In a few cases I see huge (700MB+) -wal files when I've got no 
transaction in play. I've set SQLITE_FCNTL_PERSIST_WAL=1 and want to explicitly 
checkpoint similar to how sqlite does on close i.e. Passive + truncate

SQLITE_PRIVATE int sqlite3WalClose(...){
      rc = sqlite3WalCheckpoint(pWal, db,
          SQLITE_CHECKPOINT_PASSIVE, 0, 0, sync_flags, nBuf, zBuf, 0, 0
        }else if( pWal->mxWalSize>=0 ){
          /* Try to truncate the WAL file to zero bytes if the checkpoint
          ** completed and fsynced (rc==SQLITE_OK) and we are in persistent
          ** WAL mode (bPersist) and if the PRAGMA journal_size_limit is a
          ** non-negative value (pWal->mxWalSize>=0).  Note that we truncate
          ** to zero bytes as truncating to the journal_size_limit might
          ** leave a corrupt WAL file on disk. */
          walLimitSize(pWal, 0);

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 + ";")


  *   Howard
sqlite-users mailing list
sqlite-users mailing list

Reply via email to