Random errors occurring during concurrent database access.

[arakis]

Bug Description

I am using a single BaseRepository instance across multiple threads. As long as I make a "prewarm" single-threaded call, all other parallel queries work without issue. However, without a "prewarm," I run into random errors. The errors occur seemingly at random, such as DateOnly cannot being converted to DateTime, DbNull versus non-DbNull, and so on. It seems there may be a problem while initializing the mapper (I am not using any cache or mapper of my own, I simply use the BaseRepository without any arguments other than the connection string).

I have called PostgreSqlBootstrap.Initialize before, and I ensure that it is called only once.

Question: What is the correct way to handle concurrent queries?

Exception Message:

System.InvalidOperationException: Compiler.DataReader.IsDbNull.FalseExpression: Failed to convert the value expression into its destination .NET CLR Type 'System.Collections.Generic.List`1[[Bar.Client.NsPriceListColumn, Foo.DataSource.Bar.Client, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]]'. PropertyInfo: Columns (System.Collections.Generic.List`1[Bar.Client.NsPriceListColumn]), DeclaringType: Bar.Client.NsPriceList
 ---> System.InvalidOperationException: No coercion operator is defined between types 'System.String' and 'System.Collections.Generic.List`1[Bar.Client.NsPriceListColumn]'.
   at System.Linq.Expressions.Expression.GetUserDefinedCoercionOrThrow(ExpressionType coercionType, Expression expression, Type convertToType)
   at RepoDb.Reflection.Compiler.ConvertExpressionToTypeExpression(Expression expression, Type toType)
   at RepoDb.Reflection.Compiler.GetClassPropertyParameterInfoIsDbNullFalseValueExpression(ParameterExpression readerParameterExpression, ClassPropertyParameterInfo classPropertyParameterInfo, DataReaderField readerField)
   --- End of inner exception stack trace ---
   at RepoDb.Reflection.Compiler.GetClassPropertyParameterInfoIsDbNullFalseValueExpression(ParameterExpression readerParameterExpression, ClassPropertyParameterInfo classPropertyParameterInfo, DataReaderField readerField)
   at RepoDb.Reflection.Compiler.GetClassPropertyParameterInfoValueExpression(ParameterExpression readerParameterExpression, ClassPropertyParameterInfo classPropertyParameterInfo, DataReaderField readerField)
   at RepoDb.Reflection.Compiler.GetMemberBindingsForDataEntity[TResult](ParameterExpression readerParameterExpression, IEnumerable`1 readerFields, IDbSetting dbSetting)
   at RepoDb.Reflection.Compiler.CompileDataReaderToDataEntity[TResult](DbDataReader reader, IEnumerable`1 dbFields, IDbSetting dbSetting)
   at RepoDb.Reflection.Compiler.CompileDataReaderToType[TResult](DbDataReader reader, IEnumerable`1 dbFields, IDbSetting dbSetting)
   at RepoDb.Reflection.FunctionFactory.CompileDataReaderToType[TResult](DbDataReader reader, IEnumerable`1 dbFields, IDbSetting dbSetting)
   at RepoDb.FunctionCache.DataReaderToTypeCache`1.Get(DbDataReader reader, IEnumerable`1 dbFields, IDbSetting dbSetting)
   at RepoDb.FunctionCache.GetDataReaderToTypeCompiledFunction[TResult](DbDataReader reader, IEnumerable`1 dbFields, IDbSetting dbSetting)
   at RepoDb.Reflection.DataReader.ToEnumerable[TResult](DbDataReader reader, IEnumerable`1 dbFields, IDbSetting dbSetting)+MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at RepoDb.Extensions.EnumerableExtension.AsList[T](IEnumerable`1 value)
   at RepoDb.DbConnectionExtension.ExecuteQueryInternalForType[TResult](IDbConnection connection, String commandText, Object param, Nullable`1 commandType, String cacheKey, Nullable`1 cacheItemExpiration, String traceKey, Nullable`1 commandTimeout, IDbTransaction transaction, ICache cache, ITrace trace, String tableName, Boolean skipCommandArrayParametersCheck)
   at RepoDb.DbConnectionExtension.ExecuteQueryInternal[TResult](IDbConnection connection, String commandText, Object param, Nullable`1 commandType, String cacheKey, Nullable`1 cacheItemExpiration, String traceKey, Nullable`1 commandTimeout, IDbTransaction transaction, ICache cache, ITrace trace, String tableName, Boolean skipCommandArrayParametersCheck)
   at RepoDb.DbConnectionExtension.QueryInternalBase[TEntity](IDbConnection connection, String tableName, QueryGroup where, IEnumerable`1 fields, IEnumerable`1 orderBy, Nullable`1 top, String hints, String cacheKey, Nullable`1 cacheItemExpiration, Nullable`1 commandTimeout, String traceKey, IDbTransaction transaction, ICache cache, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDb.DbConnectionExtension.QueryInternal[TEntity](IDbConnection connection, String tableName, QueryGroup where, IEnumerable`1 fields, IEnumerable`1 orderBy, Nullable`1 top, String hints, String cacheKey, Nullable`1 cacheItemExpiration, Nullable`1 commandTimeout, String traceKey, IDbTransaction transaction, ICache cache, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDb.DbConnectionExtension.Query[TEntity,TWhat](IDbConnection connection, TWhat what, IEnumerable`1 fields, IEnumerable`1 orderBy, Nullable`1 top, String hints, String cacheKey, Nullable`1 cacheItemExpiration, Nullable`1 commandTimeout, String traceKey, IDbTransaction transaction, ICache cache, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDb.DbRepository`1.Query[TEntity,TWhat](TWhat what, IEnumerable`1 fields, IEnumerable`1 orderBy, Nullable`1 top, String hints, String traceKey, String cacheKey, IDbTransaction transaction)
   at RepoDb.BaseRepository`2.Query[TWhat](TWhat what, IEnumerable`1 fields, IEnumerable`1 orderBy, Nullable`1 top, String hints, String cacheKey, String traceKey, IDbTransaction transaction)
   at Foo.DataSource.Bar.NsRepository`1.GetById(Int32 id) in C:\Users\user\projects\Foo\src\Foo.DataSource.Bar\NsRepository.cs:line 124
   at Foo.DataSource.Bar.NsDataSourceObjectConverter`2.<>c__DisplayClass1_0.<ConvertedList>b__0(Int32 id) in C:\Users\user\projects\Foo\src\Foo.DataSource.Bar\NsDataSourceObjectConverter.cs:line 29
   at System.Threading.Tasks.Parallel.<>c__DisplayClass32_0`2.<ForEachWorker>b__0(Int32 i)
   at System.Threading.Tasks.Parallel.<>c__DisplayClass19_0`1.<ForWorker>b__1(RangeWorker& currentWorker, Int32 timeout, Boolean& replicationDelegateYieldedBeforeCompletion)

