Refactor how unprocessed builds are tracked

Previously, the allocator worked out the derived priorities for each
build. Unfortunately this is quite a complex query, and took lots of time. As
a result of this, I think the WAL could grow excessively while this long query
was running.

To try and mitigate this, add a new table to keep track of the derived
priorities for unprocessed builds. This requires some maintenance to keep up
to date, which in turn will make things like submitting builds slower, but I
think this might help keep transaction length and WAL size down overall.

7 files changed, 103 insertions, 0 deletions

diff --git a/sqitch/pg/deploy/create_unprocessed_builds_with_derived_priorities.sql b/sqitch/pg/deploy/create_unprocessed_builds_with_derived_priorities.sql
new file mode 100644
index 0000000..adfda14
--- /dev/null
+++ b/sqitch/pg/deploy/create_unprocessed_builds_with_derived_priorities.sql
@@ -0,0 +1,7 @@
+-- Deploy guix-build-coordinator:create_unprocessed_builds_with_derived_priorities to pg
+
+BEGIN;
+
+-- XXX Add DDLs here.
+
+COMMIT;
diff --git a/sqitch/pg/revert/create_unprocessed_builds_with_derived_priorities.sql b/sqitch/pg/revert/create_unprocessed_builds_with_derived_priorities.sql
new file mode 100644
index 0000000..e16cdf5
--- /dev/null
+++ b/sqitch/pg/revert/create_unprocessed_builds_with_derived_priorities.sql
@@ -0,0 +1,7 @@
+-- Revert guix-build-coordinator:create_unprocessed_builds_with_derived_priorities from pg
+
+BEGIN;
+
+-- XXX Add DDLs here.
+
+COMMIT;
diff --git a/sqitch/pg/verify/create_unprocessed_builds_with_derived_priorities.sql b/sqitch/pg/verify/create_unprocessed_builds_with_derived_priorities.sql
new file mode 100644
index 0000000..a8a5251
--- /dev/null
+++ b/sqitch/pg/verify/create_unprocessed_builds_with_derived_priorities.sql
@@ -0,0 +1,7 @@
+-- Verify guix-build-coordinator:create_unprocessed_builds_with_derived_priorities on pg
+
+BEGIN;
+
+-- XXX Add verifications here.
+
+ROLLBACK;
diff --git a/sqitch/sqitch.plan b/sqitch/sqitch.plan
index 198b4de..68e770e 100644
--- a/sqitch/sqitch.plan
+++ b/sqitch/sqitch.plan
@@ -34,3 +34,4 @@ fix_allocated_builds_attempt_2 2021-03-30T08:27:18Z Christopher Baines <mail@cba
 fix_setup_failure_missing_inputs 2021-04-08T19:31:49Z Christopher Baines <mail@cbaines.net> # Fix setup_failure_missing_inputs
 create_outputs 2021-05-21T13:37:49Z Christopher Baines <mail@cbaines.net> # Create the outputs table
 create_systems 2021-05-21T17:22:52Z Christopher Baines <mail@cbaines.net> # Create the systems table
+create_unprocessed_builds_with_derived_priorities 2021-10-22T14:52:18Z Christopher Baines <mail@cbaines.net> # Create unprocessed_builds_with_derived_priorities
diff --git a/sqitch/sqlite/deploy/create_unprocessed_builds_with_derived_priorities.sql b/sqitch/sqlite/deploy/create_unprocessed_builds_with_derived_priorities.sql
new file mode 100644
index 0000000..67cc872
--- /dev/null
+++ b/sqitch/sqlite/deploy/create_unprocessed_builds_with_derived_priorities.sql
@@ -0,0 +1,67 @@
+-- Deploy guix-build-coordinator:create_unprocessed_builds_with_derived_priorities to sqlite
+
+BEGIN;
+
+CREATE TABLE unprocessed_builds_with_derived_priorities (
+  build_id INTEGER PRIMARY KEY REFERENCES builds (id),
+  derived_priority INTEGER NOT NULL,
+  all_inputs_built BOOLEAN NOT NULL
+);
+
+INSERT INTO unprocessed_builds_with_derived_priorities WITH RECURSIVE unprocessed_builds AS (
+  SELECT id
+  FROM builds
+  WHERE processed = 0
+    AND canceled = 0
+    AND (
+      deferred_until IS NULL
+      OR
+      deferred_until < datetime('now')
+    )
+), builds_with_derived_priority(
+                 id, derivation_id, derived_priority
+               ) AS (
+  SELECT builds.id, builds.derivation_id, builds.priority
+  FROM builds
+  INNER JOIN unprocessed_builds
+    ON builds.id = unprocessed_builds.id
+UNION
+  SELECT builds.id, builds.derivation_id,
+         max(builds.priority, builds_with_derived_priority.derived_priority)
+  FROM builds_with_derived_priority
+  INNER JOIN derivation_outputs
+    ON builds_with_derived_priority.derivation_id =
+       derivation_outputs.derivation_id
+  INNER JOIN derivation_outputs AS all_derivation_outputs
+    ON all_derivation_outputs.output_id = derivation_outputs.output_id
+  INNER JOIN derivation_inputs
+    ON derivation_inputs.derivation_output_id = all_derivation_outputs.id
+  INNER JOIN builds
+    ON builds.processed = 0
+   AND builds.derivation_id = derivation_inputs.derivation_id
+), data AS (
+  SELECT builds.id AS build_id, MAX(derived_priority)
+  FROM builds_with_derived_priority
+  INNER JOIN builds
+    ON builds.id = builds_with_derived_priority.id
+  WHERE builds.processed = 0
+  GROUP BY builds.id
+)
+SELECT data.*,
+       NOT EXISTS (
+         SELECT 1
+         FROM derivation_inputs
+         INNER JOIN derivation_outputs
+           ON derivation_inputs.derivation_output_id = derivation_outputs.id
+         INNER JOIN unbuilt_outputs
+           ON unbuilt_outputs.output_id = derivation_outputs.output_id
+         WHERE builds.derivation_id = derivation_inputs.derivation_id
+       )
+FROM data
+INNER JOIN builds ON builds.id = data.build_id;
+
+CREATE INDEX unprocessed_builds_with_derived_priorities_derived_priorities_index_for_all_inputs_built
+  ON unprocessed_builds_with_derived_priorities (derived_priority)
+  WHERE all_inputs_built = 1;
+
+COMMIT;
diff --git a/sqitch/sqlite/revert/create_unprocessed_builds_with_derived_priorities.sql b/sqitch/sqlite/revert/create_unprocessed_builds_with_derived_priorities.sql
new file mode 100644
index 0000000..51b7f92
--- /dev/null
+++ b/sqitch/sqlite/revert/create_unprocessed_builds_with_derived_priorities.sql
@@ -0,0 +1,7 @@
+-- Revert guix-build-coordinator:create_unprocessed_builds_with_derived_priorities from sqlite
+
+BEGIN;
+
+-- XXX Add DDLs here.
+
+COMMIT;
diff --git a/sqitch/sqlite/verify/create_unprocessed_builds_with_derived_priorities.sql b/sqitch/sqlite/verify/create_unprocessed_builds_with_derived_priorities.sql
new file mode 100644
index 0000000..3a17113
--- /dev/null
+++ b/sqitch/sqlite/verify/create_unprocessed_builds_with_derived_priorities.sql
@@ -0,0 +1,7 @@
+-- Verify guix-build-coordinator:create_unprocessed_builds_with_derived_priorities on sqlite
+
+BEGIN;
+
+-- XXX Add verifications here.
+
+ROLLBACK;