diff options
author | GitLab Bot <gitlab-bot@gitlab.com> | 2023-06-15 00:10:36 +0300 |
---|---|---|
committer | GitLab Bot <gitlab-bot@gitlab.com> | 2023-06-15 00:10:36 +0300 |
commit | beabc7d164276a8bb35c2b497a0c4dc0dc824e3c (patch) | |
tree | be60f7d352225862e3404fe89c39374fd4e64a1e /db | |
parent | 14160fad80415337f8c08755af53ee994b4a7518 (diff) |
Add latest changes from gitlab-org/gitlab@master
Diffstat (limited to 'db')
-rw-r--r-- | db/post_migrate/20230530015535_swap_notes_id_to_bigint_for_gitlab_dot_com.rb | 195 | ||||
-rw-r--r-- | db/schema_migrations/20230530015535 | 1 | ||||
-rw-r--r-- | db/structure.sql | 54 |
3 files changed, 198 insertions, 52 deletions
diff --git a/db/post_migrate/20230530015535_swap_notes_id_to_bigint_for_gitlab_dot_com.rb b/db/post_migrate/20230530015535_swap_notes_id_to_bigint_for_gitlab_dot_com.rb new file mode 100644 index 00000000000..2dfeb80aba7 --- /dev/null +++ b/db/post_migrate/20230530015535_swap_notes_id_to_bigint_for_gitlab_dot_com.rb @@ -0,0 +1,195 @@ +# frozen_string_literal: true + +class SwapNotesIdToBigintForGitlabDotCom < Gitlab::Database::Migration[2.1] + include Gitlab::Database::MigrationHelpers::ConvertToBigint + + disable_ddl_transaction! + + TABLE_NAME = 'notes' + PK_INDEX_NAME = 'index_notes_on_id_convert_to_bigint' + + SECONDARY_INDEXES = [ + { + original_name: :index_notes_on_author_id_and_created_at_and_id, + temporary_name: :index_notes_on_author_id_created_at_id_convert_to_bigint, + columns: [:author_id, :created_at, :id_convert_to_bigint], + options: {} + }, + { + original_name: :index_notes_on_id_where_confidential, + temporary_name: :index_notes_on_id_convert_to_bigint_where_confidential, + columns: [:id_convert_to_bigint], + options: { where: 'confidential = true' } + }, + { + original_name: :index_notes_on_id_where_internal, + temporary_name: :index_notes_on_id_convert_to_bigint_where_internal, + columns: [:id_convert_to_bigint], + options: { where: 'internal = true' } + }, + { + original_name: :index_notes_on_project_id_and_id_and_system_false, + temporary_name: :index_notes_on_project_id_id_convert_to_bigint_system_false, + columns: [:project_id, :id_convert_to_bigint], + options: { where: 'NOT system' } + }, + { + original_name: :note_mentions_temp_index, + temporary_name: :note_mentions_temp_index_convert_to_bigint, + columns: [:id_convert_to_bigint, :noteable_type], + options: { where: "note ~~ '%@%'::text" } + } + ] + + REFERENCING_FOREIGN_KEYS = [ + [:todos, :fk_91d1f47b13, :note_id, :cascade], + [:incident_management_timeline_events, :fk_d606a2a890, :promoted_from_note_id, :nullify], + [:system_note_metadata, :fk_d83a918cb1, :note_id, :cascade], + [:diff_note_positions, :fk_rails_13c7212859, :note_id, :cascade], + [:epic_user_mentions, :fk_rails_1c65976a49, :note_id, :cascade], + [:suggestions, :fk_rails_33b03a535c, :note_id, :cascade], + [:issue_user_mentions, :fk_rails_3861d9fefa, :note_id, :cascade], + [:note_diff_files, :fk_rails_3d66047aeb, :diff_note_id, :cascade], + [:snippet_user_mentions, :fk_rails_4d3f96b2cb, :note_id, :cascade], + [:design_user_mentions, :fk_rails_8de8c6d632, :note_id, :cascade], + [:vulnerability_user_mentions, :fk_rails_a18600f210, :note_id, :cascade], + [:commit_user_mentions, :fk_rails_a6760813e0, :note_id, :cascade], + [:merge_request_user_mentions, :fk_rails_c440b9ea31, :note_id, :cascade], + [:note_metadata, :fk_rails_d853224d37, :note_id, :cascade], + [:alert_management_alert_user_mentions, :fk_rails_eb2de0cdef, :note_id, :cascade], + [:timelogs, :fk_timelogs_note_id, :note_id, :nullify] + ] + + def up + return unless should_run? + + swap + end + + def down + return unless should_run? + + swap + + add_concurrent_index TABLE_NAME, :id_convert_to_bigint, unique: true, name: PK_INDEX_NAME + + REFERENCING_FOREIGN_KEYS.each do |(from_table, name, column, on_delete)| + temporary_name = "#{name}_tmp" + + add_concurrent_foreign_key( + from_table, + TABLE_NAME, + column: column, + target_column: :id_convert_to_bigint, + name: temporary_name, + on_delete: on_delete, + reverse_lock_order: true, + validate: false) + end + end + + def swap + # Copy existing indexes from the original column to the new column + create_indexes + + # Copy existing FKs from the original column to the new column + create_referencing_foreign_keys + + # Remove existing FKs from the referencing tables, so we don't have to lock on them when we drop the existing PK + replace_referencing_foreign_keys + + with_lock_retries(raise_on_exhaustion: true) do + # Swap the original and new column names + execute "ALTER TABLE #{TABLE_NAME} RENAME COLUMN id TO id_tmp" + execute "ALTER TABLE #{TABLE_NAME} RENAME COLUMN id_convert_to_bigint TO id" + execute "ALTER TABLE #{TABLE_NAME} RENAME COLUMN id_tmp TO id_convert_to_bigint" + + # Reset the function so PG drops the plan cache for the incorrect integer type + function_name = Gitlab::Database::UnidirectionalCopyTrigger + .on_table(TABLE_NAME, connection: connection) + .name(:id, :id_convert_to_bigint) + execute "ALTER FUNCTION #{quote_table_name(function_name)} RESET ALL" + + # Swap defaults of the two columns, and change ownership of the sequence to the new id + execute "ALTER SEQUENCE notes_id_seq OWNED BY #{TABLE_NAME}.id" + change_column_default TABLE_NAME, :id, -> { "nextval('notes_id_seq'::regclass)" } + change_column_default TABLE_NAME, :id_convert_to_bigint, 0 + + # Swap the PK constraint from the original column to the new column. + # We deliberately don't CASCADE here because the old FKs should be removed already + execute "ALTER TABLE #{TABLE_NAME} DROP CONSTRAINT notes_pkey" + rename_index TABLE_NAME, PK_INDEX_NAME, 'notes_pkey' + execute "ALTER TABLE #{TABLE_NAME} ADD CONSTRAINT notes_pkey PRIMARY KEY USING INDEX notes_pkey" + + # Remove old column indexes and change new column indexes to have the original names + rename_secondary_indexes # rubocop:disable Migration/WithLockRetriesDisallowedMethod + end + end + + private + + def should_run? + com_or_dev_or_test_but_not_jh? + end + + def create_indexes + add_concurrent_index TABLE_NAME, :id_convert_to_bigint, unique: true, name: PK_INDEX_NAME + + SECONDARY_INDEXES.each do |index_definition| + options = index_definition[:options] + options[:name] = index_definition[:temporary_name] + + add_concurrent_index(TABLE_NAME, index_definition[:columns], options) + end + end + + def rename_secondary_indexes + SECONDARY_INDEXES.each do |index_definition| + remove_index(TABLE_NAME, name: index_definition[:original_name], if_exists: true) # rubocop:disable Migration/RemoveIndex + rename_index(TABLE_NAME, index_definition[:temporary_name], index_definition[:original_name]) + end + end + + def create_referencing_foreign_keys + REFERENCING_FOREIGN_KEYS.each do |(from_table, name, column, on_delete)| + # Don't attempt to create the FK if one already exists from the table to the new column + # The check in `add_concurrent_foreign_key` already checks for this, but it looks for the foreign key + # with the new name only (containing the `_tmp` suffix). + # + # Since we might partially rename FKs and re-run the migration, we also have to check and see if a FK exists + # on those columns that might not match the `_tmp` name. + next if foreign_key_exists?( + from_table, TABLE_NAME, column: column, + primary_key: :id_convert_to_bigint, name: name) + + temporary_name = "#{name}_tmp" + + add_concurrent_foreign_key( + from_table, + TABLE_NAME, + column: column, + target_column: :id_convert_to_bigint, + name: temporary_name, + on_delete: on_delete, + reverse_lock_order: true) + end + end + + def replace_referencing_foreign_keys + REFERENCING_FOREIGN_KEYS.each do |(from_table, name, column, _)| + # Don't attempt to replace the FK unless it exists and points at the original column. + # This could happen if the migration is re-run due to failing midway. + next unless foreign_key_exists?(from_table, TABLE_NAME, column: column, primary_key: :id, name: name) + + with_lock_retries(raise_on_exhaustion: true) do + temporary_name = "#{name}_tmp" + + # Explicitly lock table in order of parent, child to attempt to avoid deadlocks + execute "LOCK TABLE #{TABLE_NAME}, #{from_table} IN ACCESS EXCLUSIVE MODE" + + remove_foreign_key(from_table, TABLE_NAME, column: column, primary_key: :id, name: name) + rename_constraint(from_table, temporary_name, name) + end + end + end +end diff --git a/db/schema_migrations/20230530015535 b/db/schema_migrations/20230530015535 new file mode 100644 index 00000000000..cfc95b4db06 --- /dev/null +++ b/db/schema_migrations/20230530015535 @@ -0,0 +1 @@ +29516b4b02e3feda356c99c7e0b3a1011de830c38781ad61ce4cb3650559e1ee
\ No newline at end of file diff --git a/db/structure.sql b/db/structure.sql index af7b399cfeb..c0e25a6f3c6 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -18859,7 +18859,7 @@ CREATE SEQUENCE note_metadata_note_id_seq ALTER SEQUENCE note_metadata_note_id_seq OWNED BY note_metadata.note_id; CREATE TABLE notes ( - id integer NOT NULL, + id_convert_to_bigint integer DEFAULT 0 NOT NULL, note text, noteable_type character varying, author_id integer, @@ -18887,7 +18887,7 @@ CREATE TABLE notes ( confidential boolean, last_edited_at timestamp with time zone, internal boolean DEFAULT false NOT NULL, - id_convert_to_bigint bigint DEFAULT 0 NOT NULL + id bigint NOT NULL ); CREATE SEQUENCE notes_id_seq @@ -31875,8 +31875,6 @@ CREATE INDEX index_notes_on_created_at ON notes USING btree (created_at); CREATE INDEX index_notes_on_discussion_id ON notes USING btree (discussion_id); -CREATE UNIQUE INDEX index_notes_on_id_convert_to_bigint ON notes USING btree (id_convert_to_bigint); - CREATE INDEX index_notes_on_id_where_confidential ON notes USING btree (id) WHERE (confidential = true); CREATE INDEX index_notes_on_id_where_internal ON notes USING btree (id) WHERE (internal = true); @@ -35657,9 +35655,6 @@ ALTER TABLE ONLY protected_tags ALTER TABLE ONLY todos ADD CONSTRAINT fk_91d1f47b13 FOREIGN KEY (note_id) REFERENCES notes(id) ON DELETE CASCADE; -ALTER TABLE ONLY todos - ADD CONSTRAINT fk_91d1f47b13_tmp FOREIGN KEY (note_id) REFERENCES notes(id_convert_to_bigint) ON DELETE CASCADE NOT VALID; - ALTER TABLE ONLY dast_site_profiles_builds ADD CONSTRAINT fk_94e80df60e FOREIGN KEY (dast_site_profile_id) REFERENCES dast_site_profiles(id) ON DELETE CASCADE; @@ -35963,9 +35958,6 @@ ALTER TABLE ONLY ci_sources_pipelines ALTER TABLE ONLY incident_management_timeline_events ADD CONSTRAINT fk_d606a2a890 FOREIGN KEY (promoted_from_note_id) REFERENCES notes(id) ON DELETE SET NULL; -ALTER TABLE ONLY incident_management_timeline_events - ADD CONSTRAINT fk_d606a2a890_tmp FOREIGN KEY (promoted_from_note_id) REFERENCES notes(id_convert_to_bigint) ON DELETE SET NULL NOT VALID; - ALTER TABLE ONLY lists ADD CONSTRAINT fk_d6cf4279f7 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE; @@ -35987,9 +35979,6 @@ ALTER TABLE ONLY ci_pipelines ALTER TABLE ONLY system_note_metadata ADD CONSTRAINT fk_d83a918cb1 FOREIGN KEY (note_id) REFERENCES notes(id) ON DELETE CASCADE; -ALTER TABLE ONLY system_note_metadata - ADD CONSTRAINT fk_d83a918cb1_tmp FOREIGN KEY (note_id) REFERENCES notes(id_convert_to_bigint) ON DELETE CASCADE NOT VALID; - ALTER TABLE ONLY sbom_occurrences ADD CONSTRAINT fk_d857c6edc1 FOREIGN KEY (component_id) REFERENCES sbom_components(id) ON DELETE CASCADE; @@ -36314,9 +36303,6 @@ ALTER TABLE ONLY bulk_imports ALTER TABLE ONLY diff_note_positions ADD CONSTRAINT fk_rails_13c7212859 FOREIGN KEY (note_id) REFERENCES notes(id) ON DELETE CASCADE; -ALTER TABLE ONLY diff_note_positions - ADD CONSTRAINT fk_rails_13c7212859_tmp FOREIGN KEY (note_id) REFERENCES notes(id_convert_to_bigint) ON DELETE CASCADE NOT VALID; - ALTER TABLE ONLY analytics_cycle_analytics_aggregations ADD CONSTRAINT fk_rails_13c8374c7a FOREIGN KEY (group_id) REFERENCES namespaces(id) ON DELETE CASCADE; @@ -36380,9 +36366,6 @@ ALTER TABLE ONLY board_assignees ALTER TABLE ONLY epic_user_mentions ADD CONSTRAINT fk_rails_1c65976a49 FOREIGN KEY (note_id) REFERENCES notes(id) ON DELETE CASCADE; -ALTER TABLE ONLY epic_user_mentions - ADD CONSTRAINT fk_rails_1c65976a49_tmp FOREIGN KEY (note_id) REFERENCES notes(id_convert_to_bigint) ON DELETE CASCADE NOT VALID; - ALTER TABLE ONLY approver_groups ADD CONSTRAINT fk_rails_1cdcbd7723 FOREIGN KEY (group_id) REFERENCES namespaces(id) ON DELETE CASCADE; @@ -36542,9 +36525,6 @@ ALTER TABLE ONLY alert_management_alert_metric_images ALTER TABLE ONLY suggestions ADD CONSTRAINT fk_rails_33b03a535c FOREIGN KEY (note_id) REFERENCES notes(id) ON DELETE CASCADE; -ALTER TABLE ONLY suggestions - ADD CONSTRAINT fk_rails_33b03a535c_tmp FOREIGN KEY (note_id) REFERENCES notes(id_convert_to_bigint) ON DELETE CASCADE NOT VALID; - ALTER TABLE ONLY requirements ADD CONSTRAINT fk_rails_33fed8aa4e FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE SET NULL; @@ -36575,9 +36555,6 @@ ALTER TABLE ONLY packages_debian_project_distribution_keys ALTER TABLE ONLY issue_user_mentions ADD CONSTRAINT fk_rails_3861d9fefa FOREIGN KEY (note_id) REFERENCES notes(id) ON DELETE CASCADE; -ALTER TABLE ONLY issue_user_mentions - ADD CONSTRAINT fk_rails_3861d9fefa_tmp FOREIGN KEY (note_id) REFERENCES notes(id_convert_to_bigint) ON DELETE CASCADE NOT VALID; - ALTER TABLE ONLY namespace_settings ADD CONSTRAINT fk_rails_3896d4fae5 FOREIGN KEY (namespace_id) REFERENCES namespaces(id) ON DELETE CASCADE; @@ -36605,9 +36582,6 @@ ALTER TABLE ONLY cluster_groups ALTER TABLE ONLY note_diff_files ADD CONSTRAINT fk_rails_3d66047aeb FOREIGN KEY (diff_note_id) REFERENCES notes(id) ON DELETE CASCADE; -ALTER TABLE ONLY note_diff_files - ADD CONSTRAINT fk_rails_3d66047aeb_tmp FOREIGN KEY (diff_note_id) REFERENCES notes(id_convert_to_bigint) ON DELETE CASCADE NOT VALID; - ALTER TABLE ONLY snippet_user_mentions ADD CONSTRAINT fk_rails_3e00189191 FOREIGN KEY (snippet_id) REFERENCES snippets(id) ON DELETE CASCADE; @@ -36713,9 +36687,6 @@ ALTER TABLE ONLY scim_identities ALTER TABLE ONLY snippet_user_mentions ADD CONSTRAINT fk_rails_4d3f96b2cb FOREIGN KEY (note_id) REFERENCES notes(id) ON DELETE CASCADE; -ALTER TABLE ONLY snippet_user_mentions - ADD CONSTRAINT fk_rails_4d3f96b2cb_tmp FOREIGN KEY (note_id) REFERENCES notes(id_convert_to_bigint) ON DELETE CASCADE NOT VALID; - ALTER TABLE ONLY protected_environment_approval_rules ADD CONSTRAINT fk_rails_4e554f96f5 FOREIGN KEY (protected_environment_id) REFERENCES protected_environments(id) ON DELETE CASCADE; @@ -37148,9 +37119,6 @@ ALTER TABLE ONLY approval_merge_request_rules_approved_approvers ALTER TABLE ONLY design_user_mentions ADD CONSTRAINT fk_rails_8de8c6d632 FOREIGN KEY (note_id) REFERENCES notes(id) ON DELETE CASCADE; -ALTER TABLE ONLY design_user_mentions - ADD CONSTRAINT fk_rails_8de8c6d632_tmp FOREIGN KEY (note_id) REFERENCES notes(id_convert_to_bigint) ON DELETE CASCADE NOT VALID; - ALTER TABLE ONLY clusters_kubernetes_namespaces ADD CONSTRAINT fk_rails_8df789f3ab FOREIGN KEY (environment_id) REFERENCES environments(id) ON DELETE SET NULL; @@ -37277,9 +37245,6 @@ ALTER TABLE ONLY project_aliases ALTER TABLE ONLY vulnerability_user_mentions ADD CONSTRAINT fk_rails_a18600f210 FOREIGN KEY (note_id) REFERENCES notes(id) ON DELETE CASCADE; -ALTER TABLE ONLY vulnerability_user_mentions - ADD CONSTRAINT fk_rails_a18600f210_tmp FOREIGN KEY (note_id) REFERENCES notes(id_convert_to_bigint) ON DELETE CASCADE NOT VALID; - ALTER TABLE ONLY dependency_proxy_packages_settings ADD CONSTRAINT fk_rails_a248d0c26f FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE; @@ -37310,9 +37275,6 @@ ALTER TABLE ONLY cluster_projects ALTER TABLE ONLY commit_user_mentions ADD CONSTRAINT fk_rails_a6760813e0 FOREIGN KEY (note_id) REFERENCES notes(id) ON DELETE CASCADE; -ALTER TABLE ONLY commit_user_mentions - ADD CONSTRAINT fk_rails_a6760813e0_tmp FOREIGN KEY (note_id) REFERENCES notes(id_convert_to_bigint) ON DELETE CASCADE NOT VALID; - ALTER TABLE ONLY vulnerability_identifiers ADD CONSTRAINT fk_rails_a67a16c885 FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE; @@ -37523,9 +37485,6 @@ ALTER TABLE ONLY project_wiki_repositories ALTER TABLE ONLY merge_request_user_mentions ADD CONSTRAINT fk_rails_c440b9ea31 FOREIGN KEY (note_id) REFERENCES notes(id) ON DELETE CASCADE; -ALTER TABLE ONLY merge_request_user_mentions - ADD CONSTRAINT fk_rails_c440b9ea31_tmp FOREIGN KEY (note_id) REFERENCES notes(id_convert_to_bigint) ON DELETE CASCADE NOT VALID; - ALTER TABLE ONLY user_achievements ADD CONSTRAINT fk_rails_c44f5b3b25 FOREIGN KEY (achievement_id) REFERENCES achievements(id) ON DELETE CASCADE; @@ -37643,9 +37602,6 @@ ALTER TABLE ONLY packages_rpm_metadata ALTER TABLE ONLY note_metadata ADD CONSTRAINT fk_rails_d853224d37 FOREIGN KEY (note_id) REFERENCES notes(id) ON DELETE CASCADE; -ALTER TABLE ONLY note_metadata - ADD CONSTRAINT fk_rails_d853224d37_tmp FOREIGN KEY (note_id) REFERENCES notes(id_convert_to_bigint) ON DELETE CASCADE NOT VALID; - ALTER TABLE ONLY merge_request_reviewers ADD CONSTRAINT fk_rails_d9fec24b9d FOREIGN KEY (merge_request_id) REFERENCES merge_requests(id) ON DELETE CASCADE; @@ -37790,9 +37746,6 @@ ALTER TABLE ONLY protected_branch_unprotect_access_levels ALTER TABLE ONLY alert_management_alert_user_mentions ADD CONSTRAINT fk_rails_eb2de0cdef FOREIGN KEY (note_id) REFERENCES notes(id) ON DELETE CASCADE; -ALTER TABLE ONLY alert_management_alert_user_mentions - ADD CONSTRAINT fk_rails_eb2de0cdef_tmp FOREIGN KEY (note_id) REFERENCES notes(id_convert_to_bigint) ON DELETE CASCADE NOT VALID; - ALTER TABLE ONLY snippet_statistics ADD CONSTRAINT fk_rails_ebc283ccf1 FOREIGN KEY (snippet_id) REFERENCES snippets(id) ON DELETE CASCADE; @@ -37943,9 +37896,6 @@ ALTER TABLE ONLY timelogs ALTER TABLE ONLY timelogs ADD CONSTRAINT fk_timelogs_note_id FOREIGN KEY (note_id) REFERENCES notes(id) ON DELETE SET NULL; -ALTER TABLE ONLY timelogs - ADD CONSTRAINT fk_timelogs_note_id_tmp FOREIGN KEY (note_id) REFERENCES notes(id_convert_to_bigint) ON DELETE SET NULL NOT VALID; - ALTER TABLE ONLY u2f_registrations ADD CONSTRAINT fk_u2f_registrations_user_id FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE; |