Library Version:

Example: RepoDb v1.13 and RepoDb.Postgres v1.13

[mikependon]

Hi, thanks for reporting this issue. Ideally, the library should be thread-safe by design, but this is a good catch if you encountered it to be not. Sounds like other threads are contaminating the cache of other threads? It is a bit tricky to replicate TBH, but we will get back to you once replicated and if we have possible solutions.
Note: For now, the team is waiting for some ideal number of issues before diving into an actual development and new release.

[arakis]

To generalize the first "prewarm", i place that method into an Init-Method within inherited MyBaseRepositry<T> : BaseRepository<T>:

if (!IsPrewarm)
{
    lock (database)
    {
        if (!IsPrewarm)
        {
            _ = QueryAll().FirstOrDefault();
            IsPrewarm = true;
        }
    }
}

This works, but QueryAll is what it means, it pulls the whole Table. Query() does not work, because it requires the TEntity what., and since whre are here in a base class, TEntity is here an generic also. So, what is the best way to fetch the very first line of a table (regardless of order), just to prewarm the cache, in a custom BaseRepository (so, we have here only the Generic arg).

this does not work:

_ = DbRepository.Query<TEntity>(new { }).FirstOrDefault();
_ = DbRepository.Query<TEntity>(new TEntity()).FirstOrDefault(); // TEntity has new() contraint

this works:

_ = DbRepository.ExecuteQuery<TEntity>($"SELECT * FROM \"" + typeof(TEntity).Name + "\" LIMIT 1").FirstOrDefault();

Is this the correct way?

