Tag: postgresql

A SET statement run once is wiped by DISCARD ALL the moment Npgsql returns the connection to its pool. Put search_path and statement_timeout in the startup packet with the Search Path and Options connection string keywords, fall back to ALTER ROLE or a ConnectionOpened interceptor, and know why UsePhysicalConnectionInitializer silently loses its SET.

Loading an entity, calling List.Add, and saving rewrites the whole jsonb document and silently loses concurrent appends. Push the append into one UPDATE with the jsonb || operator, either through ExecuteSqlAsync or a mapped function inside ExecuteUpdateAsync, and make it idempotent with a @> guard.

The existence check in your handler is not the guard you think it is. Put a unique index on the inbox table, write the marker in the same SaveChanges as the business change, and let the database pick the winner. Plus the ExecuteUpdate trap that quietly undoes all of it.

EF Core 11 still has no lock API. Here is how to take a real row lock with FromSql: WITH (UPDLOCK, ROWLOCK) on SQL Server, FOR UPDATE on PostgreSQL, the subquery trap that silently widens the lock, NOWAIT and SKIP LOCKED, deadlock retries, and what to do when the row does not exist yet.

The check-then-insert in your handler is not atomic. Catch PostgresException with SqlState 23505, or collapse the whole thing into one INSERT ... ON CONFLICT statement. EnableRetryOnFailure will not help.