There is some other way to look at my original question.  Or to cast some light 
on it.

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 
wal/shm files.

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,
Olivier Mascia

> Le 25 nov. 2017 à 09:21, Olivier Mascia <> 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

Reply via email to