Session Budgets or Driver Caps: A Debate for Agent SQL Exploration

A composite warehouse incident, rather than a claimed personal outage, is enough to frame this operational choice. An exploratory agent drafted a wide analytical query against a shared reporting database during an ordinary afternoon. The statement sorted a large fact table and held one pooled connection much longer than the dashboard jobs expected. Waiting sessions alone exhausted the pool, so a production lock was not required for the queue to stall.

Teams that let agents explore SQL face the same narrow question after that kind of stall. Should the database session carry the budget, or should the client driver refuse excess work first? Both positions are credible, and both fail in specific, observable ways that a small fixture can expose. The rest of this note compares those positions, then gives a measurement workflow you can rerun.

Two positions, stated without slogans

A session budget is a set of PostgreSQL parameters applied to the connection before the agent statement starts. Typical controls include statement_timeout, lock_timeout, and idle_in_transaction_session_timeout, and each value is a millisecond bound on the session. The server cancels the statement, or the idle transaction, when a bound is crossed, regardless of which client sent the work. That independence is the main evidence for this position, because agents often bypass a careful application wrapper.

A driver cap is a limit enforced in the client before, or while, rows are fetched from the server. Examples include a read-only transaction flag, a client-side query timeout, and a hard maximum on fetched rows. The appeal is precision, because the tool can stop a result stream without waiting for a server timeout to elapse. The weakness is coverage, because any second client, console, or retry path can skip the same wrapper.

These positions are not cosmetic variants of the same control, and the difference shows up in timing. A session budget still lets the server start the work, and only then aborts the statement. A driver cap can refuse further fetches, yet the server may already have produced a batch of rows. Measuring that split matters more than preferring whichever control happens to be easier to configure locally.

Evidence that should settle the argument

Evidence should come from a fixture you control, not from a vendor latency chart or a single anecdote. The useful observations are which bound fires, how many rows leave the server, and whether the pool slot is released. Cost estimates alone do not settle this debate, and neither does a successful run against a tiny sample. A query can be cheap to plan and still pin a connection while a client slowly drains a wide result.

PostgreSQL documents statement_timeout as a client-connection setting that aborts any statement exceeding the configured millisecond limit. The same manual chapter documents lock_timeout and idle_in_transaction_session_timeout as separate bounds that use different failure triggers. A single timeout therefore does not cover lock waits, long statements, and abandoned transactions at once. I am using that manual as the primary description of server behavior, and I am not claiming a hosted benchmark.

Primary source: Client Connection Defaults

Where a free draft host fits

Disclosure: This article was prepared as part of MonkeyCode's product outreach.

The operator describes MonkeyCode as an open-source project that offers free model access and a free server option. I am not restating a token quota, a model name, a hardware size, or a duration in this note. Those details were not attached as a primary source, and product terms of that kind change without notice. Check the current project documentation before you plan any workload around a number you saw elsewhere.

In this workflow, free model access is only a drafting aid for two candidate statements, not a safety judge. The free server option is only a disposable PostgreSQL host for the fixture, useful when a local container is inconvenient. Neither availability claim replaces the session budget or the driver cap, and neither claim decides the debate. If those options are unavailable, run the same steps on any Postgres instance you are allowed to reset.

Measurement workflow

The examples below are unexecuted proposals, and they are labeled so they are not mistaken for measured results. They use a tiny table so you can see a cancellation path without scanning a real warehouse table. Replace the connection string with a database you are allowed to reset, and never point the script at production. A passing local run still needs the decision table, because one green script does not rank the two positions.

1. Create a resettable fixture

Create the fixture table, load twenty thousand wide rows, and collect planner statistics before either probe starts. The payload column is intentionally wide, so a careless projection costs memory even when the row count looks modest. Identity keys keep the insert simple, and the region column gives the grouped query a small, stable cardinality. Run ANALYZE so the planner is not the accidental source of a timeout you meant to attribute to data volume.

