PostgreSQL Streaming Query Interrupted Mid‑Execution in an Air‑Gapped Environment

Problem Description

An AI inference service running inside a sealed laboratory network streams large SELECT result sets from PostgreSQL using a custom asynchronous Python client. During execution the client receives only a partial set of rows and then the connection is terminated. Typical symptoms include:

• Log entry on the client side: could not receive data from server: Connection reset by peer
• PostgreSQL server log line (with log_line_prefix set to %t [%p]: ):

  2024-06-12 14:23:45.123 [12345]: LOG:  unexpected EOF on client connection

• Occasional server message: canceling statement due to user request even though the client did not issue a cancel.
• Interruption occurs after a variable number of rows (often around 10 000) and is reproducible only under load or when the AI model spends time processing each row.

The environment is air‑gapped: no external network, a strict firewall appliance sits between the application host and the database server, and all traffic travels over a local Ethernet segment.

Root Cause Analysis

The failure is not a SQL error; it is a transport‑level termination of the TCP socket. The following factors, documented in the PostgreSQL manuals and community reports, converge to produce the observed mid‑stream drop:

1. Idle‑time detection by the network perimeter. The firewall appliance silently drops TCP connections that show no traffic for 30 seconds. While the query is executing, the server continuously sends rows, but the client processes each row with a CPU‑intensive model inference step, creating application‑level idle periods where no data is read from the socket.
2. Missing keep‑alive packets from the client. The default Linux tcp_keepalive_time is 7200 seconds (2 hours). In the air‑gapped lab the client never sends keep‑alive frames, so the firewall’s 30‑second idle timeout triggers a RST packet, which PostgreSQL logs as “unexpected EOF on client connection”.
3. Statement timeout mismatch. The server parameter statement_timeout is often set to 60 seconds for safety. If the AI application takes longer than this to consume rows, PostgreSQL aborts the statement, logs “canceling statement due to user request”, and closes the socket. This appears as a client‑side reset.
4. Async driver semantics. Both libpq asynchronous query execution (Chapter 54) and the higher‑level asyncpg driver require the application to poll the connection while data is pending. Missing await conn.poll() (as reported in Stack Overflow question 73584291) leaves the driver unaware that the socket is still readable, causing the event loop to consider the task idle and let the firewall close it.

Combined, these conditions cause intermittent “mid‑stream” termination that only surfaces under the specific latency and processing characteristics of the AI workload.

Investigation and Debugging Steps

1. Capture Server Logs

2024-06-12 14:23:45.123 [12345]: LOG:  unexpected EOF on client connection
2024-06-12 14:23:45.124 [12345]: LOG:  canceling statement due to user request

Enable verbose logging (log_min_error_statement = error) and a detailed log_line_prefix to correlate timestamps with client events.

2. Verify Network Idle Timeout

# On the firewall appliance (example CLI)
show firewall idle-timeout
# Expected output
Idle timeout: 30 seconds

3. Inspect TCP Keep‑Alive Settings

# On the client host
sysctl net.ipv4.tcp_keepalive_time
net.ipv4.tcp_keepalive_time = 7200

# Check current values
sysctl -a | grep tcp_keepalive

4. Reproduce with a Minimal Asyncpg Script

import asyncio
import asyncpg

async def stream():
    conn = await asyncpg.connect(dsn="postgres://user:pwd@dbhost/db")
    async with conn.transaction():
        async for record in conn.cursor("SELECT generate_series(1, 200000)"):
            # Simulate heavy processing
            await asyncio.sleep(0.001)   # 1 ms per row ≈ 200 s total
            print(record)

asyncio.run(stream())

Observe that after ~10 000 rows the script aborts with:

asyncpg.exceptions.PostgresError: could not receive data from server: Connection reset by peer

5. Check Statement Timeout

# In psql
SHOW statement_timeout;
# Example output
statement_timeout
------------------
60000   # 60 seconds

6. Verify Event‑Loop Keep‑Alive Emission

# Using psutil to monitor socket activity (optional)
import psutil, time
s = psutil.net_connections(kind='tcp')
print([c.raddr for c in s if c.status == 'ESTABLISHED'])

Resolution

1. Align Keep‑Alive Parameters

