Merge branch 'backstage/gb/migrate-pipeline-stages-index' into 'master'

Migrate pipeline stages indices

Closes #43009

See merge request gitlab-org/gitlab-ce!18420

2 files changed, 48 insertions, 0 deletions

diff --git a/lib/gitlab/background_migration/migrate_stage_index.rb b/lib/gitlab/background_migration/migrate_stage_index.rb
new file mode 100644
index 00000000000..f90f35a913d
--- /dev/null
+++ b/lib/gitlab/background_migration/migrate_stage_index.rb
@@ -0,0 +1,47 @@
+# frozen_string_literal: true
+# rubocop:disable Style/Documentation
+
+module Gitlab
+  module BackgroundMigration
+    class MigrateStageIndex
+      def perform(start_id, stop_id)
+        migrate_stage_index_sql(start_id.to_i, stop_id.to_i).tap do |sql|
+          ActiveRecord::Base.connection.execute(sql)
+        end
+      end
+
+      private
+
+      def migrate_stage_index_sql(start_id, stop_id)
+        if Gitlab::Database.postgresql?
+          <<~SQL
+            WITH freqs AS (
+              SELECT stage_id, stage_idx, COUNT(*) AS freq FROM ci_builds
+                WHERE stage_id BETWEEN #{start_id} AND #{stop_id}
+                  AND stage_idx IS NOT NULL
+                GROUP BY stage_id, stage_idx
+            ), indexes AS (
+              SELECT DISTINCT stage_id, last_value(stage_idx)
+                OVER (PARTITION BY stage_id ORDER BY freq ASC) AS index
+                FROM freqs
+            )
+
+            UPDATE ci_stages SET position = indexes.index
+              FROM indexes WHERE indexes.stage_id = ci_stages.id
+                AND ci_stages.position IS NULL;
+          SQL
+        else
+          <<~SQL
+            UPDATE ci_stages
+              SET position =
+                (SELECT stage_idx FROM ci_builds
+                  WHERE ci_builds.stage_id = ci_stages.id
+                  GROUP BY ci_builds.stage_idx ORDER BY COUNT(*) DESC LIMIT 1)
+            WHERE ci_stages.id BETWEEN #{start_id} AND #{stop_id}
+              AND ci_stages.position IS NULL
+          SQL
+        end
+      end
+    end
+  end
+end
diff --git a/lib/gitlab/ci/pipeline/seed/stage.rb b/lib/gitlab/ci/pipeline/seed/stage.rb
index c101f30d6e8..2b58d9863a0 100644
--- a/lib/gitlab/ci/pipeline/seed/stage.rb
+++ b/lib/gitlab/ci/pipeline/seed/stage.rb
@@ -19,6 +19,7 @@ module Gitlab
 
           def attributes
             { name: @attributes.fetch(:name),
+              position: @attributes.fetch(:index),
               pipeline: @pipeline,
               project: @pipeline.project }
           end