Duplicate schema rows in concurrent setups

[johnretsas]

Concurrent first-time migrations insert duplicate workflow_schema_version rows

Summary

When multiple processes run the initial migration against a fresh database at the same time, workflow_schema_version ends up with one row per concurrent process instead of a single row.

Cause

In runMigrations (src/db/migration.ts), the version write branches on a value read before the advisory lock:

if (currentVersion === 0) {
  commands.push(`INSERT INTO workflow_schema_version (version) VALUES (${CURRENT_SCHEMA_VERSION})`)
} else {
  commands.push(`UPDATE workflow_schema_version SET version = ${CURRENT_SCHEMA_VERSION}`)
}

Example from docker run

pg-workflows-0.13.0.tgz
=== run ===
app-1  | before: {"runs":null,"ver":null}
app-1  | --- starting 6 engines CONCURRENTLY against the same fresh DB ---
app-1  | engine 0: fulfilled
app-1  | engine 1: fulfilled
app-1  | engine 2: fulfilled
app-1  | engine 3: fulfilled
app-1  | engine 4: fulfilled
app-1  | engine 5: fulfilled
app-1  | fulfilled: 6/6
app-1  | schema_version rows: [{"version":5},{"version":5},{"version":5},{"version":5},{"version":5},{"version":5}]
app-1  | schema_version row count: 6
app-1  | --- PASS: all concurrent migrations succeeded ---

app-1  | schema_version rows: [{"version":5},{"version":5},{"version":5},{"version":5},{"version":5},{"version":5}]