-- Unexecuted example. Run only on a disposable database.
DROP TABLE IF EXISTS explore_events;
CREATE TABLE explore_events (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  region text NOT NULL,
  payload text NOT NULL,
  occurred_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO explore_events (region, payload)
SELECT
  CASE WHEN g % 4 = 0 THEN 'east' ELSE 'west' END,
  repeat('x', 200)
FROM generate_series(1, 20000) AS g;

ANALYZE explore_events;

2. Record a session budget on a fresh connection

Open a fresh connection, apply three local timeouts, and expect a cancellation rather than a quiet success. The sleep call is a synthetic delay, so the bound can fire without depending on your disk or CPU speed. A completed statement is not evidence that the budget works, so keep adjusting until you observe the error. The error you want, when the bound is actually hit, is SQLSTATE 57014, which means query_canceled.

-- Unexecuted example. Expect query_canceled if the sleep exceeds the budget.
BEGIN READ ONLY;
SET LOCAL statement_timeout = '150ms';
SET LOCAL lock_timeout = '100ms';
SET LOCAL idle_in_transaction_session_timeout = '2s';

SELECT region, count(*), pg_sleep(1)
FROM explore_events
GROUP BY region
ORDER BY region;

COMMIT;

3. Record a driver cap on another connection

This client stops after fifty fetched rows, which is the concrete driver-cap claim under test today. It does not prove the server avoided work, because a named cursor may already have filled one batch. Capture pg_stat_activity during the fetch if you need to separate client silence from server idle time. Close the cursor by leaving the block, so an abandoned hold does not imitate a pool leak.

# Unexecuted example. Requires psycopg 3 and a disposable DSN.
import os
import psycopg

dsn = os.environ["EXPLORE_DSN"]
sql = """
SELECT id, region, payload
FROM explore_events
ORDER BY id
"""

with psycopg.connect(dsn) as conn:
    conn.execute("SET statement_timeout = '30s'")
    with conn.cursor(name="agent_probe") as cur:
        cur.itersize = 100
        cur.execute(sql)
        rows = []
        for row in cur:
            rows.append(row)
            if len(rows) >= 50:
                break
print(len(rows))

4. Compare one shell observation

Run the activity query while each probe is in flight, using a role that can read pg_stat_activity. Note whether the session remains active, or idle in transaction, after the client has stopped reading rows. In practice, that server-side row often reverses a preference formed entirely from the client application logs. Repeat the check for both probes, because a single snapshot can miss a session that already exited.

# Unexecuted example. Replace the DSN, and do not use a production role.
psql "$EXPLORE_DSN" -c "SELECT pid, state, wait_event_type, query_start
FROM pg_stat_activity
WHERE datname = current_database()
  AND pid <> pg_backend_pid();"

Decision table

Observation on the fixture	Favors session budgets	Favors driver caps	Practical reading
A second client can open a session	Yes	No	The server bound is mandatory
A statement ends quickly but streams many rows	Weak alone	Yes	Add a fetch cap
A client dies and leaves a transaction open	Yes, if the idle timeout is set	No	Set the idle-transaction timeout
You need the error in the server log	Yes	Usually no	Keep the server bound
The wrapper is the only permitted client	Still useful as a backstop	Yes for row stops	Use both, with the server last

Decision rule

Adopt session budgets whenever any client other than your wrapper can reach the exploration database at all. Add driver caps when the tool streams rows and a finished statement can still exhaust memory or pool time. Do not treat a driver cap as a substitute for statement_timeout, lock_timeout, or an idle-transaction timeout. If the fixture cannot show a cancellation or a stopped fetch, do not promote the control into a shared tool.

Use the server budget as the backstop even when the driver cap looks sufficient on one happy script. Retry paths, SQL consoles, and later agents are new clients, and they will not inherit yesterday's wrapper settings. A read-only transaction reduces write risk, but it does not by itself limit CPU, locks, or result size. Keep the three server settings even if the fetch loop already stops at a small row count.

Limitations

This fixture uses twenty thousand rows, so it will not reproduce production planner choices or cache behavior. A statement_timeout that fires here may never fire on a selective query with a huge projected payload. Named cursors reduce client memory, yet the server can still perform substantial work before the first fetch. Free model drafts can propose unsafe SQL, so a human should read both candidates before any execution.

I did not run these statements against a hosted service for this draft, and I am not reporting timings. I am also not reporting quotas, hardware sizes, or permanence for the free server option mentioned above. Availability of that option can change, and a shared host is the wrong place for private data or load generation. Roles that can write, or that can change global configuration, sit outside this read-only exploration workflow.

Who should skip this approach

Skip this approach if the workload is latency-sensitive OLTP and a canceled statement is worse than a slow one. Skip it if policy forbids session-level settings and you also cannot wrap every client that reaches the database. Skip it if the rows are production records and the only available host is a shared evaluation server. Skip it if the agent must apply migrations, because write probes need controls beyond read exploration.

Closing the choice

Session budgets and driver caps answer different failure modes, and the fixture above makes that split visible. Prefer the server bound when you cannot enumerate every client, and add the driver bound when row volume is the risk. Re-run the four steps after any change to pool size, timeout values, or the agent tool's fetch loop. If a local database is inconvenient, confirm the current free-server terms, then run only this disposable fixture there.