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) is the same problem and so, if fixing this issue should also fix GitLab Migration - canceling statement due to s... (#382091).

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

added reproduced on GitLab.com typebug labels

added groupimport and integrate label

added devopsmanage sectioncore platform labels

added Export/Import label

changed title from Project import by file is missing issues to Project imported by file export is missing issues

changed the description

added customer label

changed title from Project imported by file export is missing issues to Project imported by file export/direct transfer missing issues due to PG timeout

changed the description

added Category:Importers Importer:Direct Transfer Importer:Project Export/Import labels

Is this issue related? GitLab Migration - canceling statement due to s... (#382091)

Certainly looks very similar

added to epic &6629

changed milestone to %17.1

added ready for next refinement label

removed ready for next refinement label

All customer typebug issues need to have a proper severity label set. Please add a severity label, remove the automation:customer-bug-missing-labels label, and then reply to this comment briefly explaining your reasoning for providing this severity.

If you are not the DRI for this area and would like help determining the best severity, please @ the appropriate person for assistance.

This message was generated automatically. You're welcome to improve it.

added automation:customer-bug-missing-labels label

added ready for next refinement label

mentioned in issue #460223 (closed)

mentioned in issue gitlab-org/quality/triage-reports#17543 (closed)

changed the description

added bugperformance label

added workflowready for development label

set weight to 2

removed ready for next refinement label

mentioned in issue gitlab-org/manage/general-discussion#17673

changed the description

mentioned in issue #382091

added Deliverable milestonep1 labels

Setting health status to on track as the milestone has just begun.

Issue participants are welcome to override this by setting the health status to another value.

changed health status to on track

added milestonep2 label and removed milestonep1 label

added milestonep3 label and removed milestonep2 label

removed Deliverable label

added milestonep4 label and removed milestonep3 label

mentioned in issue gitlab-org/quality/triage-reports#17995 (closed)

As there's workaround for this #458367, I'm deprioritizing in favour of improved user contribution mapping.

/cc @carlad-gl

changed milestone to %Backlog

removed milestonep4 label

mentioned in issue gitlab-org/quality/triage-reports#18532 (closed)

added severity3 label

removed automation:customer-bug-missing-labels label

An example of an affected SQL:

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"
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" = 'Group'
            AND (traversal_ids @> ('{52862669}'))))
ORDER BY "issues"."id" ASC
LIMIT 1

I've been unable to run an EXPLAIN in postgres.ai on it (it's been running for 4 hours so far), but a PLAN generated:

Limit  (cost=3567.55..558540.06 rows=1 width=1481)
  ->  Nested Loop Semi Join  (cost=3567.55..83298048037.85 rows=150094 width=1481)
        Join Filter: (issues.project_id = projects.id)
        ->  Index Scan using index_issues_on_id_and_weight on issues  (cost=0.57..22633090.43 rows=94763056 width=1481)
        ->  Materialize  (cost=3566.98..10557.48 rows=58585 width=4)
              ->  Nested Loop  (cost=3566.98..10264.56 rows=58585 width=4)
                    ->  HashAggregate  (cost=3566.41..3587.41 rows=2100 width=28)
                          Group Key: namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)]
                          ->  Bitmap Heap Scan on namespaces  (cost=168.62..3561.16 rows=2100 width=28)
                                Recheck Cond: ((traversal_ids @> '{52862669}'::integer[]) AND ((type)::text = 'Group'::text))
                                ->  Bitmap Index Scan on index_namespaces_on_traversal_ids_for_groups  (cost=0.00..168.10 rows=2100 width=0)
                                      Index Cond: (traversal_ids @> '{52862669}'::integer[])
                    ->  Index Only Scan using index_projects_on_namespace_id_and_id on projects  (cost=0.57..2.90 rows=28 width=8)
                          Index Cond: (namespace_id = (namespaces.traversal_ids)[array_length(namespaces.traversal_ids, 1)])

The Demo Architecture team is planning on using Direct Transfer to drive workshops and demos into the https://gitlab.com/gitlab-learn-labs/ which inherently has quite a few issues. It would be great to see a fix for this once Direct Transfer is fully re-enabled, @m_frankiewicz .

@poffey21 you mean issues with Direct transfer? Do you have specific (existing) bugs in mind?

Hi @m_frankiewicz the bug we ran into was related to an indexing problem, where because Learn Labs had so much content it was taking an extremely long time to transfer in (which I believe is the same problem this issue covers). @wortschi had helped me out when we first ran into it

