branch master updated: Remove builds trigger.

[Mathieu Othacehe]

This is an automated email from the git hooks/post-receive script.

mothacehe pushed a commit to branch master
in repository guix-cuirass.

The following commit(s) were added to refs/heads/master by this push:
     new 19da9be  Remove builds trigger.
19da9be is described below

commit 19da9be47ab7cce86a7e799a3ac2248a76f00912
Author: Mathieu Othacehe <othacehe@gnu.org>
AuthorDate: Sat May 29 15:34:15 2021 +0200

    Remove builds trigger.
    
    The build_dependencies trigger scales really poorly when there is a large
    number of pending builds. Remove this trigger and replace it with a
    periodical update thread.
    
    * src/sql/upgrade-12.sql: New file.
    * Makefile.am (dist_sql_DATA): Add it.
    * src/schema.sql (update_build_dependencies, build_dependencies): Remove 
them.
    * src/cuirass/database.scm (db-update-resumable-builds!): New procedure.
    * src/cuirass/scripts/remote-server.scm (start-periodic-updates-thread): New
    procedure.
    (remote-server): Call it.
---
 Makefile.am                           |  3 ++-
 src/cuirass/database.scm              | 14 ++++++++++++++
 src/cuirass/scripts/remote-server.scm | 25 ++++++++++++++++++++-----
 src/schema.sql                        | 32 --------------------------------
 src/sql/upgrade-12.sql                |  6 ++++++
 5 files changed, 42 insertions(+), 38 deletions(-)

diff --git a/Makefile.am b/Makefile.am
index 2b7e053..ef30452 100644
--- a/Makefile.am
+++ b/Makefile.am
@@ -98,7 +98,8 @@ dist_sql_DATA =                               \
   src/sql/upgrade-8.sql                                \
   src/sql/upgrade-9.sql                                \
   src/sql/upgrade-10.sql                       \
-  src/sql/upgrade-11.sql
+  src/sql/upgrade-11.sql                       \
+  src/sql/upgrade-12.sql
 
 dist_css_DATA =                                        \
   src/static/css/choices.min.css               \
diff --git a/src/cuirass/database.scm b/src/cuirass/database.scm
index 357ef44..fc4f106 100644
--- a/src/cuirass/database.scm
+++ b/src/cuirass/database.scm
@@ -73,6 +73,7 @@
             db-get-jobs-history
             db-add-build-dependencies
             db-get-build-dependencies
+            db-update-resumable-builds!
             db-update-failed-builds!
             db-register-builds
             db-update-build-status!
@@ -820,6 +821,19 @@ SELECT target FROM BuildDependencies WHERE source = " 
build))
          (loop rest
                (cons (string->number target) dependencies)))))))
 
+(define (db-update-resumable-builds!)
+  "Update the build status of the failed-dependency builds which all
+dependencies are successful to scheduled."
+  (with-db-worker-thread db
+    (exec-query/bind db "
+UPDATE Builds SET status = " (build-status scheduled)
+" FROM (SELECT Builds.id, count(dep.id) as deps FROM Builds
+LEFT JOIN BuildDependencies as bd ON bd.source = Builds.id
+LEFT JOIN Builds AS dep ON bd.target = dep.id AND dep.status != 0
+WHERE Builds.status = " (build-status failed-dependency)
+" GROUP BY Builds.id HAVING count(dep.id) = 0) AS deps
+ WHERE deps.id = Builds.id")))
+
 (define (db-update-failed-builds!)
   "Update the build status of the scheduled builds with failed dependencies to
 failed-dependency."
diff --git a/src/cuirass/scripts/remote-server.scm 
b/src/cuirass/scripts/remote-server.scm
index 7ffb1a5..14fca9a 100644
--- a/src/cuirass/scripts/remote-server.scm
+++ b/src/cuirass/scripts/remote-server.scm
@@ -225,11 +225,7 @@ be used to reply to the worker."
                                          #:timeout timeout
                                          #:max-silent max-silent)))
            (reply-worker
-            (zmq-no-build-message)))
-
-       ;; Do some clean-up and remove the scheduled builds with failed
-       ;; dependencies.
-       (db-update-failed-builds!)))
+            (zmq-no-build-message)))))
     (('worker-ping worker)
      (update-worker! worker))
     (('build-started ('drv drv) ('worker worker))
@@ -351,6 +347,24 @@ socket."
 
 
 ;;;
+;;; Periodic updates.
+;;;
+
+(define (start-periodic-updates-thread)
+  "Start a thread running periodic update queries."
+  (call-with-new-thread
+   (lambda ()
+     (set-thread-name "periodic-updates")
+     (let loop ()
+       (let ((resumable (db-update-resumable-builds!))
+             (failed (db-update-failed-builds!)))
+         (log-message "period update: ~a resumable, ~a failed builds."
+                      resumable failed))
+       (sleep 30)
+       (loop)))))
+
+
+;;;
 ;;; ZMQ connection.
 ;;;
 
@@ -534,6 +548,7 @@ exiting."
 
         (with-database
             (start-notification-thread)
+            (start-periodic-updates-thread)
             (for-each (lambda (number)
                         (start-fetch-worker
                          (string-append "fetch-worker-"
diff --git a/src/schema.sql b/src/schema.sql
index db78d4f..22b7a30 100644
--- a/src/schema.sql
+++ b/src/schema.sql
@@ -168,38 +168,6 @@ LEFT JOIN BuildDependencies as bd ON bd.source = Builds.id
 LEFT JOIN Builds AS dep ON bd.target = dep.id AND dep.status != 0
 WHERE Builds.status = -2 GROUP BY Builds.id;
 
--- When a build status is set to failed, update the build status of all the
--- depending builds.
-CREATE OR REPLACE FUNCTION update_build_dependencies()
-RETURNS TRIGGER AS $$
-BEGIN
--- Check if the build is failing.
-IF NEW.status > 0 AND NEW.status != OLD.status THEN
-
--- Select all the builds depending of the failing build.
-WITH RECURSIVE deps AS (
-SELECT source FROM BuildDependencies WHERE target = NEW.id
-UNION
-SELECT BD.source FROM deps INNER JOIN BuildDependencies as BD
-ON BD.target = deps.source)
-
--- If the build is cancelled, update all the depending build status to
--- cancelled. Otherwise update the build status of the depending builds to
--- failed-dependency.
-UPDATE Builds SET status =
-CASE
-WHEN NEW.status = 4 THEN 4 ELSE 2 END
-FROM deps WHERE Builds.id = deps.source;
-END IF;
-RETURN null;
-END
-$$ LANGUAGE plpgsql;
-
-CREATE TRIGGER build_dependencies AFTER UPDATE ON Builds
-FOR EACH ROW
-WHEN (pg_trigger_depth() = 0) --disable trigger cascading.
-EXECUTE PROCEDURE update_build_dependencies();
-
 CREATE INDEX Jobs_name ON Jobs (name);
 CREATE INDEX Jobs_system_status ON Jobs (system, status);
 CREATE INDEX Jobs_build ON Jobs (build); --speeds up delete cascade.
diff --git a/src/sql/upgrade-12.sql b/src/sql/upgrade-12.sql
new file mode 100644
index 0000000..6c3c71a
--- /dev/null
+++ b/src/sql/upgrade-12.sql
@@ -0,0 +1,6 @@
+BEGIN TRANSACTION;
+
+DROP TRIGGER build_dependencies ON Builds;
+DROP FUNCTION update_build_dependencies;
+
+COMMIT;