[BUG] PostgreSQL query tools return 500 (Npgsql 'Exception while reading from stream') on long-running queries

[ss4aman]

Describe the bug

Long-running queries issued through the Azure MCP PostgreSQL tools (postgres database query, postgres table schema get) fail with HTTP 500 Internal Server Error. The underlying Npgsql exception is:

Exception while reading from stream

Two factors combine to cause this:

1. Default command timeout is too low. The command timeout falls back to Npgsql's built-in default of 30 seconds, so queries such as an exact COUNT(*) over a large table, or any analytical query taking longer than 30s, time out and surface as a 500.
2. No keepalive on the connection. While a single command runs, no application data flows over the TCP socket. An intermediary in Azure (Load Balancer / NAT gateway, ~4 minute idle timeout) silently drops the idle connection. Npgsql then surfaces this as Exception while reading from stream → HTTP 500, even when the server itself is still processing the query.

This was hit by a customer running queries in the ~1–5 minute range and reproduced consistently.

Expected behavior

• Reasonable long-running read queries (up to a few minutes) complete successfully without a 500.
• The command timeout is configurable per call and server-wide.
• The connection is not dropped by an intermediary idle timeout while a query is actively running.

Actual behavior

• Queries longer than ~30s (or that stay idle on the socket past the load balancer idle timeout) fail with 500 Internal Server Error / Exception while reading from stream.
• There is no way to raise the timeout for an individual query.

Affected tools:

• postgres database query
• postgres table schema get

Reproduction Steps

1. Configure the Azure MCP PostgreSQL tools against an Azure Database for PostgreSQL Flexible Server.
2. Run a query that takes longer than 30 seconds, e.g.:

   SELECT pg_sleep(280), 1;

   or an exact count over a large table:

   SELECT COUNT(*) FROM <large_table>;

3. Observe the request fail with 500 Internal Server Error and Exception while reading from stream.

Proposed fix (validated)

The following changes resolved the issue for the customer:

1. Add an explicit, configurable command timeout with this precedence:
   • explicit per-call command-timeout option,
   • AZURE_MCP_POSTGRES_COMMAND_TIMEOUT environment variable (server-wide default),
   • default of 300 seconds (5 minutes),
   • 0 = wait indefinitely.
2. Enable keepalives on the connection string so the socket stays active during long-running queries:
   • KeepAlive = 30 (Npgsql application-level keepalive every 30 idle seconds),
   • TcpKeepAlive = true.

With both changes, the 5-minute pg_sleep and large COUNT(*) queries complete successfully instead of returning a 500.

Environment

• Tool: Azure MCP Server — PostgreSQL tools (Azure.Mcp.Tools.Postgres)
• Driver: Npgsql
• Backend: Azure Database for PostgreSQL Flexible Server

[ss4aman]

Additional findings & recommendations

After further testing against an Azure Container Apps-hosted MCP server, a few important clarifications and recommendations:

Root cause is the outbound SNAT idle timeout

Container Apps routes outbound connections (container → PostgreSQL) through Azure's SNAT layer, which is backed by a Load Balancer / NAT with a ~4-minute idle timeout. While a single long query runs, no rows flow back, so the socket is idle and the SNAT mapping is dropped. Npgsql then surfaces this as Exception while reading from stream → HTTP 500. (Symptom: the failure can come back with "duration":0, i.e. it fails instantly on a now-stale socket rather than after a timeout.)

Which keepalive actually helps (important nuance)

• KeepAlive (application-level) only probes a connection while it is idle in the pool — it does nothing during an actively executing command. So it does not protect a single long-running query.
• TcpKeepAlive = true alone uses the OS default idle time (7200s on Linux), far longer than the 4-min SNAT window — so it doesn't help either.
• TcpKeepAliveTime / TcpKeepAliveInterval are the fix. ⚠️ These are in seconds in Npgsql (not milliseconds — easy to get wrong). Setting TcpKeepAliveTime = 30 makes the OS send TCP probes every 30s during the query; each probe resets the LB/SNAT idle timer, so the flow never goes idle.

Final connection-string settings used:

KeepAlive = 30,
TcpKeepAlive = true,
TcpKeepAliveTime = 30,      // seconds
TcpKeepAliveInterval = 10   // seconds

Do you need a NAT Gateway?

No — not for query duration. TCP keepalive probes count as traffic and reset the idle timer, so a 30s probe interval (< 4-min idle window) keeps the connection alive for arbitrarily long queries. A NAT Gateway is only needed for SNAT port exhaustion under high concurrency, or if you want a longer configurable hard idle ceiling as defense-in-depth.

Remaining timeout ceilings (after the fix)

The keepalive removes the idle-drop ceiling; it does not make queries run forever. Effective max query time = min(...) of:

• Npgsql Command Timeout (defaulted to 300s / 5 min here; per-call override + AZURE_MCP_POSTGRES_COMMAND_TIMEOUT; 0 = infinite)
• Server-side statement_timeout (Azure Postgres server parameter)
• PgBouncer limits if connecting through the built-in pooler (port 6432, e.g. query_wait_timeout ~120s)
• NAT Gateway / LB idle timeout (now mitigated by keepalive)

Recommendation summary

1. Add explicit, configurable Command Timeout (default 300s; env var + per-call override; 0 = infinite).
2. Set TcpKeepAliveTime/TcpKeepAliveInterval in seconds (~30s) so long queries survive the Container Apps / Load Balancer SNAT idle timeout.
3. Keep TcpKeepAlive = true and KeepAlive = 30 for idle pooled connections.
4. NAT Gateway is not required for long queries — only consider it for SNAT port exhaustion at high concurrency.
5. For queries that must exceed the default, raise command-timeout and verify no shorter server-side statement_timeout / PgBouncer limit applies.

These changes were validated and resolved the 500s for the customer.