1
Diagnostic step 1
Enable TCP keepalives on client sockets and tune intervals (e.g., keepalive time 60s, interval 10s) on the client OS or in application so intermediate NATs do not drop connections. This is reversible and low-risk.
FULL AI ANALYSIS
PostgreSQL 14: lo_export/large object operations intermittently fail with 'could not open large object' when keepalives drop during long client transactions
Generated Oct 3, 2026, 12:35:03 AM
Analysis summary
Large objects are session-scoped objects until transaction ends; a dropped connection or forced termination will abort the transaction and can leave the application confused if it retries without creating the object again. The observed RST from firewall/NAT after idle periods causes server to terminate the backend, which aborts the transaction and makes later attempts to reference that OID within the same transaction fail. Enabling TCP keepalives or application-level heartbeats prevents NAT timeouts. Robust handling requires making the process tolerant of connection loss and avoiding long idle gaps inside transactions that create resources needed later in the same transaction.
Possible causes
Intermediate NAT/firewall closes TCP flow after idle timeout, causing backend to detect broken socket and terminate session mid-transaction.
100%Client library or OS TCP keepalive settings are too infrequent or disabled, so NAT timeouts trigger disconnections.
95%Application holds long-lived transactions open while waiting for external work, increasing chance of connection drop and leaving large-object operations vulnerable.
90%Server-side backend terminated by external admin or resource killer due to perceived idle or long-running transaction policies.
10%Suggested fixes
2
Diagnostic step 2
Refactor application to avoid creating large objects early in a transaction that will be idle for a long time: create/export within a tighter transaction window or persist file data in a temporary table with explicit commit and then perform export in a fresh transaction.
3
Diagnostic step 3
Implement robust retry logic: if the connection is detected lost, reconnect and repeat the large object creation/export sequence with idempotency checks; ensure you do not assume prior OIDs remain valid across disconnected sessions.
4
Diagnostic step 4
If network middleboxes cannot be tuned, use SSL/TLS with keepalive and/or application-level heartbeats, or move the application closer to the database (VPN, private network) to eliminate the NAT timeout. For persistent failures tied to server configuration, collect server logs and pcap traces and escalate to network operations or DBA for deeper analysis.