StructuredBatch with a state path dies after its first batch: CHECKPOINT inside the state transaction

[turbolytics]

handlers.StructuredBatch with pipeline.state.path set fails after its first batch, on every version since v1.1.0. The pipeline exits.

What happens

ERROR  error reinitializing handler  {"error": "checkpoint after truncate: Invalid Argument: TransactionContext Error: Cannot CHECKPOINT: the current transaction has transaction local changes"}
ERROR  failed to consume loop

Reproduced 2026-09-13 on v1.2.0 with a websocket source and the Bluesky demo's StructuredBatch handler, plus state: {path: /tmp/x.duckdb}. The process ran one batch and died about ten seconds after start. The same config without the state block runs for hours. The same config on handlers.InferredMemBatch with the state block runs for hours.

Why

#247 (v1.1.0) made StructuredBatch.Init run TRUNCATE TABLE <table>; CHECKPOINT; before every batch, so an in-memory database reclaims the truncated rows. That fix is correct without durable state.

With a state path, run disables autocommit on the connection (internal/cli/run/root.go, "Every batch from here on is one transaction"). Every statement then runs inside a transaction that closes only at commitState. Init runs after the commit, so its TRUNCATE opens a fresh transaction and deletes the previous batch's rows, and the CHECKPOINT that follows is inside a transaction with local changes. DuckDB refuses that.

The first Init, at startup, passes because the table is empty: a truncate that deletes nothing leaves no local changes.

Nothing in the test suite combines StructuredBatch with WithStateStore or a state path. No shipped example does either: kafka.stateful.window.yml and benchmark.stateful.mem.yml both use InferredMemBatch, and bluesky.postgres.windowed.yml uses StructuredBatch without a state block. The README's durable state section does not say the handler matters.

Options

1. Checkpoint before the truncate. CHECKPOINT; TRUNCATE TABLE <table>; The checkpoint is then the first statement of a fresh transaction, with no local changes, and it reclaims the rows the previous batch's truncate left. The in-memory reclaim is one batch later than today, still bounded by one batch.
2. Skip the checkpoint under a state transaction. A file-backed database checkpoints on its own as the WAL grows, so the truncated rows are reclaimed without the per-batch statement. Whether it reclaims them promptly is what the on-disk soak in validate: introspect sink destinations and lint the schema for DuckDB footguns #268 is measuring.
3. Move the checkpoint into commitState, after the commit and before the handler's Init. That keeps the truncate-then-checkpoint order, but it moves handler knowledge into the pipeline.

Option 1 is the smallest and does not need the pipeline to know which handler it runs.

Done when

• A test in internal/handlers runs StructuredBatch on a file-backed DuckDB with autocommit disabled: Init, Write, Invoke, commit, Init, and asserts the second Init succeeds.
• The invariant matrix marks handler.structured against state.durability, so the combination cannot go untested again.
• The README's durable state section names the handlers it has been verified with.

Related: #247, #268.

[turbolytics]

Still present on v2026.09.14. dev/config/examples/bluesky/bluesky.postgres.windowed.yml plus pipeline.state.path fails at the first batch:

ERROR  error reinitializing handler  {"error": "checkpoint after truncate: Invalid Argument: TransactionContext Error: Cannot CHECKPOINT: the current transaction has transaction local changes"}

The tumbling window tutorial on turbolytics.io now says to pair a state path with handlers.InferredMemBatch until this is fixed.