Streaming CSV (?_stream=on) returns HTTP 200 with the error in the body when a query fails mid-stream — CDN caches the error

[fgregg]

Summary

When a streamed CSV export (?_stream=on) fails after streaming has started — e.g. the backend query OOMs, hits the time limit, or is interrupted — datasette returns HTTP 200 with the error message written into the response body, instead of a 5xx. A CDN in front (Cloudflare here) caches the 200 (5xx would not be cached), so a transient origin error gets frozen into the edge cache as a "successful" response and served to everyone until the TTL expires or the cache is purged.

This bit us hard on the DuckDB deployment: a large export OOM'd once while the box was under a low memory_limit, the 200-with-error got cached with a 1-year CSV edge-TTL, and it kept being served for hours after the origin was fixed — masking a fully-working origin and sending us chasing a phantom "128 MB" bug that only existed in cache.

Repro

Export a table large enough that the streaming query (a ... ORDER BY pk sort) exceeds the backend memory limit:

GET /<db>/<bigtable>.csv?_size=max&_stream=on

with e.g. memory_limit: 30MB (datasette-duckdb). Observed:

HTTP status = 200
Content-Type: text/plain; charset=utf-8
body (373 bytes):
  Out of Memory Error: could not allocate block of size 256.0 KiB (29.x MiB/30.0 MiB used)
  ...

i.e. a 200 whose body is the DuckDB error text, not a 500.

Root cause

datasette/views/base.py stream_csv / stream_fn: the streaming response commits the 200 status + headers as soon as streaming begins (the first body write / header row), and only then iterates the backend's execute_stream. For a sort-based export the expensive, failure-prone work (the full sort) happens on the first generator step — after the 200 is already on the wire. The except Exception handler then does await r.write(str(ex)); return, appending the error to the already-200 body:

async for columns, rows in db.execute_stream(stream_sql, ..., chunk_size=...):
    ...
except Exception as ex:
    sys.stderr.write("Caught this error: {}\n".format(ex))
    await r.write(str(ex))   # <-- error goes into a 200 body
    return

Because the status is already 200, there is no way to signal failure to the client/CDN, and the corrupt-but-200 CSV is cacheable.

Impact

• CDN cache poisoning: a one-off transient error (OOM / QueryInterrupted at sql_time_limit / client of a flaky query) is cached as a 200 and served as "success" for the whole TTL — potentially long after the origin recovers. With immutable-data edge TTLs (we use ~1 year, purged on data refresh) this persists until a manual/scheduled purge.
• Corrupt downloads: a user gets a truncated CSV that silently ends with an error line, with a 200 status.
• Engine-agnostic: any backend whose stream_query can raise mid-stream (SQLite interrupts too) hits this.

Suggested fix

Don't commit the 200 until the stream is known to be producing rows. Concretely: pull the first chunk from execute_stream before sending response headers. If the query raises during that first step (where sort-based exports do all their work), return a normal error response (4xx/5xx) — which CDNs won't cache. Only once the first batch is in hand, send 200 + headers and stream the rest.

Errors that occur after the first successful chunk are unavoidable with chunked transfer (you can't un-send a 200), but they're far rarer for the dominant sort-then-stream shape, and at minimum such a partial response should be sent with Cache-Control: no-store so it can't be cached. (A simpler interim mitigation: send Cache-Control: no-store on the streaming CSV response whenever it terminates via the error path.)

Found via the datasette-duckdb deployment (related: #30 memory bounding, #31 shared instance).