Sync create indexes and prepare FK for p_ci_builds upstream_pipeline_id
What does this MR do and why?
This is the second step to synchronously create indexes for p_ci_builds See https://docs.gitlab.com/ee/development/database/adding_database_indexes.html#add-a-migration-to-create-the-index-synchronously
And prepare the foreign key to prevent invalid records See https://docs.gitlab.com/ee/development/database/add_foreign_key_to_existing_column.html#prevent-invalid-records
The corresponding indexes are created asynchronously from MR !142763 (merged)
-
For
gitlab_partitions_dynamic.ci_builds_101
:- "index_0e156a994a" btree (upstream_pipeline_id_convert_to_bigint) WHERE upstream_pipeline_id_convert_to_bigint IS NOT NULL
- "index_726c5ae963" btree (commit_id_convert_to_bigint, type, ref)
- "index_564638cbc9" btree (commit_id_convert_to_bigint, artifacts_expire_at, id) WHERE type::text = 'Ci::Build'::text AND (retried = false OR retried IS NULL) AND (name::text = ANY (ARRAY['sast'::character varying::text, 'secret_detection'::character varying::text, 'dependency_scanning'::character varying::text, 'container_scanning'::character varying::text, 'dast'::character varying::text]))
-
For
ci_builds
:-
"index_89477d6012" btree (upstream_pipeline_id_convert_to_bigint) WHERE upstream_pipeline_id_convert_to_bigint IS NOT NULL
-
"index_fc42f73fa6" btree (commit_id_convert_to_bigint, type, ref)
-
"index_357cc39ca4" btree (commit_id_convert_to_bigint, artifacts_expire_at, id) WHERE type::text = 'Ci::Build'::text AND (retried = false OR retried IS NULL) AND (name::text = ANY (ARRAY['sast'::character varying::text, 'secret_detection'::character varying::text, 'dependency_scanning'::character varying::text, 'container_scanning'::character varying::text, 'dast'::character varying::text]))
-
MR acceptance checklist
Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.
Screenshots or screen recordings
Screenshots are required for UI changes, and strongly recommended for all other merge requests.
Before | After |
---|---|
How to set up and validate locally
-
Run the following commands:
./scripts/regenerate-schema RAILS_ENV=test bin/rails runner 'Feature.enable :database_async_foreign_key_validation' RAILS_ENV=test bin/rails runner 'Feature.enable :ci_current_partition_value_101' RAILS_ENV=test bin/rake gitlab:db:validate_async_constraints:all
-
Confirm the following foreign keys are created and validated in TEST environment
psql gitlabhq_test -h $PATH_TO_POSTGRESQL/gitlab-development-kit/postgresql
\d ci_builds "fk_6b6c3f3e70" FOREIGN KEY (upstream_pipeline_id_convert_to_bigint) REFERENCES ci_pipelines(id) ON DELETE CASCADE \d gitlab_partitions_dynamic.ci_builds_101 "fk_6b6c3f3e70" FOREIGN KEY (upstream_pipeline_id_convert_to_bigint) REFERENCES ci_pipelines(id) ON DELETE CASCADE
-
Run the following commands to restore the feature flag:
RAILS_ENV=test bin/rails runner 'Feature.disable :ci_current_partition_value_101' RAILS_ENV=test bin/rails runner 'Feature.disable :database_async_foreign_key_validation'
Related to #422835 (closed) #422836 (closed)