Configure the client OS to send keep‑alive probes more frequently than the firewall’s idle timeout.

# /etc/sysctl.d/99-keepalive.conf
net.ipv4.tcp_keepalive_time = 15
net.ipv4.tcp_keepalive_intvl = 5
net.ipv4.tcp_keepalive_probes = 3

Apply changes:

sudo sysctl -p /etc/sysctl.d/99-keepalive.conf

2. Increase or Disable Server‑Side statement_timeout

For long‑running streaming queries, set the timeout per session or globally.

# In postgresql.conf
statement_timeout = 0   # disables timeout

Or per‑connection:

await conn.execute("SET statement_timeout = 0")

3. Enable Application‑Level Keep‑Alive in Async Drivers

Both asyncpg and psycopg2 expose socket options.

# asyncpg example
conn = await asyncpg.connect(
    dsn="postgres://user:pwd@dbhost/db",
    keepalive=True,               # driver‑level keep‑alive
    keepalive_interval=10         # seconds
)

For psycopg2 with a server‑side cursor:

import psycopg2
import psycopg2.extensions

conn = psycopg2.connect(dsn="postgres://user:pwd@dbhost/db")
conn.set_session(keepalive=True)
conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)

cur = conn.cursor(name="stream_cursor")
cur.itersize = 2000   # fetch size
cur.execute("SELECT generate_series(1, 200000)")

for row in cur:
    process(row)   # heavy inference

4. Ensure Proper Async Polling

When using libpq directly or a thin wrapper, always await poll() after each recv() to keep the driver state machine alive.

# libpq pseudo‑code
while True:
    if PQisBusy(conn):
        PQconsumeInput(conn)
        while PQisBusy(conn):
            await asyncio.sleep(0.01)   # allow event loop to drive I/O
    else:
        break

5. Adjust Firewall Idle Timeout (if possible)

If the perimeter device can be reconfigured, raise the idle timeout to at least the longest expected processing pause (e.g., 300 seconds) or enable TCP keep‑alive forwarding.

Verification

After applying the changes, repeat the minimal streaming script. Successful execution should print all rows without interruption.

2024-06-12 14:45:01.001 INFO  Streaming 200000 rows completed

Additional verification steps:

• Check PostgreSQL logs – no “unexpected EOF” or “canceling statement” entries.
• Monitor netstat -tanp or ss -ti to confirm keep‑alive probes are being sent (look for “keepalive” flag).
• Validate that statement_timeout is 0 for the session: SHOW statement_timeout;
• Run a load test (e.g., pgbench with a streaming cursor) to ensure stability under concurrent AI inference jobs.

Prevention and Best Practices

Practice	Why It Helps
Enable TCP keep‑alive on both OS and driver	Prevents silent firewall drops on idle periods.
Set statement_timeout = 0 for streaming workloads	Avoids server‑side cancellation while the client processes rows.
Use server‑side cursors with appropriate itersize	Limits memory pressure and reduces burst traffic that can trigger MTU fragmentation.
Poll the async connection after each network read	Ensures libpq’s state machine stays alive, preventing premature closure.
Document network device idle‑timeout values	Aligns keep‑alive intervals with perimeter policies.

Related Topic Hub: Data Infrastructure Troubleshooting Hub

FAQ

1. Why does the stream fail only after thousands of rows?
   Because the AI processing introduces pauses longer than the firewall’s idle timeout, causing the connection to be reset when no keep‑alive packets are sent.
2. Can I keep statement_timeout enabled and still stream?
   Yes, but you must set it to a value larger than the worst‑case processing time per row, or disable it for the session.
3. Is the issue related to SSL/TLS termination?
   Only indirectly. TLS adds extra framing, but the underlying problem is the TCP connection being dropped; the same “SSL connection has been closed unexpectedly” message appears when the socket is reset.
4. Do I need to adjust tcp_keepalives_idle on the server?
   Usually not; the client is responsible for sending keep‑alives. However, matching server keep‑alive settings can provide a safety net.
5. How do I detect this problem in production before it impacts users?
   Add a health‑check that opens a lightweight streaming cursor and reads a few rows; alert on any “unexpected EOF” or “canceling statement” messages in the PostgreSQL logs.