Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Can't write CLR type System.DateTimeOffset with handler type TimestampHandler #2470

Open
SeppPenner opened this issue May 12, 2019 · 7 comments

Comments

@SeppPenner
Copy link

commented May 12, 2019

Steps to reproduce

  • Checkout https://github.com/SeppPenner/SerilogSinkForPostgreSQL.

  • Setup a local PostgreSQL database.

  • Check the SerilogSinksPostgreSQL.IntegrationTests project

  • Check the class DbWriteTests with the method AutoCreateTableIsTrueShouldCreateTable()

  • Adjust the private const string ConnectionString = "User ID=postgres;Password=postgres;Host=localhost;Port=5432;Database=Serilog"; property and run the method AutoCreateTableIsTrueShouldCreateTable()

The issue

DateTimeOffset cannot be mapped to Timestamp (NpgsqlType). I do not know if this is a bug because https://www.npgsql.org/doc/types/basic.html shows:

NpgsqlDbType DbType PostgreSQL type Accepted .NET types
Timestamp DateTime, DateTime2 timestamp DateTime, DateTimeOffset, NpgsqlDateTime

for the write mapping.

Exception:

{System.InvalidCastException: Can't write CLR type System.DateTimeOffset with handler type TimestampHandler
   at Npgsql.TypeHandling.NpgsqlSimpleTypeHandler`1.ValidateObjectAndGetLength(Object value, NpgsqlLengthCache& lengthCache, NpgsqlParameter parameter) in C:\projects\npgsql\src\Npgsql\TypeHandling\NpgsqlSimpleTypeHandler.cs:line 236
   at Npgsql.NpgsqlParameter.ValidateAndGetLength() in C:\projects\npgsql\src\Npgsql\NpgsqlParameter.cs:line 553
   at Npgsql.NpgsqlCommand.ValidateParameters() in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 793
   at Npgsql.NpgsqlCommand.<ExecuteDbDataReader>d__100.MoveNext() in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1140}

My query:

INSERT INTO "Logs" ("Message", "MessageTemplate", "Level", "RaiseDate", "Exception", "Properties", "PropertyTest", "IntPropertyTest", "MachineName") VALUES (@Message, @MessageTemplate, @Level, @RaiseDate, @Exception, @Properties, @PropertyTest, @IntPropertyTest, @MachineName)

The parameter mapping:

Parameters:

DbType, Value, .Net type, NpgsqlType

String, "Test0: TestObjectType1 { IntProp: 42, StringProp: "Test" } test2: TestObjectType2 { DateProp: 05/12/2019 18:21:41, NestedProp: TestObjectType1 { IntProp: 42, StringProp: "Test" } } testStr: stringValue", string, Text
String, "Test{testNo}: {@testObject} test2: {@testObj2} testStr: {@testStr:l}", string, Text
String, "Information", Enum, Varchar
DateTime, 12.05.2019 18:21:41 +02:00, DateTimeOffset, Timestamp
String, {}, DbNull, Text
Object, "{"Timestamp":"2019-05-12T18:21:41.2106317+02:00","Level":"Information","MessageTemplate":"Test{testNo}: {@testObject} test2: {@testObj2} testStr: {@testStr:l}","Properties":{"testNo":0,"testObject":{"_typeTag":"TestObjectType1","IntProp":42,"StringProp":"Test"},"testObj2":{"_typeTag":"TestObjectType2","DateProp":"2019-05-12T18:21:41.1639858+02:00","NestedProp":{"_typeTag":"TestObjectType1","IntProp":42,"StringProp":"Test"}},"testStr":"stringValue","MachineName":"MyPC"},"Renderings":{"testStr":[{"Format":"l","Rendering":"stringValue"}]}}\r\n", string, Jsonb
String, "{"testNo":0, "testObject":{"IntProp":42,"StringProp":"Test","_typeTag":"TestObjectType1"}, "testObj2":{"DateProp":"2019-05-12T18:21:41.1639858+02:00","NestedProp":{"IntProp":42,"StringProp":"Test","_typeTag":"TestObjectType1"},"_typeTag":"TestObjectType2"}, "testStr":"stringValue", "MachineName":"MyPC"}", string, Text
Int32, 0, int, Integer
String, "MyPC", string, Text

Further technical details

Npgsql version: 4.0.6 (Latest)
PostgreSQL version: PostgreSQL 11.2 on x86_64-pc-mingw64, compiled by gcc.exe (Rev5, Built by MSYS2 project) 4.9.2, 64-bit
Operating system: Windows 10

Other details about my project setup

I'm using Serilog with the Serilog.Sinks.PeriodicBatching sink.

@SeppPenner

This comment has been minimized.

Copy link
Author

commented May 12, 2019

Changing the type to TimestampTz works...

@YohDeadfall

This comment has been minimized.

Copy link
Member

commented May 12, 2019

Yep, there was a change in the behavior, but we forgot to fix the documentation. Thanks for catching this!

@SeppPenner

This comment has been minimized.

Copy link
Author

commented May 13, 2019

Yep, there was a change in the behavior, but we forgot to fix the documentation. Thanks for catching this!

Ok, good to know. It took me some hours to figure out what caused the issue :D

@SeppPenner

This comment has been minimized.

Copy link
Author

commented May 20, 2019

What's the correct documentation here? I can provide a PR, if you want. The file (and line) is this one, isn't it?

@YohDeadfall

This comment has been minimized.

Copy link
Member

commented May 20, 2019

@SeppPenner Pull requests are welcome, but in this case we just need to run docfx on the master branch. @roji Are you going to update docs? If not I can do it tonight.

@SeppPenner

This comment has been minimized.

Copy link
Author

commented May 20, 2019

@YohDeadfall Ah okay, I didn't know how you're doing the documentation stuff.

@roji

This comment has been minimized.

Copy link
Member

commented May 20, 2019

@YohDeadfall go ahead and do it - docfx is currently failing on my Linux/mono setup for some reason and I have no time to investigate... Be sure to update the git submodule for EFCore.PG as well (there have been updates there).

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
3 participants
You can’t perform that action at this time.