DEV Community

Mads Hansen
Mads Hansen

Posted on

Your timeout returned. PostgreSQL kept working.

#mcp #database #postgres #ai

A user closes an AI chat.

The client times out.

Your MCP server returns an error.

Everyone assumes the database query stopped.

That assumption is expensive.

A PostgreSQL query can outlive the request that created it. It can keep a pooled connection busy, hold locks, scan rows, and consume capacity while the user has already moved on.

The problem is that “timeout” exists at several layers:

  • the AI agent has a deadline
  • the MCP transport has a timeout
  • the tool runner has another timeout
  • the connection pool has an acquisition timeout
  • PostgreSQL has statement_timeout and lock_timeout
  • a proxy may close the socket first

The fastest clock often ends only its own layer.

A 504 proves the proxy stopped waiting. It does not prove PostgreSQL stopped working.

Test the entire cancellation chain

A useful production test starts with something deliberately observable:

SELECT pg_backend_pid(), pg_sleep(30);
Enter fullscreen mode Exit fullscreen mode

Run it through the real MCP tool, driver, pool, and network path. Give the client a two-second deadline.

Then verify four separate facts:

  1. The client received a cancellation or deadline error.
  2. The MCP tool stopped active and queued work.
  3. The matching backend no longer runs the statement in pg_stat_activity.
  4. The pooled connection can safely execute the next query.

If you only verify the first item, you tested the UI—not cancellation.

The awkward cases matter more

The clean timeout is the easy fixture. Production needs the races:

  • disconnect while rows are streaming
  • cancel while waiting for a pooled connection
  • abort while blocked on a lock
  • terminate a worker during an active statement
  • cancel just as PostgreSQL completes
  • reuse the connection after cancellation

Queued work deserves special attention. If the caller cancels while waiting for the pool, the query must not start ten seconds later when a connection becomes available.

Database-side limits are the backstop

Application cancellation can fail. Processes crash. Abort handlers have bugs.

The database role should still have a bounded execution policy:

ALTER ROLE ai_readonly SET statement_timeout = '15s';
ALTER ROLE ai_readonly SET lock_timeout = '2s';
ALTER ROLE ai_readonly SET idle_in_transaction_session_timeout = '10s';
Enter fullscreen mode Exit fullscreen mode

Those values are examples, not universal defaults. Approved analytical workloads may need a separate role and a larger explicit budget.

The important part is that “supports timeouts” becomes a testable contract:

Cancelled requests cannot leave PostgreSQL work running beyond the database-side budget. Queued work never starts, active work is cancelled, pooled connections return in a known state, and the outcome is auditable.

A demo proves an AI agent can start a query.

Production readiness requires proof that the system can stop one.

I wrote the full test plan—including pg_stat_activity evidence, error categories, lock waits, streaming, and pool cleanup—here:

MCP server for Postgres: test query cancellation all the way to the backend

Top comments (0)