(WIP) int4→int8: alternative background migrations

     Details: https://gitlab.com/gitlab-org/gitlab-ce/issues/58363

4 files changed, 167 insertions, 0 deletions

diff --git a/db/migrate/20190120021344_int4_pk_stage1of2_new_column.rb b/db/migrate/20190120021344_int4_pk_stage1of2_new_column.rb
new file mode 100644
index 00000000000..4004bf35251
--- /dev/null
+++ b/db/migrate/20190120021344_int4_pk_stage1of2_new_column.rb
@@ -0,0 +1,19 @@
+# frozen_string_literal: true
+
+class Int4PkStage1of2NewColumn < ActiveRecord::Migration[5.0]
+  include Gitlab::Database::MigrationHelpers
+
+  DOWNTIME = false
+
+  def up
+    add_column(:events, :id_new, :bigint)
+#    add_column(:push_event_payloads, :event_id_new, :bigint)
+#    add_column(:ci_build_trace_sections, :id_new, :bigint)
+  end
+
+  def down
+#    remove_column(:ci_build_trace_sections, :id_new)
+#    remove_column(:push_event_payloads, :event_id_new)
+    remove_column(:events, :id_new)
+  end
+end
diff --git a/db/migrate/20190120021400_int4_pk_stage1of2_index.rb b/db/migrate/20190120021400_int4_pk_stage1of2_index.rb
new file mode 100644
index 00000000000..443cfe61f07
--- /dev/null
+++ b/db/migrate/20190120021400_int4_pk_stage1of2_index.rb
@@ -0,0 +1,25 @@
+# frozen_string_literal: true
+
+class Int4PkStage1of2Index < ActiveRecord::Migration[5.0]
+  include Gitlab::Database::MigrationHelpers
+
+  DOWNTIME = false
+
+  disable_ddl_transaction!
+
+  def up
+    # Time estimate for GitLab.com: ~420s (~7 min)
+    remove_concurrent_index_by_name(:events, :events_int4_to_int8_helper)
+    add_concurrent_index(
+      :events,
+      :id_new,
+      name: :events_int4_to_int8_helper,
+      where: 'id_new is null'
+    )
+  end
+
+  def down
+    remove_concurrent_index_by_name(:events, :events_int4_to_int8_helper)
+  end
+end
+
diff --git a/db/post_migrate/20190221234852_int4_pk_stage1of2_fill_column.rb b/db/post_migrate/20190221234852_int4_pk_stage1of2_fill_column.rb
new file mode 100644
index 00000000000..198d81fc278
--- /dev/null
+++ b/db/post_migrate/20190221234852_int4_pk_stage1of2_fill_column.rb
@@ -0,0 +1,43 @@
+class Int4PkStage1of2FillColumn < ActiveRecord::Migration[5.0]
+  include Gitlab::Database::MigrationHelpers
+
+  disable_ddl_transaction! 
+
+  DOWNTIME = false
+
+  DELAY = 2.minutes.to_i
+  BATCH_SIZE = 2_500
+  BATCHES_IN_ITERATION = 10
+  CONCURRENCY = 5
+  MIGRATION = 'Int4ToInt8Update'
+
+  def up
+    say('Scheduling `Int4toInt8Update` jobs')
+
+    CONCURRENCY.times do
+      BackgroundMigrationWorker.perform_in(
+        DELAY,
+        MIGRATION,
+        [:events, :id, :id_new, DELAY, BATCH_SIZE, BATCHES_IN_ITERATION]
+      )
+    end
+
+    #queue_background_migration_jobs_by_range_at_intervals(
+    #  PushEventPayload,
+    #  PUSH_EVENT_PAYLOADS_MIGRATION,
+    #  DELAY,
+    #  batch_size: BATCH_SIZE
+    #)
+
+    #queue_background_migration_jobs_by_range_at_intervals(
+    #  CiBuildTraceSection,
+    #  CI_BUILD_TRACE_SECTIONS_MIGRATION,
+    #  DELAY,
+    #  batch_size: BATCH_SIZE
+    #)
+  end
+
+  def down
+    # No op
+  end
+end
diff --git a/lib/gitlab/background_migration/int4_to_int8_update.rb b/lib/gitlab/background_migration/int4_to_int8_update.rb
new file mode 100644
index 00000000000..aea7cc676be
--- /dev/null
+++ b/lib/gitlab/background_migration/int4_to_int8_update.rb
@@ -0,0 +1,80 @@
+# frozen_string_literal: true
+
+module Gitlab
+  module BackgroundMigration
+    class Int4ToInt8Update
+      def log(message)
+        Rails.logger.info("#{self.class.name} - #{message}")
+      end
+
+      def perform(table, old_column, new_column, delay, batch_size, batches_per_iteration)
+        if Database.mysql?
+          raise 'Int4ToInt8Update is not supported for MySQL'
+        end
+
+        rescheduling_needed = true
+
+        batches_per_iteration.times do
+          result = ActiveRecord::Base.connection.execute <<~SQL.strip_heredoc
+            with rows_to_update as (
+              select #{old_column}
+              from #{table}
+              where #{new_column} is null
+              order by #{old_column}
+              limit #{batch_size}
+              for update skip locked
+            ), upd as (
+              update #{table}
+              set #{new_column} = #{old_column}
+              where #{old_column} in (select #{old_column} from rows_to_update)
+              returning id
+            )
+            select
+              count(*) as cnt,
+              min(id) as min_id,
+              max(id) as max_id
+            from upd;
+          SQL
+
+          if result[0]['cnt'] == 0 then
+            # Nothing left. So it's time to stop the processing.
+            rescheduling_needed = false
+            break
+          end
+
+          log("#{table}.#{old_column} = #{result[0]['min_id']}..#{result[0]['max_id']}")
+        end
+
+        if rescheduling_needed then
+          BackgroundMigrationWorker.perform_in(
+            delay,
+            "Int4ToInt8Update",
+            [table, old_column, new_column, delay, batch_size, batches_per_iteration]
+          )
+        end
+      end
+    end
+
+    #class Int4toInt8UpdatePushEventPayloads
+    #  def perform(start_id, stop_id)
+    #    ActiveRecord::Base.connection.execute <<~SQL
+    #      update push_event_payloads
+    #      set event_id_new = event_id
+    #      where id between #{start_id} and #{end_id}
+    #    SQL
+    #  end
+    #end
+
+    #class Int4toInt8UpdateCiBuildTraceSections
+    #  def perform(start_id, stop_id)
+    #    ActiveRecord::Base.connection.execute <<~SQL
+    #      update ci_build_trace_sections
+    #      set id_new = id
+    #      where id between #{start_id} and #{end_id}
+    #    SQL
+    #  end
+    #end
+
+  end
+end
+