I use journal_mode=WAL and have periods of checkpoint starvation (complicated
reasons) so I'm looking to prune the -wal file but in less blunt way than
SQLITE_CHECKPOINT_TRUNCATE.
Behaviorally I want SQLITE_CHECKPOINT_PASSIVE *and then* if required -wal
content < journal_size_limit, to do the truncation a la
SQLITE_CHECKPOINT_TRUNCATE
SQLITE_CHECKPOINT_PASSIVE gives me the best-effort checkpointing, but in the
best case I don't get -wal shrinkage. SQLITE_CHECKPOINT_TRUNCATE is more of an
aggressive do-it-now-wait-if-necessary which gets me the shrinkage behavior,
but with *required*-effort rather than best-effort. I'd really like both --
best-effort checkpoint AND best-effort truncate.
sqlite3WalClose does exactly what I want (the key bits)
sqlite3OsLock(pWal->pDbFd, SQLITE_LOCK_EXCLUSIVE)
sqlite3WalCheckpoint(pWal, db, SQLITE_CHECKPOINT_PASSIVE,...)
sqlite3OsFileControlHint(pWal->pDbFd, SQLITE_FCNTL_PERSIST_WAL, &bPersist)
if (bPersist) { if (pWal->mxWalSize>=0) { walLimitSize(pWal, 0) } }
But AFAICT this is only called when PRAGMA journal_mode changes to not WAL or
the pager cache is closed when via sqlite3_close(). I'm a long running process
with connection caching and associated prepared statements so torching the
connection to trigger this isn't optimal.
Can I indirectly get this behavior if I open then immediately close a
connection?
I think so. If there are no other connections to the same database. If
there are any other connections, the checkpoint-on-close won't happen of
course.
I think I can sorta approximate this it if I disable the busy-handler, do
SQLITE_CHECKPOINT_TRUNCATE, ignore Busy/Locked errors and restore the
busy-handler before returning, but that's merely 'sorta' -- ugly on several
levels.
Which part of the sorta is the problem? If you run an
SQLITE_CHECKPOINT_TRUNCATE checkpoint without a busy-handler, or with
the busy-handler rigged to return 0 immediately, then it will:
* attempt a best-effort checkpoint (same as PASSIVE),
* if the entire wal file was checkpointed, check if any readers are
still using it,
* if no readers are still using it, truncate the wal file to zero
bytes in size.
I don't see any way to directly try to best-effort truncate the -wal file e.g.
sqlite3_wal_checkpoint_v2(db, NULL, SQLITE_CHECKPOINT_PASSIVE, NULL, NULL))
sqlite3_file_control(db, NULL, SQLITE_FCNTL_TRUNCATE_WAL, NULL)
Is the idea that this file-control would truncate the wal file to zero
bytes in size iff it could safely do so without blocking on any other
clients?
Dan.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
