Remove odd package derivations with mismatched systems

This follows on from 79993bfaeff3097d37838bc0636fc20c6fc4283d, disassociating
the relevant derivations from the revisions.

1 files changed, 23 insertions, 0 deletions

diff --git a/sqitch/deploy/remove_odd_package_derivations.sql b/sqitch/deploy/remove_odd_package_derivations.sql
new file mode 100644
index 0000000..6cee32f
--- /dev/null
+++ b/sqitch/deploy/remove_odd_package_derivations.sql
@@ -0,0 +1,23 @@
+-- Deploy guix-data-service:remove_odd_package_derivations to pg
+
+BEGIN;
+
+DELETE FROM guix_revision_package_derivations
+WHERE package_derivation_id IN (
+  SELECT package_derivations.id
+  FROM package_derivations
+  INNER JOIN derivations
+    ON package_derivations.derivation_id = derivations.id
+  WHERE package_derivations.system != derivations.system
+);
+
+DELETE FROM package_derivations
+WHERE id IN (
+  SELECT package_derivations.id
+  FROM package_derivations
+  INNER JOIN derivations
+    ON package_derivations.derivation_id = derivations.id
+  WHERE package_derivations.system != derivations.system
+);
+
+COMMIT;