Make it easier to retry jobs

Add a new event 'retry', and run jobs where the number of retry events is
greater or equal to the number of failure events.

Also add an index to the git_branches table to make the finding jobs query a
bit faster.

5 files changed, 26 insertions, 3 deletions

diff --git a/guix-data-service/jobs/load-new-guix-revision.scm b/guix-data-service/jobs/load-new-guix-revision.scm
index 11db7ca..569ff62 100644
--- a/guix-data-service/jobs/load-new-guix-revision.scm
+++ b/guix-data-service/jobs/load-new-guix-revision.scm
@@ -1130,10 +1130,13 @@ SELECT
 FROM load_new_guix_revision_jobs
 WHERE
   succeeded_at IS NULL AND
-  NOT EXISTS (
-    SELECT 1
+  (
+    SELECT COUNT(*)
+    FROM load_new_guix_revision_job_events
+    WHERE job_id = load_new_guix_revision_jobs.id AND event = 'retry'
+  ) >= (
+    SELECT COUNT(*)
     FROM load_new_guix_revision_job_events
-    -- Skip jobs that have failed, to avoid trying them over and over again
     WHERE job_id = load_new_guix_revision_jobs.id AND event = 'failure'
   )
 ORDER BY latest_branch_commit DESC, id DESC
diff --git a/sqitch/deploy/add_retry_value_to_job_event_enum.sql b/sqitch/deploy/add_retry_value_to_job_event_enum.sql
new file mode 100644
index 0000000..11f9901
--- /dev/null
+++ b/sqitch/deploy/add_retry_value_to_job_event_enum.sql
@@ -0,0 +1,5 @@
+-- Deploy guix-data-service:add_retry_value_to_job_event_enum to pg
+
+ALTER TYPE job_event ADD VALUE 'retry';
+
+CREATE INDEX git_branches_name_and_datetime ON git_branches (name, datetime DESC);
diff --git a/sqitch/revert/add_retry_value_to_job_event_enum.sql b/sqitch/revert/add_retry_value_to_job_event_enum.sql
new file mode 100644
index 0000000..96e7ccd
--- /dev/null
+++ b/sqitch/revert/add_retry_value_to_job_event_enum.sql
@@ -0,0 +1,7 @@
+-- Revert guix-data-service:add_retry_value_to_job_event_enum from pg
+
+BEGIN;
+
+-- XXX Add DDLs here.
+
+COMMIT;
diff --git a/sqitch/sqitch.plan b/sqitch/sqitch.plan
index 859258a..0b16d72 100644
--- a/sqitch/sqitch.plan
+++ b/sqitch/sqitch.plan
@@ -23,3 +23,4 @@ guix_revision_lint_checkers 2019-09-01T12:17:38Z chris <chris@phact> # Associate
 package_versions_by_guix_revision_range 2019-09-26T20:23:15Z Christopher Baines <mail@cbaines.net> # Add package_versions_by_guix_revision_range table
 index_on_load_new_guix_revision_job_events_job_id 2019-09-29T10:39:04Z Christopher Baines <mail@cbaines.net> # Add index on load_new_guix_revision_job_events.job_id
 fix_null_values_in_git_branches 2019-09-29T11:06:12Z Christopher Baines <mail@cbaines.net> # Fix the NULL values in git_branches
+add_retry_value_to_job_event_enum 2019-10-02T19:13:52Z Christopher Baines <mail@cbaines.net> # Add retry value to job_event enum
diff --git a/sqitch/verify/add_retry_value_to_job_event_enum.sql b/sqitch/verify/add_retry_value_to_job_event_enum.sql
new file mode 100644
index 0000000..907334d
--- /dev/null
+++ b/sqitch/verify/add_retry_value_to_job_event_enum.sql
@@ -0,0 +1,7 @@
+-- Verify guix-data-service:add_retry_value_to_job_event_enum on pg
+
+BEGIN;
+
+-- XXX Add verifications here.
+
+ROLLBACK;