PGSQL issues with 5.1.2 (and earlier, due to Npgsql 6+)

[sanghel-payoff]

ABP Commercial 5.1.2, UI Angular, EFCore Postgresql

In a freshly created solution with PGSQL DBMS, the ABP framework does not correctly handle EFCore mapping via Npgsql for DateTime props.

Steps:

• generate a new 5.1.2 solution (via CLI or Suite) with PostgreSQL
• define the initial migration (Add-Migration...)
• setup connection strings to a PGSQL instance (in my case a PGSQL 12 container)
• apply the initial migration to the DB ( Update-Database, or launch .DBMigrator)
• start the .Host project, in the app console there will be exceptions of the form

[16:12:39 ERR] Cannot write DateTime with Kind=Local to PostgreSQL type 'timestamp with time zone', only UTC is supported. Note that it's not possible to mix DateTimes with different Kinds in an array/range. See the Npgsql.EnableLegacyTimestampBehavior AppContext switch to enable legacy behavior.
System.InvalidCastException: Cannot write DateTime with Kind=Local to PostgreSQL type 'timestamp with time zone', only UTC is supported. Note that it's not possible to mix DateTimes with different Kinds in an array/range. See the Npgsql.EnableLegacyTimestampBehavior AppContext switch to enable legacy behavior.
   at Npgsql.Internal.TypeHandlers.DateTimeHandlers.TimestampTzHandler.ValidateAndGetLength(DateTime value, NpgsqlParameter parameter)
   at Npgsql.Internal.TypeHandlers.DateTimeHandlers.TimestampTzHandler.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.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at Volo.Abp.BackgroundJobs.EntityFrameworkCore.EfCoreBackgroundJobRepository.GetWaitingListAsync(Int32 maxResultCount, CancellationToken cancellationToken)
   at Castle.DynamicProxy.AsyncInterceptorBase.ProceedAsynchronous[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo)
   at Volo.Abp.Castle.DynamicProxy.CastleAbpMethodInvocationAdapterWithReturnValue`1.ProceedAsync()
   at Volo.Abp.Uow.UnitOfWorkInterceptor.InterceptAsync(IAbpMethodInvocation invocation)
   at Volo.Abp.Castle.DynamicProxy.CastleAsyncAbpInterceptorAdapter`1.InterceptAsync[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo, Func`3 proceed)
   at Volo.Abp.BackgroundJobs.BackgroundJobStore.GetWaitingJobsAsync(Int32 maxResultCount)
   at Volo.Abp.BackgroundJobs.BackgroundJobWorker.DoWorkAsync(PeriodicBackgroundWorkerContext workerContext)
   at Volo.Abp.BackgroundJobs.BackgroundJobWorker.DoWorkAsync(PeriodicBackgroundWorkerContext workerContext)
   at Volo.Abp.BackgroundWorkers.AsyncPeriodicBackgroundWorkerBase.DoWorkAsync()

This closed issue #11357 talks of changes introduced in Npgsql 6+, and hints at setting a LEGACY behaviour flag as a workaround.

This flag IS NOT present in the generated startup template sources, nor is it clear or documented WHERE to set it manually.

Using this flag is not the way forward for PGSQL compatibility, as the breaking change in DateTime handling introduces a clearer and more correct way of handling this data type.

I have this issue on 5.0.1 also, which is why I tried starting again from the baseline 5.1.2, hoping it might have been fixed...

What is the official Volosoft policy for this PGSQL compatibily issue ?

I cannot continue working with ABP Commercial sources until DateTime props are correctly mapped to timestamp with timezone by default, and correctly handled during read/write by the framework.

[maliming]

#11371

https://github.com/abpframework/eShopOnAbp/pull/65/files#diff-1f48de2775294ef07a3d1bcd3ab54a6da27bb4bf1c7b4365b78dd8fb6096ce93R19-R21

https://github.com/abpframework/eShopOnAbp/pull/65/files#diff-1997a5d749556f5c0cae9bd351d765a86d7922d3a2a7581105de34c164f9a467R38-R40

[sanghel-payoff]

@maliming I'm sorry, I'm not sure I understand what you've linked.

Those diffs show that setting the Npgsql legacy flag generates a DB schema with columns of type timestamp without timezone.

I need datetime columns to be mapped to timestamp with timezone and correctly read / written.

Is this no longer supported?

[maliming]

hi

I haven't tried to use Npgsql timestamp with timezone in ABP, I will try it after the new ABP patch version is released, if possible I will share a sample to: https://github.com/abpframework/abp-samples

[MAlshehri]

Same problem

Failed executing DbCommand (12ms) [Parameters=[@p0='?' (DbType = Guid), @p1='?', @p2='?' (DbType = DateTime), @p3='?' (DbType = Guid), @p4='?' (DbType = Guid), @p5='?' (DbType = DateTime), @p6='?', @p7='?', @p8='?' (DbType = Boolean), @p9='?' (DbType = DateTime), @p10='?' (DbType = Guid), @p11='?' (DbType = Boolean), @p12='?' (DbType = DateTime), @p13='?', @p14='?', @p15='?', @p16='?', @p17='?', @p18='?', @p19='?', @p20='?' (DbType = Guid), @p21='?'], CommandType='Text', CommandTimeout='30']
INSERT INTO "AbpUsers" ("Id", "ConcurrencyStamp", "CreationTime", "CreatorId", "DeleterId", "DeletionTime", "Email", "ExtraProperties", "IsActive", "LastModificationTime", "LastModifierId", "LockoutEnabled", "LockoutEnd", "Name", "NormalizedEmail", "NormalizedUserName", "PasswordHash", "PhoneNumber", "SecurityStamp", "Surname", "TenantId", "UserName")
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20, @p21)

System.InvalidCastException: Cannot write DateTime with Kind=Local to PostgreSQL type 'timestamp with time zone', only UTC is supported. Note that it's not possible to mix DateTimes with different Kinds in an array/range. See the Npgsql.EnableLegacyTimestampBehavior AppContext switch to enable legacy behavior.
   at Npgsql.Internal.TypeHandlers.DateTimeHandlers.TimestampTzHandler.ValidateAndGetLength(DateTime value, NpgsqlParameter parameter)

I fixed it by adding this line
AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);
inside Main (Program.cs)

[ankitatcodingnebula]

@MAlshehri
This only enables the legacy timestamp behavior on Npgsql by using all date time columns as timestamp instead of timestampz

[sanghel-payoff]

I've requested an "official" response to this issue via a support question that anyone can follow here

[sanghel-payoff]

@hikalkan I'm having trouble getting this issue recognized on the support ticket.

Do I need to somehow further clarify this problem ??

[millsaj]

The solution posted recently on the support ticket mentioned seems to work for me.

(adding the below to the WebModule, DbMigratorModule and TestBaseModule if postgres is used there as well)

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