The seed upsert helpers rely on an ON CONFLICT clause the versioning trigger pre-empts

Table of Contents

This page is a capture in the inbox bucket of the product backlog — a pre-sprint idea, not yet pulled into a sprint as a story.

1. What

Twenty-five helpers in projects/ores.sql/create/seed/upsert_functions_create.sql insert with ON CONFLICT ... WHERE valid_to = infinity DO NOTHING against versioned tables whose BEFORE INSERT trigger refuses a version-0 create when a live row exists. The trigger runs before the conflict arbiter, so the clause never fires and the duplicate raises 23505 instead. Each helper needs the existence check the file's own header already documents.

2. Why

PR #2232 fixed the one that collides in a fresh populate, the catalogue helper. The other twenty-five have not been exercised with a duplicate, so they were left alone rather than changed without a way to prove them. A concurrent caller of any of them meets the trigger's exception rather than a quiet skip: the check-then-insert window cannot be closed with FOR UPDATE, because a missing row locks nothing; the trigger's own row lock is what serializes the callers, and the loser raises. Treating that raise as "someone else created it" is a decision for whoever hardens the file, and it should key on the trigger's message so it does not swallow a real unique violation.

3. References

  • PR #2232 — the catalogue helper's fix, and the review note that raised this.
  • projects/ores.sql/create/dq/dq_catalogs_create.sql — the trigger that pre-empts the clause.

4. See also

Emacs 29.3 (Org mode 9.6.15)