M1: ingest preflight — DuckDB lock detection, retry, and connection hygiene #508

New Issue

kert · 2026-07-08T19:41:25Z

kert commented

2026-07-08 19:41:25 +00:00

Batch ingests (dev/scripts/ingest_opps.py, PFS ingest, aco.pipe runs) fail
with a raw IOException: Could not set lock on file "data/aco.duckdb" whenever a
long-running reader (typically a marimo notebook kernel) holds a connection.
DuckDB is single-writer, so even a read-only handle blocks the writer.

Scope

Add a preflight to the ingest entrypoints: detect the lock (attempt RW open;
on failure run lsof data/aco.duckdb), then retry with backoff and, if still
held, exit with an actionable message naming the holder PID/command
("marimo kernel — close the notebook tab or stop that PID").
Add a short-lived connection helper (context manager) to conf.connect so
callers open→use→close instead of holding a connection for the process life.
Document notebook connection hygiene (don't keep a module-level DuckDB
connection alive across cells).

Background & alternatives: docs/superpowers/specs/2026-07-08-duckdb-concurrency-streaming.md (option B).

Batch ingests (`dev/scripts/ingest_opps.py`, PFS ingest, `aco.pipe` runs) fail with a raw `IOException: Could not set lock on file "data/aco.duckdb"` whenever a long-running reader (typically a marimo notebook kernel) holds a connection. DuckDB is single-writer, so even a read-only handle blocks the writer. **Scope** - Add a preflight to the ingest entrypoints: detect the lock (attempt RW open; on failure run `lsof data/aco.duckdb`), then retry with backoff and, if still held, exit with an actionable message naming the holder PID/command ("marimo kernel <pid> — close the notebook tab or stop that PID"). - Add a short-lived connection helper (context manager) to `conf.connect` so callers open→use→close instead of holding a connection for the process life. - Document notebook connection hygiene (don't keep a module-level DuckDB connection alive across cells). Background & alternatives: `docs/superpowers/specs/2026-07-08-duckdb-concurrency-streaming.md` (option B).

kert added the infra label 2026-07-08 19:41:25 +00:00

kert referenced this issue from a commit

2026-07-10 20:48:19 +00:00

fix(ingest): M1 duckdb concurrency — lock preflight + per-year merge (closes #508, #509)

kert closed this issue

2026-07-10 20:48:19 +00:00

kert commented

2026-07-10 20:49:06 +00:00

Implemented in d809a45f92:

conf.connect.duckdb_batch() — context-managed short-lived RW connection: retries the single-writer lock with exponential backoff, and on exhaustion raises naming the holder PIDs/commands via lsof ("close the notebook tab or stop that PID") instead of the raw IOException. Always closes on exit.
Wired into the ingest entrypoints dev/scripts/ingest_opps.py and dev/scripts/ingest_asp.py.
Connection-hygiene documentation: docs/docs/duckdb-concurrency.md (read-only defaults in notebooks, no module-level connections, what to do when an ingest names a marimo PID).
Tests: tests/conf/test_connect.py::TestDuckdbBatch including a real cross-process lock-holder case.

The strategic fixes (per-domain split, read replica, lake) remain M2–M5: #510–#514.

Implemented in https://git.fhirworx.io/homelab/stack/commit/d809a45f928863ce7bb65a49dc4917e9b62aef7f: - `conf.connect.duckdb_batch()` — context-managed short-lived RW connection: retries the single-writer lock with exponential backoff, and on exhaustion raises naming the holder PIDs/commands via `lsof` ("close the notebook tab or stop that PID") instead of the raw `IOException`. Always closes on exit. - Wired into the ingest entrypoints `dev/scripts/ingest_opps.py` and `dev/scripts/ingest_asp.py`. - Connection-hygiene documentation: `docs/docs/duckdb-concurrency.md` (read-only defaults in notebooks, no module-level connections, what to do when an ingest names a marimo PID). - Tests: `tests/conf/test_connect.py::TestDuckdbBatch` including a real cross-process lock-holder case. The strategic fixes (per-domain split, read replica, lake) remain M2–M5: #510–#514.

kert referenced this issue

2026-07-11 02:47:04 +00:00

M2: split aco.duckdb into per-domain files + ATTACH #511

kert referenced this issue

2026-07-11 03:31:42 +00:00

M5: migrate notebooks to lake reads; retire the monolith for reference data #514

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: homelab/stack#508