Because in analytical engines like DuckDB, the obvious cancellation controls do not reliably terminate a query. Specifically:
An asyncio timeout cancels your Python coroutine, but the C++ engine on the worker thread keeps churning. conn.interrupt() is best-effort: the engine polls the cancel flag only at internal checkpoints, and a deep cartesian materialization may never reach one. conn.close() from a different thread does not kill a cursor that is currently executing. And memory_limit does not stop a streaming join that produces rows without holding state. A single crafted query, for example a three-way CROSS JOIN inside a CTE, can pin host CPU for hours.
What actually works: run each query in a separate OS process you can kill. Keep a small pool of worker subprocesses, route each query to one, and on timeout send SIGTERM (graceful) then SIGKILL (uninterruptible) to that worker. SIGKILL is delivered by the kernel, so the process dies in milliseconds with no engine cooperation required. Other workers keep serving traffic; the dead worker respawns in the background. This is the same pattern Postgres uses internally (per-backend processes plus pg_terminate_backend), applied one layer up by the application.
Two things people miss: also detect out-of-band worker death (OOM, segfault) and respawn the slot, otherwise a dead worker becomes a black hole that fails every request routed to it. And give the admin or write path its own tighter timeout, so a long admin query cannot permanently occupy a worker. Note this only helps if the event loop is free to fire the timeout at all: see https://www.tigzig.com/agents-faq/why-does-my-fastapi-freeze-when-duckdb-runs-a-query. Full checklist: https://www.tigzig.com/security/duckdb.
← All Agents FAQ