1 files changed, 88 insertions, 0 deletions
diff --git a/db/post_migrate/20210311045139_set_traversal_ids_for_gitlab_org_group_com.rb b/db/post_migrate/20210311045139_set_traversal_ids_for_gitlab_org_group_com.rb
new file mode 100644
index 00000000000..8cef1f1cc2b
--- /dev/null
+++ b/db/post_migrate/20210311045139_set_traversal_ids_for_gitlab_org_group_com.rb
@@ -0,0 +1,88 @@
+# frozen_string_literal: true
+
+class SetTraversalIdsForGitlabOrgGroupCom < ActiveRecord::Migration[6.0]
+  include Gitlab::Database::MigrationHelpers
+
+  DOWNTIME = false
+
+  def up
+    return unless Gitlab.com?
+
+    # namespace ID 9970 is gitlab-org on .com
+    with_lock_retries do
+      execute(<<~SQL)
+        UPDATE
+          namespaces
+        SET
+          traversal_ids = cte.traversal_ids
+        FROM
+          (
+            WITH RECURSIVE cte(id, traversal_ids, cycle) AS (
+              VALUES
+                (9970, ARRAY[9970], false)
+              UNION ALL
+              SELECT
+                n.id,
+                cte.traversal_ids || n.id,
+                n.id = ANY(cte.traversal_ids)
+              FROM
+                namespaces n,
+                cte
+              WHERE
+                n.parent_id = cte.id
+                AND NOT cycle
+            )
+            SELECT
+              id,
+              traversal_ids
+            FROM
+              cte FOR
+            UPDATE
+              ) as cte
+        WHERE
+          namespaces.id = cte.id
+          AND namespaces.traversal_ids <> cte.traversal_ids
+      SQL
+    end
+  end
+
+  def down
+    return unless Gitlab.com?
+
+    # namespace ID 9970 is gitlab-org on .com
+    with_lock_retries do
+      execute(<<~SQL)
+        UPDATE
+          namespaces
+        SET
+          traversal_ids = '{}'
+        FROM
+          (
+            WITH RECURSIVE cte(id, traversal_ids, cycle) AS (
+              VALUES
+                (9970, ARRAY[9970], false)
+              UNION ALL
+              SELECT
+                n.id,
+                cte.traversal_ids || n.id,
+                n.id = ANY(cte.traversal_ids)
+              FROM
+                namespaces n,
+                cte
+              WHERE
+                n.parent_id = cte.id
+                AND NOT cycle
+            )
+            SELECT
+              id,
+              traversal_ids
+            FROM
+              cte FOR
+            UPDATE
+              ) as cte
+        WHERE
+          namespaces.id = cte.id
+      SQL
+    end
+  end
+end