DbUpdateConcurrencyException raised when inserting into Postgres partitioned table

[gfonkatz]

Bug description

DbUpdateConcurrencyException is raised when inserting into partitioned table. Specifically when inheritance partitioning is used in Postgres.
Documentation is here
https://www.postgresql.org/docs/current/ddl-partitioning.html

The particular thing about inheritance partitioning in PG is that it relies on a trigger script to divert entries into partitions.
If you look at the example in the documentation you see that the trigger script returns null

CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF ( NEW.logdate >= DATE '2006-02-01' AND
         NEW.logdate < DATE '2006-03-01' ) THEN
        INSERT INTO measurement_y2006m02 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2006-03-01' AND
            NEW.logdate < DATE '2006-04-01' ) THEN
        INSERT INTO measurement_y2006m03 VALUES (NEW.*);
    ...
    ELSIF ( NEW.logdate >= DATE '2008-01-01' AND
            NEW.logdate < DATE '2008-02-01' ) THEN
        INSERT INTO measurement_y2008m01 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Date out of range.  Fix the measurement_insert_trigger() function!';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

Inserting a new record would cause the DB to return a result statement
INSERT 0 0
Despite saying 0 0, the record was inserted, the reason it says 0 is because trigger function returns null.
That's an expected behaviour in PG.
EF Core seems to interpret the result as a concurrency problem and gives us an error saying
The database operation was expected to affect 1 row(s), but actually affected 0 row(s); data may have been modified or deleted since entities were loaded

This is wrong, there is no problem here and an entry was actually inserted correctly.

Code

            DbContext.Outbox.Add(new OutboxItem(new UserLoggedInEvent
            {
                CorrelationId = cmd.CommandId == default ? Guid.NewGuid() : cmd.CommandId,
                IdentityUserId = user.Id,
                UserName = user.UserName,
                Timestamp = DateTime.UtcNow,
                IpAddress = cmd.IpAddress,
                UserAgent = cmd.UserAgent,
                Application = Enum.TryParse(typeof(Applications), cmd.Application?.ToUpper(), out var app)
                    ? app?.ToString()
                    : null
            }));

            DbContext.LoginAuditEntries.Add(new LoginAuditEntry(user, true, cmd.UserAgent, cmd.IpAddress, DateTime.UtcNow, cmd.Source, traceId, null));
            await DbContext.SaveChangesAsync();

Stack traces

