On 10/06/2011 02:29 PM, David Barrett wrote:
Hi! Can you help me understand more exactly what the output parameters
are from sqlite3_wal_checkpoint_v2()? Specifically:
1) What is the relationship between pnLog and pnCkpt: is pnLog>=pnCkpt
always true?
Yes. Always true.
2) Under what circumstances would pnLog>pnCkpt be true?
When an SQLITE_CHECKPOINT_PASSIVE checkpoint is run, SQLite attempts to
copy the entire WAL file into the database. If it is successful,
(*pnLog==*pnCkpt).
However, if there are existing readers, then it may not be possible
to copy all WAL data into the database file. The reason being that
this might overwrite a database page that is part of the 'snapshot'
that the reader is accessing. In this case it is possible that
(*pnLog>*pnCkpt).
Using SQLITE_CHECKPOINT_FULL or SQLITE_CHECKPOINT_RESTART instead forces
the checkpoint to wait for such readers to finish. If you use one of
these, it is guaranteed that (*pnLog==*pnCkpt) (assuming your
busy-handler is prepared to wait long enough for readers to finish -
if it gives up before all readers have finished we will be back to
*pnLog>*pnCkpt).
Also, can you confirm I understand the following correctly:
3) When using SQLITE_CHECKPOINT_PASSIVE, the WAL file will grow as
needed, indefinitely, without ever shrinking.
It is possible. But we hope it's the exception, not the rule.
If a checkpoint runs and copies all WAL data into the database file,
the next writer starts writing into the start of the WAL file again.
The WAL file is not usually truncated (see PRAGMA journal_size_limit
if you want it to be) here. The reason being that it is faster to
overwrite an existing file than it is to truncate one and then start
appending to it.
So, if all goes well, SQLite should start over at the start of the
WAL file after each checkpoint. Preventing the WAL file from growing
indefinitely. There are two things that can go wrong:
* A reader might prevent a checkpointer from copying all data from
the WAL into the database file, or
* While the checkpoint is underway, some other process may be
writing to the database (appending to the WAL file).
If either of the above occur, then the next writer will append to the
WAL file, instead of writing into the start of it. If this happens
every checkpoint, then the WAL file will grow without bound.
Use CHECKPOINT_FULL or CHECKPOINT_RESTART (respectively) to prevent
the two conditions enumerated above.
4) When using a separate checkpointing thread, pnLog only goes down if
the read/write thread has no active queries when the checkpoint runs.
(Eg, if the checkpoint thread runs while the read/write thread has an
active query, it'll still checkpoint, but it just won't reset the pnLog
to 0.)
That's pretty much the gist of it. Some more details above.
Finally, and I'm not sure if this is related, but I've read in several
threads that sometimes you can get the SQLITE_CORRUPT error even if the
database isn't corrupt, and the proper response is to just retry.
5) Is this considered to be a bug, or is it the result of some temporary
hardware issue?
6) Is this aggravated by the use of WAL, or totally unrelated?
Sounds like questionable information to me. We know of no such bugs
at present.
Dan.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
