Ends in:
2 DAYS
5 HRS
30 MIN
28 SEC
Ends in:
2 D
5 H
30 M
28 S
Open Closed

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


User avatar
0
sanghel-payoff created
  • 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)
  • User Avatar
    0
    sanghel-payoff created

    Any feedback ?

  • User Avatar
    0
    sanghel-payoff created

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

  • User Avatar
    0
    sanghel-payoff created

    ..bump !?!

  • User Avatar
    0
    alper created
    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?

  • User Avatar
    0
    N335378 created

    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?

  • User Avatar
    0
    sanghel-payoff created

    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.

  • User Avatar
    0
    maliming created
    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.

  • User Avatar
    1
    maliming created
    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;
    });
    
  • User Avatar
    1
    sanghel-payoff created

    @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....

  • User Avatar
    0
    maliming created
    Support Team Fullstack Developer

    hi

    I try that in 5.1.3, options.Kind = DateTimeKind.Utc; works.

  • User Avatar
    0
    anthonyh created

    @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.

  • User Avatar
    0
    maliming created
    Support Team Fullstack Developer

    Thanks @anthonyh

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

Made with ❤️ on ABP v9.1.0-preview. Updated on December 02, 2024, 12:35