hydra-identity-service-1  | INSERT INTO "OutboxV2" ("Id", "DispatcherId", "Entity", "EntityId", "EventSequence", "EventType", "SerializedEvent", "Timestamp")
hydra-identity-service-1  | VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7);
hydra-identity-service-1  | [19:56:36 ERR] [OnException] The database operation was expected to affect 1 row(s), but actually affected 0 row(s); data may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.
hydra-identity-service-1  | Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException: The database operation was expected to affect 1 row(s), but actually affected 0 row(s); data may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.
hydra-identity-service-1  |    at Npgsql.EntityFrameworkCore.PostgreSQL.Update.Internal.NpgsqlModificationCommandBatch.ConsumeAsync(RelationalDataReader reader, CancellationToken cancellationToken)
hydra-identity-service-1  |    at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
hydra-identity-service-1  |    at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
hydra-identity-service-1  |    at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
hydra-identity-service-1  |    at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
hydra-identity-service-1  |    at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
hydra-identity-service-1  |    at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList`1 entriesToSave, CancellationToken cancellationToken)
hydra-identity-service-1  |    at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(StateManager stateManager, Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
hydra-identity-service-1  |    at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
hydra-identity-service-1  |    at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
hydra-identity-service-1  |    at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
hydra-identity-service-1  |    at Identity.Domain.Handlers.Commands.LoginCommandHandler.HandleCommand(LoginCommand cmd, Int32 refreshTokenExpirationInMinutes, String traceId, Int32 passwordResetTokenExpirationInMinutes) in /src/Identity.Domain.Handlers/Commands/LoginCommandHandler.cs:line 93
hydra-identity-service-1  |    at Identity.Service.Controllers.IdentityController.Login(LoginCommand cmd) in /src/Identity.Service/Controllers/IdentityController.cs:line 310
hydra-identity-service-1  |    at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
hydra-identity-service-1  |    at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Logged|12_1(ControllerActionInvoker invoker)
hydra-identity-service-1  |    at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
hydra-identity-service-1  |    at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
hydra-identity-service-1  |    at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
hydra-identity-service-1  |    at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeInnerFilterAsync>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
hydra-identity-service-1  |    at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextExceptionFilterAsync>g__Awaited|26_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
hydra-identity-service-1  | [19:56:36 ERR] unhandled exception
hydra-identity-service-1  | Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException: The database operation was expected to affect 1 row(s), but actually affected 0 row(s); data may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.
hydra-identity-service-1  |    at Npgsql.EntityFrameworkCore.PostgreSQL.Update.Internal.NpgsqlModificationCommandBatch.ConsumeAsync(RelationalDataReader reader, CancellationToken cancellationToken)
hydra-identity-service-1  |    at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
hydra-identity-service-1  |    at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
hydra-identity-service-1  |    at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
hydra-identity-service-1  |    at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
hydra-identity-service-1  |    at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
hydra-identity-service-1  |    at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList`1 entriesToSave, CancellationToken cancellationToken)
hydra-identity-service-1  |    at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(StateManager stateManager, Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
hydra-identity-service-1  |    at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
hydra-identity-service-1  |    at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
hydra-identity-service-1  |    at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
hydra-identity-service-1  |    at Identity.Domain.Handlers.Commands.LoginCommandHandler.HandleCommand(LoginCommand cmd, Int32 refreshTokenExpirationInMinutes, String traceId, Int32 passwordResetTokenExpirationInMinutes) in /src/Identity.Domain.Handlers/Commands/LoginCommandHandler.cs:line 93
hydra-identity-service-1  |    at Identity.Service.Controllers.IdentityController.Login(LoginCommand cmd) in /src/Identity.Service/Controllers/IdentityController.cs:line 310
hydra-identity-service-1  |    at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
hydra-identity-service-1  |    at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Logged|12_1(ControllerActionInvoker invoker)
hydra-identity-service-1  |    at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
hydra-identity-service-1  |    at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
hydra-identity-service-1  |    at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
hydra-identity-service-1  |    at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeInnerFilterAsync>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
hydra-identity-service-1  |    at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextExceptionFilterAsync>g__Awaited|26_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)

Include provider and version information

EF Core version: 6.0.7
Database provider: Postgres 9 and 11
Target framework: .net 6
Operating system: linux
IDE: Rider

[roji]

Duplicate of #10443

[roji]

This seems like another case of the well-known scenario where a trigger causes EF's concurrency checks to fail.

On interesting aspect here is that it happens with insertion, as opposed to update/delete; this is probably because the Npgsql provider's concurrency detection works at the PG protocol level - which reports how many rows were inserted - as opposed to the normal SQL Server/SQLite mechanism, where there's no concurrency checking on INSERT (unless there database-generated columns). It may make sense to disable the check on PG for insertions - though it would still happen with database-generated columns.

Out of curiosity, is there any specific reason you're using a trigger rather than PostgreSQL's built-in declarative partitioning? Aside from being simpler, it should presumably make this error go away.

[gfonkatz]

Postgres built in partitioning isn't always a solution, it is not available in earlier versions and also due to its limitations listed here in paragraph 5.11.2.3 https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE

[roji]

@gfonkatz that's right, though as a general rule it's preferable to trigger-based partitioning when it's possible to use it.

[roji]

@gfonkatz I'm told that on SQL Server, it's possible to write the trigger in such a way that it returns the desired number of rows. I don't know enough about triggers in PostgreSQL, but something like that may be possible there as well.

I'll go ahead and close this for now as a duplicate of #10443, but feel free to post back here; we can reopen if needed.

[gfonkatz]

I wish I could find a way around it by changing the trigger script. Unfortunately that's not the case. Using native partitioning is not an option for us due to its limitations. So we use the alternative recommended by PG - inheritance partitioning. That leaves us with the concurrency exception that makes no sense.
@roji Any idea when this could be fixed? It seems like MS has been aware of the problem for a long time.

[roji]

#10443 hasn't received many votes, which is why we haven't prioritized fixing it... It's definitely something we thing should be done, but there's many more higher-priority issues on our list.