AI agents write PostgreSQL like Python • pgmi
AI agents write PostgreSQL like Python#<br>By Alexey Evlampiev<br>Give a coding agent a PostgreSQL-first backend to build and it will write you working SQL. It will also, with remarkable consistency, write you Python — Python’s exception-driven control flow, Python’s trust in casts, Python’s habit of validating wherever the code happens to be — transliterated into PL/pgSQL, where those idioms carry costs the agent never sees.<br>This article is a field report. It draws on two internal reviews of a production line-of-business backend built database-first — a few hundred SQL files across roughly two dozen domains, with a kernel/handler split and a thin HTTP gateway — where AI coding agents wrote most of the SQL. The reviews rated every finding and verified each against the code before reporting it. The failure modes below are the ones that recurred; the discipline in the second half is what the same codebase used, in its healthy majority, to avoid them.<br>One scoping note up front. Published work on LLM-generated SQL quality — Readyset’s “Why LLMs write incorrect SQL,” the Spider and BIRD text-to-SQL benchmarks — covers declarative failures: wrong joins, bad grouping, missing predicates. We found no published field reports on how agents fail at procedural SQL — PL/pgSQL exception handling, handler-layer control flow, boundary validation. That absence is why this report exists.<br>Failure mode 1: try/except, in a language where catching means a savepoint#<br>The single most consistent agent habit: signaling ordinary outcomes by raising exceptions, then catching them a layer up to pick an HTTP status. The reviews found a dozen or so kernel functions doing this — “not found,” “already processed,” “nothing left to assign” — all normal outcomes, all expressed as RAISE EXCEPTION.<br>The shape (anonymized, runnable):<br>-- Agent-written kernel: raises to say "nothing to do"<br>CREATE FUNCTION core.mark_alert_seen(p_id uuid)<br>RETURNS core.alert<br>LANGUAGE plpgsql<br>AS $$<br>DECLARE<br>v_row core.alert;<br>BEGIN<br>UPDATE core.alert<br>SET is_seen = true, seen_at = now()<br>WHERE id = p_id AND NOT is_seen<br>RETURNING * INTO v_row;
IF NOT FOUND THEN<br>RAISE EXCEPTION 'alert % not found or already seen', p_id<br>USING ERRCODE = 'no_data_found';<br>END IF;
RETURN v_row;<br>END;<br>$$;
-- Agent-written handler: catches to pick a status code<br>BEGIN<br>v_row := core.mark_alert_seen(v_id);<br>RETURN api.json_response(200, to_jsonb(v_row));<br>EXCEPTION<br>WHEN no_data_found THEN<br>RETURN api.problem_response(404, 'Not Found');<br>END;<br>In Python, this is idiomatic. In PL/pgSQL, it is not a stylistic choice — it changes the transaction machinery. Every BEGIN ... EXCEPTION block establishes an implicit subtransaction: PostgreSQL sets a savepoint on entry so it can roll persistent changes back to the block boundary if a handler fires. The documentation is unambiguous about the cost: a block containing an EXCEPTION clause is “significantly more expensive to enter and exit than a block without one,” and the manual’s own tip is not to use EXCEPTION without need.<br>To be precise about the mechanics, because precision matters here: the subtransaction consumes a subtransaction XID only when the block performs DML — a read-only exception block is assigned none and is comparatively benign. And a single shallow exception block on a request path is cheap in absolute terms. The damage is a concurrency-and-scale effect, not a per-block tax.<br>PostgreSQL caches 64 subtransaction XIDs per backend; workloads that exceed that under load force snapshot visibility checks through the pg_subtrans SLRU, where they contend. GitLab’s engineering team published the canonical incident: under a specific combination of a long-running transaction and subtransaction-heavy traffic, replica throughput collapsed from roughly 360,000 to 50,000 transactions per second, with unrelated queries timing out cluster-wide, until they eliminated subtransactions from hot paths — a workload-specific incident, cited here for the failure class rather than the numbers. An agent that stamps an exception block into every handler and kernel — once per request, on every request path — is quietly signing you up for that class.<br>The deeper problem is architectural, though, and it would remain even if subtransactions were free: the exception channel is being used as a data channel. The kernel knows a perfectly ordinary fact (“no unseen alert with that id”) and encodes it as an error, which the handler must then decode by SQLSTATE. Two functions now share a contract that lives in neither’s signature.<br>The fix deletes code. A kernel that returns NULL for “nothing to do” needs no exception machinery at all — and can usually stop being PL/pgSQL entirely:<br>-- Kernel: pure SQL. Returns the row, or NULL if there was nothing to do.<br>CREATE FUNCTION core.mark_alert_seen(p_id uuid)<br>RETURNS...