[arakis]

Here's another exception (happens only when using multiple Threads):

Npgsql.PostgresException (0x80004005): 42804: column "Name" is of type jsonb but expression is of type text

POSITION: 36
   at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|226_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery()
   at RepoDb.DbConnectionExtension.UpdateInternalBase[TEntity](IDbConnection connection, String tableName, TEntity entity, QueryGroup where, IEnumerable`1 fields, String hints, Nullable`1 commandTimeout, String traceKey, IDbTransaction transaction, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDb.DbConnectionExtension.Update[TEntity](IDbConnection connection, TEntity entity, IEnumerable`1 fields, String hints, Nullable`1 commandTimeout, String traceKey, IDbTransaction transaction, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDb.DbRepository`1.Update[TEntity](TEntity entity, IEnumerable`1 fields, String hints, String traceKey, IDbTransaction transaction)
   at RepoDb.BaseRepository`2.Update(TEntity entity, IEnumerable`1 fields, String hints, String traceKey, IDbTransaction transaction)
   at Foo.EntityBaseRepository`2.<>c__DisplayClass25_0.<UpdateInternal>b__0() in C:\Users\user\projects\Foo\src\Foo\Repository\Implementations\Shared\EntityBaseRepository.cs:line 190
   at RecordHistory.RecordHistoryServiceExtensions.GetChangesAndStore[T](IRecordHistoryService`1 service, T entity, Action callback, Nullable`1 changeType) in C:\Users\user\projects\Foo\src\Foo.RecordHistory\RecordHistoryService.cs:line 137
   at Foo.EntityBaseRepository`2.UpdateInternal(TEntity entity, RepoUpdateOptions options) in C:\Users\user\projects\Foo\src\Foo\Repository\Implementations\Shared\EntityBaseRepository.cs:line 190
   at Foo.EntityBaseRepository`2.Update(TEntity item) in C:\Users\user\projects\Foo\src\Foo\Repository\Implementations\Shared\EntityBaseRepository.cs:line 236
   at Foo.Mergers.Merger`1.Update(T dst) in C:\Users\user\projects\Foo\src\Foo\Mergers\Merger.cs:line 48
   at Foo.Mergers.Merger`1.<>c__DisplayClass8_1.<Merge>b__5(IEnumerable`1 group) in C:\Users\user\projects\Foo\src\Foo\Mergers\Merger.cs:line 162
   at System.Threading.Tasks.Parallel.<>c__DisplayClass19_0`1.<ForWorker>b__1(RangeWorker& currentWorker, Int32 timeout, Boolean& replicationDelegateYieldedBeforeCompletion)
  Exception data:
    Severity: ERROR
    SqlState: 42804
    MessageText: column "Name" is of type jsonb but expression is of type text
    Hint: You will need to rewrite or cast the expression.
    Position: 36
    File: parse_target.c
    Line: 595
    Routine: transformAssignedExpr

[arakis]

Maybe it's useful: It seems it has todo with the PropertyHandlers. They are all declared at Property Level, but it seems they are sometimes not correctly respected.

public class FooClass : IdModelBase
{
    [PropertyHandler(typeof(JsonPropertyHandler))]
    [NpgsqlDbType(NpgsqlTypes.NpgsqlDbType.Jsonb)]
    public Translation Name { get; set; }
}

public class JsonPropertyHandler : IPropertyHandler<string?, object?>
{
    public object? Get(string? input, PropertyHandlerGetOptions options)
    {
        if (!string.IsNullOrEmpty(input))
            return JsonConvert.DeserializeObject(input, options.ClassProperty.PropertyInfo.PropertyType);

        return null;
    }

    public string? Set(object? input, PropertyHandlerSetOptions options)
    {
        if (input != null)
            return JsonHelper.ToString(input);

        return null;
    }
}

Again, the behavior only happens when using concurrent connections.

[alexn-tinwell]

@arakis FWIW, we came across this exact issue too. The fact you filed this issue gave me the determination to debug RepoDb when the issue seemed very elusive! Thank you and hope the PR helps.

[psi-skillian]

Hi, all, we're seeing this "Failed to convert the value expression into its destination .NET CLR Type" error on properties where we have handlers defined. Has anyone had the opportunity to review that PR?