PostgreSQL 14: lo_export/large object operations intermittently fail with 'could not open large object' when keepalives drop during long client transactions

Environment

PostgreSQL 14.6 on Ubuntu 22.04 LTS, replica streaming WAL via replication slot; application uses libpq-based client on Python (psycopg2) performing large object operations within long-lived transactions over a TCP connection with an intermediate NAT/firewall that drops idle flows after 300s.

Symptoms

Occasionally, during long transactions that create and later export large objects (lo_create + lo_open + lo_export), the server errors with 'could not open large object' for the OID that was created earlier in the same transaction. The client stacktrace shows the error after a network stall. If the same sequence is re-run in a fresh connection, it succeeds. The issue is intermittent and correlates with long idle periods between large object creation and export.

Observations

Postgres server logs show 'terminating connection due to administrator command' around the same time for some sessions; tcpdump on server shows RST from firewall/NAT targeting the client connection after ~300s of idle. When the client enables TCP keepalives with interval 60s, the failures drop to near-zero. The large object OIDs referenced are present in pg_largeobject and pg_largeobject_metadata when queried after a failure, but the transaction that attempted export is in an aborted state. VACUUM and autovacuum are not removing the objects (they remain).

Already attempted

Enabled client-side statement_timeout and increased application retries; added explicit periodic simple queries to keep connection active (workaround). Upgraded psycopg2 to latest; tested with a direct LAN connection bypassing NAT — problem not observed. Examined server-side logs; no disk corruption detected. Replaying problematic sequence in psql with same delays reproduced the error when the connection was dropped mid-transaction.

Question

How should I handle large object workflows over unreliable intermediate networks to avoid transient 'could not open large object' errors and ensure transaction safety, and what server/client-side configurations are recommended to prevent this class of failure?

Post timeline

COMMUNITY RESPONSES

Verified community troubleshooting

0 replies

No answers yet..

Showing 0 of 0 community responses

AI DIAGNOSTICS

Incident analysis

Updated 1w ago• Trouble ID

Most likely 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%

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.

Suggested Fixes

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.

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.

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.