Store each model's result as it finishes, so a run that dies can be resumed

[adrpo]

A library test run writes nothing to the database until it has finished. Every model's result is kept in memory and inserted in a single transaction at the very end, after the reports have been generated and uploaded:

# test.py, after everything else
db.commit()

A master run takes days. Anything that ends it before that last commit - a reboot, a killed job, an omc crash that takes the process with it, an out-of-disk - throws away every model tested so far. #300 makes a lost database connection survivable, but it does not help with any of the others, because there is still nothing written to survive with.

Write each model as it finishes

test.py already runs testmodel.py per model and reads back files/<name>.stat.json. The result row can be written when that result is collected, so a run that dies has cost one model rather than the week. Roughly 19500 small transactions instead of one large one, which is nothing against ~19 seconds of work per model.

Resuming

A resumed run must adopt the date of the run it continues, not start a new one. Then skipping what is already stored needs no new schema at all:

SELECT model FROM "master-fmi" WHERE date = ? AND libname = ?

and the unique key (date, libname, model) from #295 makes a re-inserted model a no-op, so a resume can be sloppy without ever duplicating a row.

What invalidates partial data

Partial results may only be reused when the inputs that produced them are unchanged, and the granularity differs:

• the compiler changed - discard the whole partial run: omcversion is per run, so every row in it came from a different omc;
• a library was bumped - discard only that library's rows: libversion and confighash are per (run, library), so a new Buildings says nothing about the Modelica models already tested in the same run.

The test for this is the one the run already makes before testing a library:

SELECT ... FROM libversion NATURAL JOIN omcversion
WHERE libversion=? AND libname=? AND branch=? AND omcversion=? AND confighash=?

confighash covering the configuration and the reference files means a changed reference file invalidates too, which is right.

Discarding should delete the rows. An abandoned run is invisible to the reports, so its rows would otherwise accumulate silently, and nothing existing cleans them up - clean-empty-omcversion-dates.py does the opposite, dropping run rows that have no results.

Knowing whether a run finished

This is the part that needs a new record, and it is also what
#301 asks for:

CREATE TABLE run (
  branch text NOT NULL,
  date   bigint NOT NULL,
  host   text,                 -- which machine, see #301
  omcversion text,
  started_at  timestamptz NOT NULL,
  finished_at timestamptz,     -- NULL: still running, or died
  PRIMARY KEY (branch, date)
);

The run inserts its row at the start and sets finished_at at the end. A resume looks for the newest run of this branch, with the same compiler, whose finished_at is NULL.

The dangerous part: the reports

Every place that picks "the latest run" reads it straight out of a branch table:

# report.py
cursor.execute("SELECT date FROM %s WHERE libname=? ORDER BY date DESC LIMIT 1" ...)

With partial data that silently selects a half-written run, and a library showing 200 of its 3000 models looks like a catastrophic regression - with the emails that go with it. Every such query has to require a finished run: report.py, all-reports.py, all-plots.py, single-model.py.

This is most of the work in this change, and getting it wrong is worse than not doing the change at all, so it wants testing against real data before it goes anywhere near the nightly jobs.

Smaller things to decide

• The job claim stays per library, since it is about two machines not doing the same work. But a resumed run has to be able to re-claim the libraries it had partly done, or the machine that died holds them until its heartbeat goes stale, 30 minutes by default.
• libversion is written per library at the end of a run today. It has to move to when that library's first model is stored, otherwise a resume cannot tell which library version the partial rows belong to.
• A resumed run spans more than one day. The rows of one date may have been produced over two days; started_at and finished_at make that visible rather than implied.

Context: the database is the shared PostgreSQL one from #295; #300 is the narrower fix for losing the connection; #301 wants the machine recorded per run, which the run table above would carry.

---

Generated by Claude Code.

[adrpo]

Design: keep partial results out of the branch tables entirely

Having looked at what "ignore incomplete runs" would actually cost, I want to propose a variant that avoids it.

The reports choose which run to show in a dozen places, and each one reads a branch table directly:

report.py:46    SELECT date FROM <branch> ORDER BY date DESC LIMIT 1
report.py:67    SELECT date FROM <branch> WHERE libname=? ORDER BY date DESC LIMIT 1
all-reports.py:159  SELECT date FROM <branch> WHERE date<? AND libname=? ORDER BY date DESC LIMIT 1
all-plots.py:117    SELECT date,libname,COUNT(...) FROM <branch> GROUP BY date,libname
single-model.py:42  SELECT finalphase,date,libversion FROM <branch> NATURAL JOIN libversion ...

Teaching all of them about finished runs is most of the work in this change, and the failure mode of getting one wrong is a report built on half a run - which looks exactly like a catastrophic regression, and mails the list about it.

Instead: stage the partial rows

Let a run write each model as it finishes into a staging table, and move the whole set into the branch table when the run completes:

CREATE TABLE partial_results (
  branch text NOT NULL, date bigint NOT NULL,
  -- the columns of a branch table
  libname text NOT NULL, model text NOT NULL, exectime double precision, ...
  PRIMARY KEY (branch, date, libname, model)
);

CREATE TABLE run (
  branch text NOT NULL, date bigint NOT NULL,
  host text, omcversion text,
  started_at timestamptz NOT NULL, finished_at timestamptz,
  PRIMARY KEY (branch, date)
);

• during the run: one small insert into partial_results per model, as its result is collected;
• at the end: one transaction moves the rows into the branch table, writes libversion and omcversion, deletes the staged rows and sets finished_at;
• the reports do not change at all: a branch table still only ever contains complete runs, so a run still appears atomically, exactly as today.

What this buys

• A run that dies has lost one model, not days. Its work is durable in partial_results the moment each model finishes.
• No report can accidentally see partial data, because partial data is not in the tables the reports read. The riskiest part of Store each model's result as it finishes, so a run that dies can be resumed #302 disappears rather than being carefully handled.
• The final move is one transaction, but it is no longer "hours of work riding on one commit": if it fails, the rows are still staged and the next attempt moves them again. Combined with the keys from Several improvements to LibraryTesting needed: network SQLDB and docker images to run the tests #295 the move is idempotent.

Resuming

A resumed run adopts the date of the run it continues, found in run as the newest row for this branch with finished_at IS NULL and the same omcversion. Models already staged for that (branch, date, libname) are skipped.

Invalidation stays as discussed, and both cases are a delete from partial_results rather than from a branch table:

• omc changed - no unfinished run matches, so nothing is resumed; the stale rows are deleted with their run row;
• a library bumped (libversion or confighash differs from what that library's staged rows were produced with) - delete just that library's staged rows and test it again.

To know what a staged library was produced with, libversion rows are written when a library's first model is staged rather than at the end of the run, which #302 already calls for.

Costs

• One extra table and one extra copy of each row at the end - 19500 rows, a second or so.
• The staging table is wide, like a branch table. It stays small: it holds only what an unfinished run has produced.
• The run table also answers Record which machine ran a test, and make performance comparisons machine-aware #301, which wants the machine recorded per run.

What still has to be decided

• Whether partial_results should be one table with a branch column, or one per branch like the results. One table is simpler and it is never large.
• Whether a resumed run should re-claim its libraries immediately (otherwise the dead machine's claims block it until the heartbeat goes stale, 30 minutes).
• Whether to keep the old behaviour available - a run that writes only at the end - for the sqlite3 backend, where there is a single writer and no shared database to coordinate with.

---

Generated by Claude Code.