Why I occasionally get the following:
CDR.Remove_Old_DataAsync: Npgsql.NpgsqlException (0x80004005): Exception while reading from stream ---> System.TimeoutException: Timeout during reading attempt at Npgsql.Internal.NpgsqlConnector.g__ReadMessageLong|233_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage) at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken) at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken) at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken) at Dapper.SqlMapper.ExecuteImplAsync(IDbConnection cnn, CommandDefinition command, Object param)
Production Environment:
- Linux CentOS 7
- ASP NetCore 6 MVC
- PostgreSQL 14
I searched and found this and followed the provided answer but no luck.
I enclosed the connectionstring and the C# codes below, hope someone can help me to find out what did I do wrong?
Server=pg.example.com;Database=mydb;Port=5432;User ID=example;Password=example!!;Keepalive=300; Pooling=true;Minimum Pool Size = 0; Maximum Pool Size = 500; Connection Idle Lifetime=300; CommandTimeout=300; KeepAlive=300; Tcp Keepalive=true; Include Error Detail=true
internal static async Task Remove_Old_DataAsync()
{
try
{
using (NpgsqlConnection conn = new
NpgsqlConnection(Models.AppSettings.PG_SQL.Connection_String))
{
try
{
string sql = "Delete From cdrs Where EXTRACT(EPOCH FROM (Current_Timestamp at time zone 'UTC' - cdatetime at time zone 'UTC')) / @Seconds_To_Year > @Max_Data_Retention;" +
"Delete From sessions Where EXTRACT(EPOCH FROM (Current_Timestamp at time zone 'UTC' - cdatetime at time zone 'UTC')) / @Seconds_To_Year > @Max_Data_Retention";
int numDeleted = await conn.ExecuteAsync(sql, new
{
Seconds_To_Year = 31556926,
Max_Data_Retention = Models.AppSettings.Max_Data_Retention
});
if (numDeleted > 0)
{
await Models.Audit.AddAsync((long)0, numDeleted.ToString() + " Old CDRs & Sessions Data Removed");
}
}
catch (Exception e) { Helper.SaveLogAsync("CDR.Remove_Old_DataAsync: " + e.ToString(), Models.Errs.ErrType.Err); }
finally { }
}
}
catch (Exception e) { Helper.SaveLogAsync("CDR.Remove_Old_DataAsync: " + e.ToString(), Models.Errs.ErrType.Err); }
finally { }
}