connection was closed in the middle of operation

asyncpg version: 0.15.0
PostgreSQL version: 10.3
local PostgreSQL:
Python version: 3.6.5
Platform: Ubuntu 14.04.5
Do you use pgbouncer?: no
Did you install asyncpg with pip?: yes

I use asyncpg with sanic. Before server start, a connection pool is made and attached to the app. In every route, the handler acquire a connection if pg access is needed. The problem is, if the pool is silent for too long, new acquired connection is not usable.

asyncpg.exceptions.ConnectionDoesNotExistError: connection was closed in the middle of operation

After one-time exception. The next acquisition is normal again. No exception with queries. Which is really weird for me.

I’ve tried to twist max_inactive_connection_lifetime parameter, got no luck. Actually I don’t quite understand this parameter. Why would I need this parameter?

Any help is welcomed.

Issue Analytics

State:
Created 5 years ago
Reactions:6
Comments:23 (3 by maintainers)

Top GitHub Comments

12reactions

q210commented, Oct 9, 2018

@Jeffwhen we had the same problem when deploying with docker-swarm.

In our case, the root cause was that ipvs, used by swarm to route packets, have default expiration time for idle connections set to 900 seconds. So if connection had no activity for more than 15 minutes, ipvs broke it. 900 seconds is significantly less than default linux tcp keepalive setting (7200 seconds) used by most of the services that can send keepalive tcp packets to keep connections from going idle.

The same problem is described here https://github.com/moby/moby/issues/31208

To fix this we had to set the following in postgresql.conf:

tcp_keepalives_idle = 600               # TCP_KEEPIDLE, in seconds;
                                        # 0 selects the system default
tcp_keepalives_interval = 30            # TCP_KEEPINTVL, in seconds;
                                        # 0 selects the system default
tcp_keepalives_count = 10               # TCP_KEEPCNT;
                                        # 0 selects the system default

These settings are forcing PostgreSQL to keep connections from going idle by sending keepalive packets more often than ipvs default setting (that we can’t change in docker-swarm, sadly). I guess the same could be achieved by changing corresponding linux settings (net.ipv4.tcp_keepalive_time and the like), 'cause PostgreSQL uses them by default, but in our case changing these was a bit more cumbersome.

11reactions

Olegt0rrcommented, Apr 18, 2021

This bug happens only with uvloop installed