oxidecomputer / oxidecomputer/omicron

[db] Audit usage of "ON CONFLICT DO NOTHING RETURNING ..."

Open
#1,168 5 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

database
Dominant language
Rust
Stars
572
Forks
97
Avg merge
2d 12h
Merged PRs (30d)
96

Description

(here's some background that covers this use-case)

There are a handful of spots in the datastore where we:

  • INSERT INTO a table,
  • ON CONFLICT, DO NOTHING
  • and have a RETURNING clause

Example: https://github.com/oxidecomputer/omicron/blob/3ae28c77cbd2355abeab07e358c5ccbeb035f641/nexus/src/db/datastore.rs#L880-L884

This is valid SQL, but it's a little suspect:

  • If there are no conflicts (we're inserting the object with the UUID for the first time) we get the value that we intended to insert.
  • However, if there are conflicts, this group of statements does not return any rows. Instead, it results in a NotFound error from the database.

This means that, especially in situations where we expect idempotency, this statement may return different results if invoked multiple times.

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start in nexus/src/db/datastore.rs around lines 880-884, then search the datastore for INSERT statements combining ON CONFLICT DO NOTHING with RETURNING. Review each use against the described conflict behavior; the audit is done when all affected usages have been identified and their inconsistent idempotent results are addressed.

Written by the indexing model from the issue text.

Assessment

Tech stack
postgresql, rust
Domain
backend, databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.