Repodb: Bug: Anonymous record Insert fails if no id is provided

[AngelMunoz]

Bug Description

Trying to Insert an anonymous record fails if no id is provided which seems weird because 7 months ago this seemed to be working https://github.com/mikependon/RepoDB.Tutorials/blob/master/FSharp/FSharpRepoDB/FSharpRepoDB/Program.fs#L50

I was writing a blogpost about repodb when I found the issue

Exception Message:

PS C:\Users\scyth\repos\blogpostdrafts> dotnet fsi scripts/database/repodb.fsx
System.AggregateException: One or more errors occurred. (The non-identity primary field must be present during insert operation.)
 ---> RepoDb.Exceptions.PrimaryFieldNotFoundException: The non-identity primary field must be present during insert operation.
   at RepoDb.StatementBuilders.BaseStatementBuilder.CreateInsert(QueryBuilder queryBuilder, String tableName, IEnumerable`1 fields, DbField primaryField, DbField identityField, String hints)      
   at RepoDb.StatementBuilders.PostgreSqlStatementBuilder.CreateInsert(QueryBuilder queryBuilder, String tableName, IEnumerable`1 fields, DbField primaryField, DbField identityField, String hints)
   at RepoDb.CommandTextCache.GetInsertTextInternal(InsertRequest request, IEnumerable`1 fields)
   at RepoDb.CommandTextCache.GetInsertTextAsync(InsertRequest request, CancellationToken cancellationToken)
   at RepoDb.Contexts.Providers.InsertExecutionContextProvider.CreateAsync[TEntity](IDbConnection connection, String tableName, IEnumerable`1 fields, String hints, IDbTransaction transaction, IStatementBuilder statementBuilder, CancellationToken cancellationToken)
   at RepoDb.DbConnectionExtension.InsertAsyncInternalBase[TEntity,TResult](IDbConnection connection, String tableName, TEntity entity, IEnumerable`1 fields, String hints, Nullable`1 commandTimeout, IDbTransaction transaction, ITrace trace, IStatementBuilder statementBuilder, CancellationToken cancellationToken)
   at [email protected](Unit unitVar0)
   at Ply.TplPrimitives.AwaitableContinuation`3.Invoke(Unit r)
   at [email protected](AwaitableContinuation`3 this)
   at Ply.TplPrimitives.ContinuationStateMachine`1.System-Runtime-CompilerServices-IAsyncStateMachine-MoveNext()
   --- End of inner exception stack trace ---
   at Microsoft.FSharp.Control.AsyncResult`1.Commit() in F:\workspace\_work\1\s\src\fsharp\FSharp.Core\async.fs:line 337
   at Microsoft.FSharp.Control.AsyncPrimitives.RunSynchronouslyInCurrentThread[a](CancellationToken cancellationToken, FSharpAsync`1 computation) in F:\workspace\_work\1\s\src\fsharp\FSharp.Core\async.fs:line 870
   at Microsoft.FSharp.Control.AsyncPrimitives.RunSynchronously[T](CancellationToken cancellationToken, FSharpAsync`1 computation, FSharpOption`1 timeout) in F:\workspace\_work\1\s\src\fsharp\FSharp.Core\async.fs:line 878
   at Microsoft.FSharp.Control.FSharpAsync.RunSynchronously[T](FSharpAsync`1 computation, FSharpOption`1 timeout, FSharpOption`1 cancellationToken) in F:\workspace\_work\1\s\src\fsharp\FSharp.Core\async.fs:line 1142
   at <StartupCode$FSI_0002>.$FSI_0002.main@()
Stopped due to error

Schema and Model:
Using the following Schema

CREATE TABLE todos (
  Id serial NOT NULL,
  Title varchar(100) NOT NULL,
  is_done bool NOT NULL,
  CONSTRAINT todos_pkey PRIMARY KEY (id)
);

To run the following script create a file with the .fsx extension (example: script.fsx)
and run
dotnet fsi script.fsx

#r "nuget: RepoDb.PostgreSql, 1.1.4-beta1"
#r "nuget: Ply"

open FSharp.Control.Tasks
open System.Collections.Generic
open Npgsql
open RepoDb
open RepoDb.Attributes

let url =
    "Server=192.168.0.33;Port=5432;Database=simplefsharp;User Id=admin;Password=Admin123;"

[<Map("public.todos")>]
type Todo =
    { id: int64
      title: string
      is_done: bool }

PostgreSqlBootstrap.Initialize()

task {
    let todo =
        {| title = "Some Todo"
           is_done = false |}

    use connection = new NpgsqlConnection(url)
    return! connection.InsertAsync<int64>(ClassMappedNameCache.Get<Todo>(), todo)
}
|> Async.AwaitTask
|> Async.RunSynchronously

however if you add an Id field it works

#r "nuget: RepoDb.PostgreSql, 1.1.4-beta1"
#r "nuget: Ply"

open FSharp.Control.Tasks
open System.Collections.Generic
open Npgsql
open RepoDb
open RepoDb.Attributes

let url =
    "Server=192.168.0.33;Port=5432;Database=simplefsharp;User Id=admin;Password=Admin123;"

[<Map("public.todos")>]
type Todo =
    { id: int64
      title: string
      is_done: bool }

PostgreSqlBootstrap.Initialize()

task {
    let todo =
        {| id = 0L
           title = "Some Todo"
           is_done = false |}

    use connection = new NpgsqlConnection(url)
    return! connection.InsertAsync<int64>(ClassMappedNameCache.Get<Todo>(), todo)
}
|> Async.AwaitTask
|> Async.RunSynchronously
|> printfn "Inserted id: %i"

prints Inserted id: 0 if you run it again it will give a different stack trace

