Project imported by file export/direct transfer missing issues due to PG timeout

Summary

Project import using file export may not have issues imported.

The root of the problem lies in fetching all issues from all projects in the root group before performing the import. The more projects and issues there are in the group namespace, this can result in timeout before it gets completed.

Imports via direct transfer uses the same logic so it is also impacted.

Steps to reproduce

Example Project

What is the current bug behavior?

Issues in the project file export are missing in the imported project.

What is the expected correct behavior?

All issues in the project file export imported successfully.

Relevant logs and/or screenshots

• Kibana logs
• Sentry logs

Those with access to the logs will find the error PG::QueryCanceled: ERROR: canceling statement due to statement timeout.

json.exception.sql

/*application:sidekiq,correlation_id:47b9dd3d896d06db48b82d3050218456,jid:154f5e9f37d4bb2f98595c87,endpoint_id:RepositoryImportWorker,db_config_name:main*/ SELECT "issues"."id", "issues"."title", "issues"."author_id", "issues"."project_id", "issues"."created_at", "issues"."updated_at", "issues"."description", "issues"."milestone_id", "issues"."iid", "issues"."updated_by_id", "issues"."weight", "issues"."confidential", "issues"."moved_to_id", "issues"."due_date", "issues"."lock_version", "issues"."title_html", "issues"."description_html", "issues"."time_estimate", "issues"."relative_position", "issues"."service_desk_reply_to", "issues"."cached_markdown_version", "issues"."last_edited_at", "issues"."last_edited_by_id", "issues"."discussion_locked", "issues"."closed_at", "issues"."closed_by_id", "issues"."state_id", "issues"."duplicated_to_id", "issues"."promoted_to_epic_id", "issues"."health_status", "issues"."external_key", "issues"."sprint_id", "issues"."blocking_issues_count", "issues"."upvotes_count", "issues"."work_item_type_id", "issues"."namespace_id", "issues"."start_date", "issues"."imported" FROM "issues" WHERE "issues"."project_id" IN (SELECT "projects"."id" FROM "projects" WHERE "projects"."namespace_id" IN (SELECT namespaces.traversal_ids[array_length(namespaces.traversal_ids, $1)] AS id FROM "namespaces" WHERE "namespaces"."type" = $2 AND (traversal_ids @> ($3)))) ORDER BY "issues"."id" ASC LIMIT $2493941 

Timeout is happening when trying to calculate relative position of the issue: https://gitlab.com/gitlab-org/gitlab/-/blob/27715f2f0248cbfdb8f93131b98633bc17bf1fc6/lib/gitlab/import_export/project/relation_factory.rb#L238

Output of checks

This bug happens on GitLab.com

Workaround

Import projects via file export into another root group that does not have many issues, and then perform a project transfer to the intended namespace.

Possible fixes

Slack discussion (90 days retention period):

::RelativePositioning.mover.context(Issue.in_projects(@importable.root_ancestor.all_projects).limit(1).first)&.max_relative_position

Possibly related

Please check if GitLab Migration - canceling statement due to s... (#382091 - closed) is the same problem and so, if fixing this issue should also fix GitLab Migration - canceling statement due to s... (#382091 - closed).

Please also check if Projects created from template fail to import i... (#497255 - closed) is related.