From f787df32634323d627e5f0f14f96aa3ac58aad21 Mon Sep 17 00:00:00 2001 From: Christopher Baines Date: Wed, 4 Nov 2020 09:02:17 +0000 Subject: 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... --- guix-build-coordinator/datastore/sqlite.scm | 126 ++++++++++++++++++---------- 1 file changed, 83 insertions(+), 43 deletions(-) (limited to 'guix-build-coordinator/datastore') 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 -- cgit v1.2.3