06/06: Delete derivation_source_files that are unreferenced

[Christopher Baines]

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

commit 470573b318fb23c7fbf96ce83efc9d9de949e94d
Author: Christopher Baines <mail@cbaines.net>
AuthorDate: Fri Oct 2 20:14:05 2020 +0100

    Delete derivation_source_files that are unreferenced
    
    This will also delete unreferenced derivation_source_file_nars.
---
 guix-data-service/data-deletion.scm | 16 ++++++++++++++++
 1 file changed, 16 insertions(+)

diff --git a/guix-data-service/data-deletion.scm 
b/guix-data-service/data-deletion.scm
index c1d20e7..6c4e0b9 100644
--- a/guix-data-service/data-deletion.scm
+++ b/guix-data-service/data-deletion.scm
@@ -17,6 +17,7 @@
 
 (define-module (guix-data-service data-deletion)
   #:use-module (srfi srfi-1)
+  #:use-module (srfi srfi-11)
   #:use-module (ice-9 match)
   #:use-module (ice-9 threads)
   #:use-module (squee)
@@ -300,6 +301,17 @@ DELETE FROM builds WHERE id IN ("
           (string-join build-ids ",")
           ")")))))
 
+  (define (delete-unreferenced-derivations-source-files conn)
+    (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
+)"))
+
   (define (maybe-delete-derivation conn id)
     (match (map
             car
@@ -459,6 +471,10 @@ SET CONSTRAINTS 
derivations_by_output_details_set_derivation_id_fkey DEFERRED")
                (close-postgresql-connection-channel conn-channel)
                (simple-format
                 (current-output-port)
+                "Deleting unused derivation_source_files entries")
+               (delete-unreferenced-derivations-source-files conn)
+               (simple-format
+                (current-output-port)
                 "Finished deleting derivations, deleted ~A in total\n"
                 total-deleted))
              (loop (+ total-deleted batch-deleted-count))))))))