cbaines/guix-data-service
1
Fork 0
Code Issues Pull requests Projects Releases Packages Wiki Activity Actions
guix-data-service/sqitch/sqitch.plan

105 lines
13 KiB
Text
Raw Permalink Normal View History

Initialise Sqitch It's a tool for managing the database schema.
2019-04-13 12:22:14 +01:00
%syntax-version=1.0.0
%project=guix-data-service
%uri=https://git.cbaines.net/guix/data-service/
Add some initial Sqitch migrations These are based on the state of the current manually managed database.
2019-04-14 10:06:13 +01:00
appschema 2019-04-13T11:43:59Z Christopher Baines <mail@cbaines.net> # Add schema for the Guix Data Service
buildstatus_enum [appschema] 2019-04-13T11:56:37Z Christopher Baines <mail@cbaines.net> # Creates the buildstatus enum
initial_import 2019-04-13T13:06:28Z Christopher Baines <mail@cbaines.net> # Import the manually managed database schema
Switch to storing Git repositories in a table Rather than just storing the URL in the guix_revisions and load_new_guix_revision_jobs tables. This will help when storing more information like tags and branches in the future.
2019-05-05 13:35:48 +01:00
git_repositories 2019-05-04T19:03:38Z Christopher Baines <mail@cbaines.net> # Add a git_repositories table
Start to handle information about Git branches Add some new pages /branches and /branch/... as well as a new git_branches table. Also extend the email processing to enter the branch information in to the database.
2019-05-05 20:06:28 +01:00
git_branches 2019-05-05T14:53:12Z Christopher Baines <mail@cbaines.net> # Add a git_branches table
Remove the sha1_hash from the package_metadata table I'm thinking about adding more fields to this table, and the sha1_hash values will make this tricker. Therefore, remove the value, and adjust the existing code to cope. This commit also adds a new test which coveres some of the changed functionality.
2019-05-12 17:01:51 +01:00
remove_package_metadata_sha1_hash 2019-05-12T10:37:40Z Christopher Baines <mail@cbaines.net> # Remove the sha1_hash field from package_metadata
Store and display the location of packages Store the location a package can be found at, and display this on the package page. If available, link off to the git repository containing the package.
2019-05-13 21:02:53 +01:00
add_location_information 2019-05-12T20:27:48Z Christopher Baines <mail@cbaines.net> # Add locations table and location to package_metadata
add_git_repositories_cgit_url_base 2019-05-13T18:45:14Z Christopher Baines <mail@cbaines.net> # Add cgit_url_base to git_repositories
Store license information for packages And display this on the package page. This uses a couple of new tables, and an additional field in the package_metadata table. Currently, the order of the licenses in the package definition isn't stored, as I'm not sure the order in the list is significant.
2019-05-15 08:05:14 +01:00
license_support 2019-05-13T20:37:40Z Christopher Baines <mail@cbaines.net> # Add support for storing license information
Record job success without deleting the job record Previously, the records for jobs would be deleted. It's useful to know when jobs were inserted in to the database, as well as when they succeeded (if they have). This change also makes it possible to keep track of jobs that have failed, as they won't be deleted.
2019-06-02 16:20:51 +01:00
dates_to_load_new_guix_revision_jobs 2019-06-02T07:39:49Z Christopher Baines <mail@cbaines.net> # Add dates to the load_new_guix_revision_jobs table
Add more detailed new revision job handling Create a new events table for the new guix revision jobs, and update this when processing a job starts, as well as finished with success or failure. Additionally, remove the dependnency on open-inferior/container, as this functionality isn't merged in to Guix master yet.
2019-06-02 22:00:29 +01:00
load_new_guix_revision_job_events 2019-06-02T15:44:41Z Christopher Baines <mail@cbaines.net> # Add new table for guix_revision_job_events
Record the output from loading new revisions to the database So that it can easily be shown through the web interface. There's two tables being used. One which temporarily stores the output as it's output while the job is running, and other which stores the whole log once the job has finished.
2019-06-22 01:13:36 +02:00
load_new_guix_revision_job_logs 2019-06-21T14:33:09Z chris <chris@phact> # Add load_new_guix_revision_job_logs
Tweak how logs are stored Previously, the query for the jobs page was really slow, as it checked the load_new_guix_revision_job_log_parts table for each job, doing a sequential scan through the potentially large table. Adding an index didn't seem to help, as the query planner would belive the query could return loads of rows, where actually, all that needed checking is whether a single row existed with a given job_id. To avoid adding the index to the load_new_guix_revision_job_log_parts table, and fighting with the query planner, this commit changes the load_new_guix_revision_job_logs table to include a blank entry for jobs which are currently being processed. This is inserted at the start of the job, and then updated at the end to combine and replace all the parts. This all means that the jobs page should render quickly now.
2019-07-07 21:23:18 +01:00
change_load_new_guix_revision_job_logs_contents_to_be_nullable 2019-07-07T20:10:54Z Christopher Baines <mail@cbaines.net> # Change the contents field in the load_new_guix_revision_job_logs table\nto be nullable.\n\nwith '#' will # be ignored, and an empty message aborts the add. #\nChange to add: # #\nchange_load_new_guix_revision_job_logs_contents_to_be_nullable #\nsqitch/deploy/change_load_new_guix_revision_job_logs_contents_to_be_nullable.sql\nsqitch/revert/change_load_new_guix_revision_job_logs_contents_to_be_nullable.sql\nsqitch/verify/change_load_new_guix_revision_job_logs_contents_to_be_nullable.sql
Fix some duplicated values in tables The licenses table, along with the package_metadata table had duplicate values. This could happen as the unique constraints on those tables didn't properly account for the nullable fields. The duplicates in those tables also affected the license_sets, packages, package_derivations tables in a similar way. Finally, the guix_revision_package_derivations table was also affected. This commit adds a migration to fix the data, as well as the constraints. THe code to populate the licenses and package_metadata tables is also updated.
2019-08-04 09:39:40 +01:00
fix_duplicated_licenses 2019-07-30T05:48:17Z Christopher Baines <mail@cbaines.net> # Fix duplicated licenses, and add constraints
Change the git_branches table primary key To allow for having branches in multiple git repositories.
2019-08-05 20:02:06 +01:00
change_git_branches_primary_key 2019-08-05T18:57:41Z Christopher Baines <mail@cbaines.net> # Change the git_branches primary key to include the git_repository_id,\nas this will allow having the same branch in different repositories.
Avoid erroring when processing emails again These changes allow processing emails again, and just creating job and branch entries where data is missing.
2019-08-05 20:59:48 +01:00
remove_duplicate_load_new_guix_revision_jobs 2019-08-05T19:06:36Z Christopher Baines <mail@cbaines.net> # Remove duplicate load_new_guix_revision_jobs
Store lint warnings in the database This commit adds the relevant tables and code to store lint warnings in the database. Currently, only lint checkers which don't require access to the network will be run, as this allows the processing to happen without network access. Also, this functionality won't work in older versions of Guix which don't expose the lint warnings in a compatible way.
2019-08-31 12:11:58 +01:00
lint_warnings 2019-08-18T17:10:12Z Christopher Baines <mail@cbaines.net> # Store lint warnings
Add a new table guix_revision_lint_checkers To associate a set of lint checkers with a specific revision. While there is the association through the lint warnings, that only works for checkers where there are lint warnings for that revision. Therefore, to allow finding all the checkers for a specific revision, also associate them directly with the revision.
2019-09-01 13:35:23 +01:00
guix_revision_lint_checkers 2019-09-01T12:17:38Z chris <chris@phact> # Associate lint_checkers to guix_revisions
Add a new table to store package versions by revision ranges This isn't new information, but derived from information already in the database. It's collected here to make querying faster. The table is updated when each new revision is entered.
2019-09-26 23:45:07 +01:00
package_versions_by_guix_revision_range 2019-09-26T20:23:15Z Christopher Baines <mail@cbaines.net> # Add package_versions_by_guix_revision_range table
Add migration to create an index on load_new_guix_revision_job_events To speed up the rendering of the index page.
2019-09-29 12:04:57 +01:00
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 the 'NULL' values in git_branches for the commits The git_branches table had 'NULL' values for some commits where the branch was deleted, importantly this was the string 'NULL', not an actual NULL value. This commit fixes that, migrating the existing values to be '', and changing the relevant code. The primary key is also extended to include the datetime field, as this is important to allow a branch to be deleted twice.
2019-09-29 17:10:04 +01:00
fix_null_values_in_git_branches 2019-09-29T11:06:12Z Christopher Baines <mail@cbaines.net> # Fix the NULL values in git_branches
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.
2019-10-02 21:54:11 +01:00
add_retry_value_to_job_event_enum 2019-10-02T19:13:52Z Christopher Baines <mail@cbaines.net> # Add retry value to job_event enum
Remove duplicates from the guix_revisions table
2019-10-05 09:14:40 +01:00
remove_guix_revision_duplicates 2019-10-05T08:00:14Z Christopher Baines <mail@cbaines.net> # Remove duplicates in the guix_revisions table
Add a new table to describe the history of derivations There's already the package_versions_by_guix_revision_range table, but I think it would be also useful to be able to see how derivations change over time.
2019-11-09 19:59:55 +00:00
package_derivations_by_guix_revision_range 2019-11-09T19:09:48Z Christopher Baines <mail@cbaines.net> # Add package_derivations_by_guix_revision_range
Add a new channel-news module, along with tables the relevant data
2019-11-16 08:55:54 +00:00
channel_news_tables 2019-11-15T07:32:07Z Christopher Baines <mail@cbaines.net> # Add tables to store channel news
Add new table to store token seeds for build servers
2019-11-23 11:30:36 +00:00
build_server_token_seeds 2019-11-23T09:26:48Z Christopher Baines <mail@cbaines.net> # Add build_server_token_seeds table
Rework the builds and build_status tables as well as related code Allow for build status information to be submitted by POST request. This required some changes to the builds and build_status tables, as for example, the Cuirass build id may not be available, and the derivation may not be know yet, so just record the derivation file name.
2019-11-24 12:59:09 +00:00
rework_builds 2019-11-23T20:41:20Z Christopher Baines <mail@cbaines.net> # Rework the build tables
Begin to add support for importing narinfo files This commit adds the tables, as well as code to support extracting data from narinfo files.
2019-11-30 10:51:58 +00:00
nar_related_tables 2019-11-29T20:28:19Z Christopher Baines <mail@cbaines.net> # Add nar related tables
Add an index on the derivation_file_name field in the builds table As this helps when finding builds relating to specific derivations.
2019-12-04 22:03:52 +01:00
add_index_on_builds_derivation_file_name 2019-12-03T16:23:55Z <chris@phact> # Add index on builds_derivation_file_name
Add a table to record where narinfo files were fetched from Otherwise it's hard to associated narinfo files to build servers.
2019-12-04 22:04:44 +01:00
narinfo_fetch_record 2019-12-03T20:53:28Z <chris@phact> # Add a table to describe fetching a narinfo
Change nars size to bigint As some nars can be bigger than the maximum size of an int.
2019-12-05 14:09:28 +01:00
change_nars_size_to_bigint 2019-12-04T21:24:21Z <chris@phact> # Change nars.size to bigint
Change nar_urls size to bigint As some nars can be bigger than the size of an int.
2019-12-05 14:09:47 +01:00
change_nar_urls_size_to_bigint 2019-12-04T21:49:07Z <chris@phact> # Change nar_urls.size to bigint
Deduplicate builds and add a unique index Duplicate builds could creep in if the code to create them ran concurrently. I didn't exclude them initially, as I was unsure if there should be such a restriction, but at least for now, Cuirass builds map exactly to a single derivation, so use the same restriction here.
2019-12-05 14:10:09 +01:00
sort_out_duplicate_builds 2019-12-05T12:43:53Z <chris@phact> # Sort out duplicate builds
Add some database indexes
2019-12-05 17:19:04 +01:00
add_some_database_indexes 2019-12-05T15:53:04Z Christopher Baines <mail@cbaines.net> # Add some indexes
Start storing and identifying sets of derivation outputs Derivations are effectively equivalent if they produce the same set of outputs, which is possible because of the equivalence of fixed output derivations. A fixed output derivation can be different, but equivalent, because it produces the same fixed output. To better allow tracking equivalent derivations, primarily to allow working out what derivations might correspond to a build, store the sets of derivation outputs, and which derivations they relate to.
2019-12-07 19:21:02 +01:00
derivation_output_sets 2019-12-05T23:19:05Z Christopher Baines <mail@cbaines.net> # Describe the sets of derivation outputs
Add new derivation_output_details_set_id column to the builds table As this will hopefully provide a faster way of associating derivations with builds.
2019-12-07 19:27:31 +01:00
builds_add_derivation_output_details_set_id 2019-12-07T18:25:38Z Christopher Baines <mail@cbaines.net> # Add a derivation_output_details_set_id column to builds
Create an index for the hash component of derivation filenames This is to aid rendering of narinfo files. They're requested with the path /HASH.narinfo, so to quickly find the relevant derivation, this index can be used.
2019-12-26 09:28:20 +00:00
derivations_hash_index 2019-12-24T22:54:19Z Christopher Baines <mail@cbaines.net> # Add index on derivations for the hash component of the filename
Add a new table to nars for derivation source files This will allow serving the nars for derivation source files.
2019-12-28 22:47:58 +00:00
add_derivation_source_file_nars 2019-12-28T20:37:06Z Christopher Baines <mail@cbaines.net> # Add table for derivation source file nars
Create an index on the hash component of the store path For derivation_source_files.
2019-12-29 17:55:04 +00:00
derivation_source_files_store_path_hash_index 2019-12-29T17:53:08Z Christopher Baines <mail@cbaines.net> # Add index on the hash part of the derivation source files store path
Add a table to configure which build servers build what
2020-01-05 12:16:21 +00:00
build_servers_build_config 2020-01-05T12:06:13Z Christopher Baines <mail@cbaines.net> # Add build_servers_build_config table
Don't hardcode the expected x-git-repo header value Rather than expecting it always to be "guix", store the expected value in the database, and use the value of the header to find the relevant repository.
2020-01-11 17:25:08 +00:00
git-repositories-x-git-repo-header 2020-01-11T16:39:32Z Christopher Baines <mail@cbaines.net> # Add x_git_repo_header to git_repositories
Store information about system tests
2020-02-02 20:30:07 +01:00
system_test_tables 2020-02-02T11:36:20Z Christopher Baines <mail@cbaines.net> # Add tables for storing system tests
Rework cross derivations support Stop using the system values as targets, and remove package derivation entries where this is the case. Switch the non-cross derivation case to have a target of "", rather than matching the system, as this makes more sense, and is more consistent now that the target values no longer match the system values. Hardcode some more correct target values, and use these instead. Hopefully this can be better integrated with Guix in the future. This commit also includes a migration attempting to shrink some indexes.
2020-02-08 11:16:08 +00:00
change_package_derivations_target 2020-02-07T19:29:50Z Christopher Baines <mail@cbaines.net> # Change package_derivations target
remove_old_cross_derivations 2020-02-07T19:42:54Z Christopher Baines <mail@cbaines.net> # Remove old cross derivations
increase_fillfactor_for_some_indexes 2020-02-07T20:49:17Z Christopher Baines <mail@cbaines.net> # Increase the fillfactor for some btree indexes
change_package_derivations_by_guix_revision_range_target 2020-02-08T10:13:07Z Christopher Baines <mail@cbaines.net> # Change the values for package_derivations_by_guix_revision_range target
Support excluding and including branches from a repository So that you can have the Guix Data Service only pay attention to some of the branches.
2020-02-08 12:03:41 +00:00
allow_including_and_excluding_branches_for_repositories 2020-02-08T11:30:02Z Christopher Baines <mail@cbaines.net> # Allow including and excluding branches for repositories
Start storing channel instance derivations These are the ones that relate to Guix pull.
2020-02-11 08:56:24 +00:00
channel_instance_derivations 2020-02-10T07:59:03Z Christopher Baines <mail@cbaines.net> # Add tables to store derivations for channel instances
Update the build_servers_build_config values Change target to '' if it matches system, as this matches the updated way of storing derivations.
2020-02-13 20:13:46 +00:00
update_build_servers_build_config 2020-02-13T20:07:19Z Christopher Baines <mail@cbaines.net> # Update build_servers_build_config values
Make a constraint deferrable To allow for deferring it in a transaction when deleting derivations.
2020-02-16 22:28:37 +00:00
make_some_constraints_deferrable 2020-02-16T10:54:22Z Christopher Baines <mail@cbaines.net> # Make some constraints deferrable
Generate and store system test derivations for all supported systems Rather than just the native system. I'm not quite sure of the value here, as I guess system tests should behave the same regardless of the way the software is compiled, but this seems like it could be useful, and being explicit about the system the derivation is for is good.
2020-03-20 01:41:32 +00:00
guix_revision_system_test_derivations_add_system 2020-03-19T21:30:33Z Christopher Baines <mail@cbaines.net> # Add a system column to the guix_revision_system_test_derivations table
Stop using package_versions_by_guix_revision_range It's been replaced by the package_derivations_by_guix_revision_range table.
2020-03-24 20:44:57 +00:00
drop_package_versions_by_guix_revision_range 2020-03-24T20:40:38Z Christopher Baines <mail@cbaines.net> # Drop package_versions_by_guix_revision_range
Create an index on narinfo_fetch_records This greatly improves the performance of the derivation-outputs page.
2020-03-25 19:11:03 +00:00
create_narinfo_fetch_records_index 2020-03-25T19:07:28Z Christopher Baines <mail@cbaines.net> # Create an index on narinfo_fetch_records
Make jobs unique by commit Given that a commit, regardless of what repository it comes from should contain the same exactly the same data, just track jobs by commit.
2020-03-28 19:59:35 +00:00
load_new_guix_revision_jobs_make_commits_unique 2020-03-27T21:38:42Z Christopher Baines <mail@cbaines.net> # Make load_new_guix_revision_jobs commits unique
Remove odd package derivations with mismatched systems This follows on from 79993bfaeff3097d37838bc0636fc20c6fc4283d, disassociating the relevant derivations from the revisions.
2020-04-24 21:47:06 +01:00
remove_odd_package_derivations 2020-04-24T20:36:06Z Christopher Baines <mail@cbaines.net> # Remove odd package derivations
Add a lookup_builds field to the build_servers table This is to allow for build servers where only the substitutes should be queried, and it shouldn't be assumed that they're running Cuirass.
2020-05-24 17:02:53 +01:00
build_servers_lookup_builds 2020-05-24T15:18:09Z Christopher Baines <mail@cbaines.net> # Add build_servers.lookup_builds
Make nar_urls file sizes optional
2020-06-03 06:56:59 +01:00
make_nar_urls_file_size_optional 2020-06-03T05:27:29Z Christopher Baines <mail@cbaines.net> # Make the nar_urls.file_size optional
Get the translated lint checker descriptions into the database Signed-off-by: Christopher Baines <mail@cbaines.net>
2020-05-28 22:17:11 +02:00
translations_for_lint_checker_descriptions 2020-05-22T19:49:37Z daniela <daniela@linux-ijv5> # Support translations for lint checker descriptions
Get the translated package synopsis and descriptions into the database Signed-off-by: Christopher Baines <mail@cbaines.net>
2020-06-11 19:04:01 +02:00
translations_for_package_synopsis_and_descriptions 2020-06-09T12:42:54Z daniela <daniela@linux-ijv5> # Support translations for package synopsis and descriptions
Make build_status.timestamp nullable The Guix Build Coordinator doesn't record timestamps for events currently. This is something to fix, but allow for build statuses without timestamps as they're not necessary.
2020-06-27 13:35:34 +01:00
build_status_nullable_timestamp 2020-06-27T12:34:29Z Christopher Baines <mail@cbaines.net> # Make build_status.timestamp nullable
Support storing the id's representing builds in other services Like Cuirass, or the Guix Data Service. This isn't so necessary with Cuirass, as the derivation/output are unique identifiers, but for the Guix Build Coordinator, one derivation/output can be built many times, with potentially differing results.
2020-06-27 13:58:21 +01:00
build_add_build_server_build_id 2020-06-27T12:48:57Z Christopher Baines <mail@cbaines.net> # Add builds.build_server_build_id
Avoid duplicate build status entries This effectively makes adding build status entries idempotent to some degree.
2020-06-30 18:30:01 +01:00
build_status_add_unique_index 2020-06-30T17:19:30Z Christopher Baines <mail@cbaines.net> # Add a unique index to the build_status table
Remove the constraint on having one derivation build per server As the Guix Build Coordinator can build one derivation many times.
2020-07-01 09:35:40 +01:00
builds_remove_build_server_id_derivation_unique_constraint 2020-06-30T20:17:48Z Christopher Baines <mail@cbaines.net> # Allow multiple builds of the same derivation per build server
Add a index to derivation_output_details_sets This helps with queries like: SELECT * FROM derivation_output_details_sets WHERE ARRAY[5563227] <@ derivation_output_details_ids;
2020-07-04 08:59:29 +01:00
derivation_output_details_sets_derivation_output_details_ids_index 2020-07-04T07:56:49Z Christopher Baines <mail@cbaines.net> # Add GIN index on derivation_output_details_ids
Add the package_metadata_tsvectors table Signed-off-by: Christopher Baines <mail@cbaines.net>
2020-07-31 16:35:28 +02:00
add-tsvectors-per-locale 2020-07-16T18:19:44Z daniela <daniela@linux-ijv5> # Add tsvectors per locale
Change a package_descriptions index to use MD5 of the description To avoid the index data being too large. This was first seen in guix commit 1bb4fd64b7bbe5a17eda6f0ca8105283c038f7c8: psql-query-error (fatal-error PGRES_FATAL_ERROR ERROR: index row size 2808 exceeds maximum 2712 for index "package_descriptions_locale_description_key" HINT: Values larger than 1/3 of a buffer page cannot be indexed.
2020-08-23 14:40:17 +01:00
change_package_descriptions_index 2020-08-23T11:56:00Z Christopher Baines <mail@cbaines.net> # Change the package_descriptions index
Change the locale codeset representation From the normalized one, to the one actually contained within glibc. Recent versions of glibc also contain symlinks linking the normalized codeset to the locales with the .UTF-8 ending, but older ones do not. Maybe handling codeset normalisation for queries would be good, but the locale values ending in .UTF-8 are more compatible and allow the code to be simplified. For querying, maybe there should be a locales table which handles different representations.
2020-09-26 11:45:57 +01:00
change_locale_values 2020-09-26T10:20:22Z Christopher Baines <mail@cbaines.net> # Change locale values
Change the autovacuum config for some tables Looking at data for the the patches deployment of the Guix Data Service, these tables look like they might benefit from vacuuming/analyzing more often, so adjust the configuration so this will hopefully happen.
2020-10-01 22:30:39 +01:00
change_autovacuum_config 2020-10-01T21:24:46Z Christopher Baines <mail@cbaines.net> # Change autovacuum config
Change a constraint to add ON DELETE CASCADE I've not used these in many places, to try and avoid hiding deleting data, but in this case, this will allow more easily deleting the derivation source file nars, by just deleting the derivation_source_files table entry.
2020-10-02 18:14:40 +01:00
change_derivation_source_file_nars_constraint 2020-10-02T17:12:58Z Christopher Baines <mail@cbaines.net> # Change derivation source file nars constraint
Add an index for derivation_sources.derivation_source_file_id As this speeds up deleting derivation_source_files.
2020-10-02 20:13:28 +01:00
add_derivation_sources_derivation_source_file_id_index 2020-10-02T19:11:59Z Christopher Baines <mail@cbaines.net> # Add derivation_sources.derivation_source_file_id index
Support storing whether to authenticate commits by git repository To allow using the Guix Data Service when commits aren't authenticated.
2020-10-07 18:50:13 +01:00
git_repositories_add_fetch_with_authentication_field 2020-10-07T17:31:20Z Christopher Baines <mail@cbaines.net> # Add git_repositories.fetch_with_authentication
Add a latest_build_status table This will avoid many queries trying to figure out what the latest build status is, which will hopefuly simplify queries as well as improving performance.
2020-10-13 19:31:43 +01:00
create_latest_build_status 2020-10-13T17:22:39Z Christopher Baines <mail@cbaines.net> # Create the latest_build_status table
Regenerate the latest_build_status table As it doesn't handle wierdness for statuses, like builds being canceled with a timestamp of 0.
2020-10-21 19:46:27 +01:00
regenerate_latest_build_status 2020-10-21T18:39:03Z Christopher Baines <mail@cbaines.net> # Regenerate the latest_build_status table
Add index to speed up deleting revisions This is necessary to quickly delete entries from package_derivaitons.
2020-10-27 16:59:09 +00:00
guix_revision_package_derivations_add_package_derivation_index 2020-10-27T16:58:08Z Christopher Baines <mail@cbaines.net> # Add index for guix_revision_package_derivations.package_derivation_id
Increase statistics targets for the derivation_inputs table It has a lot of rows, and this seems to help the query planning for select-required-builds-that-failed, as I think it helps PostgreSQL better estimate the row counts that different parts of the query will produce.
2020-12-27 10:37:06 +00:00
increase_derivation_inputs_statistics_targets 2020-12-27T10:34:58Z Christopher Baines <mail@cbaines.net> # Increase stats targets on derivation_inputs fields
Store channel instance derivations in a separate transaction This means that these derivations are stored, even if a later part of the process fails. Having the channel instance derivations stored might help work out why the failure occurred, or better display information about it.
2021-02-02 23:36:56 +00:00
remove_guix_revisions_store_path 2021-02-02T20:06:18Z Christopher Baines <mail@cbaines.net> # Drop guix_revisions.store_path
Create a table for systems And use it for the systems in the derivations and package derivations tables. The primary motivation here is to allow quickly working out what systems the database contains, and having a small table with just the right data seems a good way to do that.
2021-04-23 11:14:51 +01:00
systems_table 2021-04-22T08:12:10Z Christopher Baines <mail@cbaines.net> # Add a systems table
Add some indexes This is from reading the slow query log on data.guix.gnu.org.
2021-05-17 18:40:45 +01:00
some_indexes 2021-05-17T17:36:38Z Christopher Baines <mail@cbaines.net> # Add some indexes
Add index for package_metadata This index speeds up inserting new entries to the package_metadata table. Signed-off-by: Christopher Baines <mail@cbaines.net>
2021-05-27 22:59:13 +03:00
package_metadata_location_id_index 2021-05-27T19:51:13Z Canan Talayhan <canan.t.talayhan@gmail.com> # Add index for location id
Track package replacements Start at least looking for package replacements, and storing the details (particularly the derivation). I'm looking at doing this so that build servers using the Guix Data Service can build these derivations.
2021-07-11 10:44:59 +01:00
packages_replacement 2021-04-24T04:52:57Z Christopher Baines <mail@cbaines.net> # Add packages.replacement_package_id
Add an index on derivation_output_details.id Where hash is not null, since this helps with the fixed output package derivations query.
2021-07-11 15:25:12 +01:00
package_derivations_not_null_hash_index 2021-07-11T14:19:32Z Christopher Baines <mail@cbaines.net> # Add an index to package_derivations
Partition the package_derivations_by_guix_revision_range table And create a proper git_branches table in the process. I'm hoping this will help with slow deletions from the package_derivations_by_guix_revision_range table in the case where there are lots of branches, since it'll separate the data for one branch from another. These migrations will remove the existing data, so rebuild-package-derivations-table will currently need manually running to regenerate it.
2022-05-23 19:10:25 +01:00
git_branch_id 2022-05-23T18:11:14Z Chris <chris@felis> # Add git_branch.id
partition_package_derivations_by_guix_revision_range 2022-05-23T18:20:37Z Chris <chris@felis> # Partition package_derivations_by_guix_revision_range
Add an index on package_derivations_by_guix_revision_range This helps render the package version range related pages.
2022-06-17 12:51:10 +01:00
package_range_index 2022-06-17T10:39:31Z Chris <chris@felis> # Add index on package_derivations_by_guix_revision_range.package_name
Add an index on git_commits And also remove the duplicates that have crept in.
2022-06-17 12:54:43 +01:00
fix_git_commits_duplicates 2022-06-17T10:39:50Z Chris <chris@felis> # Fix git_commits duplicates
Support per repository configuration for fetching substitutes This is so that data.qa.guix.gnu.org can be configured only to query the branches from the main repository.
2022-09-09 12:39:38 +01:00
git_repositories_query_substitutes 2022-09-09T11:35:16Z Chris <chris@felis> # Add git_repositories.query_substitutes
Speed up finding the locales for a revision
2022-09-14 10:26:03 +01:00
package_derivations_id_package_id_idx 2022-09-14T09:24:30Z Chris <chris@felis> # Add index on package_derivations id and package_id
Add support for incrementally tracking blocked builds This will hopefully provide a less expensive way of finding out if a scheduled build is probably blocked by other builds failing or being canceled. By working this out when the build events are recieved, it should be more feasible to include information about whether builds are likely blocked or not in various places (e.g. revision comparisons).
2022-11-10 16:06:45 +00:00
blocked_builds 2022-11-07T11:27:28Z Chris <chris@felis> # Add blocked_builds
Add extended statistics on package_derivations This helps row count estimates when filtering on system_id and target.
2022-11-12 10:40:43 +00:00
package_derivations_extended_statistics 2022-11-12T10:40:18Z Chris <chris@felis> # Add extended statistics on package_derivations
Add index on derivation_outputs id and derivation_id fields This might help with doing recursive queries on the derivations graph.
2022-11-12 10:42:04 +00:00
derivation_outputs_id_and_derivation_id_idx 2022-11-12T10:41:42Z Chris <chris@felis> # Add index on derivation_outputs id and derivation_id
Add an extra index on blocked_builds I think this might help with queries that don't use the build_server_id.
2023-03-05 10:21:14 +00:00
blocked_builds_blocked_builds_blocked_derivation_output_details_set_id_2 2023-03-05T10:19:53Z Chris <chris@felis> # Add index on blocked_builds_blocked_derivation_output_details_set_id
Store the distribution of derivations related to packages This might be generally useful, but I've been looking at it as it offers a way to try and improve query performance when you want to select all the derivations related to the packages for a revision. The data looks like this (for a specified system and target): ┌───────┬───────┐ │ level │ count │ ├───────┼───────┤ │ 15 │ 2 │ │ 14 │ 3 │ │ 13 │ 3 │ │ 12 │ 3 │ │ 11 │ 14 │ │ 10 │ 25 │ │ 9 │ 44 │ │ 8 │ 91 │ │ 7 │ 1084 │ │ 6 │ 311 │ │ 5 │ 432 │ │ 4 │ 515 │ │ 3 │ 548 │ │ 2 │ 2201 │ │ 1 │ 21162 │ │ 0 │ 22310 │ └───────┴───────┘ Level 0 reflects the number of packages. Level 1 is similar as you have all the derivations for the package origins. The remaining levels contain less packages since it's mostly just derivations involved in bootstrapping. When using a recursive CTE to collect all the derivations, PostgreSQL assumes that the each derivation has the same number of inputs, and this leads to a large overestimation of the number of derivations per a revision. This in turn can lead to PostgreSQL picking a slower way of running the query. When it's known how many new derivations you should see at each level, it's possible to inform PostgreSQL this by using LIMIT's at various points in the query. This reassures the query planner that it's not going to be handling lots of rows and helps it make better decisions about how to execute the query.
2023-03-09 08:29:39 +00:00
guix_revision_package_derivation_distribution_counts 2023-03-08T16:53:44Z Chris <chris@felis> # Add guix_revision_package_derivation_distribution_counts table
Make database changes to help with deleting entries in the nars table
2023-08-01 14:12:48 +01:00
cascade_nar_foreign_keys 2023-08-01T09:42:33Z Chris <chris@felis> # Make it easier to delete nars entries
nar_indexes 2023-08-01T11:37:35Z Chris <chris@felis> # Add nar related indexes
Support polling git repositories for new branches/revisions This is mostly a workaround for the occasional problems with the guix-commits mailing list, as it can break and then the data service doesn't learn about new revisions until the problem is fixed. I think it's still a generally good feature though, and allows deploying the data service without it consuming emails to learn about new revisions, and is a step towards integrating some kind of way of notifying the data service to poll.
2023-10-09 21:29:58 +01:00
git_repositories_poll_interval 2023-10-08T20:36:09Z Chris <chris@felis> # Add git_repositories.poll_interval
Allow prioritising jobs from specific repositories To help the QA data service prioritise branches over patches.
2024-03-07 09:44:47 +00:00
git_repositories_job_priority 2024-03-07T09:39:27Z Chris <chris@felis> # Add git_repositories.job_priority
Add an index on builds.build_server_build_id As this is used when rendering builds.
2024-09-07 18:45:26 +01:00
build_server_build_id_index 2024-09-07T17:42:28Z Chris <chris@felis> # Add index on builds.build_server_build_id
Make build_servers.id just generated by default As to make it easier to set explicitly.
2024-12-15 20:54:11 +00:00
alter_build_servers_id_default 2024-12-15T20:48:51Z Chris <chris@fang> # Alter build_servers id default
Reliably perform the background processing for build events Insert the build ids to a table and remove the ids once processed. If the data service is restarted and the in memory queue is lost, process all the items from the database queue.
2025-05-14 15:30:07 +01:00
build_background_processing_queue 2025-05-14T14:18:14Z Chris <chris@fang> # Add build_background_processing_queue
Move away from cgit to more flexible linking to repositories
2025-05-24 22:55:35 +01:00
git_repository_link_fields 2025-05-23T14:42:24Z Chris <chris@fang> # Change git repository link related fields
Reference in a new issue Copy permalink