PGSQL DateTime mapping issue with 5.1.2 (and earlier, due to Npgsql 6+) #2489 | Support

sanghel-payoff created 2 years ago

ABP Framework version: v5.0.1, problem also present with latest 5.1.2
UI type: Angular
DB provider: EF Core with PGSQL (Npgsql 6+)
Tiered (MVC) or Identity Server Separated (Angular): no
Exception message and stack trace: see Github issue linked below
Steps to reproduce the issue: see Github issue linked below

I'm "escalating" to a support question this Github issue , because I need an official-as-possible response to the problem I'm facing.

It seems that with ABP Commercial based on ABP Framework 5+, when used with PostgreSQL DBMS with EFCore, there is no way to have DateTime entity properties mapped to DB columns of type timestamp with timezone and be correctly written / read by the ABP Framework infrastructure.

As far as I can tell, this issue has been introduced with the upgrade to Npgsql 6+, which has a breaking change with regards to the handling of .NET DateTime entity properties.

I require that DateTime columns be always mapped to timestamp with timezone and:

that all provided framework modules follow this mapping and correctly handle writing / reading
a documented way to correctly configure this behaviour for our own application module(s)

As I've commented elsewhere, Npgsql 6+ needs DateTime props to always have a DateTimeKind of UTC when written / read ( see this SO question and relevant answers ). If this is not supported, it entails that the ABP Framework with EFCore + Npgsql cannot correctly handle a multi-timezone application, which is a huge blocking issue for a "Saas framework".

What is the official stance on this issue?

12 Answer(s)

0

sanghel-payoff created 2 years ago

Any feedback ?
0

sanghel-payoff created 2 years ago

This is a support question, is it going to be ignored?
0

sanghel-payoff created 2 years ago

..bump !?!
0

alper created 2 years ago
Support Team Director

this case is not directly related to ABP Framework. ABP Framework uses Entity Framework Core as the database provider. Do you get the same problem with a vanilla .NET console application?
0

N335378 created 2 years ago

This is blocking our upgrade to ABP 5.1.3 with Npgsql 6 and our route to production for a business critical application.

Here it suggests a fix has been added: https://github.com/abpframework/abp/issues/10273

Can we confirm whether this fix has been applied?
0

sanghel-payoff created 2 years ago

this case is not directly related to ABP Framework. ABP Framework uses Entity Framework Core as the database provider. Do you get the same problem with a vanilla .NET console application?

I'll try to explain the issue once more....

With Npgsql < 6 mapping a DateTime prop to PGSQL's timestampz (timestamp with timezone) was cumbersome (or perhaps impossible).

With Npgsql 6+ , the default behavior has been changed (as I've linked above ), so DateTime is mapped to timestampz, but also requires explicit handling of DateTimeKind for the DateTime property, during read / write (I've also linked to this above). The Npgsql.EnableLegacyTimestampBehavior reverts Npgsql 6+'s behavior to that of previous versions, which means mapping DateTime props to timestamp (timestamp without timezone)

This does not allow read/write of temporal data with timezone information from/to PGSQL.

Having just started using ABP Commercial with version 5.0.1, I suspect that ABP <5 has never handled on PGSQL read/write of timezone-rich DateTime props (mapped to timestampz).

ABP Framework / Commercial, starting from 5.0.1 (I might be wrong on the precise version) has upgraded its Npgsql dependency to 6+. This has, obviously, caused issues due to the breaking change, and the above flag enables falling back to the previous behavior.

But a major issue remains: the fact that it is impossible to configure ABP framework modules (Identity...), as well as the default solution template, to map and then correctly read / write, DateTime properties with DateTimeKind = UTC to timestampz PGSQL columns. <<<< This is absolutely required for a Saas framework, as storing local DateTime is a major no-no with a geographically distributed user base.
0

maliming created 2 years ago
Support Team Fullstack Developer

hi @N335378

See https://github.com/abpframework/abp/pull/11371 https://github.com/abpframework/eShopOnAbp/pull/65

hi @sanghel

I will make a sample that timestamp with timezone.
1
maliming created 2 years ago
Support Team Fullstack Developer
hi @sanghel

You can try to use the latest version(5.1.3) and set the clock to UTC.

Configure<AbpClockOptions>(options => { options.Kind = DateTimeKind.Utc; });
1

sanghel-payoff created 2 years ago

@maliming I've already tried doing just that, but it will not configure DateTime props to be mapped to timestampz (timestamp to timezone) in PGSQL, especially for framework modules (Identity...ecc) or any other modules integrated via ABP Suite..

I believe this to be an "infrastructural" issue.

When ABP is set to use PGSQL, it should configure the default handling of DateTimeKind (during read/write) , maybe something like this, but this code should be present upstream in ABP modules, it is not something configurable by end users of ABP; and event if it were, it would be really cumbersome and it should be clearly and extensively documented.

For example: all handling of I**AuditedEntity uses DateTime props (CreationTime, LastModificationTime, ecc), these should all be correctly mapped to timestampz (timestamp to timezone) and read / written with DateTimeKind.Utc....

1
0

maliming created 2 years ago
Support Team Fullstack Developer

hi

I try that in 5.1.3, options.Kind = DateTimeKind.Utc; works.
0
anthonyh created 2 years ago
@maliming I've already tried doing just that, but it will not configure DateTime props to be mapped to timestampz (timestamp to timezone) in PGSQL, especially for framework modules (Identity...ecc) or any other modules integrated via ABP Suite..

I believe this to be an "infrastructural" issue.

When ABP is set to use PGSQL, it should configure the default handling of DateTimeKind (during read/write) , maybe something like this, but this code should be present upstream in ABP modules, it is not something configurable by end users of ABP; and event if it were, it would be really cumbersome and it should be clearly and extensively documented.

For example: all handling of I**AuditedEntity uses DateTime props (CreationTime, LastModificationTime, ecc), these should all be correctly mapped to timestampz (timestamp to timezone) and read / written with DateTimeKind.Utc....

Hi sanghel,

Thanks for posting this issue. I encountered this problem too. I am using ABP v5.1.3. I do not know what kind of front-end you use. I am using MVC and here is what I have done and it seems to work.

I added

Configure<AbpClockOptions>(options => { options.Kind = DateTimeKind.Utc; });

to the WebModule and DbMigratorModule.

Adding it to the WebModule makes it work properly when running the application. Adding it to the DbMigratorModule makes the migration run properly.

Hope this helps.
0

maliming created 2 years ago
Support Team Fullstack Developer

Thanks @anthonyh

You can add options.Kind = DateTimeKind.Utc; to EF Core module.

Have an answer to this question? Log in and write your answer.