aboutsummaryrefslogtreecommitdiff
path: root/sqitch/deploy
diff options
context:
space:
mode:
authorChristopher Baines <mail@cbaines.net>2019-10-05 09:14:40 +0100
committerChristopher Baines <mail@cbaines.net>2019-10-05 09:14:40 +0100
commit9fc6dd08e681ee8ce12c6d1d5036710d05b5e9db (patch)
tree2df53fdfe072f36f647997108dcf06a2b4ed75be /sqitch/deploy
parenta104fbc5e1cd1485c2ce97e555d91d28f3c6eb67 (diff)
downloaddata-service-9fc6dd08e681ee8ce12c6d1d5036710d05b5e9db.tar
data-service-9fc6dd08e681ee8ce12c6d1d5036710d05b5e9db.tar.gz
Remove duplicates from the guix_revisions table
Diffstat (limited to 'sqitch/deploy')
-rw-r--r--sqitch/deploy/remove_guix_revision_duplicates.sql37
1 files changed, 37 insertions, 0 deletions
diff --git a/sqitch/deploy/remove_guix_revision_duplicates.sql b/sqitch/deploy/remove_guix_revision_duplicates.sql
new file mode 100644
index 0000000..913f8be
--- /dev/null
+++ b/sqitch/deploy/remove_guix_revision_duplicates.sql
@@ -0,0 +1,37 @@
+-- Deploy guix-data-service:remove_guix_revision_duplicates to pg
+
+BEGIN;
+
+WITH data AS (
+ SELECT unnest(old_ids) AS old, id FROM (
+ SELECT MIN(id) AS id, ARRAY_AGG(id) AS old_ids
+ FROM guix_revisions
+ GROUP BY commit, git_repository_id
+ HAVING COUNT(id) > 1
+ ) AS d2
+)
+DELETE FROM guix_revision_package_derivations
+WHERE revision_id IN (SELECT old FROM data WHERE old != id);
+
+WITH data AS (
+ SELECT unnest(old_ids) AS old, id FROM (
+ SELECT MIN(id) AS id, ARRAY_AGG(id) AS old_ids
+ FROM guix_revisions
+ GROUP BY commit, git_repository_id
+ HAVING COUNT(id) > 1
+ ) AS d2
+)
+UPDATE package_versions_by_guix_revision_range
+SET first_guix_revision_id = data.id
+FROM data
+WHERE first_guix_revision_id = data.old;
+
+DELETE FROM guix_revisions AS g WHERE id NOT IN (
+ SELECT MIN(id)
+ FROM guix_revisions
+ GROUP BY (commit, git_repository_id)
+);
+
+CREATE UNIQUE INDEX ON guix_revisions (commit, git_repository_id);
+
+COMMIT;