npgsql: Closing connection before reader disposing leads to broken connector

Steps to reproduce

MyMethod()
{
            if (_connection.State != ConnectionState.Open)
            {
                await _connection.OpenAsync(token);
                await _command.PrepareAsync(token);
            }

            using var reader = await _command.ExecuteReaderAsync(token);

            while (await reader.ReadAsync(token))
            {
                ...
                    yield return someResult;
                    
            }

            if (_connection.State != ConnectionState.Closed)
                _connection.Close();

            if (...)
                yield return someResult;
}

The issue

I have 2 Exceptions one by one:

  1. For new opening connection (exception messages can be different)
Npgsql.NpgsqlException (0x80004005): Received backend message BindComplete while expecting ReadyForQueryMessage. Please file a bug.\n  
 at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming)\n  
 at Npgsql.NpgsqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)\n   
at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)\n  
 at Lib.Pgsql.ConnectionExtensions.OpenWithParametersAsync(NpgsqlConnection connection, String defaultSchema)\n  
 at ...
  1. For reader disposing
Npgsql.NpgsqlException (0x80004005): Exception while reading from stream\n ---> System.IO.IOException: Unable to read data from the transport connection: Operation canceled.\n ---> System.Net.Sockets.SocketException (125): Operation canceled\n   
at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)\n  
 --- End of inner exception stack trace ---\n   
at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)\n   
at Npgsql.NpgsqlReadBuffer.<>c__DisplayClass34_0.<<Ensure>g__EnsureLong|0>d.MoveNext()\n  
at Npgsql.NpgsqlReadBuffer.<>c__DisplayClass34_0.<<Ensure>g__EnsureLong|0>d.MoveNext()\n
--- End of stack trace from previous location where exception was thrown ---\n  
 at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()\n
--- End of stack trace from previous location where exception was thrown ---\n  
 at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()\n
--- End of stack trace from previous location where exception was thrown ---\n 
  at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming)\n  
 at Npgsql.NpgsqlDataReader.Consume(Boolean async)\n  
 at Npgsql.NpgsqlDataReader.Close(Boolean connectionClosing, Boolean async)\n 
  at Npgsql.NpgsqlDataReader.Close()\n   at 
SomeClass.MyMethod(...)   at 

And finally this leads to exhausted connection pool. I got my mistake in the code above but I hope you can make this issue more explicit.

Further technical details

Npgsql version: 4.1.3 PostgreSQL version: 10.7 Operating system: Any

About this issue

  • Original URL
  • State: closed
  • Created 4 years ago
  • Comments: 28 (14 by maintainers)

Commits related to this issue

Most upvoted comments

Thanks @vonzshik, I didn’t think of that scenario but it makes sense. And thanks for the test, I’ll have another look at this.

Hi @roji, I’ve been looking at this for a little while now and unfortunately I’ve not been able to figure this one out. In either a multi threaded or asynchronous application, I can see that in some instances when disposing of the reader (after the connection has been closed) that the connectors ConnectorState is “Executing” (I think the state is meant to be “Fetching” at this point when being disposed) which results in an await Consume() not returning the expected ReadyForQuery message (usually in this instance it is DataRow or BindComplete). I’ve been looking at this and can’t figure out how it gets into this state as explicitly closing the connection calls the connectors CloseOnGoingOperations which should complete any current calls. I’ve tried a few things but my understanding of this area is limited and I’ve got to a point where I can’t seem to make any further headway. Probably best if you took over from here and had a look, really sorry I couldn’t find a resolution to this issue.

@gardnero thanks for explaining why you close the connection before disposing of the reader.

I’m currently looking at this and hoping to find a solution to it soon. If you can reproduce the errors occurring in your test environment in a sample app that would be great. Thanks.

Thanks @roji for clarifying. I’ll look into the errors that are getting thrown as you suggested and hopefully can work out what is going on. If I hit a wall I’ll let you know. Thanks again.

@warcha, a reader must be closed if the corresponding connection is closed because all resources are freed: no socket to read data.

Sorry for taking so long to reply, was on vacation and slowly going through my mail box (: