There is some other way to look at my original question. Or to cast some light
SQLite does a full checkpoint on the last attachment close to a database file,
actually checkpointing whatever left in the wal file and finally removing the
I have implemented a PASSIVE checkpoint right before every attachment close in
my application, because the way it is designed, it is a nice opportunity to pay
a small delay for that checkpointing: the bulk of the work for the consumer
process has been done, and while that passive checkpoint and close occurs,
nothing/nobody is waiting on it, other workloads might be at work or starting,
but no user pay a (small) hit with this checkpointing (passive, so probably
partially) at close. Because after the close the work-unit is done. If the
user continues to interact, that will be through the help of other work-units.
This of course is in addition to the full final checkpoint that SQLite will do
on last close because chances are that anyway, I have multiple concurrent
attachments to a single database for a long time. The last close won't
typically occur before hours.
The above passive checkpoint on every attachment close (which are generally not
the last close) is better, for this application architecture, than a checkpoint
occurring on commit, after some delay (number of pages in the wal files for
instance). The checkpoint on commit will imply a delay for the caller of the
commit, delay which might impact the overall delay of computing something. That
the caller work-unit commits does not imply it is done with what it has do, yet
it will pay a (small) unexpected price on commit() and might delay its answer
to its consumer task.
The scheme works well. But could be slightly even better if right before doing
a passive checkpoint the application could decide wether to do it or postpone
it, based on the number of pages in the wal file. The idea being that there is
no real need to do it unless there are clearly a potential for some amount of
work to be done. This is exactly what the default automatic checkpointing of
SQLite does (by default on 1000 pages or more), but at commit time. I'm
interested to do this same thing at close() time (those closes which are not
the last one).
Using wal_hook() each attachment can be informed of that number of wal pages at
commit time and decide to checkpoint or not. It can also take note of the
number of pages. Which could allow it to decide later, before its own close(),
to checkpoint or not. But that behaviour implies that the current attachment
has done commit()s recently. Else the information they have on the count of
wal pages is at best very inexact and at worse (an attachment which made no
commit) inexistant. Implementing it this way is inherently wrong and it
currently is better to blindly call checkpoint passive right before each
This is why I was looking for a proper API way to query for the count of wal
pages; to use it before each attachment close() and decide wether or not I take
this opportunity to first run a passive checkpoint.
Best Regards, Meilleures salutations, Met vriendelijke groeten,
> Le 25 nov. 2017 à 09:21, Olivier Mascia <o...@integral.be> a écrit :
> Dear all,
> I'm looking for a mean to query the current WAL page count (at other times
> than at commit through the wal_hook() callback).
> Did I overlooked something in the C API which would allow me to query this?
sqlite-users mailing list