PS C:\Users\scyth\repos\blogpostdrafts> dotnet fsi scripts/database/repodb.fsx
System.AggregateException: One or more errors occurred. (23505: duplicate key value violates unique constraint "todos_pkey")
 ---> Npgsql.PostgresException (0x80004005): 23505: duplicate key value violates unique constraint "todos_pkey"
   at Npgsql.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|194_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at Npgsql.NpgsqlDataReader.<>c__DisplayClass41_0.<<ReadMessage>g__ReadMessageSequential|0>d.MoveNext()
--- End of stack trace from previous location ---
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteScalar(Boolean async, CancellationToken cancellationToken)
   at RepoDb.DbConnectionExtension.InsertAsyncInternalBase[TEntity,TResult](IDbConnection connection, String tableName, TEntity entity, IEnumerable`1 fields, String hints, Nullable`1 commandTimeout, IDbTransaction transaction, ITrace trace, IStatementBuilder statementBuilder, CancellationToken cancellationToken)
   at [email protected](Unit unitVar0)
   at Ply.TplPrimitives.AwaitableContinuation`3.Invoke(Unit r)
   at [email protected](AwaitableContinuation`3 this)
   at Ply.TplPrimitives.ContinuationStateMachine`1.System-Runtime-CompilerServices-IAsyncStateMachine-MoveNext()
  Exception data:
    Severity: ERROR
    SqlState: 23505
    MessageText: duplicate key value violates unique constraint "todos_pkey"
    Detail: Detail redacted as it may contain sensitive data. Specify 'Include Error Detail' in the connection string to include this information.
    SchemaName: public
    TableName: todos
    ConstraintName: todos_pkey
    File: nbtinsert.c
    Line: 649
    Routine: _bt_check_unique
   --- End of inner exception stack trace ---
   at Microsoft.FSharp.Control.AsyncResult`1.Commit() in F:\workspace\_work\1\s\src\fsharp\FSharp.Core\async.fs:line 337
   at Microsoft.FSharp.Control.AsyncPrimitives.RunSynchronouslyInCurrentThread[a](CancellationToken cancellationToken, FSharpAsync`1 computation) in F:\workspace\_work\1\s\src\fsharp\FSharp.Core\async.fs:line 870
   at Microsoft.FSharp.Control.AsyncPrimitives.RunSynchronously[T](CancellationToken cancellationToken, FSharpAsync`1 computation, FSharpOption`1 timeout) in F:\workspace\_work\1\s\src\fsharp\FSharp.Core\async.fs:line 878
   at Microsoft.FSharp.Control.FSharpAsync.RunSynchronously[T](FSharpAsync`1 computation, FSharpOption`1 timeout, FSharpOption`1 cancellationToken) in F:\workspace\_work\1\s\src\fsharp\FSharp.Core\async.fs:line 1142
   at <StartupCode$FSI_0002>.$FSI_0002.main@()
Stopped due to error

but that is likely to be a different issue

I don't have an timeframe for a fix I was doing some exploration work

Library Version:

Running: RepoDb.PostgreSql, 1.1.4-beta1

[mikependon]

@AngelMunoz - the Id column is defined as serial and not as the GENERATED IDENTITY AS ALWAYS as fully supported. You are the 4th person who reported this issue and I will take action on this. 😄

FYI: The serial is the old way of defining an auto-increment column in the table at Postgre Database. It is been there for ages by now, but it seems various systems are still using it, that is why we are receiving this kind of report. To be more compliant, specially if you are using the v10, use the GENEERATED IDENTITY AS ALWAYS.

This StackOverflow thread also mentioned that.

[AngelMunoz]

I see I need to brush up my postgres knowledge up a bit then, interesting would it be worth mentioning it in the docs rather than trying to "fix it"? because you're right it is not a RepoDB issue changing the table to this

CREATE TABLE todos (
  Id integer primary key GENERATED always as identity,
  Title varchar(100) NOT NULL,
  is_done bool NOT NULL
);

as you mentioned it works completely fine

#r "nuget: RepoDb.PostgreSql"
#r "nuget: Ply"

open FSharp.Control.Tasks
open System.Collections.Generic
open Npgsql
open RepoDb
open RepoDb.Attributes

let url =
    "Server=192.168.0.33;Port=5432;Database=simplefsharp;User Id=admin;Password=Admin123;"

[<Map("public.todos")>]
type Todo =
    { id: int64
      title: string
      is_done: bool }

PostgreSqlBootstrap.Initialize()

task {
    let todo =
        {| id = 0L
           title = "Some Todo"
           is_done = false |}

    use connection = new NpgsqlConnection(url)
    return! connection.InsertAsync<int64>(ClassMappedNameCache.Get<Todo>(), todo)
}
|> Async.AwaitTask
|> Async.RunSynchronously
|> printfn "Inserted id: %i"

PS C:\Users\scyth\repos\blogpostdrafts> dotnet fsi scripts/database/repodb.fsx
Inserted id: 1
PS C:\Users\scyth\repos\blogpostdrafts> dotnet fsi scripts/database/repodb.fsx
Inserted id: 2

Even removing the Id field in the anonymous records works as expected

[mikependon]

Thank you for verifying. I like your suggestion about the documentation, TBH. I will assess if the support to the Serial keyword requires only minor changes (hopefully), and if not, I may not fix it. I will just redirect the users/issue-reporters to the documentation site (as you suggested) then.

Note: I am trying to avoid further major changes to the library to further help its stability.

[mikependon]

Referencing #786

[mikependon]

The fixes has been added to support the SERIAL-based identity column. Please do let me know if you need a beta release for this, otherwise, the fixes will be available on the next version > RepoDb.PostgreSql v1.1.4-beta1.

[AngelMunoz]

Thanks Mike, no problem I will go with the correct way to define it in postgres (Id integer primary key GENERATED always as identity) so I'm good