Skip to content

Create `FindOrCreate` type methods for database services

Context

!154 (comment 340667680)

Rational

Currently, before inserting an entity into the database, we have a two phase approach, where we find the entity (usually by its unique digest) to confirm that it is not present, and then we will create it it it is not present.

Convenience

This pattern seems to repeat a fair amount of times already, so this could reduce some duplication that we're already seeing, even in this early (at the time of this writing) stage of using the database in registry code.

Correctness

Since our current technique is done with two separate SQL statements, there is a race condition such that with multiple concurrent writers that an entity with a conflicting digest is successfully inserted into the database after another writer's find, but before its create, causing an error on the unique digest constraint.

sequenceDiagram
    participant C1 as Client One
    participant D as Database
    participant C2 as Client Two
    C1->>+D: FindByDigest(BEEFCAFE...)
    D->>C1: BEEFCAFE not present
    C2->>+D: FindByDigest(BEEFCAFE...)
    D->>C2: BEEFCAFE not present
    C2->>D: Create(object{Digest:BEEFCAFE})
    C1->>D: Create(object{Digest:BEEFCAFE})
    D->>-C2: BEEFCAFE created
    D->>-C1: CONFLICT ON CONSTRAINT uq_digest BEEFCAFE

With the example of manifest_configurations, we should be able to ignore conflicting digest_hexs by adding the following to the create statement.

ON CONFLICT ON CONSTRAINT uq_manifest_configurations_digest_hex DO NOTHING

Or having an idempotent insert with a SELECT: https://stackoverflow.com/questions/47452603/doing-an-idempotent-insert-with-postrgres

It's possible to do this on the normal Create methods that are already present, but there might be a possibility that there are cases where the existence of an entity is an unexpected error.

Efficiency

Given a single FindOrCreate function, it should be possible to accomplish this work with a single SQL statement, which should have a positive impact on performance.