PostgreSQL 57P05 Error: Causes and Solutions Complete Guide

PostgreSQL Error 57P05: idle_session_timeout Explained

PostgreSQL error code 57P05 is raised when a client session remains idle (connected but sending no queries) for longer than the duration configured in the idle_session_timeout parameter, introduced in PostgreSQL 14. When the timeout expires, the server forcibly terminates the connection and returns this error code to the client on its next attempted operation. This is commonly seen in environments with strict connection management policies or misconfigured connection poolers.

---

Top 3 Causes

1. idle_session_timeout Set Too Aggressively

If idle_session_timeout is configured with a very short value at the server, database, or role level, even briefly idle connections will be terminated. This often surprises developers using interactive psql sessions or batch jobs with pauses between queries.

-- Check current setting
SHOW idle_session_timeout;

-- Check where it's applied (system, database, or role level)
SELECT name, setting, unit, source
FROM pg_settings
WHERE name = 'idle_session_timeout';

-- Disable timeout for current session (for debugging)
SET idle_session_timeout = 0;

-- Adjust globally (requires reload, not restart)
ALTER SYSTEM SET idle_session_timeout = '30min';
SELECT pg_reload_conf();

-- Set per database or role
ALTER DATABASE myapp SET idle_session_timeout = '1h';
ALTER ROLE app_user SET idle_session_timeout = '20min';

---

2. Connection Pooler Misconfiguration

When using PgBouncer or pgpool-II, if the pooler's server_idle_timeout is set longer than PostgreSQL's idle_session_timeout, the pooler holds onto connections that PostgreSQL has already terminated. The next query routed through that stale connection triggers a 57P05 error.

-- Identify long-running idle sessions right now
SELECT pid,
       usename,
       datname,
       application_name,
       state,
       now() - state_change AS idle_for,
       client_addr
FROM pg_stat_activity
WHERE state = 'idle'
ORDER BY idle_for DESC;

-- Forcibly terminate sessions idle longer than 5 minutes (emergency fix)
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
  AND now() - state_change > interval '5 minutes'
  AND pid <> pg_backend_pid();

PgBouncer fix: In pgbouncer.ini, always set server_idle_timeout lower than PostgreSQL's idle_session_timeout. For example, if PostgreSQL is set to 10min, set PgBouncer to 8min.

---

3. Application Missing Connection Validation Logic

Many applications and ORM frameworks cache database connections and reuse them without first checking if the server has closed them. When PostgreSQL silently drops an idle connection via 57P05, the application only discovers this on the next query attempt. Libraries like SQLAlchemy, JDBC connection pools (HikariCP), and Node.js pg pools all require explicit configuration to handle this.

-- Lightweight health-check / keep-alive query used by most drivers
SELECT 1;

-- Monitor idle connection counts over time (run periodically)
SELECT datname,
       usename,
       state,
       COUNT(*) AS session_count,
       MAX(EXTRACT(EPOCH FROM (now() - state_change))) AS max_idle_seconds
FROM pg_stat_activity
WHERE state IN ('idle', 'idle in transaction')
GROUP BY datname, usename, state
ORDER BY max_idle_seconds DESC;

ORM-level fixes:

• SQLAlchemy: Set pool_pre_ping=True and pool_recycle to a value less than idle_session_timeout.
• HikariCP (Java): Set keepaliveTime and maxLifetime shorter than the server timeout.
• Node.js pg: Implement an idleTimeoutMillis in the pool config shorter than the server setting.

---

Quick Fix Summary

-- 1. Check what's idle right now
SELECT pid, usename, state, now() - state_change AS idle_duration
FROM pg_stat_activity WHERE state = 'idle' ORDER BY idle_duration DESC;

-- 2. Temporarily disable timeout in your session
SET idle_session_timeout = 0;

-- 3. Increase timeout globally and reload
ALTER SYSTEM SET idle_session_timeout = '30min';
SELECT pg_reload_conf();

---

Prevention Tips

• Layer your timeouts: Always configure server_idle_timeout in your connection pooler to be shorter than PostgreSQL's idle_session_timeout. This ensures the pooler recycles connections before PostgreSQL forces them closed, preventing the error from ever reaching application code.

• Enable connection validation in your application: Activate pre_ping (SQLAlchemy), connectionTestQuery (HikariCP), or equivalent options in your driver/ORM. Set maxLifetime / pool_recycle to a value safely below idle_session_timeout. This guarantees your application always uses a live connection and gracefully handles server-side disconnections.

---

Related Errors

Code	Name	Description
57P01	admin_shutdown	Session killed by admin or server shutdown
57P04	database_dropped	Connected database was dropped
25P03	idle_in_transaction_session_timeout	Similar timeout, but applies inside an open transaction (idle_in_transaction_session_timeout parameter)

---

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.