diff options
author | Yorick Peterse <yorickpeterse@gmail.com> | 2018-01-18 14:22:41 +0100 |
---|---|---|
committer | Yorick Peterse <yorickpeterse@gmail.com> | 2018-01-18 14:22:41 +0100 |
commit | 0054d3838c07ece45d47086b7fe75aa4cf70726b (patch) | |
tree | 855d1998f033cd6f9d14c1ca08f40f930e541cf4 /lib | |
parent | 50a649530a53a04d2044f9e4c5502586a7ed0c59 (diff) | |
download | gitlab-ce-0054d3838c07ece45d47086b7fe75aa4cf70726b.tar.gz |
Reduce UPDATEs for background column type changesbackground-migration-fix
Prior to this commit we would essentially update all rows in a table,
even those where the source column (e.g. `issues.closed_at`) was NULL.
This in turn could lead to statement timeouts when using the default
batch size of 10 000 rows per job.
To work around this we don't schedule jobs for rows where the source
value is NULL. We also don't update rows where the source column is NULL
(as an extra precaution) or the target column already has a non-NULL
value. Using this approach it should be possible to update 10 000 rows
in the "issues" table in about 7.5 - 8 seconds.
Fixes https://gitlab.com/gitlab-org/gitlab-ce/issues/42158
Diffstat (limited to 'lib')
-rw-r--r-- | lib/gitlab/background_migration/copy_column.rb | 2 | ||||
-rw-r--r-- | lib/gitlab/database/migration_helpers.rb | 5 |
2 files changed, 5 insertions, 2 deletions
diff --git a/lib/gitlab/background_migration/copy_column.rb b/lib/gitlab/background_migration/copy_column.rb index a2cb215c230..ef70f37d5eb 100644 --- a/lib/gitlab/background_migration/copy_column.rb +++ b/lib/gitlab/background_migration/copy_column.rb @@ -28,6 +28,8 @@ module Gitlab UPDATE #{quoted_table} SET #{quoted_copy_to} = #{quoted_copy_from} WHERE id BETWEEN #{start_id} AND #{end_id} + AND #{quoted_copy_from} IS NOT NULL + AND #{quoted_copy_to} IS NULL SQL end diff --git a/lib/gitlab/database/migration_helpers.rb b/lib/gitlab/database/migration_helpers.rb index 592a1956ceb..dbe6259fce7 100644 --- a/lib/gitlab/database/migration_helpers.rb +++ b/lib/gitlab/database/migration_helpers.rb @@ -525,8 +525,9 @@ module Gitlab install_rename_triggers(table, column, temp_column) # Schedule the jobs that will copy the data from the old column to the - # new one. - relation.each_batch(of: batch_size) do |batch, index| + # new one. Rows with NULL values in our source column are skipped since + # the target column is already NULL at this point. + relation.where.not(column => nil).each_batch(of: batch_size) do |batch, index| start_id, end_id = batch.pluck('MIN(id), MAX(id)').first max_index = index |