Proxy Adapter Query Timeouts

Why per-query timeouts exist

The ZedGi proxy bridge accepts arbitrary SQL from authenticated RPC callers and forwards it to the user's own database. Without a timeout, a single runaway query — a missing WHERE on a 100M-row table, a Cartesian join, a locking scan — can hold a pooled connection indefinitely. That one stuck connection starves the pool, blocking every other request queued behind it.

A 5‑second cap ensures:

• No single query can monopolize a connection. If a query runs longer than 5s, the database cancels it and the connection is returned to the pool.
• Predictable failure. Callers get a timeout error instead of an indeterminate hang.
• Fair resource sharing. All tenants/projects sharing the proxy get equal access to connection slots.

The timeout is deliberately applied by the proxy on the database server side (not via a client-side AbortController or Promise.race). A client-side cancel would orphan the query on the server — it would keep running, consuming CPU and holding locks, even though nobody is waiting for the result. Server-side cancellation is the only way to actually free database resources.

---

PostgreSQL (proxy/adapters/postgres.ts)

Mechanism

// SET LOCAL + user query sent in a single round-trip.
// pg returns only the last statement's result set.
const result = await client.query(
  `SET LOCAL statement_timeout = '5s'; ${sql}`,
  params,
);

statement_timeout is a PostgreSQL server parameter that aborts any statement taking longer than the configured duration. SET LOCAL scopes the setting to the current transaction — when the transaction ends, the parameter reverts to its previous value automatically.

The SET LOCAL runs immediately before the user's SQL inside the same multi-statement query string, so both statements share one network round-trip and execute within the same implicit transaction.

Connection-pool safety

Aspect	Behavior
Scope	LOCAL — transaction-scoped
When does it revert?	Immediately when the transaction commits or rolls back
Does it leak to the next pool consumer?	No — the setting dies with the transaction
Does the user query inherit the timeout?	Yes, because SET LOCAL and the target query run inside the same explicit or implicit transaction

Why SET LOCAL is safe for pooled connections:

PostgreSQL's SET LOCAL is explicitly designed for this use case. The setting is bound to the transaction, not the session. Even if the proxy crashes between the SET LOCAL and the client.release(), PostgreSQL automatically discards the setting when it detects the connection drop and rolls back the transaction. No explicit RESET is needed — the revert is guaranteed by the database itself.

Compare this to SET SESSION, which would persist on the connection after client.release() and leak the timeout to whichever request picks up that connection next. pg.Pool reuses connections, so a leaked SET SESSION would silently break legitimate long-running queries from unrelated callers.

Why the code does NOT need an explicit RESET in the finally block:

• SET LOCAL is automatically discarded on transaction end (commit or rollback).
• In auto-commit mode (the default for pg clients), each statement is its own implicit transaction. The timeout applies to the next statement and then evaporates.
• Adding RESET statement_timeout would be redundant — the value is already gone by the time the finally block runs.

Transaction method

The handleTransaction method does not apply a per-statement timeout. A transaction may legitimately hold locks across multiple statements and should not be killed mid-flight. If a transaction-level timeout is needed, the caller should set it in the SQL itself (SET LOCAL statement_timeout = ... inside the transaction body).

---

MySQL (proxy/adapters/mysql.ts)

Mechanism

// SET SESSION + user query sent in a single round-trip.
// mysql2 returns only the last statement's result set.
const [rows, fields] = await conn.query(
  `SET SESSION max_execution_time = 5000; ${sql}`,
  params,
);

max_execution_time is a MySQL server parameter (available since MySQL 5.7.8) that limits the execution time of SELECT statements in milliseconds. SET SESSION scopes the setting to the current session (connection).

The SET SESSION and the user query are sent together as a single multi-statement string, avoiding a second network round-trip.

Connection-pool safety

Aspect	Behavior
Scope	SESSION — connection-scoped
When does it revert?	Never — it persists until explicitly changed or the connection closes
Does it leak to the next pool consumer?	Potentially — see below
Does the user query inherit the timeout?	Yes, because it's set on the session before the query runs

Caveat — session-level persistence:

Unlike PostgreSQL's SET LOCAL, MySQL has no transaction-scoped equivalent for max_execution_time. SET SESSION persists for the lifetime of the MySQL connection. When conn.release() returns the connection to the pool, the 5‑second timeout remains active on that connection.

Why this is still safe in practice:

1. The next query on that connection also gets a fresh SET SESSION call. Every handleQuery invocation starts by setting max_execution_time = 5000 — it overwrites whatever was there before. The timeout is always re-applied to exactly 5s, so there is no accumulation or drift.

2. The timeout only affects SELECT statements. max_execution_time applies exclusively to read queries (SELECT). INSERT, UPDATE, DELETE, DDL, and transaction control statements (BEGIN, COMMIT, ROLLBACK) are unaffected. This means the handleTransaction method is not impacted by any residual timeout from a prior handleQuery call.

3. The proxy does not run user queries outside handleQuery. Every query path through the adapter sets the timeout explicitly. There is no code path where a user query executes on a connection without first calling SET SESSION max_execution_time.

4. mysql2's connection pool does not expose raw connections to callers. Connections are only ever used inside handleQuery / handleTransaction / handlePing, all of which manage their own state.

Theoretical edge case — what if the proxy crashes after SET SESSION but before conn.release()?

The MySQL server will detect the TCP connection drop and close the session. The session-level setting dies with the connection. The pool creates a fresh connection for the next request, with default (max_execution_time = 0, meaning no limit). No leak survives a connection close.

Transaction method

The handleTransaction method does not apply max_execution_time. This is intentional — the timeout only applies to SELECT statements anyway, and a transaction may contain DML that should not be interrupted.

---

Summary

Concern	PostgreSQL	MySQL
Timeout mechanism	statement_timeout	max_execution_time
Scope	LOCAL (transaction)	SESSION (connection)
Applies to	All statements	SELECT only
Auto-reverts on release	Yes (transaction end)	No (persists on connection)
Leaks to next pool user?	No	No — overwritten on next handleQuery
Survives proxy crash?	No	No — dies with TCP connection
Explicit RESET needed?	No	No — next call overwrites it

Both approaches are safe for the proxy's connection-pool model. PostgreSQL's SET LOCAL is the cleaner pattern because the database itself guarantees cleanup at transaction boundaries. MySQL's SET SESSION relies on the proxy's discipline of always setting the timeout before every query — which it does.