03/04: Make backfilling blocked_builds a bit smarter

[Christopher Baines]

cbaines pushed a commit to branch master
in repository data-service.

commit c46ee476325e018f43aa5d0c73b938cf726c9fce
Author: Christopher Baines <mail@cbaines.net>
AuthorDate: Sat Nov 12 11:42:33 2022 +0000

    Make backfilling blocked_builds a bit smarter
    
    And drop the chunk size.
---
 guix-data-service/model/blocked-builds.scm | 20 ++++++++++++++++++--
 1 file changed, 18 insertions(+), 2 deletions(-)

diff --git a/guix-data-service/model/blocked-builds.scm 
b/guix-data-service/model/blocked-builds.scm
index 480b554..1867f66 100644
--- a/guix-data-service/model/blocked-builds.scm
+++ b/guix-data-service/model/blocked-builds.scm
@@ -312,7 +312,23 @@ WHERE EXISTS (
     "
 SELECT build_id
 FROM latest_build_status
- WHERE status IN ('failed', 'failed-dependency', 'failed-other', 'canceled')")
+INNER JOIN builds
+  ON latest_build_status.build_id = builds.id
+WHERE status IN ('failed', 'failed-dependency', 'failed-other', 'canceled')
+  AND NOT EXISTS (
+    SELECT 1
+    FROM builds AS other_builds
+    INNER JOIN latest_build_status AS other_latest_build_status
+      ON other_builds.id = other_latest_build_status.build_id
+    WHERE other_builds.derivation_output_details_set_id =
+          builds.derivation_output_details_set_id
+      AND other_latest_build_status.status IN ('succeeded', 'scheduled')
+  )
+  AND NOT EXISTS (
+    SELECT 1
+    FROM blocked_builds
+    WHERE blocking_derivation_output_details_set_id = 
builds.derivation_output_details_set_id
+  )")
 
   (let ((build-ids
          (map car (exec-query conn query '()))))
@@ -329,7 +345,7 @@ FROM latest_build_status
             (handle-populating-blocked-builds-for-build-failures
              conn
              (map string->number ids))))))
-     500
+     200
      build-ids)))
 
 (define* (select-blocking-builds conn revision-commit