diff options
author | GitLab Bot <gitlab-bot@gitlab.com> | 2022-09-20 02:18:09 +0300 |
---|---|---|
committer | GitLab Bot <gitlab-bot@gitlab.com> | 2022-09-20 02:18:09 +0300 |
commit | 6ed4ec3e0b1340f96b7c043ef51d1b33bbe85fde (patch) | |
tree | dc4d20fe6064752c0bd323187252c77e0a89144b /db/post_migrate | |
parent | 9868dae7fc0655bd7ce4a6887d4e6d487690eeed (diff) |
Add latest changes from gitlab-org/gitlab@15-4-stable-eev15.4.0-rc42
Diffstat (limited to 'db/post_migrate')
59 files changed, 1484 insertions, 1 deletions
diff --git a/db/post_migrate/20210812013042_remove_duplicate_project_authorizations.rb b/db/post_migrate/20210812013042_remove_duplicate_project_authorizations.rb index 6fdc30d09c6..1c2e2b52e8b 100644 --- a/db/post_migrate/20210812013042_remove_duplicate_project_authorizations.rb +++ b/db/post_migrate/20210812013042_remove_duplicate_project_authorizations.rb @@ -55,7 +55,8 @@ class RemoveDuplicateProjectAuthorizations < ActiveRecord::Migration[6.1] end def batch(&block) - order = Gitlab::Pagination::Keyset::Order.build([ + order = Gitlab::Pagination::Keyset::Order.build( + [ Gitlab::Pagination::Keyset::ColumnOrderDefinition.new( attribute_name: 'user_id', order_expression: ProjectAuthorization.arel_table[:user_id].asc, diff --git a/db/post_migrate/20220601110011_schedule_remove_self_managed_wiki_notes.rb b/db/post_migrate/20220601110011_schedule_remove_self_managed_wiki_notes.rb new file mode 100644 index 00000000000..9e6594bb9b6 --- /dev/null +++ b/db/post_migrate/20220601110011_schedule_remove_self_managed_wiki_notes.rb @@ -0,0 +1,35 @@ +# frozen_string_literal: true + +class ScheduleRemoveSelfManagedWikiNotes < Gitlab::Database::Migration[2.0] + restrict_gitlab_migration gitlab_schema: :gitlab_main + + MIGRATION = 'RemoveSelfManagedWikiNotes' + INTERVAL = 2.minutes + + disable_ddl_transaction! + + def up + return if skip_migration? + + queue_batched_background_migration( + MIGRATION, + :notes, + :id, + job_interval: INTERVAL, + batch_size: 10_000, + sub_batch_size: 1_000 + ) + end + + def down + return if skip_migration? + + delete_batched_background_migration(MIGRATION, :notes, :id, []) + end + + private + + def skip_migration? + Gitlab.staging? || Gitlab.com? + end +end diff --git a/db/post_migrate/20220606054503_add_tmp_index_job_artifacts_id_and_expire_at.rb b/db/post_migrate/20220606054503_add_tmp_index_job_artifacts_id_and_expire_at.rb new file mode 100644 index 00000000000..28346eb1a97 --- /dev/null +++ b/db/post_migrate/20220606054503_add_tmp_index_job_artifacts_id_and_expire_at.rb @@ -0,0 +1,29 @@ +# frozen_string_literal: true + +class AddTmpIndexJobArtifactsIdAndExpireAt < Gitlab::Database::Migration[2.0] + disable_ddl_transaction! + + INDEX_NAME = 'tmp_index_ci_job_artifacts_on_id_expire_at_file_type_trace' + + EXPIRE_AT_ON_22_MIDNIGHT_IN_TIMEZONE_OR_TRACE = <<~SQL + (EXTRACT(day FROM timezone('UTC', expire_at)) IN (21, 22, 23) + AND EXTRACT(minute FROM timezone('UTC', expire_at)) IN (0, 30, 45) + AND EXTRACT(second FROM timezone('UTC', expire_at)) = 0) + OR file_type = 3 + SQL + + def up + return if Gitlab.com? + return if index_exists_by_name?(:ci_job_artifacts, INDEX_NAME) + + add_concurrent_index :ci_job_artifacts, :id, + where: EXPIRE_AT_ON_22_MIDNIGHT_IN_TIMEZONE_OR_TRACE, name: INDEX_NAME + end + + def down + return if Gitlab.com? + return unless index_exists_by_name?(:ci_job_artifacts, INDEX_NAME) + + remove_concurrent_index_by_name :ci_job_artifacts, INDEX_NAME + end +end diff --git a/db/post_migrate/20220606080509_fix_incorrect_job_artifacts_expire_at.rb b/db/post_migrate/20220606080509_fix_incorrect_job_artifacts_expire_at.rb new file mode 100644 index 00000000000..8fea22f5579 --- /dev/null +++ b/db/post_migrate/20220606080509_fix_incorrect_job_artifacts_expire_at.rb @@ -0,0 +1,31 @@ +# frozen_string_literal: true + +class FixIncorrectJobArtifactsExpireAt < Gitlab::Database::Migration[2.0] + disable_ddl_transaction! + + restrict_gitlab_migration gitlab_schema: :gitlab_ci + + MIGRATION = 'RemoveBackfilledJobArtifactsExpireAt' + BATCH_CLASS = 'RemoveBackfilledJobArtifactsExpireAtBatchingStrategy' + BATCH_SIZE = 500 + INTERVAL = 2.minutes.freeze + + def up + return if Gitlab.com? + + queue_batched_background_migration( + MIGRATION, + :ci_job_artifacts, + :id, + job_interval: INTERVAL, + batch_class_name: BATCH_CLASS, + batch_size: BATCH_SIZE + ) + end + + def down + return if Gitlab.com? + + delete_batched_background_migration(MIGRATION, :ci_job_artifacts, :id, []) + end +end diff --git a/db/post_migrate/20220615154500_schedule_backfill_cluster_agents_has_vulnerabilities.rb b/db/post_migrate/20220615154500_schedule_backfill_cluster_agents_has_vulnerabilities.rb new file mode 100644 index 00000000000..74aeb2947a9 --- /dev/null +++ b/db/post_migrate/20220615154500_schedule_backfill_cluster_agents_has_vulnerabilities.rb @@ -0,0 +1,30 @@ +# frozen_string_literal: true + +class ScheduleBackfillClusterAgentsHasVulnerabilities < Gitlab::Database::Migration[2.0] + disable_ddl_transaction! + + MIGRATION = 'BackfillClusterAgentsHasVulnerabilities' + DELAY_INTERVAL = 2.minutes + + restrict_gitlab_migration gitlab_schema: :gitlab_main + + def up + ensure_batched_background_migration_is_finished( + job_class_name: 'BackfillVulnerabilityReadsClusterAgent', + table_name: :vulnerability_reads, + column_name: :id, + job_arguments: [] + ) + + queue_batched_background_migration( + MIGRATION, + :cluster_agents, + :id, + job_interval: DELAY_INTERVAL + ) + end + + def down + delete_batched_background_migration(MIGRATION, :cluster_agents, :id, []) + end +end diff --git a/db/post_migrate/20220616171355_update_vulnerabilities_project_id_id_active_cis_index.rb b/db/post_migrate/20220616171355_update_vulnerabilities_project_id_id_active_cis_index.rb new file mode 100644 index 00000000000..047ae0d1132 --- /dev/null +++ b/db/post_migrate/20220616171355_update_vulnerabilities_project_id_id_active_cis_index.rb @@ -0,0 +1,24 @@ +# frozen_string_literal: true + +class UpdateVulnerabilitiesProjectIdIdActiveCisIndex < Gitlab::Database::Migration[2.0] + disable_ddl_transaction! + + NEW_INDEX_NAME = 'idx_vulnerabilities_on_project_id_and_id_active_cis_dft_branch' + OLD_INDEX_NAME = 'index_vulnerabilities_on_project_id_and_id_active_cis' + OLD_INDEX_FILTER_CONDITION = 'report_type = 7 AND state = ANY(ARRAY[1, 4])' + NEW_INDEX_FILTER_CONDITION = 'report_type = 7 AND state = ANY(ARRAY[1, 4]) AND present_on_default_branch IS TRUE' + + def up + add_concurrent_index :vulnerabilities, [:project_id, :id], + where: NEW_INDEX_FILTER_CONDITION, + name: NEW_INDEX_NAME + + remove_concurrent_index_by_name(:vulnerabilities, OLD_INDEX_NAME) + end + + def down + add_concurrent_index :vulnerabilities, [:project_id, :id], where: OLD_INDEX_FILTER_CONDITION, name: OLD_INDEX_NAME + + remove_concurrent_index_by_name(:vulnerabilities, NEW_INDEX_NAME) + end +end diff --git a/db/post_migrate/20220706145113_backfill_namespace_id_on_issues.rb b/db/post_migrate/20220706145113_backfill_namespace_id_on_issues.rb new file mode 100644 index 00000000000..8114967ac8e --- /dev/null +++ b/db/post_migrate/20220706145113_backfill_namespace_id_on_issues.rb @@ -0,0 +1,28 @@ +# frozen_string_literal: true + +class BackfillNamespaceIdOnIssues < Gitlab::Database::Migration[2.0] + restrict_gitlab_migration gitlab_schema: :gitlab_main + disable_ddl_transaction! + + MIGRATION = 'BackfillProjectNamespaceOnIssues' + DELAY_INTERVAL = 2.minutes + BATCH_SIZE = 500 + MAX_BATCH_SIZE = 10_000 + SUB_BATCH_SIZE = 10 + + def up + queue_batched_background_migration( + MIGRATION, + :issues, + :id, + job_interval: DELAY_INTERVAL, + batch_size: BATCH_SIZE, + max_batch_size: MAX_BATCH_SIZE, + sub_batch_size: SUB_BATCH_SIZE + ) + end + + def down + delete_batched_background_migration(MIGRATION, :issues, :id, []) + end +end diff --git a/db/post_migrate/20220707192420_remove_tmp_idx_merge_requests_draft_and_status.rb b/db/post_migrate/20220707192420_remove_tmp_idx_merge_requests_draft_and_status.rb new file mode 100644 index 00000000000..a8cfea02463 --- /dev/null +++ b/db/post_migrate/20220707192420_remove_tmp_idx_merge_requests_draft_and_status.rb @@ -0,0 +1,18 @@ +# frozen_string_literal: true + +class RemoveTmpIdxMergeRequestsDraftAndStatus < Gitlab::Database::Migration[2.0] + disable_ddl_transaction! + + INDEX_NAME = "tmp_index_merge_requests_draft_and_status" + CORRECTED_REGEXP_STR = "^(\\[draft\\]|\\(draft\\)|draft:|draft|\\[WIP\\]|WIP:|WIP)" + + def up + remove_concurrent_index_by_name :merge_requests, INDEX_NAME + end + + def down + add_concurrent_index :merge_requests, :id, + where: "draft = false AND state_id = 1 AND ((title)::text ~* '#{CORRECTED_REGEXP_STR}'::text)", + name: INDEX_NAME + end +end diff --git a/db/post_migrate/20220726225114_remove_tmp_index_group_membership_namespace_id_column.rb b/db/post_migrate/20220726225114_remove_tmp_index_group_membership_namespace_id_column.rb new file mode 100644 index 00000000000..db47be6a1e3 --- /dev/null +++ b/db/post_migrate/20220726225114_remove_tmp_index_group_membership_namespace_id_column.rb @@ -0,0 +1,17 @@ +# frozen_string_literal: true + +class RemoveTmpIndexGroupMembershipNamespaceIdColumn < Gitlab::Database::Migration[2.0] + INDEX_NAME = 'tmp_index_for_namespace_id_migration_on_group_members' + + disable_ddl_transaction! + + def up + remove_concurrent_index_by_name :members, INDEX_NAME + end + + def down + add_concurrent_index :members, :id, + where: "members.member_namespace_id IS NULL and members.type = 'GroupMember'", + name: INDEX_NAME + end +end diff --git a/db/post_migrate/20220801155858_schedule_disable_legacy_open_source_licence_for_recent_public_projects.rb b/db/post_migrate/20220801155858_schedule_disable_legacy_open_source_licence_for_recent_public_projects.rb new file mode 100644 index 00000000000..a662cfe4be3 --- /dev/null +++ b/db/post_migrate/20220801155858_schedule_disable_legacy_open_source_licence_for_recent_public_projects.rb @@ -0,0 +1,33 @@ +# frozen_string_literal: true + +class ScheduleDisableLegacyOpenSourceLicenceForRecentPublicProjects < Gitlab::Database::Migration[2.0] + MIGRATION = 'DisableLegacyOpenSourceLicenceForRecentPublicProjects' + INTERVAL = 2.minutes + BATCH_SIZE = 1_000 + MAX_BATCH_SIZE = 5_000 + SUB_BATCH_SIZE = 200 + + disable_ddl_transaction! + + restrict_gitlab_migration gitlab_schema: :gitlab_main + + def up + return unless Gitlab.com? + + queue_batched_background_migration( + MIGRATION, + :projects, + :id, + job_interval: INTERVAL, + batch_size: BATCH_SIZE, + max_batch_size: MAX_BATCH_SIZE, + sub_batch_size: SUB_BATCH_SIZE + ) + end + + def down + return unless Gitlab.com? + + delete_batched_background_migration(MIGRATION, :projects, :id, []) + end +end diff --git a/db/post_migrate/20220809002011_schedule_destroy_invalid_group_members.rb b/db/post_migrate/20220809002011_schedule_destroy_invalid_group_members.rb new file mode 100644 index 00000000000..3db21c290b9 --- /dev/null +++ b/db/post_migrate/20220809002011_schedule_destroy_invalid_group_members.rb @@ -0,0 +1,30 @@ +# frozen_string_literal: true + +class ScheduleDestroyInvalidGroupMembers < Gitlab::Database::Migration[2.0] + MIGRATION = 'DestroyInvalidGroupMembers' + DELAY_INTERVAL = 2.minutes + BATCH_SIZE = 1_000 + MAX_BATCH_SIZE = 2_000 + SUB_BATCH_SIZE = 50 + + restrict_gitlab_migration gitlab_schema: :gitlab_main + + disable_ddl_transaction! + + def up + queue_batched_background_migration( + MIGRATION, + :members, + :id, + job_interval: DELAY_INTERVAL, + batch_size: BATCH_SIZE, + max_batch_size: MAX_BATCH_SIZE, + sub_batch_size: SUB_BATCH_SIZE, + gitlab_schema: :gitlab_main + ) + end + + def down + delete_batched_background_migration(MIGRATION, :members, :id, []) + end +end diff --git a/db/post_migrate/20220809214730_add_note_metadata_temp_index_on_id_where_task.rb b/db/post_migrate/20220809214730_add_note_metadata_temp_index_on_id_where_task.rb new file mode 100644 index 00000000000..1c5a1b68041 --- /dev/null +++ b/db/post_migrate/20220809214730_add_note_metadata_temp_index_on_id_where_task.rb @@ -0,0 +1,15 @@ +# frozen_string_literal: true + +class AddNoteMetadataTempIndexOnIdWhereTask < Gitlab::Database::Migration[2.0] + INDEX_NAME = 'tmp_index_system_note_metadata_on_id_where_task' + + disable_ddl_transaction! + + def up + add_concurrent_index :system_note_metadata, [:id, :action], where: "action = 'task'", name: INDEX_NAME + end + + def down + remove_concurrent_index_by_name :system_note_metadata, INDEX_NAME + end +end diff --git a/db/post_migrate/20220809223215_change_task_system_note_wording_to_checklist_item.rb b/db/post_migrate/20220809223215_change_task_system_note_wording_to_checklist_item.rb new file mode 100644 index 00000000000..df7c2d325d2 --- /dev/null +++ b/db/post_migrate/20220809223215_change_task_system_note_wording_to_checklist_item.rb @@ -0,0 +1,28 @@ +# frozen_string_literal: true + +class ChangeTaskSystemNoteWordingToChecklistItem < Gitlab::Database::Migration[2.0] + restrict_gitlab_migration gitlab_schema: :gitlab_main + disable_ddl_transaction! + + MIGRATION = 'RenameTaskSystemNoteToChecklistItem' + DELAY_INTERVAL = 2.minutes + BATCH_SIZE = 10_000 + MAX_BATCH_SIZE = 20_000 + SUB_BATCH_SIZE = 100 + + def up + queue_batched_background_migration( + MIGRATION, + :system_note_metadata, + :id, + job_interval: DELAY_INTERVAL, + batch_size: BATCH_SIZE, + max_batch_size: MAX_BATCH_SIZE, + sub_batch_size: SUB_BATCH_SIZE + ) + end + + def down + delete_batched_background_migration(MIGRATION, :system_note_metadata, :id, []) + end +end diff --git a/db/post_migrate/20220815061621_rename_web_hooks_service_id_to_integration_id.rb b/db/post_migrate/20220815061621_rename_web_hooks_service_id_to_integration_id.rb new file mode 100644 index 00000000000..6bcee7f51df --- /dev/null +++ b/db/post_migrate/20220815061621_rename_web_hooks_service_id_to_integration_id.rb @@ -0,0 +1,13 @@ +# frozen_string_literal: true + +class RenameWebHooksServiceIdToIntegrationId < Gitlab::Database::Migration[2.0] + disable_ddl_transaction! + + def up + rename_column_concurrently :web_hooks, :service_id, :integration_id + end + + def down + undo_rename_column_concurrently :web_hooks, :service_id, :integration_id + end +end diff --git a/db/post_migrate/20220816075638_drop_uuid_and_id_index_from_security_findings.rb b/db/post_migrate/20220816075638_drop_uuid_and_id_index_from_security_findings.rb new file mode 100644 index 00000000000..7a41e0b7835 --- /dev/null +++ b/db/post_migrate/20220816075638_drop_uuid_and_id_index_from_security_findings.rb @@ -0,0 +1,15 @@ +# frozen_string_literal: true + +class DropUuidAndIdIndexFromSecurityFindings < Gitlab::Database::Migration[2.0] + INDEX_NAME = :index_on_security_findings_uuid_and_id_order_desc + + disable_ddl_transaction! + + def up + remove_concurrent_index_by_name :security_findings, name: INDEX_NAME + end + + def down + add_concurrent_index :security_findings, [:uuid, :id], order: { id: :desc }, name: INDEX_NAME + end +end diff --git a/db/post_migrate/20220816163444_update_start_date_for_iterations_cadences.rb b/db/post_migrate/20220816163444_update_start_date_for_iterations_cadences.rb new file mode 100644 index 00000000000..631e8941add --- /dev/null +++ b/db/post_migrate/20220816163444_update_start_date_for_iterations_cadences.rb @@ -0,0 +1,53 @@ +# frozen_string_literal: true + +class UpdateStartDateForIterationsCadences < Gitlab::Database::Migration[2.0] + include ::Gitlab::Database::DynamicModelHelpers + + restrict_gitlab_migration gitlab_schema: :gitlab_main + disable_ddl_transaction! + + def up + each_batch_range('iterations_cadences', connection: connection) do |min, max| + execute(<<~SQL) + UPDATE iterations_cadences + SET start_date=ic.first_upcoming_iteration_start_date + FROM ( + SELECT ic.id, sprints2.first_upcoming_iteration_start_date + FROM iterations_cadences as ic, + LATERAL ( + -- For each cadence, query for the due date of its current iteration + SELECT due_date as current_iteration_due_date FROM sprints + WHERE iterations_cadence_id=ic.id AND start_date <= current_date AND due_date >= current_date + LIMIT 1 + ) as sprints1, + LATERAL ( + -- For each cadence, query for the start date of the first upcoming iteration (i.e, it starts after the current iteration) + SELECT start_date as first_upcoming_iteration_start_date FROM sprints + WHERE iterations_cadence_id=ic.id AND start_date > sprints1.current_iteration_due_date + ORDER BY start_date ASC LIMIT 1 + ) as sprints2 + WHERE ic.automatic=true AND ic.id BETWEEN #{min} AND #{max} + ) as ic + WHERE iterations_cadences.id=ic.id; + SQL + end + end + + def down + each_batch_range('iterations_cadences', connection: connection) do |min, max| + execute(<<~SQL) + UPDATE iterations_cadences + SET start_date=ic.first_iteration_start_date + FROM ( + SELECT ic.id, sprints.start_date as first_iteration_start_date + FROM iterations_cadences as ic, + LATERAL ( + SELECT start_date FROM sprints WHERE iterations_cadence_id=ic.id ORDER BY start_date ASC LIMIT 1 + ) as sprints + WHERE ic.automatic=true AND ic.id BETWEEN #{min} AND #{max} + ) as ic + WHERE iterations_cadences.id=ic.id; + SQL + end + end +end diff --git a/db/post_migrate/20220820221036_update_tmp_non_migrated_index_on_container_repositories.rb b/db/post_migrate/20220820221036_update_tmp_non_migrated_index_on_container_repositories.rb new file mode 100644 index 00000000000..eea58ad7951 --- /dev/null +++ b/db/post_migrate/20220820221036_update_tmp_non_migrated_index_on_container_repositories.rb @@ -0,0 +1,25 @@ +# frozen_string_literal: true + +class UpdateTmpNonMigratedIndexOnContainerRepositories < Gitlab::Database::Migration[2.0] + disable_ddl_transaction! + + NEW_INDEX_NAME = 'tmp_index_container_repos_on_non_migrated' + OLD_INDEX_NAME = 'tmp_idx_container_repos_on_non_migrated' + MIGRATION_PHASE_1_ENDED_AT = '2022-01-23' + + def up + add_concurrent_index :container_repositories, + [:project_id, :id], + name: NEW_INDEX_NAME, + where: "migration_state != 'import_done'" + remove_concurrent_index_by_name :container_repositories, OLD_INDEX_NAME + end + + def down + add_concurrent_index :container_repositories, + [:project_id, :id], + name: OLD_INDEX_NAME, + where: "migration_state != 'import_done' AND created_at < '#{MIGRATION_PHASE_1_ENDED_AT}'" + remove_concurrent_index_by_name :container_repositories, NEW_INDEX_NAME + end +end diff --git a/db/post_migrate/20220822071909_remove_other_role_from_user_details.rb b/db/post_migrate/20220822071909_remove_other_role_from_user_details.rb new file mode 100644 index 00000000000..a0177bf2605 --- /dev/null +++ b/db/post_migrate/20220822071909_remove_other_role_from_user_details.rb @@ -0,0 +1,9 @@ +# frozen_string_literal: true + +class RemoveOtherRoleFromUserDetails < Gitlab::Database::Migration[2.0] + enable_lock_retries! + + def change + remove_column :user_details, :other_role, :text + end +end diff --git a/db/post_migrate/20220822090656_drop_build_coverage_regex_from_project.rb b/db/post_migrate/20220822090656_drop_build_coverage_regex_from_project.rb new file mode 100644 index 00000000000..70c6b660318 --- /dev/null +++ b/db/post_migrate/20220822090656_drop_build_coverage_regex_from_project.rb @@ -0,0 +1,13 @@ +# frozen_string_literal: true + +class DropBuildCoverageRegexFromProject < Gitlab::Database::Migration[2.0] + enable_lock_retries! + + def up + remove_column :projects, :build_coverage_regex + end + + def down + add_column :projects, :build_coverage_regex, :string # rubocop: disable Migration/AddColumnsToWideTables + end +end diff --git a/db/post_migrate/20220822094804_add_issues_authorization_index.rb b/db/post_migrate/20220822094804_add_issues_authorization_index.rb new file mode 100644 index 00000000000..e09b5f8d93b --- /dev/null +++ b/db/post_migrate/20220822094804_add_issues_authorization_index.rb @@ -0,0 +1,15 @@ +# frozen_string_literal: true + +class AddIssuesAuthorizationIndex < Gitlab::Database::Migration[2.0] + disable_ddl_transaction! + + INDEX_NAME = 'idx_open_issues_on_project_and_confidential_and_author_and_id' + + def up + prepare_async_index :issues, [:project_id, :confidential, :author_id, :id], name: INDEX_NAME, where: 'state_id = 1' + end + + def down + unprepare_async_index :issues, INDEX_NAME + end +end diff --git a/db/post_migrate/20220823084747_prepare_removal_partial_trigram_indexes_for_issues.rb b/db/post_migrate/20220823084747_prepare_removal_partial_trigram_indexes_for_issues.rb new file mode 100644 index 00000000000..a8ea6abdcba --- /dev/null +++ b/db/post_migrate/20220823084747_prepare_removal_partial_trigram_indexes_for_issues.rb @@ -0,0 +1,16 @@ +# frozen_string_literal: true + +class PrepareRemovalPartialTrigramIndexesForIssues < Gitlab::Database::Migration[2.0] + TITLE_INDEX_NAME = 'index_issues_on_title_trigram_non_latin' + DESCRIPTION_INDEX_NAME = 'index_issues_on_description_trigram_non_latin' + + def up + prepare_async_index_removal :issues, :title, name: TITLE_INDEX_NAME + prepare_async_index_removal :issues, :description, name: DESCRIPTION_INDEX_NAME + end + + def down + unprepare_async_index_by_name :issues, DESCRIPTION_INDEX_NAME + unprepare_async_index_by_name :issues, TITLE_INDEX_NAME + end +end diff --git a/db/post_migrate/20220824114218_add_tmp_index_approval_merge_request_rules.rb b/db/post_migrate/20220824114218_add_tmp_index_approval_merge_request_rules.rb new file mode 100644 index 00000000000..64171b3053e --- /dev/null +++ b/db/post_migrate/20220824114218_add_tmp_index_approval_merge_request_rules.rb @@ -0,0 +1,19 @@ +# frozen_string_literal: true + +class AddTmpIndexApprovalMergeRequestRules < Gitlab::Database::Migration[2.0] + disable_ddl_transaction! + + TMP_INDEX_NAME = 'tmp_index_approval_merge_request_rules_on_report_type_equal_one' + + def up + # to be removed as part of https://gitlab.com/gitlab-org/gitlab/-/issues/372224 + add_concurrent_index :approval_merge_request_rules, + [:id, :report_type], + name: TMP_INDEX_NAME, + where: "report_type = 1" + end + + def down + remove_concurrent_index_by_name :approval_merge_request_rules, TMP_INDEX_NAME + end +end diff --git a/db/post_migrate/20220825061250_drop_tmp_index_todos_attention_request_action_idx.rb b/db/post_migrate/20220825061250_drop_tmp_index_todos_attention_request_action_idx.rb new file mode 100644 index 00000000000..091de49e1c9 --- /dev/null +++ b/db/post_migrate/20220825061250_drop_tmp_index_todos_attention_request_action_idx.rb @@ -0,0 +1,18 @@ +# frozen_string_literal: true + +class DropTmpIndexTodosAttentionRequestActionIdx < Gitlab::Database::Migration[2.0] + INDEX_NAME = "tmp_index_todos_attention_request_action" + ATTENTION_REQUESTED = 10 + + disable_ddl_transaction! + + def up + remove_concurrent_index_by_name :todos, INDEX_NAME + end + + def down + add_concurrent_index :todos, [:id], + where: "action = #{ATTENTION_REQUESTED}", + name: INDEX_NAME + end +end diff --git a/db/post_migrate/20220825142324_replace_issues_authorization_index.rb b/db/post_migrate/20220825142324_replace_issues_authorization_index.rb new file mode 100644 index 00000000000..b033cb22490 --- /dev/null +++ b/db/post_migrate/20220825142324_replace_issues_authorization_index.rb @@ -0,0 +1,18 @@ +# frozen_string_literal: true + +class ReplaceIssuesAuthorizationIndex < Gitlab::Database::Migration[2.0] + disable_ddl_transaction! + + INDEX_NAME = 'idx_open_issues_on_project_and_confidential_and_author_and_id' + OLD_INDEX_NAME = 'idx_open_issues_on_project_id_and_confidential' + + def up + add_concurrent_index :issues, [:project_id, :confidential, :author_id, :id], name: INDEX_NAME, where: 'state_id = 1' + remove_concurrent_index_by_name :issues, OLD_INDEX_NAME + end + + def down + add_concurrent_index :issues, [:project_id, :confidential], name: OLD_INDEX_NAME, where: 'state_id = 1' + remove_concurrent_index_by_name :issues, INDEX_NAME + end +end diff --git a/db/post_migrate/20220826165048_drop_temporary_job_trace_index.rb b/db/post_migrate/20220826165048_drop_temporary_job_trace_index.rb new file mode 100644 index 00000000000..0cad7cd1968 --- /dev/null +++ b/db/post_migrate/20220826165048_drop_temporary_job_trace_index.rb @@ -0,0 +1,13 @@ +# frozen_string_literal: true + +class DropTemporaryJobTraceIndex < Gitlab::Database::Migration[2.0] + INDEX_NAME = 'tmp_index_ci_job_artifacts_on_id_where_trace_and_expire_at' + + def up + prepare_async_index_removal :ci_job_artifacts, :id, name: INDEX_NAME + end + + def down + unprepare_async_index_by_name :ci_job_artifacts, INDEX_NAME + end +end diff --git a/db/post_migrate/20220826175058_fully_remove_temporary_job_trace_index.rb b/db/post_migrate/20220826175058_fully_remove_temporary_job_trace_index.rb new file mode 100644 index 00000000000..2a18e63106a --- /dev/null +++ b/db/post_migrate/20220826175058_fully_remove_temporary_job_trace_index.rb @@ -0,0 +1,15 @@ +# frozen_string_literal: true + +class FullyRemoveTemporaryJobTraceIndex < Gitlab::Database::Migration[2.0] + INDEX_NAME = 'tmp_index_ci_job_artifacts_on_id_where_trace_and_expire_at' + + disable_ddl_transaction! + + def up + remove_concurrent_index_by_name :ci_job_artifacts, name: INDEX_NAME + end + + def down + add_concurrent_index :ci_job_artifacts, :id, name: INDEX_NAME + end +end diff --git a/db/post_migrate/20220830051704_add_temporary_index_for_orphaned_invited_members.rb b/db/post_migrate/20220830051704_add_temporary_index_for_orphaned_invited_members.rb new file mode 100644 index 00000000000..90254ac3d86 --- /dev/null +++ b/db/post_migrate/20220830051704_add_temporary_index_for_orphaned_invited_members.rb @@ -0,0 +1,21 @@ +# frozen_string_literal: true + +class AddTemporaryIndexForOrphanedInvitedMembers < Gitlab::Database::Migration[2.0] + disable_ddl_transaction! + + TMP_INDEX_NAME = 'tmp_idx_orphaned_invited_members' + + def up + add_concurrent_index('members', :id, where: query_condition, name: TMP_INDEX_NAME) + end + + def down + remove_concurrent_index_by_name('members', TMP_INDEX_NAME) if index_exists_by_name?('members', TMP_INDEX_NAME) + end + + private + + def query_condition + 'invite_token IS NULL and invite_accepted_at IS NOT NULL AND user_id IS NULL' + end +end diff --git a/db/post_migrate/20220830061704_orphaned_invited_members_cleanup.rb b/db/post_migrate/20220830061704_orphaned_invited_members_cleanup.rb new file mode 100644 index 00000000000..c5249510164 --- /dev/null +++ b/db/post_migrate/20220830061704_orphaned_invited_members_cleanup.rb @@ -0,0 +1,29 @@ +# frozen_string_literal: true + +class OrphanedInvitedMembersCleanup < Gitlab::Database::Migration[2.0] + disable_ddl_transaction! + + restrict_gitlab_migration gitlab_schema: :gitlab_main + + def up + # rubocop:disable Style/SymbolProc + membership.where(query_condition).each_batch(of: 100) do |relation| + relation.delete_all + end + # rubocop:enable Style/SymbolProc + end + + def down + # This migration is irreversible + end + + private + + def membership + @membership ||= define_batchable_model('members') + end + + def query_condition + 'invite_token IS NULL and invite_accepted_at IS NOT NULL AND user_id IS NULL' + end +end diff --git a/db/post_migrate/20220830071704_remove_temporary_index_for_orphaned_invited_members.rb b/db/post_migrate/20220830071704_remove_temporary_index_for_orphaned_invited_members.rb new file mode 100644 index 00000000000..c6b712da4c0 --- /dev/null +++ b/db/post_migrate/20220830071704_remove_temporary_index_for_orphaned_invited_members.rb @@ -0,0 +1,21 @@ +# frozen_string_literal: true + +class RemoveTemporaryIndexForOrphanedInvitedMembers < Gitlab::Database::Migration[2.0] + disable_ddl_transaction! + + TMP_INDEX_NAME = 'tmp_idx_orphaned_invited_members' + + def up + remove_concurrent_index_by_name('members', TMP_INDEX_NAME) if index_exists_by_name?('members', TMP_INDEX_NAME) + end + + def down + add_concurrent_index('members', :id, where: query_condition, name: TMP_INDEX_NAME) + end + + private + + def query_condition + 'invite_token IS NULL and invite_accepted_at IS NOT NULL AND user_id IS NULL' + end +end diff --git a/db/post_migrate/20220830172142_reschedule_issue_work_item_type_id_backfill.rb b/db/post_migrate/20220830172142_reschedule_issue_work_item_type_id_backfill.rb new file mode 100644 index 00000000000..5495f0e53b4 --- /dev/null +++ b/db/post_migrate/20220830172142_reschedule_issue_work_item_type_id_backfill.rb @@ -0,0 +1,40 @@ +# frozen_string_literal: true + +class RescheduleIssueWorkItemTypeIdBackfill < Gitlab::Database::Migration[2.0] + MIGRATION = 'BackfillWorkItemTypeIdForIssues' + BATCH_SIZE = 10_000 + MAX_BATCH_SIZE = 30_000 + SUB_BATCH_SIZE = 100 + INTERVAL = 1.minute + + restrict_gitlab_migration gitlab_schema: :gitlab_main + + class MigrationWorkItemType < MigrationRecord + self.table_name = 'work_item_types' + + def self.id_by_type + where(namespace_id: nil).order(:base_type).pluck(:base_type, :id).to_h + end + end + + def up + # We expect no more than 5 types. Only 3 of them are expected to have associated issues at the moment + MigrationWorkItemType.id_by_type.each do |base_type, type_id| + queue_batched_background_migration( + MIGRATION, + :issues, + :id, + base_type, + type_id, + job_interval: INTERVAL, + batch_size: BATCH_SIZE, + max_batch_size: MAX_BATCH_SIZE, + sub_batch_size: SUB_BATCH_SIZE + ) + end + end + + def down + Gitlab::Database::BackgroundMigration::BatchedMigration.where(job_class_name: MIGRATION).delete_all + end +end diff --git a/db/post_migrate/20220831021358_add_index_on_issue_health_status.rb b/db/post_migrate/20220831021358_add_index_on_issue_health_status.rb new file mode 100644 index 00000000000..940bf6b5c38 --- /dev/null +++ b/db/post_migrate/20220831021358_add_index_on_issue_health_status.rb @@ -0,0 +1,16 @@ +# frozen_string_literal: true + +class AddIndexOnIssueHealthStatus < Gitlab::Database::Migration[2.0] + disable_ddl_transaction! + + TABLE_NAME = :issues + INDEX_NAME = 'index_issues_on_project_id_health_status_created_at_id' + + def up + add_concurrent_index TABLE_NAME, [:project_id, :health_status, :created_at, :id], name: INDEX_NAME + end + + def down + remove_concurrent_index TABLE_NAME, [:project_id, :health_status, :created_at, :id], name: INDEX_NAME + end +end diff --git a/db/post_migrate/20220831132802_delete_approval_rules_for_vulnerability.rb b/db/post_migrate/20220831132802_delete_approval_rules_for_vulnerability.rb new file mode 100644 index 00000000000..b29678f1826 --- /dev/null +++ b/db/post_migrate/20220831132802_delete_approval_rules_for_vulnerability.rb @@ -0,0 +1,42 @@ +# frozen_string_literal: true + +class DeleteApprovalRulesForVulnerability < Gitlab::Database::Migration[2.0] + restrict_gitlab_migration gitlab_schema: :gitlab_main + disable_ddl_transaction! + + BATCH_SIZE = 500 + MAX_BATCH_SIZE = 1_000 + SUB_BATCH_SIZE = 10 + MIGRATION = 'DeleteApprovalRulesWithVulnerability' + INTERVAL = 2.minutes + + def up + return unless Gitlab.ee? + + queue_batched_background_migration( + MIGRATION, + :approval_project_rules, + :id, + job_interval: INTERVAL, + batch_size: BATCH_SIZE, + max_batch_size: MAX_BATCH_SIZE, + sub_batch_size: SUB_BATCH_SIZE + ) + + queue_batched_background_migration( + MIGRATION, + :approval_merge_request_rules, + :id, + job_interval: INTERVAL, + batch_size: BATCH_SIZE, + max_batch_size: MAX_BATCH_SIZE, + sub_batch_size: SUB_BATCH_SIZE + ) + end + + def down + # the data deleted is related to a feature removed in 15.0: https://gitlab.com/gitlab-org/gitlab/-/issues/357300 + delete_batched_background_migration(MIGRATION, :approval_project_rules, :id, []) + delete_batched_background_migration(MIGRATION, :approval_merge_request_rules, :id, []) + end +end diff --git a/db/post_migrate/20220901035725_schedule_destroy_invalid_project_members.rb b/db/post_migrate/20220901035725_schedule_destroy_invalid_project_members.rb new file mode 100644 index 00000000000..bc90232f855 --- /dev/null +++ b/db/post_migrate/20220901035725_schedule_destroy_invalid_project_members.rb @@ -0,0 +1,28 @@ +# frozen_string_literal: true + +class ScheduleDestroyInvalidProjectMembers < Gitlab::Database::Migration[2.0] + MIGRATION = 'DestroyInvalidProjectMembers' + DELAY_INTERVAL = 2.minutes + BATCH_SIZE = 50_000 + MAX_BATCH_SIZE = 100_000 + SUB_BATCH_SIZE = 200 + + restrict_gitlab_migration gitlab_schema: :gitlab_main + + def up + queue_batched_background_migration( + MIGRATION, + :members, + :id, + job_interval: DELAY_INTERVAL, + batch_size: BATCH_SIZE, + max_batch_size: MAX_BATCH_SIZE, + sub_batch_size: SUB_BATCH_SIZE, + gitlab_schema: :gitlab_main + ) + end + + def down + delete_batched_background_migration(MIGRATION, :members, :id, []) + end +end diff --git a/db/post_migrate/20220901071310_add_tmp_index_user_callouts_on_attention_request_feature_names.rb b/db/post_migrate/20220901071310_add_tmp_index_user_callouts_on_attention_request_feature_names.rb new file mode 100644 index 00000000000..10b339b90c1 --- /dev/null +++ b/db/post_migrate/20220901071310_add_tmp_index_user_callouts_on_attention_request_feature_names.rb @@ -0,0 +1,18 @@ +# frozen_string_literal: true + +class AddTmpIndexUserCalloutsOnAttentionRequestFeatureNames < Gitlab::Database::Migration[2.0] + INDEX_NAME = "tmp_index_user_callouts_on_attention_request_feature_names" + ATTENTION_REQUEST_CALLOUTS = [47, 48] + + disable_ddl_transaction! + + def up + add_concurrent_index :user_callouts, [:id], + where: "feature_name IN (#{ATTENTION_REQUEST_CALLOUTS.join(',')})", + name: INDEX_NAME + end + + def down + remove_concurrent_index_by_name :user_callouts, INDEX_NAME + end +end diff --git a/db/post_migrate/20220901071355_cleanup_attention_request_user_callouts.rb b/db/post_migrate/20220901071355_cleanup_attention_request_user_callouts.rb new file mode 100644 index 00000000000..cbd0d120bb8 --- /dev/null +++ b/db/post_migrate/20220901071355_cleanup_attention_request_user_callouts.rb @@ -0,0 +1,18 @@ +# frozen_string_literal: true +class CleanupAttentionRequestUserCallouts < Gitlab::Database::Migration[2.0] + disable_ddl_transaction! + restrict_gitlab_migration gitlab_schema: :gitlab_main + + ATTENTION_REQUEST_CALLOUTS = [47, 48] + # 47 and 48 were removed with https://gitlab.com/gitlab-org/gitlab/-/merge_requests/95446 + + def up + define_batchable_model('user_callouts') + .where(feature_name: ATTENTION_REQUEST_CALLOUTS) + .each_batch { |batch| batch.delete_all } # rubocop:disable Style/SymbolProc + end + + def down + # Attention request feature has been reverted. + end +end diff --git a/db/post_migrate/20220901073300_remove_partial_trigram_indexes_for_issues.rb b/db/post_migrate/20220901073300_remove_partial_trigram_indexes_for_issues.rb new file mode 100644 index 00000000000..096b74bc1c6 --- /dev/null +++ b/db/post_migrate/20220901073300_remove_partial_trigram_indexes_for_issues.rb @@ -0,0 +1,25 @@ +# frozen_string_literal: true + +class RemovePartialTrigramIndexesForIssues < Gitlab::Database::Migration[2.0] + TITLE_INDEX_NAME = 'index_issues_on_title_trigram_non_latin' + DESCRIPTION_INDEX_NAME = 'index_issues_on_description_trigram_non_latin' + + disable_ddl_transaction! + + def up + remove_concurrent_index_by_name :issues, TITLE_INDEX_NAME + remove_concurrent_index_by_name :issues, DESCRIPTION_INDEX_NAME + end + + def down + add_concurrent_index :issues, :title, + name: TITLE_INDEX_NAME, + using: :gin, opclass: { description: :gin_trgm_ops }, + where: "title NOT SIMILAR TO '[\\u0000-\\u218F]*' OR description NOT SIMILAR TO '[\\u0000-\\u218F]*'" + + add_concurrent_index :issues, :description, + name: DESCRIPTION_INDEX_NAME, + using: :gin, opclass: { description: :gin_trgm_ops }, + where: "title NOT SIMILAR TO '[\\u0000-\\u218F]*' OR description NOT SIMILAR TO '[\\u0000-\\u218F]*'" + end +end diff --git a/db/post_migrate/20220901184106_add_not_null_to_board_group_recent_visits.rb b/db/post_migrate/20220901184106_add_not_null_to_board_group_recent_visits.rb new file mode 100644 index 00000000000..1dead32efb6 --- /dev/null +++ b/db/post_migrate/20220901184106_add_not_null_to_board_group_recent_visits.rb @@ -0,0 +1,17 @@ +# frozen_string_literal: true + +class AddNotNullToBoardGroupRecentVisits < Gitlab::Database::Migration[2.0] + disable_ddl_transaction! + + def up + add_not_null_constraint :board_group_recent_visits, :user_id, validate: false + add_not_null_constraint :board_group_recent_visits, :group_id, validate: false + add_not_null_constraint :board_group_recent_visits, :board_id, validate: false + end + + def down + remove_not_null_constraint :board_group_recent_visits, :user_id + remove_not_null_constraint :board_group_recent_visits, :board_id + remove_not_null_constraint :board_group_recent_visits, :group_id + end +end diff --git a/db/post_migrate/20220901184246_add_not_null_to_board_project_recent_visits.rb b/db/post_migrate/20220901184246_add_not_null_to_board_project_recent_visits.rb new file mode 100644 index 00000000000..4f0cb4d3d68 --- /dev/null +++ b/db/post_migrate/20220901184246_add_not_null_to_board_project_recent_visits.rb @@ -0,0 +1,17 @@ +# frozen_string_literal: true + +class AddNotNullToBoardProjectRecentVisits < Gitlab::Database::Migration[2.0] + disable_ddl_transaction! + + def up + add_not_null_constraint :board_project_recent_visits, :user_id, validate: false + add_not_null_constraint :board_project_recent_visits, :project_id, validate: false + add_not_null_constraint :board_project_recent_visits, :board_id, validate: false + end + + def down + remove_not_null_constraint :board_project_recent_visits, :user_id + remove_not_null_constraint :board_project_recent_visits, :project_id + remove_not_null_constraint :board_project_recent_visits, :board_id + end +end diff --git a/db/post_migrate/20220902111016_delete_null_records_from_board_group_recent_visits.rb b/db/post_migrate/20220902111016_delete_null_records_from_board_group_recent_visits.rb new file mode 100644 index 00000000000..4b55ecc013d --- /dev/null +++ b/db/post_migrate/20220902111016_delete_null_records_from_board_group_recent_visits.rb @@ -0,0 +1,15 @@ +# frozen_string_literal: true + +class DeleteNullRecordsFromBoardGroupRecentVisits < Gitlab::Database::Migration[2.0] + disable_ddl_transaction! + + restrict_gitlab_migration gitlab_schema: :gitlab_main + + def up + execute('DELETE FROM board_group_recent_visits WHERE user_id is null OR group_id is null OR board_id is null') + end + + def down + # no-op + end +end diff --git a/db/post_migrate/20220902111038_delete_null_records_from_board_project_recent_visits.rb b/db/post_migrate/20220902111038_delete_null_records_from_board_project_recent_visits.rb new file mode 100644 index 00000000000..bb261f80f73 --- /dev/null +++ b/db/post_migrate/20220902111038_delete_null_records_from_board_project_recent_visits.rb @@ -0,0 +1,15 @@ +# frozen_string_literal: true + +class DeleteNullRecordsFromBoardProjectRecentVisits < Gitlab::Database::Migration[2.0] + disable_ddl_transaction! + + restrict_gitlab_migration gitlab_schema: :gitlab_main + + def up + execute('DELETE FROM board_project_recent_visits WHERE user_id is null OR project_id is null OR board_id is null') + end + + def down + # no-op + end +end diff --git a/db/post_migrate/20220902204048_move_security_findings_table_to_gitlab_partitions_dynamic_schema.rb b/db/post_migrate/20220902204048_move_security_findings_table_to_gitlab_partitions_dynamic_schema.rb new file mode 100644 index 00000000000..7b80b6a15bd --- /dev/null +++ b/db/post_migrate/20220902204048_move_security_findings_table_to_gitlab_partitions_dynamic_schema.rb @@ -0,0 +1,232 @@ +# frozen_string_literal: true + +# rubocop:disable Migration/WithLockRetriesDisallowedMethod +class MoveSecurityFindingsTableToGitlabPartitionsDynamicSchema < Gitlab::Database::Migration[2.0] + disable_ddl_transaction! + + INDEX_MAPPING_OF_PARTITION = { + index_security_findings_on_unique_columns: :security_findings_1_uuid_scan_id_partition_number_idx, + index_security_findings_on_confidence: :security_findings_1_confidence_idx, + index_security_findings_on_project_fingerprint: :security_findings_1_project_fingerprint_idx, + index_security_findings_on_scan_id_and_deduplicated: :security_findings_1_scan_id_deduplicated_idx, + index_security_findings_on_scan_id_and_id: :security_findings_1_scan_id_id_idx, + index_security_findings_on_scanner_id: :security_findings_1_scanner_id_idx, + index_security_findings_on_severity: :security_findings_1_severity_idx + }.freeze + + INDEX_MAPPING_AFTER_CREATING_FROM_PARTITION = { + partition_name_placeholder_pkey: :security_findings_pkey, + partition_name_placeholder_uuid_scan_id_partition_number_idx: :index_security_findings_on_unique_columns, + partition_name_placeholder_confidence_idx: :index_security_findings_on_confidence, + partition_name_placeholder_project_fingerprint_idx: :index_security_findings_on_project_fingerprint, + partition_name_placeholder_scan_id_deduplicated_idx: :index_security_findings_on_scan_id_and_deduplicated, + partition_name_placeholder_scan_id_id_idx: :index_security_findings_on_scan_id_and_id, + partition_name_placeholder_scanner_id_idx: :index_security_findings_on_scanner_id, + partition_name_placeholder_severity_idx: :index_security_findings_on_severity + }.freeze + + INDEX_MAPPING_AFTER_CREATING_FROM_ITSELF = { + security_findings_pkey1: :security_findings_pkey, + security_findings_uuid_scan_id_partition_number_idx1: :index_security_findings_on_unique_columns, + security_findings_confidence_idx1: :index_security_findings_on_confidence, + security_findings_project_fingerprint_idx1: :index_security_findings_on_project_fingerprint, + security_findings_scan_id_deduplicated_idx1: :index_security_findings_on_scan_id_and_deduplicated, + security_findings_scan_id_id_idx1: :index_security_findings_on_scan_id_and_id, + security_findings_scanner_id_idx1: :index_security_findings_on_scanner_id, + security_findings_severity_idx1: :index_security_findings_on_severity + }.freeze + + LATEST_PARTITION_SQL = <<~SQL + SELECT + partitions.relname AS partition_name + FROM pg_inherits + JOIN pg_class parent ON pg_inherits.inhparent = parent.oid + JOIN pg_class partitions ON pg_inherits.inhrelid = partitions.oid + WHERE + parent.relname = 'security_findings' + ORDER BY (regexp_matches(partitions.relname, 'security_findings_(\\d+)'))[1]::int DESC + LIMIT 1 + SQL + + CURRENT_CHECK_CONSTRAINT_SQL = <<~SQL + SELECT + pg_get_constraintdef(pg_catalog.pg_constraint.oid) + FROM + pg_catalog.pg_constraint + INNER JOIN pg_class ON pg_class.oid = pg_catalog.pg_constraint.conrelid + WHERE + conname = 'check_partition_number' AND + pg_class.relname = 'security_findings' + SQL + + def up + with_lock_retries do + lock_tables + + execute(<<~SQL) + ALTER TABLE security_findings RENAME TO security_findings_#{candidate_partition_number}; + SQL + + execute(<<~SQL) + ALTER INDEX security_findings_pkey RENAME TO security_findings_#{candidate_partition_number}_pkey; + SQL + + execute(<<~SQL) + CREATE TABLE security_findings ( + LIKE security_findings_#{candidate_partition_number} INCLUDING ALL + ) PARTITION BY LIST (partition_number); + SQL + + execute(<<~SQL) + ALTER SEQUENCE security_findings_id_seq OWNED BY #{connection.current_schema}.security_findings.id; + SQL + + execute(<<~SQL) + ALTER TABLE security_findings + ADD CONSTRAINT fk_rails_729b763a54 FOREIGN KEY (scanner_id) REFERENCES vulnerability_scanners(id) ON DELETE CASCADE; + SQL + + execute(<<~SQL) + ALTER TABLE security_findings + ADD CONSTRAINT fk_rails_bb63863cf1 FOREIGN KEY (scan_id) REFERENCES security_scans(id) ON DELETE CASCADE; + SQL + + execute(<<~SQL) + ALTER TABLE security_findings_#{candidate_partition_number} SET SCHEMA gitlab_partitions_dynamic; + SQL + + execute(<<~SQL) + ALTER TABLE security_findings ATTACH PARTITION gitlab_partitions_dynamic.security_findings_#{candidate_partition_number} FOR VALUES IN (#{candidate_partition_number}); + SQL + + execute(<<~SQL) + ALTER TABLE security_findings DROP CONSTRAINT check_partition_number; + SQL + + index_mapping = INDEX_MAPPING_OF_PARTITION.transform_values do |value| + value.to_s.sub('partition_name_placeholder', "security_findings_#{candidate_partition_number}") + end + + rename_indices('gitlab_partitions_dynamic', index_mapping) + end + end + + def down + # If there is already a partition for the `security_findings` table, + # we can promote that table to be the original one to save the data. + # Otherwise, we have to bring back the non-partitioned `security_findings` + # table from the partitioned one. + if latest_partition + create_non_partitioned_security_findings_with_data + else + create_non_partitioned_security_findings_without_data + end + end + + private + + def lock_tables + execute(<<~SQL) + LOCK TABLE vulnerability_scanners, security_scans, security_findings IN ACCESS EXCLUSIVE MODE + SQL + end + + def current_check_constraint + execute(CURRENT_CHECK_CONSTRAINT_SQL).first['pg_get_constraintdef'] + end + + def candidate_partition_number + @candidate_partition_number ||= current_check_constraint.match(/partition_number\s?=\s?(\d+)/).captures.first + end + + def latest_partition + @latest_partition ||= execute(LATEST_PARTITION_SQL).first&.fetch('partition_name', nil) + end + + def latest_partition_number + latest_partition.match(/security_findings_(\d+)/).captures.first + end + + # rubocop:disable Migration/DropTable (These methods are called from the `down` method) + def create_non_partitioned_security_findings_with_data + with_lock_retries do + lock_tables + + execute(<<~SQL) + ALTER TABLE security_findings DETACH PARTITION gitlab_partitions_dynamic.#{latest_partition}; + SQL + + execute(<<~SQL) + ALTER TABLE gitlab_partitions_dynamic.#{latest_partition} SET SCHEMA #{connection.current_schema}; + SQL + + execute(<<~SQL) + ALTER SEQUENCE security_findings_id_seq OWNED BY #{latest_partition}.id; + SQL + + execute(<<~SQL) + DROP TABLE security_findings; + SQL + + execute(<<~SQL) + ALTER TABLE #{latest_partition} RENAME TO security_findings; + SQL + + index_mapping = INDEX_MAPPING_AFTER_CREATING_FROM_PARTITION.transform_keys do |key| + key.to_s.sub('partition_name_placeholder', latest_partition) + end + + rename_indices(connection.current_schema, index_mapping) + end + + add_check_constraint(:security_findings, "(partition_number = #{latest_partition_number})", :check_partition_number) + end + + def create_non_partitioned_security_findings_without_data + with_lock_retries do + lock_tables + + execute(<<~SQL) + ALTER TABLE security_findings RENAME TO security_findings_1; + SQL + + execute(<<~SQL) + CREATE TABLE security_findings ( + LIKE security_findings_1 INCLUDING ALL + ); + SQL + + execute(<<~SQL) + ALTER SEQUENCE security_findings_id_seq OWNED BY #{connection.current_schema}.security_findings.id; + SQL + + execute(<<~SQL) + DROP TABLE security_findings_1; + SQL + + execute(<<~SQL) + ALTER TABLE ONLY security_findings + ADD CONSTRAINT fk_rails_729b763a54 FOREIGN KEY (scanner_id) REFERENCES vulnerability_scanners(id) ON DELETE CASCADE; + SQL + + execute(<<~SQL) + ALTER TABLE ONLY security_findings + ADD CONSTRAINT fk_rails_bb63863cf1 FOREIGN KEY (scan_id) REFERENCES security_scans(id) ON DELETE CASCADE; + SQL + + rename_indices(connection.current_schema, INDEX_MAPPING_AFTER_CREATING_FROM_ITSELF) + end + + add_check_constraint(:security_findings, "(partition_number = 1)", :check_partition_number) + end + + def rename_indices(schema, mapping) + mapping.each do |index_name, new_index_name| + execute(<<~SQL) + ALTER INDEX #{schema}.#{index_name} RENAME TO #{new_index_name}; + SQL + end + end + # rubocop:enable Migration/DropTable +end +# rubocop:enable Migration/WithLockRetriesDisallowedMethod diff --git a/db/post_migrate/20220904173342_validate_not_null_constraint_board_group_recent_visits.rb b/db/post_migrate/20220904173342_validate_not_null_constraint_board_group_recent_visits.rb new file mode 100644 index 00000000000..0e5a504d0eb --- /dev/null +++ b/db/post_migrate/20220904173342_validate_not_null_constraint_board_group_recent_visits.rb @@ -0,0 +1,15 @@ +# frozen_string_literal: true + +class ValidateNotNullConstraintBoardGroupRecentVisits < Gitlab::Database::Migration[2.0] + disable_ddl_transaction! + + def up + validate_not_null_constraint :board_group_recent_visits, :user_id + validate_not_null_constraint :board_group_recent_visits, :group_id + validate_not_null_constraint :board_group_recent_visits, :board_id + end + + def down + # no-op + end +end diff --git a/db/post_migrate/20220904173430_validate_not_null_constraint_board_project_recent_visits.rb b/db/post_migrate/20220904173430_validate_not_null_constraint_board_project_recent_visits.rb new file mode 100644 index 00000000000..ff73a179f69 --- /dev/null +++ b/db/post_migrate/20220904173430_validate_not_null_constraint_board_project_recent_visits.rb @@ -0,0 +1,15 @@ +# frozen_string_literal: true + +class ValidateNotNullConstraintBoardProjectRecentVisits < Gitlab::Database::Migration[2.0] + disable_ddl_transaction! + + def up + validate_not_null_constraint :board_project_recent_visits, :user_id + validate_not_null_constraint :board_project_recent_visits, :project_id + validate_not_null_constraint :board_project_recent_visits, :board_id + end + + def down + # no-op + end +end diff --git a/db/post_migrate/20220905090300_add_tmp_index_merge_request_reviewers_attention_request_state.rb b/db/post_migrate/20220905090300_add_tmp_index_merge_request_reviewers_attention_request_state.rb new file mode 100644 index 00000000000..e192f76484c --- /dev/null +++ b/db/post_migrate/20220905090300_add_tmp_index_merge_request_reviewers_attention_request_state.rb @@ -0,0 +1,18 @@ +# frozen_string_literal: true + +class AddTmpIndexMergeRequestReviewersAttentionRequestState < Gitlab::Database::Migration[2.0] + INDEX_NAME = "tmp_index_merge_request_reviewers_on_attention_requested_state" + ATTENTION_REQUESTED_STATE = 2 + + disable_ddl_transaction! + + def up + add_concurrent_index :merge_request_reviewers, [:id], + where: "state = #{ATTENTION_REQUESTED_STATE}", + name: INDEX_NAME + end + + def down + remove_concurrent_index_by_name :merge_request_reviewers, INDEX_NAME + end +end diff --git a/db/post_migrate/20220905090339_reset_attention_requested_merge_requests_reviewers_state_to_unreviewed.rb b/db/post_migrate/20220905090339_reset_attention_requested_merge_requests_reviewers_state_to_unreviewed.rb new file mode 100644 index 00000000000..d335095e674 --- /dev/null +++ b/db/post_migrate/20220905090339_reset_attention_requested_merge_requests_reviewers_state_to_unreviewed.rb @@ -0,0 +1,30 @@ +# frozen_string_literal: true + +class ResetAttentionRequestedMergeRequestsReviewersStateToUnreviewed < Gitlab::Database::Migration[2.0] + disable_ddl_transaction! + restrict_gitlab_migration gitlab_schema: :gitlab_main + + BATCH_SIZE = 500 + + class MergeRequestReviewer < MigrationRecord + self.table_name = 'merge_request_reviewers' + + enum state: { + unreviewed: 0, + reviewed: 1, + attention_requested: 2 + } + + include ::EachBatch + end + + def up + MergeRequestReviewer + .where(state: MergeRequestReviewer.states['attention_requested']) + .each_batch(of: BATCH_SIZE) { |batch| batch.update_all(state: MergeRequestReviewer.states['unreviewed']) } + end + + def down + # no op + end +end diff --git a/db/post_migrate/20220905112710_add_async_index_to_todos_to_cover_pending_query.rb b/db/post_migrate/20220905112710_add_async_index_to_todos_to_cover_pending_query.rb new file mode 100644 index 00000000000..e2bca2fae1a --- /dev/null +++ b/db/post_migrate/20220905112710_add_async_index_to_todos_to_cover_pending_query.rb @@ -0,0 +1,16 @@ +# frozen_string_literal: true + +class AddAsyncIndexToTodosToCoverPendingQuery < Gitlab::Database::Migration[2.0] + disable_ddl_transaction! + + INDEX_NAME = 'index_on_todos_user_project_target_and_state' + COLUMNS = %i[user_id project_id target_type target_id id].freeze + + def up + prepare_async_index :todos, COLUMNS, name: INDEX_NAME, where: "state = 'pending'" + end + + def down + unprepare_async_index :todos, COLUMNS, name: INDEX_NAME, where: "state='pending'" + end +end diff --git a/db/post_migrate/20220906074449_schedule_disable_legacy_open_source_license_for_projects_less_than_one_mb.rb b/db/post_migrate/20220906074449_schedule_disable_legacy_open_source_license_for_projects_less_than_one_mb.rb new file mode 100644 index 00000000000..ee48ace13e0 --- /dev/null +++ b/db/post_migrate/20220906074449_schedule_disable_legacy_open_source_license_for_projects_less_than_one_mb.rb @@ -0,0 +1,33 @@ +# frozen_string_literal: true + +class ScheduleDisableLegacyOpenSourceLicenseForProjectsLessThanOneMb < Gitlab::Database::Migration[2.0] + MIGRATION = 'DisableLegacyOpenSourceLicenseForProjectsLessThanOneMb' + INTERVAL = 2.minutes + BATCH_SIZE = 4_000 + MAX_BATCH_SIZE = 50_000 + SUB_BATCH_SIZE = 250 + + disable_ddl_transaction! + + restrict_gitlab_migration gitlab_schema: :gitlab_main + + def up + return unless Gitlab.com? + + queue_batched_background_migration( + MIGRATION, + :project_settings, + :project_id, + job_interval: INTERVAL, + batch_size: BATCH_SIZE, + max_batch_size: MAX_BATCH_SIZE, + sub_batch_size: SUB_BATCH_SIZE + ) + end + + def down + return unless Gitlab.com? + + delete_batched_background_migration(MIGRATION, :project_settings, :project_id, []) + end +end diff --git a/db/post_migrate/20220906212931_add_partial_index_for_ci_pipeline_artifacts_unlocked_with_expire_at.rb b/db/post_migrate/20220906212931_add_partial_index_for_ci_pipeline_artifacts_unlocked_with_expire_at.rb new file mode 100644 index 00000000000..a24187dd56b --- /dev/null +++ b/db/post_migrate/20220906212931_add_partial_index_for_ci_pipeline_artifacts_unlocked_with_expire_at.rb @@ -0,0 +1,17 @@ +# frozen_string_literal: true + +class AddPartialIndexForCiPipelineArtifactsUnlockedWithExpireAt < Gitlab::Database::Migration[2.0] + disable_ddl_transaction! + + TABLE_NAME = 'ci_pipeline_artifacts' + INDEX_NAME = 'ci_pipeline_artifacts_on_expire_at_for_removal' + CONDITIONS = 'locked = 0 AND expire_at IS NOT NULL' + + def up + add_concurrent_index TABLE_NAME, [:expire_at], where: CONDITIONS, name: INDEX_NAME + end + + def down + remove_concurrent_index_by_name TABLE_NAME, INDEX_NAME + end +end diff --git a/db/post_migrate/20220908125146_remove_free_user_cap_remediation_worker.rb b/db/post_migrate/20220908125146_remove_free_user_cap_remediation_worker.rb new file mode 100644 index 00000000000..e95ea9c58b4 --- /dev/null +++ b/db/post_migrate/20220908125146_remove_free_user_cap_remediation_worker.rb @@ -0,0 +1,11 @@ +# frozen_string_literal: true + +class RemoveFreeUserCapRemediationWorker < Gitlab::Database::Migration[2.0] + def up + Sidekiq::Cron::Job.find('free_user_cap_data_remediation')&.destroy + end + + def down + # no-op + end +end diff --git a/db/post_migrate/20220909114220_drop_environments_project_name_lower_pattern_ops_index.rb b/db/post_migrate/20220909114220_drop_environments_project_name_lower_pattern_ops_index.rb new file mode 100644 index 00000000000..3ab71809039 --- /dev/null +++ b/db/post_migrate/20220909114220_drop_environments_project_name_lower_pattern_ops_index.rb @@ -0,0 +1,16 @@ +# frozen_string_literal: true + +# reverts db/migrate/20220901131828_add_environments_project_name_lower_pattern_ops_index.rb +class DropEnvironmentsProjectNameLowerPatternOpsIndex < Gitlab::Database::Migration[2.0] + disable_ddl_transaction! + + INDEX_NAME = 'index_environments_on_project_name_varchar_pattern_ops' + + def up + remove_concurrent_index_by_name :environments, INDEX_NAME + end + + def down + add_concurrent_index :environments, 'project_id, lower(name) varchar_pattern_ops', name: INDEX_NAME + end +end diff --git a/db/post_migrate/20220912085047_add_index_to_todos_pending_query.rb b/db/post_migrate/20220912085047_add_index_to_todos_pending_query.rb new file mode 100644 index 00000000000..7d721421463 --- /dev/null +++ b/db/post_migrate/20220912085047_add_index_to_todos_pending_query.rb @@ -0,0 +1,16 @@ +# frozen_string_literal: true + +class AddIndexToTodosPendingQuery < Gitlab::Database::Migration[2.0] + disable_ddl_transaction! + + INDEX_NAME = 'index_on_todos_user_project_target_and_state' + COLUMNS = %i[user_id project_id target_type target_id id].freeze + + def up + add_concurrent_index :todos, COLUMNS, name: INDEX_NAME, where: "state = 'pending'" + end + + def down + remove_concurrent_index_by_name :todos, INDEX_NAME + end +end diff --git a/db/post_migrate/20220912110433_prepare_create_internal_notes_index_on_id.rb b/db/post_migrate/20220912110433_prepare_create_internal_notes_index_on_id.rb new file mode 100644 index 00000000000..241bdfa4715 --- /dev/null +++ b/db/post_migrate/20220912110433_prepare_create_internal_notes_index_on_id.rb @@ -0,0 +1,13 @@ +# frozen_string_literal: true + +class PrepareCreateInternalNotesIndexOnId < Gitlab::Database::Migration[2.0] + INDEX_NAME = 'index_notes_on_id_where_internal' + + def up + prepare_async_index :notes, :id, where: 'internal = true', name: INDEX_NAME + end + + def down + unprepare_async_index_by_name :notes, INDEX_NAME + end +end diff --git a/db/post_migrate/20220913030552_add_tmp_index_system_note_metadata_on_attention_request_actions.rb b/db/post_migrate/20220913030552_add_tmp_index_system_note_metadata_on_attention_request_actions.rb new file mode 100644 index 00000000000..3418dabc0e9 --- /dev/null +++ b/db/post_migrate/20220913030552_add_tmp_index_system_note_metadata_on_attention_request_actions.rb @@ -0,0 +1,17 @@ +# frozen_string_literal: true + +class AddTmpIndexSystemNoteMetadataOnAttentionRequestActions < Gitlab::Database::Migration[2.0] + INDEX_NAME = "tmp_index_system_note_metadata_on_attention_request_actions" + + disable_ddl_transaction! + + def up + add_concurrent_index :system_note_metadata, [:id], + where: "action IN ('attention_requested', 'attention_request_removed')", + name: INDEX_NAME + end + + def down + remove_concurrent_index_by_name :system_note_metadata, INDEX_NAME + end +end diff --git a/db/post_migrate/20220913030624_cleanup_attention_request_related_system_notes.rb b/db/post_migrate/20220913030624_cleanup_attention_request_related_system_notes.rb new file mode 100644 index 00000000000..b7d6908696b --- /dev/null +++ b/db/post_migrate/20220913030624_cleanup_attention_request_related_system_notes.rb @@ -0,0 +1,30 @@ +# frozen_string_literal: true + +class CleanupAttentionRequestRelatedSystemNotes < Gitlab::Database::Migration[2.0] + disable_ddl_transaction! + restrict_gitlab_migration gitlab_schema: :gitlab_main + + BATCH_SIZE = 100 + + class SystemNoteMetadata < MigrationRecord + include EachBatch + + self.table_name = 'system_note_metadata' + end + + class Note < MigrationRecord + self.table_name = 'notes' + end + + def up + SystemNoteMetadata + .where(action: %w[attention_requested attention_request_removed]) + .each_batch(of: BATCH_SIZE) do |batch| + Note.where(id: batch.pluck(:note_id)).delete_all + end + end + + def down + # no op + end +end diff --git a/db/post_migrate/20220913082728_drop_index_cadence_create_iterations_automation.rb b/db/post_migrate/20220913082728_drop_index_cadence_create_iterations_automation.rb new file mode 100644 index 00000000000..8b961bc1c7e --- /dev/null +++ b/db/post_migrate/20220913082728_drop_index_cadence_create_iterations_automation.rb @@ -0,0 +1,21 @@ +# frozen_string_literal: true + +class DropIndexCadenceCreateIterationsAutomation < Gitlab::Database::Migration[2.0] + INDEX_NAME = 'cadence_create_iterations_automation' + + disable_ddl_transaction! + + def up + remove_concurrent_index_by_name :iterations_cadences, INDEX_NAME + end + + def down + execute( + <<-SQL + CREATE INDEX CONCURRENTLY #{INDEX_NAME} ON iterations_cadences + USING BTREE(automatic, duration_in_weeks, (DATE ((COALESCE("iterations_cadences"."last_run_date", DATE('01-01-1970')) + "iterations_cadences"."duration_in_weeks" * INTERVAL '1 week')))) + WHERE duration_in_weeks IS NOT NULL + SQL + ) + end +end diff --git a/db/post_migrate/20220913083015_clean_up_rename_iterations_cadences_last_run_date_to_next_run_date.rb b/db/post_migrate/20220913083015_clean_up_rename_iterations_cadences_last_run_date_to_next_run_date.rb new file mode 100644 index 00000000000..7618e1841bd --- /dev/null +++ b/db/post_migrate/20220913083015_clean_up_rename_iterations_cadences_last_run_date_to_next_run_date.rb @@ -0,0 +1,13 @@ +# frozen_string_literal: true + +class CleanUpRenameIterationsCadencesLastRunDateToNextRunDate < Gitlab::Database::Migration[2.0] + disable_ddl_transaction! + + def up + cleanup_concurrent_column_rename :iterations_cadences, :last_run_date, :next_run_date + end + + def down + undo_cleanup_concurrent_column_rename :iterations_cadences, :last_run_date, :next_run_date + end +end diff --git a/db/post_migrate/20220914093408_add_unique_id_partition_id_index_to_ci_build_metadata.rb b/db/post_migrate/20220914093408_add_unique_id_partition_id_index_to_ci_build_metadata.rb new file mode 100644 index 00000000000..1f40118c809 --- /dev/null +++ b/db/post_migrate/20220914093408_add_unique_id_partition_id_index_to_ci_build_metadata.rb @@ -0,0 +1,14 @@ +# frozen_string_literal: true + +class AddUniqueIdPartitionIdIndexToCiBuildMetadata < Gitlab::Database::Migration[2.0] + TABLE_NAME = :ci_builds_metadata + INDEX_NAME = :index_ci_builds_metadata_on_id_partition_id_unique + + def up + prepare_async_index(TABLE_NAME, %i[id partition_id], unique: true, name: INDEX_NAME) + end + + def down + unprepare_async_index(:ci_builds_metadata, %i[id partition_id], name: INDEX_NAME) + end +end diff --git a/db/post_migrate/20220915103831_add_unique_build_id_partition_id_index_to_ci_build_metadata.rb b/db/post_migrate/20220915103831_add_unique_build_id_partition_id_index_to_ci_build_metadata.rb new file mode 100644 index 00000000000..74b60390057 --- /dev/null +++ b/db/post_migrate/20220915103831_add_unique_build_id_partition_id_index_to_ci_build_metadata.rb @@ -0,0 +1,14 @@ +# frozen_string_literal: true + +class AddUniqueBuildIdPartitionIdIndexToCiBuildMetadata < Gitlab::Database::Migration[2.0] + TABLE_NAME = :ci_builds_metadata + INDEX_NAME = :index_ci_builds_metadata_on_build_id_partition_id_unique + + def up + prepare_async_index(TABLE_NAME, %i[build_id partition_id], unique: true, name: INDEX_NAME) + end + + def down + unprepare_async_index(:ci_builds_metadata, %i[build_id partition_id], name: INDEX_NAME) + end +end diff --git a/db/post_migrate/20220916112841_remove_unused_aggregation_columns.rb b/db/post_migrate/20220916112841_remove_unused_aggregation_columns.rb new file mode 100644 index 00000000000..f5333c84042 --- /dev/null +++ b/db/post_migrate/20220916112841_remove_unused_aggregation_columns.rb @@ -0,0 +1,59 @@ +# frozen_string_literal: true + +class RemoveUnusedAggregationColumns < Gitlab::Database::Migration[2.0] + disable_ddl_transaction! + + def up + with_lock_retries do + remove_column :analytics_cycle_analytics_aggregations, :last_full_run_processed_records + remove_column :analytics_cycle_analytics_aggregations, :last_full_run_runtimes_in_seconds + remove_column :analytics_cycle_analytics_aggregations, :last_full_run_issues_updated_at + remove_column :analytics_cycle_analytics_aggregations, :last_full_run_mrs_updated_at + remove_column :analytics_cycle_analytics_aggregations, :last_full_run_issues_id + remove_column :analytics_cycle_analytics_aggregations, :last_full_run_merge_requests_id + end + end + + def down + with_lock_retries do + add_column(:analytics_cycle_analytics_aggregations, + :last_full_run_processed_records, + :integer, + array: true, + default: [], + null: false, + if_not_exists: true) + add_column(:analytics_cycle_analytics_aggregations, + :last_full_run_runtimes_in_seconds, + :integer, + array: true, + default: [], + null: false, + if_not_exists: true) + add_column(:analytics_cycle_analytics_aggregations, + :last_full_run_issues_updated_at, + :datetime_with_timezone, + if_not_exists: true) + add_column(:analytics_cycle_analytics_aggregations, + :last_full_run_mrs_updated_at, + :datetime_with_timezone, + if_not_exists: true) + add_column(:analytics_cycle_analytics_aggregations, + :last_full_run_issues_id, + :integer, + if_not_exists: true) + add_column(:analytics_cycle_analytics_aggregations, + :last_full_run_merge_requests_id, + :integer, + if_not_exists: true) + end + + add_check_constraint(:analytics_cycle_analytics_aggregations, + 'CARDINALITY(last_full_run_runtimes_in_seconds) <= 10', + 'chk_rails_7810292ec9') + + add_check_constraint(:analytics_cycle_analytics_aggregations, + 'CARDINALITY(last_full_run_processed_records) <= 10', + 'chk_rails_8b9e89687c') + end +end |