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
- Clean ores.dq to the component clean standard — the story whose SQL this belongs to.