Npgsql doesn't close idle connections

See original GitHub issue

After some time i fail with exception below Npgsql.NpgsqlException (0x80004005): The connection pool has been exhausted, either raise MaxPoolSize (currently 50) or Timeout (currently 10 seconds) *note 50 just for testing now we use 500 which exhausted in few minutes.

After reading a lot here and on stackoverflow, trying pgbouncer and what not I came with nothing. Postgres and Pgbouncer are just fine and never reach the limit, checked their logs and pg_stat_activity

After debugging Npgsql code which clears the pool is seem like a bug or my misconfiguration

My call and connection string

Server=10.0.75.1;Port=5432;User Id=admin;Password=admin;Database=db;Connection Idle Lifetime=10;Minimum Pool Size=20;Maximum Pool Size=50;Timeout=10;"

        public async Task<IDbConnection> GetConnection()
        {
            var cn = new NpgsqlConnection(_connectionString);
            try
            {
                await cn.OpenAsync();
            }
            catch (Exception e)
            {
                NpgsqlConnection.ClearPool(cn);
                await cn.OpenAsync();
            }
}

_idle below is an array with nulls thus does not release _idle counter

        internal void Clear()
        {
            for (var i = 0; i < _idle.Length; i++)
            {
                var connector = Interlocked.Exchange(ref _idle[i], null);
                if (connector != null)
                    CloseConnector(connector, true);
            }

            _clearCounter++;
        }

Sorry i’m hopeless…

Npgsql > 4 ( tried lower with not luck either ) Dotnet core 2.2 ( Windows ) Dapper postgres 11 - docker image

Issue Analytics

State:
Created 4 years ago
Comments:10 (6 by maintainers)

Top GitHub Comments

1reaction

NinoFloriscommented, Oct 8, 2019

Ah great to hear that, I was just writing a lengthy reply asking you to triple check your disposing code, but I’m happy to read you found your issue!

That Dapper problem is interesting though, as I remember they ‘should’ only do open/close if the connection wasn’t open before calling Dapper. It’s intriguing that you found that even though you did an explicit open, Dapper would still open/close the connection by itself. If you think there’s something there for us to investigate please let us know!

1reaction

NinoFloriscommented, Oct 8, 2019

@EvgenyKhaliper thanks for your helpful description!

There could be two things going on here:

You might be running on a version of npgsql 4 (before 4.1) that doesn’t have idle connection pruning, as this was broken for a while.
Your connections aren’t getting closed, for the Npgsql connection pool to work correctly you really have to return the connection (either Close or Dispose does this) before it is available as ‘idle’ in the pool again. We don’t have another way to know if you might want to keep using it otherwise.

Given your description that the idle array is filled with nulls it points to 2 going wrong somewhere. Could you tell me how you’re handling that?

The Clear() snippet you referenced does one important thing to make sure all connections will be closed at some point, by incrementing _clearCounter. When a connection is opened we store the current value of _clearCounter in a field on that instance. During Release we can then check if the pool value hasn’t been increment past the value on the connection, if it did though we will close the connection then and there. Nevertheless it still requires the connector to be released back to the pool for that to happen.

Right now we don’t have a way to force close the connections from the side of the connection pool, is that what you hope to do?