A versioned seed that says it is idempotent fails on a second run
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
A populate script that inserts version 0 rows with on conflict do update fails on a second run with already_exists. The insert trigger refuses a version 0 write when a current row exists, unless a version replace is allowed. Many of these scripts still state in their header that they are idempotent.
2. Why
A fresh compass db recreate starts with empty tables, so it does not reach this path. A re-run of a populate script against a live database does, and the header comment says that re-run is safe.
Two scripts are proved to fail: refdata_currency_market_tiers_populate.sql and refdata_currency_pair_classifications_populate.sql. The check ran each one in a transaction that rolled back. A text search finds about 102 populate scripts that have both on conflict and a version 0 row. Only those two were run, so the real number is not known.
Decide one rule: either the scripts allow a version replace and are idempotent, or they run once and their headers say so.
3. References
- Found on PR #4507, and raised by its review.