Cancelling Async Spatial Scans Safely

Cancelling a DuckDB spatial scan from asyncio does not stop the query: task.cancel() unwinds the coroutine but the blocking C call keeps pegging a core until you call con.interrupt(). This walkthrough sits under async execution patterns and isolates the exact failure — a cancelled or timed-out coroutine whose underlying ST_ scan is still running on a worker thread, holding memory and spill files open — then gives the interrupt-and-drain pattern, cursor-per-task isolation, and cleanup that make cancellation actually free the resources it promises.

Root-Cause Analysis: the coroutine died but the query did not #

The DuckDB Python client has no native async API. Every awaitable spatial query on this site is a blocking con.execute() handed to a worker thread through asyncio.to_thread() or loop.run_in_executor(). That indirection creates a hard asymmetry: asyncio can cancel the future that is awaiting the thread, but it cannot cancel the thread, and it certainly cannot reach into the C++ engine that is mid-scan on a ten-million-row geometry column. When a coroutine is cancelled, the Future raises CancelledError in the event loop, your await returns control immediately, and the query underneath keeps executing to completion — invisible, uninterruptible, and still consuming a whole core plus its share of memory_limit.

The observable symptom is “cancelled coroutine but the query is still pegging CPU”. It has four distinct root causes, and each needs a different guard:

• Naive task.cancel() with no interrupt. The task is marked cancelled and the loop moves on, but the executor thread never received a signal. The scan runs to the end, then discards its result into a future nobody is awaiting. Fix: catch CancelledError and call con.interrupt() on the same connection before re-raising.
• asyncio.wait_for timeout without interrupt. wait_for implements a timeout by cancelling the inner awaitable, so it hits the identical dead-end — the timer fires, the coroutine raises TimeoutError, and the spatial scan grinds on in the background. A timeout is just a cancellation with a clock attached and needs the same interrupt treatment.
• Interrupting a shared connection and killing siblings. con.interrupt() aborts whatever query is currently running on that connection object. If several coroutines dispatch through one connection, interrupting for one cancelled task tears down an unrelated sibling’s scan. Fix: give each task its own cursor so an interrupt is scoped to one query.
• Abandoned spill and temp files. A cancelled scan that spilled a hash aggregate or an ST_Union working set to temp_directory may leave partial spill files if the process is torn down before the engine unwinds. Fix: always await the drained future after interrupting so the engine cleans up, and scope temp_directory per job so orphans are trivially reclaimable.

The through-line: cancellation in this stack is a two-part operation. Part one unwinds Python; part two must explicitly abort the engine. Skip part two and you have leaked a thread, a core, and a chunk of your memory budget.

Deterministic Configuration #

The only settings this pattern needs are the ones that make an interrupt fast and its aftermath cheap to clean up. Keep the spill target local and per-job so an aborted scan’s temp files are isolated and reclaimable.

import asyncio
import duckdb

def open_cancellable_connection(job_spill: str) -> duckdb.DuckDBPyConnection:
    con = duckdb.connect(":memory:")
    con.execute("INSTALL spatial; LOAD spatial;")

    # Engine parallelism, NOT asyncio concurrency. A scan you may cancel should
    # not also be oversubscribing cores; keep it at/below physical core count.
    con.execute("SET threads = 8;")

    # Hard ceiling so a runaway pre-cancellation scan spills instead of OOM-killing
    # the process before your interrupt ever lands.
    con.execute("SET memory_limit = '4GB';")

    # Per-job spill dir: an interrupted scan's orphaned temp files stay contained
    # here, so cleanup is a single rmtree rather than sifting a shared directory.
    con.execute(f"SET temp_directory = '{job_spill}';")

    # Drops the row-order barrier so cancellation doesn't race a serial merge phase
    # that ignores interrupt checkpoints; re-impose order with ORDER BY downstream.
    con.execute("SET preserve_insertion_order = false;")
    return con

Preconditions before you rely on cancellation in production:

• Each concurrently cancellable query owns its own connection or con.cursor(), never a shared one.
• Every long scan is dispatched through an executor whose future you keep a reference to, so you can await it after interrupting.
• temp_directory is per-job (or per-worker) so an aborted scan’s spill files can be reclaimed without touching a sibling’s.
• The cancellation handler awaits the drained future before returning, so the engine finishes unwinding and releases memory.

con.interrupt() is thread-safe by design — it is meant to be called from a different thread than the one running the query, which is exactly the event-loop thread signalling a worker. That is the one cross-thread call the synchronous client sanctions.

Optimized Execution Pattern #

The core move is to catch CancelledError, interrupt the connection from the event-loop thread, then await the now-aborting future so the worker unwinds cleanly before the coroutine re-raises.

# BEFORE — cancellation leaks the query. The task is cancelled, but the scan
# keeps running on the executor thread until it finishes on its own.
async def run_scan_leaky(con, query: str):
    return await asyncio.to_thread(con.execute, query)   # cancel() cannot reach this

# AFTER — cancellation aborts the engine, then drains the worker.
async def run_scan_cancellable(con, query: str):
    loop = asyncio.get_running_loop()
    # Keep an explicit handle to the future so we can await it after interrupting.
    fut = loop.run_in_executor(None, con.execute, query)
    try:
        return await fut
    except asyncio.CancelledError:
        con.interrupt()                       # abort the blocking C call (thread-safe)
        # Let the worker observe the InterruptException and unwind: memory released,
        # spill files closed. return_exceptions swallows the resulting InterruptException.
        await asyncio.gather(fut, return_exceptions=True)
        raise                                 # preserve cancellation semantics upstream

