npgsql: Connection pool return broken connection after some minutes of inactivity

I use aplha-6 version of Npgsql with .NET Core

I have connection factory

public class ContextFactory : IContextFactory
{
    ......
    public DbConnection Create()
    {
        return new NpgsqlConnection(_settings.Value.Database.ConnectionString);
    }
}

And some data manager with Dapper.

    public async Task<PagedList<Post>> GetPostsAsync(int languageId, int page, int pageSize)
    {
        using (var context = _contextFactory.Create())
        {
            var offSet = (page - 1) * pageSize;

            var totalCount = await context.ExecuteScalarAsync(
                    "SELECT COUNT(*) FROM blog.posts WHERE published = true AND languageid = @languageid",
                    new { languageId });

            var sql = @"SELECT id, title, url, short, date, published, thumbnail FROM blog.posts
                        WHERE published = true AND languageid = @languageid
                        ORDER BY date DESC
                        LIMIT @pageSize OFFSET @offSet";

            var result = await context.QueryAsync<Post>(sql, new { languageId, offSet, pageSize });

            return new PagedList<Post>(result, Convert.ToInt32(totalCount));
        }
    }

First requests is Ok, but after some IDLE minutes (~3~10) code throws exception

System.IO.IOException: Unable to read data from the transport connection: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond. ---> System.Net.Sockets.SocketException: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond
   at System.Net.Sockets.Socket.Receive(Byte[] buffer, Int32 offset, Int32 size, SocketFlags socketFlags)
   at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
   --- End of inner exception stack trace ---
   at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
   at Npgsql.NpgsqlBuffer.Ensure(Int32 count)
   at Npgsql.NpgsqlConnector.DoReadSingleMessage(DataRowLoadingMode dataRowLoadingMode, Boolean returnNullForAsyncMessage, Boolean isPrependedMessage)
   at Npgsql.NpgsqlConnector.ReadSingleMessageWithPrepended(DataRowLoadingMode dataRowLoadingMode, Boolean returnNullForAsyncMessage)
   at Npgsql.NpgsqlCommand.Execute(CommandBehavior behavior)
   at Npgsql.NpgsqlCommand.<"ExecuteScalarInternalAsync">d__2.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Npgsql.NpgsqlCommand.<"ExecuteScalarAsync">d__84.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Dapper.SqlMapper.<"ExecuteScalarImplAsync">d__37`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at Blog.Data.DataManagers.PostDataManager.<"GetPostsAsync">d__2.MoveNext() in D:\GIT\Blog\src\Blog.Data\DataManagers\PostDataManager.cs:line 29

Why i catched this error ? I remove Postgres 9.5 RC1 and return to Postgres 9.4.5 and it no differences. When i refresh page - it work normally Ok, and work perfectly if i press F5 many times, but after some IDLE minutes, error comeback again!!! What is it ? Maybe need force close connection ?

About this issue

Original URL
State: closed
Created 8 years ago
Reactions: 2
Comments: 25 (10 by maintainers)

Most upvoted comments

@roji, I am curious what corrective measures you’d suggest, say, for the polly retry logic: if the pool hands us a connection that is “corrupted”, we then handle the IO error, how do we properly ensure that we’re not handed the same corrupted connection during the retry? I can assume Npgsql will NOT return tot he pool a connection that experienced an error during execution, but can you confirm that in an handle-error-and-retry, the second time around new Connection + connection.Open will produce a “fresh” connection? Or we need to do something else?

Also just a thought - your position that an IO error could happen at any time so it’s not the driver’s responsibility to ensure the connection is okay is plausible only to a degree. If patterns have emerged that due to platform or a use case it’s all but guaranteed that a connection will be “corrupted” when handed by the pool, then I perhaps your position should adapt to allow for some built-in health check. Saying all this based on issues we observed in AWS Lambda - despite creating/disposing of connections at each execution (we’re not keeping any connection in the “global” lambda state; we’re relying on the pool), we see “corrupted” connections often enough to want to have that handled by the driver.

GStoynev on May 6, 2022

It looks like I have the same problem #958 .

It was introduced in v3.0.0. Older Npgsql providers do not have this problem.

dstarosta on Feb 2, 2016

I have the same issue with Npgsql 3.0.5 on Azure Web Sites (and there’s a clean install of pgsql 9.4 on Ubuntu Trusty on a VM in Azure).

Agares4 on Feb 2, 2016