On 11/25/15, T?r?k Edwin <edwin+sqlite3 at etorok.net> wrote: >> >> The WAL file contains 15 separate transactions. But every single one >> of those transactions says that the database is only 24 pages long. >> Furthermore, 12 of the 15 transactions (including the first two and >> the last one) only touch pages that are on the freelist of the >> database, which is not really possible. > > Hmm after the WAL is recovered the DB is indeed 24 pages long: > -rw-r--r-- 1 edwin edwin 24K Nov 25 17:47 temp.db
Correct, because when the WAL file is applied, SQLite truncates the database file to the size of the file according to the commit record in the WAL file - 24 pages. > > We do use SQLITE_CHECKPOINT_PASSIVE and SQLITE_CHECKPOINT_RESTART when > certain thresholds or timer trigger, what happens if during a passive > checkpoint it doesn't sync the DB? > "Checkpoint as many frames as possible without waiting for any database > readers or writers to finish, then sync the database file if all frames in > the log were checkpointed." > Would it still update the size of the .db file without syncing the parent > dir? Yes. Truncating the database to the last transaction size is safe because if power is lost and some pages fail to be written into the database prior to the truncation, the WAL will be played back again after power is restored and the pages will be fixed up then. Syncing only occurs prior to deleting or truncating the WAL file, since only then does the necessary recovery information get destroyed. > > The I/O path is more complicated than I originally thought though: > FreeBSD server (running our app with SQLite) with UFS+SU -> Linux (Xen host) > -> FreeBSD (ISCSI + ZFS) I don't know what this means. Is there a network filesystem involved? -- D. Richard Hipp drh at sqlite.org