The behavioural change is entirely in the except block. con.interrupt() sets an interrupt flag the engine polls at operator boundaries; the running ST_Contains or ST_Union scan raises duckdb.InterruptException in the worker thread within a scheduling quantum, releases its intermediates, and closes any spill file. Awaiting the drained future is not optional politeness — it is what guarantees the engine has finished cleaning up before your coroutine returns. Re-raising CancelledError keeps the task’s cancellation contract intact for whatever awaited it.

Timeouts route through the same handler, because wait_for cancels the inner awaitable when the clock expires:

async def run_scan_with_timeout(con, query: str, seconds: float):
    try:
        # wait_for cancels run_scan_cancellable on timeout, which triggers the
        # interrupt-and-drain path above — the scan is aborted, not orphaned.
        return await asyncio.wait_for(run_scan_cancellable(con, query), seconds)
    except asyncio.TimeoutError:
        # The engine was already interrupted inside the cancelled inner coroutine.
        raise

Cursor-per-task isolation #

When several scans share one process, con.interrupt() on the parent connection is a blunt instrument: it aborts whichever query is running on that object, so a cancellation for task A can kill task B. Isolate each task with con.cursor(), which returns a lightweight child connection over the same in-memory database. Interrupting a cursor aborts only that cursor’s query and leaves its siblings untouched — the same isolation principle detailed in sharing DuckDB connections across threads.

async def run_isolated(parent: duckdb.DuckDBPyConnection, query: str):
    cur = parent.cursor()                     # child connection, shares the same database
    loop = asyncio.get_running_loop()
    fut = loop.run_in_executor(None, cur.execute, query)
    try:
        return await fut
    except asyncio.CancelledError:
        cur.interrupt()                       # scoped to THIS query — siblings keep running
        await asyncio.gather(fut, return_exceptions=True)
        raise
    finally:
        cur.close()                           # release the cursor's resources deterministically

The broader rules for how many cursors a database can safely fan out, and where the shared buffer pool becomes the contention point, live in the connection and concurrency management guide. Cursor-per-task is also what lets you cancel one scan in a fan-out without disturbing the others dispatched under the running async spatial queries in Python pattern.

Diagnostic Queries & Plan Validation #

You cannot see a leaked scan in Python — the coroutine already returned — so watch the engine directly. Poll active memory and spill from a second cursor while a cancellation is in flight; a healthy interrupt drops both to baseline within a second or two.

# From a monitoring cursor, sample engine-side resource use during/after a cancel.
def resource_snapshot(mon: duckdb.DuckDBPyConnection) -> list:
    return mon.execute("""
        SELECT tag, memory_usage_bytes, temporary_storage_bytes
        FROM duckdb_memory()
        ORDER BY memory_usage_bytes DESC
    """).fetchall()

Metric thresholds that flag a botched cancellation:

If CPU stays pinned after a cancel, you almost certainly caught CancelledError without calling interrupt(), or you interrupted but never awaited the drained future — so the worker is still finishing the scan. Confirm the plan itself is interruptible with EXPLAIN: a scan that routes through an R-tree index scan checks the interrupt flag frequently at operator boundaries and aborts within milliseconds, whereas a single monolithic ST_Union over one giant group has coarse checkpoints and takes longer to notice the flag — one more reason to keep spatial working sets bounded.

Geometry Validation & Fallback Routing #

Cancellation and validity failures interact: an invalid-geometry error and an interrupt both surface as exceptions on the worker future, so distinguish them before deciding whether to retry. Repair inside SQL so the scan you may need to cancel never carries a poison row that would abort it for the wrong reason.

-- Filter/repair before the long scan so an ST_Union can't die on a bad ring
-- mid-flight and be mistaken for an interrupt.
SELECT id, ST_MakeValid(geom) AS geom
FROM parcels
WHERE NOT ST_IsValid(geom);

For a scan that keeps breaching memory_limit before you can even reach a timeout, do not rely on cancellation as a memory guard — chunk the work so each dispatched query is small enough to abort cleanly and cheaply.

async def scan_in_chunks(con, base_sql: str, bounds: list[tuple], seconds: float):
    # Each windowed chunk is an independently cancellable unit: a timeout aborts
    # one bounded scan, not an unbounded one, so interrupt latency stays low.
    for lo, hi in bounds:
        q = f"{base_sql} WHERE ST_XMin(geom) >= {lo} AND ST_XMin(geom) < {hi}"
        yield await run_scan_with_timeout(con, q, seconds)

Chunked cancellation keeps interrupt latency proportional to one window rather than the whole dataset, and it dovetails with the checkpointed, restartable units described in batch processing pipelines. When a chunk is aborted, its per-job temp_directory is the only cleanup surface — reclaim it with a single directory removal and re-run just that window.

Related #

See also

• Running async spatial queries in Python — the dispatch-and-stream pattern whose futures this page teaches you to cancel cleanly.
• Sharing DuckDB connections across threads — why con.cursor() scopes an interrupt to one query and how the shared buffer pool behaves under concurrency.
• Connection and concurrency management — connection topology and cursor fan-out limits that govern safe cancellation.

Up: Async Execution Patterns · Python & DuckDB Integration Workflows