.NET DateTime.MaxValue Exceeds Supported Timestamp Bounds

[RDoll-Repo]

Describe the problem

When updating an entity with a field of DateTime, if we attempt to give that field a value of DateTime.MaxValue, we get the following exception:

An exception of type 'Microsoft.EntityFrameworkCore.DbUpdateException' occurred in System.Private.CoreLib.dll but was not handled in user code: 'An error occurred while saving the entity changes. See the inner exception for details.'
 Inner exceptions found, see $exception in variables window for more details.
 Innermost exception     Npgsql.PostgresException : 22009: error in argument for $2: timestamp "294277-01-09T04:00:54Z" exceeds supported timestamp bounds
   at Npgsql.Internal.NpgsqlConnector.<ReadMessageLong>d__233.MoveNext()
   at System.Runtime.CompilerServices.PoolingAsyncValueTaskMethodBuilder`1.StateMachineBox`1.System.Threading.Tasks.Sources.IValueTaskSource<TResult>.GetResult(Int16 token)
   at Npgsql.NpgsqlDataReader.<NextResult>d__52.MoveNext()
   at Npgsql.NpgsqlDataReader.<NextResult>d__52.MoveNext()
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.<ExecuteReader>d__119.MoveNext()
   at Npgsql.NpgsqlCommand.<ExecuteReader>d__119.MoveNext()
   at System.Threading.Tasks.ValueTask`1.get_Result()
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)

I originally opened this issue with the EF Core team here, but after determining that this only happens with Cockroach and not Postgres, I was directed to try opening the issue here.

To Reproduce

1.) Use the provided reproduction project DateTimeSample.zip

2.) Run docker-compose up to start the server

3.) Run dotnet ef migrations add Initial then dotnet ef database update to create a table reflecting the Purchase class in Program.cs

4.) Run project to attempt to insert a row into the database. You should see the error shown above.

Expected behavior When running this, I would expect the table to be updated with a row that reflects an Expiry of 9999-12-31T23:59:59Z

Additional data / screenshots N/A

Environment:

• CockroachDB version 22.2.3 (through Docker)
• EF Core version: 8.0.5
• Target framework: .NET 8.0
• Operating system: MacOS 14.2.1
• IDE: Visual Studio Code 1.89.1

Additional context We effectively need to hardcode the expected value instead of using DateTime.MaxValue

I also wasn't sure how exactly to present this issue, since it occurs in a .NET project which is perhaps not entirely in the wheelhouse of this repo, so if there is a more helpful way to present this, let me know.

Jira issue: CRDB-39217

[blathers-crl[bot]]

Hi @RDoll-Repo, please add branch-* labels to identify which branch(es) this C-bug affects.

_{:owl: Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[blathers-crl[bot]]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I was unable to automatically find someone to ping.

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{:owl: Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[RDoll-Repo]

Hi @RDoll-Repo, please add branch-* labels to identify which branch(es) this C-bug affects.

It doesn't look like I can add further labels beyond what was automatically assigned to the issue.

[rafiss]

Thanks for the report!

One question about the expected behavior:

When running this, I would expect the table to be updated with a row that reflects an Expiry of 9999-12-31T23:59:59Z

When I tried your example with Postgres, I see that it actually has a row with an expiry of infinity. Are you sure you see 9999-12-31T23:59:59Z?

CockroachDB currently cannot represent infinity timestamps. The tracking issue is: https://github.com/cockroachdb/cockroach/issues/41564

[RDoll-Repo]

Thanks for the report!

One question about the expected behavior:

When running this, I would expect the table to be updated with a row that reflects an Expiry of 9999-12-31T23:59:59Z

When I tried your example with Postgres, I see that it actually has a row with an expiry of infinity. Are you sure you see 9999-12-31T23:59:59Z?

CockroachDB currently cannot represent infinity timestamps. The tracking issue is: #41564

When I inspect what DateTime.MaxValue evaluates to in .NET, I'm seeing the attached. I guess when this data goes through npgsql, it's coming out as infinity and that's what's getting inserted SQL side? But yeah, that essentially seems to mean that DateTime.MaxValue is unusable with CRDB right now. Hardcoding an equivalent string seems to be the workaround, but I figured I should report this behavior nonetheless and see if there's anything to be done about it

[roji]

Just noting that Npgsql by default convert DateTime.{Min,Max}Value to PG infinity values. This behavior can be disabled (see docs).

[rafiss]

v24.3 will support the infinity timestamp in the same way Postgres does. see https://github.com/cockroachdb/cockroach/pull/127141