pool.connect() borrows a connection: the code must give it back with client.release(). In 4.1,
the error path return res.status(422) leaves the function without ROLLBACK or release(). Every
refused order therefore keeps a connection, in the middle of an open transaction, holding a FOR UPDATE
lock on the stock row. After 20 refusals the pool is empty: every request waits 5 seconds
(connectionTimeoutMillis) and then fails with "timeout exceeded when trying to connect".
The environment variables were complete, and PostgreSQL was still accepting connections (you could
connect with psql): it was the application's pool that had run dry.
Incident resolved
Analysis and fix
Fix: wrap the transaction in try { … } catch { ROLLBACK } finally { client.release() }
(or roll back 4.1 with git revert), then pm2 reload hanout-api. As an emergency measure,
pg_terminate_backend on the "idle in transaction" sessions brings relief, but fixes nothing.
To prevent it from happening again:
- for a single query, use
pool.query(), which returns the connection by itself; keeppool.connect()for transactions, always withfinally; - on the PostgreSQL side,
idle_in_transaction_session_timeout = '30s'for the API role; - monitor the pool (
pool.totalCount,pool.idleCount,pool.waitingCount) and alert when requests are waiting; - an integration test that sends 50 refused orders in a row, then checks the API still answers.