Cannot read result row of pragma wal_checkpoint

[CyrusJavan]

The documentation https://www.sqlite.org/pragma.html#pragma_wal_checkpoint for pragma wal_checkpoint states that it returns a row with 3 columns

The wal_checkpoint pragma returns a single row with three integer columns. The first column is usually 0 but will be 1 if a RESTART or FULL or TRUNCATE checkpoint was blocked from completing, for example because another thread or process was actively using the database. In other words, the first column is 0 if the equivalent call to sqlite3_wal_checkpoint_v2() would have returned SQLITE_OK or 1 if the equivalent call would have returned SQLITE_BUSY. The second column is the number of modified pages that have been written to the write-ahead log file. The third column is the number of pages in the write-ahead log file that have been successfully moved back into the database file at the conclusion of the checkpoint. The second and third column are -1 if there is no write-ahead log, for example if this pragma is invoked on a database connection that is not in WAL mode.

This is true from my experience when using the sqlite3 command

sqlite> PRAGMA WAL_CHECKPOINT(TRUNCATE);
1|14478|14452

I expect the same to work when using accessing a SQLite db from Go. However, that is not what I experience. When using this snippet:

row := db.QueryRow("PRAGMA WAL_CHECKPOINT(TRUNCATE)")
var (
	blocked           int
	pagesWrittenToWAL int
	pagesCheckpointed int
)
err := row.Scan(&blocked, &pagesWrittenToWAL, &pagesCheckpointed)
if err != nil {
	s.logger.Error("Scanning wal_checkpoint result", zap.Error(err))
}
s.logger.Debug("WAL file checkpoint completed",
	zap.Duration("latency", time.Since(t0)),
	zap.Int("was checkpoint blocked", blocked),
	zap.Int("pagesWrittenToWAL", pagesWrittenToWAL),
	zap.Int("pagesCheckpointed", pagesCheckpointed))

The variables blocked , pagesWrittenToWAL and pagesCheckpointed. Always have 0 for their value, even when I know for a fact that the WAL file was checkpointed and some pages were checkpointed.

[rittneje]

Please share a complete reproducible example, including which version of this library you are using.

[otoolep]

FWIW, I use this functionality heavily in rqlite, and I get non-zero values back for the scan. In other words, it seems to work fine for me.

• https://github.com/rqlite/rqlite/blob/master/db/db.go#L368
• https://github.com/rqlite/rqlite/blob/master/db/db.go#L398

Here is me running a hacked up unit test from rqlite:

$ git diff
diff --git a/db/db.go b/db/db.go
index 6135ff90..75fc0b96 100644
--- a/db/db.go
+++ b/db/db.go
@@ -398,6 +398,7 @@ func (db *DB) CheckpointWithTimeout(mode CheckpointMode, dur time.Duration) (err
        if err := db.rwDB.QueryRow(checkpointPRAGMAs[mode]).Scan(&ok, &nPages, &nMoved); err != nil {
                return fmt.Errorf("error checkpointing WAL: %s", err.Error())
        }
+       fmt.Println("checkpointed", nPages, "pages", nMoved, "moved")
        stats.Add(numCheckpointedPages, int64(nPages))
        stats.Add(numCheckpointedMoves, int64(nMoved))
        if ok != 0 {
$ go test -run Test_DBSums
checkpointed 3 pages 3 moved
PASS
ok  	github.com/rqlite/rqlite/v8/db	0.048s
~/repos/rqlite/src/github.com/rqlite/rqlite/db (master)$ 

[CyrusJavan]

Nevermind, I could not reproduce in an isolated environment. My theory is that SQLite is doing an automatic checkpoint already once all the inflight transactions are finished. That is why the manual checkpoint returns all zeroes.

[otoolep]

Easy enough to validate, disable automatic checkpoints as rqlite does.