Thanks @lfstucker, so it's this issue. Ok. We should be able to work on it after user contribution mapping is done. So a couple of milestones from now.

added gitlab-org#6629 as parent epic

changed milestone to %17.6

marked this issue as related to #497255 (closed)

mentioned in issue #497255 (closed)

changed the description

marked #497255 (closed) as a duplicate of this issue

changed milestone to %17.8

removed from epic &6629

added gitlab-org#11398 as parent epic

Added to Direct Transfer General Availability (&11398) epic

changed milestone to %17.9

changed milestone to %17.10

mentioned in issue #509325

mentioned in issue #504039

mentioned in issue gitlab-org/foundations/general-discussion#17694

mentioned in issue gitlab-org/foundations/general-discussion#17696

changed milestone to %17.11

mentioned in incident gitlab-com/gl-infra/production#19364 (closed)

On this incident log, a different SQL failed

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"."correct_work_item_type_id"
FROM
    "issues"
WHERE
    "issues"."project_id" IN (
        SELECT
            "projects"."id"
        FROM
            "projects"
        WHERE
            "projects"."namespace_id" IN (
                SELECT
                    "namespaces"."id"
                FROM
                    UNNEST(COALESCE((
                            SELECT
                                ids
                            FROM (
                                SELECT
                                    "namespace_descendants"."self_and_descendant_group_ids" AS ids
                                FROM "namespace_descendants"
                            WHERE
                                "namespace_descendants"."outdated_at" IS NULL
                                AND "namespace_descendants"."namespace_id" = $1) cached_query), (
                            SELECT
                                ids
                            FROM (
                                SELECT
                                    ARRAY_AGG("namespaces"."id") AS ids
                                FROM (
                                    SELECT
                                        namespaces.traversal_ids[array_length(namespaces.traversal_ids, $2)] AS id
                                    FROM "namespaces"
                                WHERE
                                    "namespaces"."type" = $3
                                    AND (traversal_ids @> ($4))) namespaces) consistent_query))) AS namespaces (id)))
    ORDER BY
        "issues"."id" ASC
    LIMIT $5

Stacktrace

[
 lib/gitlab/import_export/project/relation_factory.rb:270:in `max_relative_position',
 lib/gitlab/import_export/project/relation_factory.rb:264:in `compute_relative_position',
 lib/gitlab/import_export/project/relation_factory.rb:208:in `setup_work_item',
 lib/gitlab/import_export/project/relation_factory.rb:115:in `setup_models',
 ee/lib/ee/gitlab/import_export/project/relation_factory.rb:69:in `setup_models',
 lib/gitlab/import_export/base/relation_factory.rb:88:in `create',
 lib/gitlab/import_export/project/relation_factory.rb:91:in `create',
 lib/gitlab/import_export/base/relation_factory.rb:38:in `create',
 lib/gitlab/import_export/group/relation_tree_restorer.rb:367:in `persist_relation',
 ee/lib/bulk_imports/epic_object_creator.rb:20:in `persist_relation',
 lib/gitlab/import_export/group/relation_tree_restorer.rb:239:in `build_relation',
 lib/gitlab/import_export/group/relation_tree_restorer.rb:96:in `process_relation_item!',
 lib/gitlab/import_export/group/relation_tree_restorer.rb:91:in `block in process_relation!',
 lib/gitlab/import_export/json/ndjson_reader.rb:42:in `<<',
 lib/gitlab/import_export/json/ndjson_reader.rb:42:in `block (2 levels) in consume_relation',
 lib/gitlab/import_export/json/ndjson_reader.rb:41:in `foreach',
 lib/gitlab/import_export/json/ndjson_reader.rb:41:in `with_index',
 lib/gitlab/import_export/json/ndjson_reader.rb:41:in `block in consume_relation',
 lib/gitlab/import_export/group/relation_tree_restorer.rb:90:in `each',
 lib/gitlab/import_export/group/relation_tree_restorer.rb:90:in `each',
 lib/gitlab/import_export/group/relation_tree_restorer.rb:90:in `process_relation!',
 lib/gitlab/import_export/group/relation_tree_restorer.rb:85:in `block in create_relations!',
 lib/gitlab/import_export/group/relation_tree_restorer.rb:84:in `each',
 lib/gitlab/import_export/group/relation_tree_restorer.rb:84:in `create_relations!',
 lib/gitlab/import_export/group/relation_tree_restorer.rb:37:in `block in restore',
 lib/gitlab/import_export/group/relation_tree_restorer.rb:56:in `block (3 levels) in bulk_insert_without_cache_or_touch',
 ]