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

The user closed the chat. The AI client timed out. Your MCP server returned an error.

PostgreSQL may still be working.

That gap matters. A database query can outlive the request that created it, keep a pooled connection busy, hold locks, scan millions of rows, and consume the exact capacity your next request needs. An MCP server for Postgres is not production-ready because its HTTP timeout works. It is production-ready when cancellation reaches the database backend.

There are several different clocks

A typical AI-to-PostgreSQL request crosses multiple boundaries:

1. the user or agent has an overall deadline;
2. the MCP client has a transport timeout;
3. the MCP server has a tool-execution timeout;
4. the connection pool may have an acquisition timeout;
5. PostgreSQL has statement_timeout and possibly lock_timeout;
6. the upstream proxy or load balancer may close the connection first.

If these limits are configured independently, the fastest timeout often ends only the layer that observed it. A reverse proxy can return 504 while the application continues waiting. An application future can be abandoned while the driver still waits for rows. A client disconnect can disappear inside a queue before the database even starts.

The safe design is a single request deadline propagated across every stage, with database-side limits as a final backstop.

Cancellation is a chain, not a setting

For PostgreSQL, cancellation usually requires an active driver operation to send a protocol-level cancel request or close the backend connection. That behavior depends on the driver, pool, framework, and the code that handles abort signals. Setting a JavaScript, Python, or HTTP timeout alone does not prove it happens.

The chain you need to verify is:

user abort
  -> MCP transport abort
  -> tool execution receives cancellation
  -> queued or active work is stopped
  -> PostgreSQL backend stops the statement
  -> connection returns to the pool in a known state
  -> audit record explains why the request ended

Every arrow is a possible failure point.

A concrete PostgreSQL cancellation test

Do not begin with a real expensive report. Use a deterministic query that is easy to observe:

SELECT pg_backend_pid(), pg_sleep(30);

Run it through the same MCP tool, driver, pool, and network path used in production. Give the client a two-second deadline, then capture four pieces of evidence:

• the client received a timeout or cancellation response;
• the MCP server stopped executing the tool;
• the corresponding backend disappeared from pg_stat_activity or returned to an idle state without the query;
• a subsequent query can safely reuse the connection.

A useful observer query is:

SELECT pid, state, wait_event_type, wait_event,
       query_start, now() - query_start AS age,
       left(query, 160) AS query
FROM pg_stat_activity
WHERE application_name = 'your-mcp-service'
ORDER BY query_start;

Tag database sessions with a recognizable application_name. Better still, attach a request identifier through safe session metadata or structured query comments so the backend can be correlated with the MCP audit event.

Test more than the happy timeout

A two-second timeout is only the first fixture. A reliable MCP server setup should cover the lifecycle cases that create orphaned work:

• Client disconnect: close the socket while rows are still being produced.
• Queue cancellation: abort while waiting for a pooled connection; the query must never start later.
• Lock wait: block a statement behind another transaction and verify lock_timeout plus cancellation behavior.
• Streaming result: cancel after the first batch; the server must stop fetching additional rows.
• Server shutdown: terminate an MCP worker during an active query and inspect pool/backend cleanup.
• Cancellation race: cancel just as the query completes; the connection must not be left in a failed transaction.

Run the same tests with pooling enabled. A direct connection that cancels correctly does not prove a pooled connection is clean enough to reuse.

Database-side timeouts are the seat belt

Application cancellation can fail. Processes crash. Networks partition. Abort handlers contain bugs. PostgreSQL should still enforce a maximum execution budget for the AI role.

Prefer role- or transaction-scoped controls over a global value that surprises every workload:

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';

Choose values from your workload and service objectives, not from this example. Some approved analytical queries legitimately need more time. If so, create a separate role or tool with an explicit budget instead of silently removing the guardrail for everything.

Query limits also belong beside row limits, concurrency controls, and plan checks. The query-plan regression budget catches work that became expensive; cancellation guarantees that rejected or abandoned work actually stops.

Keep the error contract precise

“Database error” is not enough for an AI client deciding whether to retry. Return a structured category such as:

• client_cancelled — the caller no longer wants the result;
• deadline_exceeded — the end-to-end budget expired;
• database_statement_timeout — PostgreSQL enforced its limit;
• pool_acquisition_timeout — execution never began;
• lock_timeout — the query could not safely obtain a lock.

These categories should drive different behavior. A client cancellation should not be retried. Pool pressure might justify bounded backoff. A statement timeout usually needs a smaller question, a better index, or an approved analytical path—not three immediate retries of the same SQL.

Record the category, deadline, elapsed time, backend identifier, cancellation result, and rows returned in your AI database audit trail. Do not log sensitive row contents merely to prove cancellation worked.

What to ask when comparing PostgreSQL MCP servers

When evaluating the best MCP servers for PostgreSQL, ask for an observable test rather than a checkbox:

1. Can a client deadline propagate to active and queued database work?
2. Does the server apply PostgreSQL-side statement and lock budgets?
3. Are pooled connections validated after cancellation?
4. Can operations correlate an MCP request with pg_stat_activity?
5. Are timeout classes exposed without leaking database internals?
6. Does the test suite include disconnects, lock waits, streaming, and races?

A demo proves that an AI can start a query. Production readiness requires proof that the system can stop one.

The operational contract

Write the guarantee down:

When a request is cancelled or its deadline expires, no associated PostgreSQL statement may continue beyond the database-side execution budget. Queued work must not start, active work must be cancelled, pooled connections must return in a known state, and the outcome must be auditable.

That sentence is testable. “Supports timeouts” is not.

If you are connecting AI workflows to PostgreSQL, start with the MCP server setup guide, then add cancellation propagation to the production test plan before the first expensive natural-language query reaches the database.