watch mode: writer holds an open transaction, WAL never checkpoints and grows unbounded

[EltonSoares]

Version: 2.3.7 · OS: Windows 11 · Mode: code-review-graph watch (daemon)

Summary

In watch mode the writer leaves a transaction open between events. An open transaction pins
the WAL checkpoint mark, so graph.db-wal never drains and grows without bound. On a
368 MB graph the WAL reached 1.8 GB in a few hours, and kept growing.

Evidence that the watcher is the holder (not readers)

Several code_review_graph serve (MCP) readers were alive the whole time and were never
touched. Only the watcher process was killed:

watcher alive:  458462 frames,  28 checkpointed,   WAL 1801.4 MB
watcher killed: 458462 frames,  458462 checkpointed, WAL 0.0 MB

PRAGMA wal_checkpoint(PASSIVE) returned busy=0 throughout — no writer lock; the backfill
simply could not advance past frame 28 because of a held read mark. Killing the watcher
released 100% of it instantly, with all readers still connected.

The watcher's own log names the cause

5940  WARNING: Rolling back uncommitted transaction before BEGIN IMMEDIATE
1088  INFO:    FTS index rebuilt: <n> rows indexed
 694  ERROR:   Post-update callback failed: database table is locked
 349  WARNING: FTS index rebuild failed: database table is locked
 117  ERROR:   Post-update callback failed: bad parameter or other API misuse

Two separate problems that compound:

1. The rollback warning is the leak. Each write finds a stale open transaction from the
   previous event and rolls it back — meaning between events the connection sits inside a
   transaction, holding the read mark that blocks checkpointing.
2. Write volume. 1088 full FTS index rebuilds is how a 368 MB database produces 1.8 GB
   of WAL frames. A rebuild per update looks like far more work than an incremental update
   should need.

Impact

graph.db-wal grows until the disk notices. journal_size_limit cannot help, because it only
truncates after a successful checkpoint, and the checkpoint never completes. Any tooling that
tries wal_checkpoint(TRUNCATE) just burns its busy_timeout and returns busy=1 — measured
at 33.1 s with a 30 s timeout, versus 0.015 s for PASSIVE, which returns the same
information.

Workaround

Bounce the watcher; the WAL then truncates fully, with readers still attached.

Suggested fix

Commit (or close) the transaction at the end of each update callback rather than leaving it
open until the next BEGIN IMMEDIATE. Separately, consider an incremental FTS update instead
of a full rebuild per change.

Note

crg-daemon stop is still broken on Windows (os.kill(pid, 0) -> WinError 87), so the
bounce has to kill by PID — that one is already filed as #843.