更新
更旧
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
# frozen_string_literal: true
require 'rails/generators'
require 'rails/generators/active_record'
require 'rails/generators/active_record/migration/migration_generator'
require_relative 'helpers/file_helper'
require_relative '../spec/support/helpers/database/duplicate_indexes'
module Keeps
# This is an implementation of a ::Gitlab::Housekeeper::Keep. This keep will look for duplicated indexes
# and it will generate the corresponding files to have the index dropped. For each index to be dropped, the Keep will
# generate a new migration and its schema migration file. It also updates the `db/schema.sql` for each migration file.
#
# This keep uses the test databases to generate a updated version of the schema. Each time the keep is invoked it will
# recreate the `gitlabhq_test` and `gitlabhq_test_ci` databases.
#
# You can run it individually with:
#
# ```
# bundle exec gitlab-housekeeper -d \
# -k Keeps::RemoveDuplicatedIndexes
# ```
class RemoveDuplicatedIndexes < ::Gitlab::Housekeeper::Keep
MIGRATION_TEMPLATE = 'generator_templates/active_record/migration/'
DEFAULT_REVIEWER_GROUP = 'database'
def initialize
::Gitlab::Application.load_tasks
::ActiveRecord::Generators::MigrationGenerator.source_root(MIGRATION_TEMPLATE)
@indexes_to_drop = {}
reset_db
migrate
load_indexes_to_drop
super
end
def each_change
indexes_to_drop.each do |table_name, indexes|
change = build_change(table_name, indexes)
change.changed_files = []
indexes.each do |index_to_drop, _|
migration_file, migration_number = generate_migration_file(table_name, index_to_drop)
change.changed_files << migration_file
change.changed_files << Pathname.new('db').join('schema_migrations', migration_number).to_s
end
migrate
change.changed_files << Pathname.new('db').join('structure.sql').to_s
yield(change)
reset_db
end
end
private
attr_reader :indexes_to_drop
def load_indexes_to_drop
establish_test_db_connection do |connection|
connection.tables.sort.each do |table|
# Skip partitioned tables for now
next if Gitlab::Database::PostgresPartition.partition_exists?(table)
result = process_result(Database::DuplicateIndexes.new(table, connection.indexes(table)).duplicate_indexes)
next if result.empty?
indexes_to_drop[table] = result
end
end
end
def build_change(table_name, indexes)
change = ::Gitlab::Housekeeper::Change.new
change.title = "Remove duplicated index from #{table_name}".truncate(70, omission: '')
change.identifiers = [self.class.name.demodulize, table_name]
change.labels = labels(table_name)
change.reviewers = pick_reviewers(table_name, change.identifiers).uniq
removes_section = indexes.map do |index_to_drop, matching_indexes|
matching_indexes_table = matching_indexes.map do |idx|
<<-MARKDOWN.strip
| `#{idx.name}` | #{idx.columns.map { |col| "`#{col[:name]} #{col[:order]}`" }.join(', ')} |
MARKDOWN
end
<<~MARKDOWN.strip
Drop `#{index_to_drop.name}` as it's already covered by:
| Index | Columns |
| ----- | ------ |
#{matching_indexes_table.join("\n")}
MARKDOWN
end
change.description = <<~MARKDOWN.chomp
## What does this MR do and why?
Remove duplicated index from `#{table_name}` table.
### It removes:
#{removes_section.join("\n")}
It is possible that this MR will still need some changes to drop the index from the database.
Currently, the `gitlab-housekeeper` is not always capable of removing all references, so you must check the diff and pipeline failures to confirm if there are any issues.
Ensure that the index exists in the production database by checking Joe Bot trough https://console.postgres.ai/gitlab.
If the index was already removed or if the index it's being removed in another merge request, consider closing this merge request.
MARKDOWN
change
end
def process_result(duplicated_indexes)
duplicates_map = Hash.new { |h, k| h[k] = [] }
duplicated_indexes.each do |index, duplicates|
duplicates.each do |duplicate|
duplicates_map[duplicate] << index
end
end
duplicates_map
end
def generate_migration_file(table_name, index_to_drop)
migration_name = "drop_#{index_to_drop.name}".truncate(100, omission: '')
generator = ::ActiveRecord::Generators::MigrationGenerator.new([migration_name])
migration_content = <<~RUBY.strip
disable_ddl_transaction!
TABLE_NAME = :#{table_name}
INDEX_NAME = :#{index_to_drop.name}
COLUMN_NAMES = #{index_to_drop.columns.map { |col| col[:name].to_sym }}
def up
remove_concurrent_index_by_name(TABLE_NAME, INDEX_NAME)
end
def down
add_concurrent_index(TABLE_NAME, COLUMN_NAMES, name: INDEX_NAME)
end
RUBY
migration_file = generator.invoke_all.first
file_helper = ::Keeps::Helpers::FileHelper.new(migration_file)
file_helper.replace_method_content(:change, migration_content, strip_comments_from_file: true)
::Gitlab::Housekeeper::Shell.execute('rubocop', '-a', migration_file)
[migration_file, generator.migration_number]
end
def pick_reviewers(table_name, identifiers)
table_info = Gitlab::Database::Dictionary.entries.find_by_table_name(table_name)
table_info.feature_categories.map do |feature_category|
group = groups_helper.group_for_feature_category(feature_category)
group = groups_helper.group_for_feature_category(DEFAULT_REVIEWER_GROUP) if group['backend_engineers'].empty?
groups_helper.pick_reviewer(group, identifiers)
end
end
def labels(table_name)
table_info = Gitlab::Database::Dictionary.entries.find_by_table_name(table_name)
group_labels = table_info.feature_categories.map do |feature_category|
groups_helper.group_for_feature_category(feature_category)['label']
end
group_labels + %w[maintenance::scalability type::maintenance Category:Database]
end
def establish_test_db_connection
# rubocop:disable Database/EstablishConnection -- We should use test database only
yield ActiveRecord::Base.establish_connection(ActiveRecord::Base.configurations.find_db_config('test')).connection
# rubocop:enable Database/EstablishConnection
end
def reset_db
ApplicationRecord.clear_all_connections!
::Gitlab::Housekeeper::Shell.execute({ 'RAILS_ENV' => 'test' }, 'rails', 'db:reset')
end
def migrate
::Gitlab::Housekeeper::Shell.execute({ 'RAILS_ENV' => 'test' }, 'rails', 'db:migrate')
end
def groups_helper
@groups_helper ||= ::Keeps::Helpers::Groups.new
end
end
end