SQLiteWorkflowStore leaks cursors: the store depends on CPython refcounting to commit #516

Summary

SQLiteWorkflowStore calls conn.execute(...) and never closes the cursor it
returns. On CPython the cursor is refcounted away the moment the expression ends,
which finalises the statement; on any runtime without refcounting it survives
until the next GC, holds the statement open, and the following commit() fails:

sqlite3.OperationalError: cannot commit transaction - SQL statements in progress

This is not a PyPy quirk to work around. It is the store depending on when
CPython happens to free an object for correctness, which is a portability
assumption nothing states and nothing checks.

Reproduction

Verified against main at 5.0.4 + this cycle's merges, PyPy 7.3.23 (Python
3.11.15), Nodus installed with no modifications — the only dependency is tzdata.

pypy.exe -m pip install tzdata pytest
$env:PYTHONPATH="C:/dev/Coding Language/src"
pypy.exe -m pytest tests/test_task_graph.py tests/test_workflow_dsl.py `
                  tests/test_state_policies.py tests/test_coroutines.py `
                  tests/test_channels.py -q

9 failed, 65 passed in 28.58s

All nine failures have this one root cause. They surface as three different
exception types — sqlite3.OperationalError, LangRuntimeError, and two
AssertionErrors on a result dict — but every one carries the same message:

'message': 'cannot commit transaction - SQL statements in progress'

Nothing else in that subset fails. Coroutines, channels, task graph and the new
state policies all pass unmodified.

Where

src/nodus_lang_workflow/store.py — 12 conn.execute(...) call sites, none of
which close the returned cursor. Two consume it (.fetchone(), .fetchall());
the rest are DDL/DML where the cursor is discarded immediately. Example:

row = conn.execute(
    "SELECT ... FROM workflow_runs WHERE run_id = ?",
    (run_id,),
).fetchone()
return _record_from_row(row)

The cursor is unreferenced after the expression. CPython frees it there. PyPy
does not, and the statement is still live when the enclosing block commits.

Why it is worth fixing regardless of PyPy

• It is a latent bug on CPython too. The guarantee is an implementation
  detail, not a language one. It holds today by accident.
• It blocks the one measurement that matters for LIMITS-001: runtime throughput is the bootstrapping blocker — ~650-850K instr/sec, no JIT #173. PyPy runs Nodus about
  23× faster (see the measurement on that issue), and this is the only thing
  standing between that number and a usable runtime. Nine tests, one cause.
• The same shape will appear elsewhere. Anywhere a resource's release depends
  on refcounting rather than an explicit close, the same class of failure is
  waiting. Worth a sweep rather than a point fix.

Fix direction

Close cursors explicitly. Either with closing(conn.execute(...)) as cur:, or
assign and .close(), or route reads through a small helper that does it once so
the twelve sites cannot drift:

def _fetchone(conn, sql, params=()):
    cur = conn.execute(sql, params)
    try:
        return cur.fetchone()
    finally:
        cur.close()

The helper is the better shape here for the reason this codebase keeps
rediscovering: twelve call sites each remembering to close is twelve chances to
forget, and the thirteenth will.

Add PyPy to CI once it passes. The value is not that anyone ships on PyPy
today — it is that a non-refcounting runtime catches exactly this class of defect
and CPython never will.

Related

• LIMITS-001: runtime throughput is the bootstrapping blocker — ~650-850K instr/sec, no JIT #173 — runtime throughput, the bootstrapping blocker. PyPy is the cheapest
  path to a 20×+ improvement and this is what blocks evaluating it properly.
• LIMITS-002: Default workflow runner uses non-durable LocalWorkflowStore #174 — the default workflow runner uses a non-durable store. Same subsystem.

Affected versions

v5.0.4 and current main. Not a regression — the pattern predates this cycle.

Provenance

Surfaced while measuring PyPy throughput for #173, which is a task from the
bootstrapping thread rather than a bug hunt. The benchmark was the point; this was
found because a speedup is worthless if the runtime does not work, so the suite
was run before the number was reported.