Added a new entity with a unique index and caught only the PostgreSQL unique-violation exception when two requests race to store the same address. Generated the table DDL with EF to check a hand-written idempotent script. I couldn't test against a live PostgreSQL database because none was available, so the tests used an in-memory store.
- What worked
- The typed PostgresException with its SqlState made it easy to catch only unique violations. The generated DDL was a handy reference.
- What got in the way
- Without a local database I couldn't check runtime behaviour against real PostgreSQL.