diff options
8 files changed, 111 insertions, 5 deletions
diff --git a/guix-build-coordinator/datastore/sqlite.scm b/guix-build-coordinator/datastore/sqlite.scm index bc349d9..7cc66fe 100644 --- a/guix-build-coordinator/datastore/sqlite.scm +++ b/guix-build-coordinator/datastore/sqlite.scm @@ -3662,17 +3662,17 @@ UNION FROM relevant_builds INNER JOIN builds AS relevant_builds_full ON relevant_builds.id = relevant_builds_full.id + INNER JOIN derivation_inputs + ON relevant_builds_full.derivation_id = derivation_inputs.derivation_id INNER JOIN derivation_outputs - ON relevant_builds_full.derivation_id = derivation_outputs.derivation_id + ON derivation_inputs.derivation_output_id = derivation_outputs.id INNER JOIN unbuilt_outputs ON unbuilt_outputs.output_id = derivation_outputs.output_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 + ON all_derivation_outputs.output_id = unbuilt_outputs.output_id INNER JOIN builds ON builds.processed = 0 - AND builds.derivation_id = derivation_inputs.derivation_id + AND builds.derivation_id = all_derivation_outputs.derivation_id ) SELECT build_id FROM relevant_builds diff --git a/sqitch/pg/deploy/recreate_unprocessed_builds_with_derived_priorities.sql b/sqitch/pg/deploy/recreate_unprocessed_builds_with_derived_priorities.sql new file mode 100644 index 0000000..adf111a --- /dev/null +++ b/sqitch/pg/deploy/recreate_unprocessed_builds_with_derived_priorities.sql @@ -0,0 +1,7 @@ +-- Deploy guix-build-coordinator:recreate_unprocessed_builds_with_derived_priorities to pg + +BEGIN; + +-- XXX Add DDLs here. + +COMMIT; diff --git a/sqitch/pg/revert/recreate_unprocessed_builds_with_derived_priorities.sql b/sqitch/pg/revert/recreate_unprocessed_builds_with_derived_priorities.sql new file mode 100644 index 0000000..74580bb --- /dev/null +++ b/sqitch/pg/revert/recreate_unprocessed_builds_with_derived_priorities.sql @@ -0,0 +1,7 @@ +-- Revert guix-build-coordinator:recreate_unprocessed_builds_with_derived_priorities from pg + +BEGIN; + +-- XXX Add DDLs here. + +COMMIT; diff --git a/sqitch/pg/verify/recreate_unprocessed_builds_with_derived_priorities.sql b/sqitch/pg/verify/recreate_unprocessed_builds_with_derived_priorities.sql new file mode 100644 index 0000000..828f1aa --- /dev/null +++ b/sqitch/pg/verify/recreate_unprocessed_builds_with_derived_priorities.sql @@ -0,0 +1,7 @@ +-- Verify guix-build-coordinator:recreate_unprocessed_builds_with_derived_priorities on pg + +BEGIN; + +-- XXX Add verifications here. + +ROLLBACK; diff --git a/sqitch/sqitch.plan b/sqitch/sqitch.plan index 7c0c20e..333d7b2 100644 --- a/sqitch/sqitch.plan +++ b/sqitch/sqitch.plan @@ -37,3 +37,4 @@ create_systems 2021-05-21T17:22:52Z Christopher Baines <mail@cbaines.net> # Crea create_unprocessed_builds_with_derived_priorities 2021-10-22T14:52:18Z Christopher Baines <mail@cbaines.net> # Create unprocessed_builds_with_derived_priorities add_agents_active 2021-11-12T14:00:52Z Christopher Baines <mail@cbaines.net> # Add agents.active output_metadata_index 2021-11-18T00:18:12Z Christopher Baines <mail@cbaines.net> # Add index on output_metadata +recreate_unprocessed_builds_with_derived_priorities 2021-12-22T11:09:54Z Christopher Baines <mail@cbaines.net> # Recreate unprocessed_builds_with_derived_priorities diff --git a/sqitch/sqlite/deploy/recreate_unprocessed_builds_with_derived_priorities.sql b/sqitch/sqlite/deploy/recreate_unprocessed_builds_with_derived_priorities.sql new file mode 100644 index 0000000..dc56ce8 --- /dev/null +++ b/sqitch/sqlite/deploy/recreate_unprocessed_builds_with_derived_priorities.sql @@ -0,0 +1,70 @@ +-- Deploy guix-build-coordinator:recreate_unprocessed_builds_with_derived_priorities to sqlite + +BEGIN; + +DROP TABLE unprocessed_builds_with_derived_priorities; + +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_inputs + ON builds_with_derived_priority.derivation_id = derivation_inputs.derivation_id + 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 + INNER JOIN derivation_outputs AS all_derivation_outputs + ON all_derivation_outputs.output_id = unbuilt_outputs.output_id + INNER JOIN builds + ON builds.processed = 0 + AND builds.derivation_id = all_derivation_outputs.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/recreate_unprocessed_builds_with_derived_priorities.sql b/sqitch/sqlite/revert/recreate_unprocessed_builds_with_derived_priorities.sql new file mode 100644 index 0000000..652a2df --- /dev/null +++ b/sqitch/sqlite/revert/recreate_unprocessed_builds_with_derived_priorities.sql @@ -0,0 +1,7 @@ +-- Revert guix-build-coordinator:recreate_unprocessed_builds_with_derived_priorities from sqlite + +BEGIN; + +-- XXX Add DDLs here. + +COMMIT; diff --git a/sqitch/sqlite/verify/recreate_unprocessed_builds_with_derived_priorities.sql b/sqitch/sqlite/verify/recreate_unprocessed_builds_with_derived_priorities.sql new file mode 100644 index 0000000..fcb94c6 --- /dev/null +++ b/sqitch/sqlite/verify/recreate_unprocessed_builds_with_derived_priorities.sql @@ -0,0 +1,7 @@ +-- Verify guix-build-coordinator:recreate_unprocessed_builds_with_derived_priorities on sqlite + +BEGIN; + +-- XXX Add verifications here. + +ROLLBACK; |