01/03: Delete unreferenced derivations in batches

[Christopher Baines]

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

commit 7495085f631a3589ecdf5f282b750a318b422394
Author: Christopher Baines <mail@cbaines.net>
AuthorDate: Tue Aug 1 10:16:31 2023 +0100

    Delete unreferenced derivations in batches
    
    To avoid a long blocking query.
---
 guix-data-service/data-deletion.scm | 26 ++++++++++++++++++--------
 1 file changed, 18 insertions(+), 8 deletions(-)

diff --git a/guix-data-service/data-deletion.scm 
b/guix-data-service/data-deletion.scm
index b48f78c..0dd4a78 100644
--- a/guix-data-service/data-deletion.scm
+++ b/guix-data-service/data-deletion.scm
@@ -425,15 +425,25 @@ WHERE build_server_id = $1
        build-server-ids)))
 
   (define (delete-unreferenced-derivations-source-files conn)
-    (exec-query
-     conn
-     "
+    (define (delete-batch)
+      (exec-query
+       conn
+       "
 DELETE FROM derivation_source_files
-WHERE NOT EXISTS (
-  SELECT 1
-  FROM derivation_sources
-  WHERE derivation_source_file_id = derivation_source_files.id
-)"))
+WHERE id IN (
+  SELECT id
+  FROM derivation_source_files
+  WHERE NOT EXISTS (
+    SELECT 1
+    FROM derivation_sources
+    WHERE derivation_source_file_id = derivation_source_files.id
+  )
+  LIMIT 100
+)
+RETURNING id"))
+
+    (while (not (null? (delete-batch)))
+      #t))
 
   (define (maybe-delete-derivation conn id)
     (match (map