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, and refdata_currency_pair_convention_calendar_create.sql — the three affected junction outputs.

See also

Emacs 29.3 (Org mode 9.6.15)