Fix junction codegen template's current_timestamp version-collision bug
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.
What
Every SQL create script generated by ores.codegen.junction (e.g.
refdata_currency_country_create.sql, refdata_currency_calendar_create.sql,
refdata_currency_pair_convention_calendar_create.sql)
uses current_timestamp throughout its insert trigger and version-close
update statement. ores.codegen.entity's SQL template was already fixed
to use clock_timestamp() instead (see refdata_currencies_create.sql,
which closes the prior version with clock_timestamp() and comments
explaining why), but the junction template was never given the same fix.
Update the junction codegen template (mirroring the entity template's
fix) and regenerate every existing junction's create script.
Why
current_timestamp is frozen for the whole transaction in Postgres, so
a multi-row write to the same junction table within one transaction can
produce two rows whose valid_from=/=valid_to are identical, tripping
the GIST exclusion constraint or silently colliding on the same
timestamp. clock_timestamp() always advances and avoids this.
Flagged as a non-blocking observation during PR #1588's review (task
Migrate currency.holiday_calendar to a real FK relationship) — not a
regression from that PR, since it's inherited unchanged from the
currency_country junction template precedent. A third junction,
currency_pair_convention_calendar,
inherited the same gap when it was modelled — all three junctions (and
any future one) carry the same latent version-collision risk that was
just diagnosed and fixed one table over on the entity side.
References
projects/ores.sql/create/refdata/refdata_currencies_create.sql— the already-fixed entity template output, for comparison.projects/ores.sql/create/refdata/refdata_currency_country_create.sql,refdata_currency_calendar_create.sql, andrefdata_currency_pair_convention_calendar_create.sql— the three affected junction outputs.
See also
- Model a currency-country junction — task that introduced the first affected junction.
- Migrate currency.holiday_calendar to a real FK relationship — task where the gap was flagged.
- Migrate currency_pair_convention.advance_calendar onto the calendar model — task that added the third affected junction.