Recently I stumbled upon certain problem while communicating with PostgreSQL 13 database hosted on CentOS 8. I cannot use the method OpenAsync() from NpgsqlConnection class to open a connection. Synchronous Open() method works without problems and allow me to create and use the connection. I hope that maybe someone stumbled upon similar problem. Thank you in advance for help!
Example code:
[Fact]
public async Task TestDatabaseConnection()
{
NpgsqlLogManager.Provider = new NLogLoggingProvider();
NpgsqlLogManager.IsParameterLoggingEnabled = true;
Npgsql.NpgsqlConnection con = new Npgsql.NpgsqlConnection("Server=xxx;Database=xxx;User Id=xxx;Password=xxx");
try
{
//con.Open(); works ok
await con.OpenAsync(); //cause error
}
catch (Exception exc)
{
Console.WriteLine("test " + exc);
}
}
Logs:
Opening connection...
Opening connection...
Attempting to connect to [x:x:x:x::x]:5432
Exception thrown: 'System.TimeoutException' in Npgsql.dll
Exception thrown: 'System.TimeoutException' in System.Private.CoreLib.dll
Failed to connect to [x:x:x:x::x]:5432
Exception thrown: 'System.ObjectDisposedException' in System.Net.Sockets.dll
Attempting to connect to x.x.x.x:5432
Exception thrown: 'System.TimeoutException' in Npgsql.dll
Exception thrown: 'System.ObjectDisposedException' in System.Private.CoreLib.dll
Exception thrown: 'System.TimeoutException' in System.Private.CoreLib.dll
Failed to connect to x.x.x.x:5432
Exception thrown: 'Npgsql.NpgsqlException' in Npgsql.dll
Exception thrown: 'Npgsql.NpgsqlException' in System.Private.CoreLib.dll
Exception thrown: 'Npgsql.NpgsqlException' in Npgsql.dll
Exception thrown: 'Npgsql.NpgsqlException' in System.Private.CoreLib.dll
Breaking connector
Cleaning up connector
Exception thrown: 'System.NullReferenceException' in Npgsql.dll
Object reference not set to an instance of an object.
We managed to find a solution. Adding the Timeout parameter in the connection string should be sufficient. For some reason establishing the connection is taking a very long time. The reason for this is still under investigation. On average it takes 21 seconds to open the connection - but a higher Timeout parameter protects us from exception.
Example:
Server=xxx;Database=yyy;User Id=bbb;Password=aaa;Timeout=100
Further discussion of this issue can be found here: https://github.com/npgsql/npgsql/issues/3295