DateTime Utc Error after migrating NpgSql 6.0.0-rc.1

[3ldar]

We have encountered this error before we migrate EntityFramework using Postgresql. We fixed the error by applying a migration that converts DateTime columns into timestamp with time zone.

The exception is like below :

HangFire:lock:recurring-job:ApplyFiltersJob: Failed to remove lock","Exception":"System.InvalidCastException: Cannot write DateTime with Kind=UTC to PostgreSQL type 'timestamp without time zone', consider using 'timestamp with time zone'. Note that it's not possible to mix DateTimes with different Kinds in an array/range. See the Npgsql.EnableLegacyTimestampBehavior AppContext sNpgsql.EnableLegacyTimestampBehavior AppContext swwitch to enable legacy behavior. at Npgsql.Internal.TypeHandlers.DateTimeHandlers.TimestampHandler.ValidateAndGetLength(DateTime value, NpgsqlParameter parameter) at Npgsql.Internal.TypeHandlers.DateTimeHandlers.TimestampHandler.ValidateObjectAndGetLength(Object value, NpgsqlLengthCache& lengthCache, NpgsqlParameter parameter) at Npgsql.NpgsqlParameter.ValidateAndGetLength() at Npgsql.NpgsqlParameterCollection.ValidateAndBind(ConnectorTypeMapper typeMapper) 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 Npgsql.NpgsqlCommand.ExecuteNonQuery() at Dapper.SqlMapper.ExecuteCommand(IDbConnection cnn, CommandDefinition& command, Action`2 paramReader) in //Dapper/SqlMapper.cs:line 2813 at Dapper.SqlMapper.ExecuteImpl(IDbConnection cnn, CommandDefinition& command) in //Dapper/SqlMapper.cs:line 572 at Dapper.SqlMapper.Execute(IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Nullable`1 commandTimeout, Nullable`1 commandType) in /_/Dapper/SqlMapper.cs:line 443 at Hangfire.PostgreSql.PostgreSqlDistributedLock.TryRemoveDeadlock(String resource, IDbConnection connection, PostgreSqlStorageOptions options)","State":{"Message":" HangFire:lock:recurring-job:ApplyFiltersJob: Failed to remove lock"

We also don't want to set switch Npgsql.EnableLegacyTimestampBehavior because we are using EntityFramework in the same application. I will share a workaround when we found one. We also tried to fiddle DateTimeZoneHandling setting in the SerializationSettings but it didn't work. Also, we tried to alter all the columns into timestamp with time zone it didn't help.

I managed to isolate to issue. It is related to dapper. Tried to execute the same query with raw NpgsqlCommand and it succeeded. But executing the same query with dapper fails. Here is the sample code :

var connection = new NpgsqlConnection("aconnectionstring");
await connection.OpenAsync();
using var transaction = await connection.BeginTransactionAsync(IsolationLevel.RepeatableRead);
var command = new NpgsqlCommand($@"DELETE FROM ""hangfire"".""lock"" WHERE ""resource"" = @resource AND ""acquired"" < @timeout")
            {
                Connection = connection,
                Transaction = transaction
            };

command.Parameters.Add(new NpgsqlParameter("@resource", "abuzer"));
command.Parameters.Add(new NpgsqlParameter("@timeout", DateTime.UtcNow - TimeSpan.FromMinutes(10)));

await command.ExecuteNonQueryAsync(); // this runs without any issue

connection.Execute($@"DELETE FROM ""hangfire"".""lock"" WHERE ""resource"" = @resource AND ""acquired"" < @timeout",
               new
               {
                   resource = "abuzer",
                   timeout = DateTime.UtcNow - options.DistributedLockTimeout
               }); // this throws the error
            transaction.Commit();

By the way if we change this timeout = DateTime.UtcNow - options.DistributedLockTimeout into this : timeout = DateTimeOffset.UtcNow - options.DistributedLockTimeout it works as expected. I have also created an issue for dapper

[frankhommers]

I guess we'll wait for Dappers' response. But if you upgraded to a newer Npgsql version, wouldn't that mean that it the problem is in Npgsql? Especially since it's an RC?

[3ldar]

@frankhommers It seems it will be resolved on the Dapper's side as you said. So I'm closing this issue.