Improve SQLite statement handling

The Guix Build Coordinator would segfault, and this seemed to come when
preparing statements. I think this is happening because the (sqlite3) bindings
finalize statements when they're out of scope, and this happens in the garbage
collector thread. SQLite is running in multi-threaded mode, which means
actions relating to one database connection shouldn't happen concurrently in
different threads, hence I think this is leading to a segfault.

To work around this behaviour, pass #:cache? #t to sqlite-prepare so
statements are long lived where possible, or in the few cases where the SQL is
dynamic, make sure to finalize it before the garbage collector gets a chance.

This'll hopefully mean that there's less segfaults...

1 files changed, 83 insertions, 43 deletions

diff --git a/guix-build-coordinator/datastore/sqlite.scm b/guix-build-coordinator/datastore/sqlite.scm
index 0f3e5df..110f577 100644
--- a/guix-build-coordinator/datastore/sqlite.scm
+++ b/guix-build-coordinator/datastore/sqlite.scm
@@ -170,7 +170,8 @@
             (sqlite-prepare
              db
              "
-SELECT description FROM agents WHERE id = :id")))
+SELECT description FROM agents WHERE id = :id"
+             #:cache? #t)))
 
        (sqlite-bind-arguments
         statement
@@ -207,7 +208,8 @@ SELECT description FROM agents WHERE id = :id")))
             (sqlite-prepare
              db
              "
-SELECT id, description FROM agents ORDER BY id")))
+SELECT id, description FROM agents ORDER BY id"
+             #:cache? #t)))
 
        (let ((agents (sqlite-map
                        (match-lambda
@@ -241,7 +243,8 @@ SELECT id, description FROM agents ORDER BY id")))
              db
              "
 SELECT 1 FROM agent_passwords \
-WHERE agent_id = :agent_id AND password = :password")))
+WHERE agent_id = :agent_id AND password = :password"
+             #:cache? #t)))
 
        (sqlite-bind-arguments
         statement
@@ -304,7 +307,8 @@ WHERE related_derivations.name != :derivation
     WHERE derivation_outputs.derivation_name = related_derivations.name
       AND other_derivations.system = derivations.system
   )
-")))
+"
+             #:cache? #t)))
 
        (sqlite-bind-arguments
         statement
@@ -363,7 +367,8 @@ WHERE builds.processed = 1
       ON other_builds.uuid = other_build_results.build_id
     WHERE derivation_outputs.derivation_name = builds.derivation_name
   AND other_build_results.result = 'success'
-) ORDER BY 3 DESC, 2, 1")))
+) ORDER BY 3 DESC, 2, 1"
+             #:cache? #t)))
 
        (let ((result (sqlite-map
                       (match-lambda
@@ -489,7 +494,8 @@ INSERT INTO build_tags (build_id, tag_id) VALUES (:build_id, :tag_id)"
             (sqlite-prepare
              db
              "
-SELECT agent_id, result, COUNT(*) FROM build_results GROUP BY agent_id, result")))
+SELECT agent_id, result, COUNT(*) FROM build_results GROUP BY agent_id, result"
+             #:cache? #t)))
 
        (let ((result
               (sqlite-map
@@ -561,7 +567,8 @@ SELECT derivation_outputs.id
 FROM derivation_outputs
 INNER JOIN builds
   ON builds.derivation_name = derivation_outputs.derivation_name
-WHERE builds.uuid = :build_id AND derivation_outputs.name = :name")))
+WHERE builds.uuid = :build_id AND derivation_outputs.name = :name"
+              #:cache? #t)))
 
         (sqlite-bind-arguments
          statement
@@ -660,7 +667,8 @@ INSERT INTO build_starts (
 SELECT start_time, agent_id
 FROM build_starts
 WHERE build_id = :build_id
-ORDER BY start_time DESC")))
+ORDER BY start_time DESC"
+             #:cache? #t)))
 
        (sqlite-bind-arguments
         statement
@@ -760,7 +768,8 @@ INSERT INTO setup_failure_missing_inputs (
              "
 SELECT missing_input_store_path
 FROM setup_failure_missing_inputs
-WHERE setup_failure_id = :id")))
+WHERE setup_failure_id = :id"
+             #:cache? #t)))
 
        (sqlite-bind-arguments
         statement
@@ -800,7 +809,8 @@ SELECT derivations.system, COUNT(*)
 FROM builds
 INNER JOIN derivations
   ON builds.derivation_name = derivations.name
-GROUP BY derivations.system")))
+GROUP BY derivations.system"
+             #:cache? #t)))
 
        (let ((result
               (sqlite-map
@@ -822,7 +832,8 @@ GROUP BY derivations.system")))
             (sqlite-prepare
              db
              "
-SELECT uuid FROM builds ORDER BY uuid")))
+SELECT uuid FROM builds ORDER BY uuid"
+             #:cache? #t)))
 
        (let loop ((row (sqlite-step statement)))
          (match row
@@ -847,7 +858,8 @@ SELECT uuid FROM builds ORDER BY uuid")))
              "
 SELECT uuid, derivation_name, priority, processed, created_at, end_time
 FROM builds
-WHERE uuid = :uuid")))
+WHERE uuid = :uuid"
+             #:cache? #t)))
 
        (sqlite-bind-arguments
         statement
@@ -888,7 +900,8 @@ WHERE uuid = :uuid")))
              "
 SELECT agent_id, result, failure_reason
 FROM build_results
-WHERE build_id = :build_id")))
+WHERE build_id = :build_id"
+             #:cache? #t)))
 
        (sqlite-bind-arguments
         statement
@@ -918,7 +931,8 @@ WHERE build_id = :build_id")))
 SELECT derivations.system
 FROM builds
 INNER JOIN derivations ON builds.derivation_name = derivations.name
-WHERE builds.uuid = :build_id")))
+WHERE builds.uuid = :build_id"
+             #:cache? #t)))
 
        (sqlite-bind-arguments
         statement
@@ -1019,7 +1033,8 @@ WHERE derivation_outputs.output = :output
             (sqlite-prepare
              db
              "
-SELECT uuid FROM builds WHERE derivation_name = :derivation")))
+SELECT uuid FROM builds WHERE derivation_name = :derivation"
+             #:cache? #t)))
 
        (sqlite-bind-arguments
         statement
@@ -1053,7 +1068,8 @@ SELECT uuid FROM builds WHERE derivation_name = :derivation")))
              "
 SELECT agent_id, failure_reason, COUNT(*)
 FROM setup_failures
-GROUP BY agent_id, failure_reason")))
+GROUP BY agent_id, failure_reason"
+             #:cache? #t)))
 
        (let ((result
               (sqlite-map
@@ -1077,7 +1093,8 @@ GROUP BY agent_id, failure_reason")))
              "
 SELECT id, agent_id, failure_reason
 FROM setup_failures
-WHERE build_id = :build_id")))
+WHERE build_id = :build_id"
+             #:cache? #t)))
 
        (sqlite-bind-arguments
         statement
@@ -1107,7 +1124,8 @@ SELECT id, build_id, agent_id, failure_reason
 FROM setup_failures
 INNER JOIN builds
   ON builds.uuid = setup_failures.build_id
-WHERE builds.processed = 0")))
+WHERE builds.processed = 0"
+             #:cache? #t)))
 
        (let ((result (sqlite-fold
                       (lambda (row result)
@@ -1139,7 +1157,8 @@ WHERE builds.processed = 0")))
             (sqlite-prepare
              db
              "
-SELECT uuid, derivation_name, priority FROM builds WHERE processed = 1")))
+SELECT uuid, derivation_name, priority FROM builds WHERE processed = 1"
+             #:cache? #t)))
 
        (let ((builds (sqlite-map
                       (match-lambda
@@ -1164,7 +1183,8 @@ SELECT uuid, derivation_name, priority FROM builds WHERE processed = 1")))
 SELECT uuid, derivation_name, priority
 FROM builds
 WHERE processed = 0
-ORDER BY priority DESC")))
+ORDER BY priority DESC"
+             #:cache? #t)))
 
        (let ((builds (sqlite-map
                       (match-lambda
@@ -1224,7 +1244,7 @@ EXCEPT
                       (match-lambda
                         (#(uuid) uuid))
                       statement)))
-         (sqlite-reset statement)
+         (sqlite-finalize statement)
 
          builds)))))
 
@@ -1282,7 +1302,7 @@ GROUP BY builds_with_derived_priority.uuid")
                         result)
                       (make-hash-table 10000)
                       statement)))
-         (sqlite-reset statement)
+         (sqlite-finalize statement)
 
          result)))))
 
@@ -1317,7 +1337,8 @@ VALUES (:event, :arguments)")))
             (sqlite-prepare
              db
              "
-SELECT event, COUNT(*) FROM unprocessed_hook_events GROUP BY event")))
+SELECT event, COUNT(*) FROM unprocessed_hook_events GROUP BY event"
+             #:cache? #t)))
 
        (let ((counts (sqlite-map
                       (match-lambda
@@ -1343,7 +1364,8 @@ SELECT event, COUNT(*) FROM unprocessed_hook_events GROUP BY event")))
 SELECT id, event, arguments
 FROM unprocessed_hook_events
 WHERE event = :event
-LIMIT :limit")))
+LIMIT :limit"
+             #:cache? #t)))
 
        (sqlite-bind-arguments
         statement
@@ -1387,7 +1409,8 @@ DELETE FROM unprocessed_hook_events WHERE id = "
              "
 SELECT agent_id, COUNT(*)
 FROM build_allocation_plan
-GROUP BY agent_id")))
+GROUP BY agent_id"
+             #:cache? #t)))
 
        (let ((result
               (sqlite-map
@@ -1447,7 +1470,8 @@ INSERT INTO build_allocation_plan (build_id, agent_id, ordering) VALUES "
             (sqlite-prepare
              db
              "
-SELECT agent_id, COUNT(*) FROM allocated_builds GROUP BY agent_id")))
+SELECT agent_id, COUNT(*) FROM allocated_builds GROUP BY agent_id"
+             #:cache? #t)))
 
        (let ((result
               (sqlite-map
@@ -1472,7 +1496,8 @@ SELECT agent_id, COUNT(*) FROM allocated_builds GROUP BY agent_id")))
 SELECT system
 FROM build_allocation_agent_requested_systems
 WHERE agent_id = :agent_id
-ORDER BY system ASC")))
+ORDER BY system ASC"
+             #:cache? #t)))
 
        (sqlite-bind-arguments
         statement
@@ -1581,7 +1606,7 @@ LIMIT 1"
                (#(uuid derivation_name)
                 `((uuid            . ,uuid)
                   (derivation-name . ,derivation_name))))))
-        (sqlite-reset statement)
+        (sqlite-finalize statement)
 
         build)))
 
@@ -1659,7 +1684,8 @@ WHERE build_allocation_plan.agent_id = :agent_id
   AND builds.processed = 0
   AND builds.uuid NOT IN (SELECT build_id FROM allocated_builds)
 ORDER BY build_allocation_plan.ordering ASC
-LIMIT :limit")))
+LIMIT :limit"
+             #:cache? #t)))
 
        (sqlite-bind-arguments
         statement
@@ -1690,7 +1716,8 @@ SELECT builds.uuid, builds.derivation_name, builds.priority
 FROM builds
 INNER JOIN allocated_builds
   ON builds.uuid = allocated_builds.build_id
-WHERE allocated_builds.agent_id = :agent_id")))
+WHERE allocated_builds.agent_id = :agent_id"
+             #:cache? #t)))
 
        (sqlite-bind-arguments
         statement
@@ -1722,7 +1749,8 @@ FROM allocated_builds
 WHERE allocated_builds.build_id = :build_id
 UNION SELECT agent_id
 FROM build_results
-WHERE build_results.build_id = :build_id")))
+WHERE build_results.build_id = :build_id"
+             #:cache? #t)))
 
        (sqlite-bind-arguments
         statement
@@ -1779,7 +1807,8 @@ WHERE build_results.build_id = :build_id")))
   (let ((statement
          (sqlite-prepare
           db
-          "SELECT changes();")))
+          "SELECT changes();"
+          #:cache? #t)))
     (let ((count
            (vector-ref (sqlite-step statement)
                        0)))
@@ -1792,7 +1821,8 @@ WHERE build_results.build_id = :build_id")))
   (let ((statement
          (sqlite-prepare
           db
-          "SELECT last_insert_rowid();")))
+          "SELECT last_insert_rowid();"
+          #:cache? #t)))
     (let ((id
            (vector-ref (sqlite-step statement)
                        0)))
@@ -1806,7 +1836,8 @@ WHERE build_results.build_id = :build_id")))
          (sqlite-prepare
           db
           "
-SELECT name, id FROM derivation_outputs WHERE derivation_name = :derivation_name")))
+SELECT name, id FROM derivation_outputs WHERE derivation_name = :derivation_name"
+          #:cache? #t)))
 
     (sqlite-bind-arguments
      statement
@@ -1828,7 +1859,8 @@ SELECT name, id FROM derivation_outputs WHERE derivation_name = :derivation_name
           "
 SELECT system, fixed_output
 FROM derivations
-WHERE name = :name")))
+WHERE name = :name"
+          #:cache? #t)))
 
     (sqlite-bind-arguments
      statement
@@ -1867,7 +1899,8 @@ WHERE name = :name")))
              "
 SELECT name, output
 FROM derivation_outputs
-WHERE derivation_name = :derivation_name")))
+WHERE derivation_name = :derivation_name"
+             #:cache? #t)))
 
        (sqlite-bind-arguments
         statement
@@ -1901,7 +1934,8 @@ INNER JOIN derivation_outputs
 LEFT JOIN output_metadata
   ON output_metadata.derivation_output_id = derivation_outputs.id
  AND output_metadata.build_id = builds.uuid
-WHERE builds.uuid = :build_id")))
+WHERE builds.uuid = :build_id"
+             #:cache? #t)))
 
        (sqlite-bind-arguments
         statement
@@ -1936,7 +1970,8 @@ WHERE builds.uuid = :build_id")))
              "
 SELECT system
 FROM derivations
-WHERE name = :name")))
+WHERE name = :name"
+             #:cache? #t)))
 
        (sqlite-bind-arguments
         statement
@@ -2009,7 +2044,8 @@ FROM related_derivations
 INNER JOIN derivation_outputs
   ON related_derivations.name = derivation_outputs.derivation_name
 WHERE output = :output
-")))
+"
+             #:cache? #t)))
 
        (sqlite-bind-arguments
         statement
@@ -2048,7 +2084,8 @@ UPDATE derivations SET fixed_output = ~A WHERE name = '~A'"
                   db
                   "
 INSERT OR IGNORE INTO derivations (name, system, fixed_output)
-  VALUES (:name, :system, :fixed_output)")))
+  VALUES (:name, :system, :fixed_output)"
+                  #:cache? #t)))
 
             (sqlite-bind-arguments
              statement
@@ -2130,7 +2167,8 @@ INSERT INTO derivation_outputs (derivation_name, name, output) VALUES "
           db
           "
 INSERT INTO builds (uuid, derivation_name, priority, created_at)
-VALUES (:uuid, :derivation_name, :priority, datetime('now'))")))
+VALUES (:uuid, :derivation_name, :priority, datetime('now'))"
+          #:cache? #t)))
 
     (sqlite-bind-arguments
      statement
@@ -2147,7 +2185,8 @@ VALUES (:uuid, :derivation_name, :priority, datetime('now'))")))
           db
           "
 INSERT INTO agents (id, description)
-VALUES (:id, :description)")))
+VALUES (:id, :description)"
+          #:cache? #t)))
 
     (sqlite-bind-arguments
      statement
@@ -2163,7 +2202,8 @@ VALUES (:id, :description)")))
           db
           "
 INSERT INTO agent_passwords (agent_id, password)
-VALUES (:agent_id, :password)")))
+VALUES (:agent_id, :password)"
+          #:cache? #t)))
 
     (sqlite-bind-arguments
      statement