An error occurred while getting merge request counts or listing MRs

Summary

When users view the merge requests list page, they sometimes see An error occurred while getting merge request counts as the query for getting the counts we display in that page is failing.

Sometimes the list of MRs fails to load as well.

Filtering by Approver can also lead to failures.

This occurs at both project and group levels.

Steps to reproduce

View the MR list page for a large group. See that it fails.

What is the current bug behavior?

It sometimes fail and An error occurred while getting merge request counts is shown.

What is the expected correct behavior?

The query should succeed and the error shouldn't show.

Relevant logs and/or screenshots

From @j.castillo and @sataylor in #555364 (comment 2681698404):

Support tickets:

• https://gitlab.zendesk.com/agent/tickets/647975
• https://gitlab-federal-support.zendesk.com/agent/tickets/13214 (requires US GOV access)
  • https://gitlab.lightning.force.com/lightning/r/Account/0016100001cEfnzAAC/view

Sample queries:

SELECT COUNT(*) AS "count_all", "merge_requests"."id" AS "merge_requests_id" FROM "merge_requests" INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id" LEFT JOIN project_features ON projects
.id = project_features.project_id WHERE (NOT EXISTS (SELECT 1 FROM "banned_users" WHERE (merge_requests.author_id = banned_users.user_id))) AND "projects"."namespace_id" IN (SELECT "namespaces"."i

SELECT COUNT(*) AS "count_all", "merge_requests"."id" AS "merge_requests_id" FROM "merge_requests" INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE (NOT EXISTS (SELECT 1 FROM "banned_users" WHERE (merge_requests.author_id = banned_users.user_id))) AND "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" = 3) cached_query),
    (SELECT ids FROM (SELECT ARRAY_AGG("namespaces"."id") AS ids FROM (SELECT namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{3}'))) namespaces) consistent_query))
) AS namespaces(id)
) AND (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 100 AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 20)) OR projects.visibility_level IN (10,20)) AND ("project_features"."merge_requests_access_level" IS NULL OR "project_features"."merge_requests_access_level" IN (20,30) OR ("project_features"."merge_requests_access_level" = 10 AND EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 100 AND (project_authorizations.project_id = project_features.project_id) AND (project_authorizations.access_level >= 20)))) AND "merge_requests"."state_id" = 3 AND "projects"."archived" = FALSE AND NOT (EXISTS (SELECT "label_links"."label_id" FROM "label_links" WHERE "label_links"."target_type" = 'MergeRequest' AND "label_links"."target_id" = "merge_requests"."id" AND "label_links"."label_id" IN (1125, 1124, 1123, 1122, 1022, 1522, 1121, 1120, 1119, 1118, 1117, 1116, 1115, 1114, 1113, 1128, 1112, 1111, 1110, 1109, 1108, 1107, 1106, 998, 1105, 1104, 1103, 1102, 1101, 1100, 1099, 1098, 997, 1097, 1096, 1095, 1094, 1093, 1092, 1091, 1090, 1089, 1088, 1087, 1086, 1085, 1084, 1083, 1082, 1081, 1080, 1079, 1078, 1077, 1076, 1075, 1074, 1073, 1072, 1071, 1069, 1068, 1067, 1066, 1065, 1064, 1063, 1062, 1061, 1060, 1059, 1058, 1057, 1056, 1055, 1054, 1053, 1052, 1051, 1050, 1049, 1048, 1047, 1046, 1045, 1044, 1043, 1488, 1042, 1041, 1040, 1039, 1038, 1021, 1020, 1019, 1018, 1017, 1016, 1015, 1014, 1013, 1012, 1037, 1011, 1036, 1010, 1035, 1009, 1034, 1033, 1008, 1032, 1031, 1030, 1029, 1028, 1007, 1027, 1006, 1005, 1004, 1026, 1025, 1024, 1598, 1023, 1556, 1564, 1635, 1567, 1878, 2080, 1877, 1641, 1876, 1875, 1874, 1873, 1872, 1832, 1871, 2074, 2075))) AND "merge_requests"."id" IN (SELECT "merge_requests"."id" FROM ((SELECT "merge_requests"."id", "merge_requests"."target_branch", "merge_requests"."source_branch", "merge_requests"."source_project_id", "merge_requests"."author_id", "merge_requests"."assignee_id", "merge_requests"."title", "merge_requests"."created_at", "merge_requests"."updated_at", "merge_requests"."milestone_id", "merge_requests"."merge_status", "merge_requests"."target_project_id", "merge_requests"."iid", "merge_requests"."description", "merge_requests"."updated_by_id", "merge_requests"."merge_error", "merge_requests"."merge_params", "merge_requests"."merge_when_pipeline_succeeds", "merge_requests"."merge_user_id", "merge_requests"."merge_commit_sha", "merge_requests"."in_progress_merge_commit_sha", "merge_requests"."lock_version", "merge_requests"."title_html", "merge_requests"."description_html", "merge_requests"."time_estimate", "merge_requests"."cached_markdown_version", "merge_requests"."last_edited_at", "merge_requests"."last_edited_by_id", "merge_requests"."merge_jid", "merge_requests"."rebase_commit_sha", "merge_requests"."discussion_locked", "merge_requests"."latest_merge_request_diff_id", "merge_

Possible fixes

TBD. Needs investigation.

Ideas:

1. Collaborate with the DB team to improve the performance of queries related to the Approver filter: #563367 (comment 2748402029).
2. Reduce the amount of data fetched and displayed on the MR list.

Workarounds

Applying certain filters can help.

Patch release information for backports

If the bug fix needs to be backported in a patch release to a version under the maintenance policy, please follow the steps on the patch release runbook for GitLab engineers.

Refer to the internal "Release Information" dashboard for information about the next patch release, including the targeted versions, expected release date, and current status.