hydra-eval-jobsets is disruptive for ongoing builds on big jobsets

[mweinelt]

Describe the bug
When hydra-eval-jobset adds builds in bulk to the database it apparently locks the builds table, so that the queue-runner cannot mark new builds as finished for the duration of the insertion. For big branches like staging-next this builds for around 30 minutes.

To Reproduce
Steps to reproduce the behavior:

1. Eval staging-next or trunk-combined
2. Wait until the queue-runner logs, e.g. warning: 278 concurrent database updates; PostgreSQL may be stalled
3. Browse the active queries on the database
4. See the

Expected behavior
Better coexistence with the queue-runner.

Screenshots

Example with just a few builds showing this behavior. The oldest UPDATE query is already waiting for 13m36s in this example:

hydra=# SELECT datname, pid, state, query, age(clock_timestamp(), query_start) AS age 
FROM pg_stat_activity
WHERE state <> 'idle' 
    AND query NOT LIKE '% FROM pg_stat_activity %' 
ORDER BY age;
 datname |   pid   |        state        |                                                                                              query                                                                                              |       age       
---------+---------+---------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------
 hydra   | 1789843 | idle in transaction | INSERT INTO jobsetevalmembers ( build, eval, isnew) VALUES ( $1, $2, $3 )                                                                                                                       | 00:00:00.001482
 hydra   | 1811605 | active              | SELECT COUNT( * ) FROM builds me WHERE ( finished = $1 )                                                                                                                                        | 00:00:00.020301
 hydra   | 1811808 | active              | SELECT COUNT( * ) FROM builds me WHERE ( finished = $1 )                                                                                                                                        | 00:00:00.026519
 hydra   | 1811805 | active              | SELECT COUNT( * ) FROM builds me WHERE ( finished = $1 )                                                                                                                                        | 00:00:00.034964
 hydra   | 1139762 | active              | update Builds set finished = 1, buildStatus = $2, startTime = $3, stopTime = $4, size = $5, closureSize = $6, releaseName = $7, isCachedBuild = $8, notificationPendingSince = $4 where id = $1 | 00:00:00.230578
 hydra   | 1306954 | active              | update Builds set finished = 1, buildStatus = $2, startTime = $3, stopTime = $4, size = $5, closureSize = $6, releaseName = $7, isCachedBuild = $8, notificationPendingSince = $4 where id = $1 | 00:09:31.063888
 hydra   | 1306136 | active              | update Builds set finished = 1, buildStatus = $2, startTime = $3, stopTime = $4, size = $5, closureSize = $6, releaseName = $7, isCachedBuild = $8, notificationPendingSince = $4 where id = $1 | 00:13:02.730224
 hydra   | 1138851 | active              | update Builds set finished = 1, buildStatus = $2, startTime = $3, stopTime = $4, size = $5, closureSize = $6, releaseName = $7, isCachedBuild = $8, notificationPendingSince = $4 where id = $1 | 00:13:36.981237

Hydra Server:

• OS and version: NixOS 24.11
• Version of Hydra: 031aaa0
• Version of Nix Hydra is built against: 2.24.8
• Version of the Nix daemon: 2.24.8

Additional context
This affects hydra.nixos.org.