Add pipeline status to GraphQL projects query (!164943) · Merge requests · GitLab.org / GitLab

Peter Hegman requested to merge add-pipline-status-to-graphql-query into master Sep 03, 2024

What does this MR do and why?

Related to #482803

In &13066 we are moving Your work -> Projects to Vue and using GraphQL as the data source. We need to be able to get the pipeline status for projects from the GraphQL query but this is not possible at the moment. This MR adds a lastest_pipeline_detailed_status field that returns detailed pipeline status for the latest pipeline.

Originally I was going to just expose project.pipeline_status but then I realized the cached pipeline status does not use ci/status/pipeline/factory.rb#L9 so we would miss out on status such as Status::SuccessWarning which we need. I also realized it is more standard to expose app/graphql/types/ci/detailed_status_type.rb and that has more helpful information. Because of these requirements I settled on project.lastestPipelineDetailedStatus

To limit the number of Gitaly calls we repurpose the same cache used in the HAML version so Gitaly calls are only made if the pipeline status is not cached.

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Database review

Query 1

SQL

Click to expand

SELECT DISTINCT ON (sha, project_id)
    *
FROM
    "p_ci_pipelines"
WHERE ("p_ci_pipelines"."project_id" = 7750063
    AND "p_ci_pipelines"."sha" = 'ddc336dd390fdad2f5abb353eac4230b15f7ac98'
    OR "p_ci_pipelines"."project_id" = 7071551
    AND "p_ci_pipelines"."sha" = 'c98665605248a130e825326db99ddaf96a49b929'
    OR "p_ci_pipelines"."project_id" = 278964
    AND "p_ci_pipelines"."sha" = '946c03839c36348ece7243e1d08bf7873063fe93'
    OR "p_ci_pipelines"."project_id" = 74823
    AND "p_ci_pipelines"."sha" = 'f0e71067189b4a400434cbc74d45587886f735b8')
ORDER BY
    "p_ci_pipelines"."sha" ASC,
    "p_ci_pipelines"."project_id" ASC,
    "p_ci_pipelines"."id" DESC

Query plan

Query 2

SQL

Click to expand

SELECT
    "projects"."id",
    "projects"."name",
    "projects"."path",
    "projects"."description",
    "projects"."created_at",
    "projects"."updated_at",
    "projects"."creator_id",
    "projects"."namespace_id",
    "projects"."last_activity_at",
    "projects"."import_url",
    "projects"."visibility_level",
    "projects"."archived",
    "projects"."avatar",
    "projects"."merge_requests_template",
    "projects"."star_count",
    "projects"."merge_requests_rebase_enabled",
    "projects"."import_type",
    "projects"."import_source",
    "projects"."approvals_before_merge",
    "projects"."reset_approvals_on_push",
    "projects"."merge_requests_ff_only_enabled",
    "projects"."issues_template",
    "projects"."mirror",
    "projects"."mirror_last_update_at",
    "projects"."mirror_last_successful_update_at",
    "projects"."mirror_user_id",
    "projects"."shared_runners_enabled",
    "projects"."runners_token",
    "projects"."build_allow_git_fetch",
    "projects"."build_timeout",
    "projects"."mirror_trigger_builds",
    "projects"."pending_delete",
    "projects"."public_builds",
    "projects"."last_repository_check_failed",
    "projects"."last_repository_check_at",
    "projects"."only_allow_merge_if_pipeline_succeeds",
    "projects"."has_external_issue_tracker",
    "projects"."repository_storage",
    "projects"."repository_read_only",
    "projects"."request_access_enabled",
    "projects"."has_external_wiki",
    "projects"."ci_config_path",
    "projects"."lfs_enabled",
    "projects"."description_html",
    "projects"."only_allow_merge_if_all_discussions_are_resolved",
    "projects"."repository_size_limit",
    "projects"."printing_merge_request_link_enabled",
    "projects"."auto_cancel_pending_pipelines",
    "projects"."service_desk_enabled",
    "projects"."cached_markdown_version",
    "projects"."delete_error",
    "projects"."last_repository_updated_at",
    "projects"."disable_overriding_approvers_per_merge_request",
    "projects"."storage_version",
    "projects"."resolve_outdated_diff_discussions",
    "projects"."remote_mirror_available_overridden",
    "projects"."only_mirror_protected_branches",
    "projects"."pull_mirror_available_overridden",
    "projects"."jobs_cache_index",
    "projects"."external_authorization_classification_label",
    "projects"."mirror_overwrites_diverged_branches",
    "projects"."pages_https_only",
    "projects"."external_webhook_token",
    "projects"."packages_enabled",
    "projects"."merge_requests_author_approval",
    "projects"."pool_repository_id",
    "projects"."runners_token_encrypted",
    "projects"."bfg_object_map",
    "projects"."detected_repository_languages",
    "projects"."merge_requests_disable_committers_approval",
    "projects"."require_password_to_approve",
    "projects"."max_pages_size",
    "projects"."max_artifacts_size",
    "projects"."pull_mirror_branch_prefix",
    "projects"."remove_source_branch_after_merge",
    "projects"."marked_for_deletion_at",
    "projects"."marked_for_deletion_by_user_id",
    "projects"."autoclose_referenced_issues",
    "projects"."suggestion_commit_message",
    "projects"."project_namespace_id",
    "projects"."hidden",
    "projects"."organization_id"
FROM
    "projects"
WHERE
    "projects"."id" IN (74823, 278964, 7071551, 7750063)

Query plan

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/33198/commands/102524

Click to expand

 Index Scan using projects_pkey on public.projects  (cost=0.56..13.76 rows=4 width=857) (actual time=6.008..17.247 rows=4 loops=1)
   Index Cond: (projects.id = ANY ('{74823,278964,7071551,7750063}'::integer[]))
   Buffers: shared hit=12 read=14 dirtied=6
   WAL: records=2 fpi=2 bytes=14170
   I/O Timings: read=15.305 write=0.000
Settings: jit = 'off', random_page_cost = '1.5', seq_page_cost = '4', work_mem = '100MB', effective_cache_size = '472585MB'

Query 3

SQL

Click to expand

SELECT
    "project_features".*
FROM
    "project_features"
WHERE
    "project_features"."project_id" IN (74823, 278964, 7071551, 7750063)

Query plan

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/33198/commands/102523

Click to expand

 Index Scan using index_project_features_on_project_id_include_container_registry on public.project_features  (cost=0.57..12.56 rows=4 width=112) (actual time=4.991..14.738 rows=4 loops=1)
   Index Cond: (project_features.project_id = ANY ('{74823,278964,7071551,7750063}'::integer[]))
   Buffers: shared hit=9 read=14
   I/O Timings: read=14.637 write=0.000
Settings: effective_cache_size = '472585MB', jit = 'off', random_page_cost = '1.5', seq_page_cost = '4', work_mem = '100MB'

Comparison to current HAML view queries

To compare just the pipeline status query I changed app/views/shared/projects/_project.html.haml to:

- avatar = local_assigns[:avatar].nil? || local_assigns[:avatar]
- stars = local_assigns[:stars].nil? || local_assigns[:stars]
- forks = local_assigns[:forks].nil? || local_assigns[:forks]
- merge_requests = local_assigns[:merge_requests].nil? || local_assigns[:merge_requests]
- issues = local_assigns[:issues].nil? || local_assigns[:issues]
- pipeline_status = local_assigns[:pipeline_status].nil? || local_assigns[:pipeline_status]
- skip_namespace = local_assigns[:skip_namespace]
- compact_mode = local_assigns[:compact_mode]
- use_creator_avatar = local_assigns[:use_creator_avatar]
- show_pipeline_status_icon = pipeline_status && can?(current_user, :read_cross_project) && project.pipeline_status.has_status? && can?(current_user, :read_build, project)
- last_pipeline = last_pipeline_from_status_cache(project) if show_pipeline_status_icon
- css_controls_class = "with-pipeline-status" if show_pipeline_status_icon && last_pipeline.present?
- css_metadata_classes = "gl-flex gl-items-center !gl-text-inherit icon-wrapper has-tooltip"

%li.project-row
  .project-cell
    .gl-flex.gl-items-center.gl-mt-3
      .controls.gl-flex.gl-items-center
        - if show_pipeline_status_icon && last_pipeline.present?
          - pipeline_path = pipelines_project_commit_path(project.pipeline_status.project, project.pipeline_status.sha, ref: project.pipeline_status.ref)
          %span.icon-wrapper.pipeline-status
            = render 'ci/status/icon', status: last_pipeline.detailed_status(current_user), tooltip_placement: 'top', path: pipeline_path

HAML version

Makes a SQL call for each project causing N+1

Click to expand

SELECT
    "ci_pipelines".*
FROM
    "ci_pipelines"
WHERE
    "ci_pipelines"."project_id" = 8
    AND ("ci_pipelines"."source" IN (1, 2, 3, 4, 5, 6, 7, 8, 10, 11)
        OR "ci_pipelines"."source" IS NULL)
    AND "ci_pipelines"."sha" = '588440f66559714280628a4f9799f0c4eb880a4a'
ORDER BY
    "ci_pipelines"."id" DESC
LIMIT 1


SELECT
    "ci_pipelines".*
FROM
    "ci_pipelines"
WHERE
    "ci_pipelines"."project_id" = 7
    AND ("ci_pipelines"."source" IN (1, 2, 3, 4, 5, 6, 7, 8, 10, 11)
        OR "ci_pipelines"."source" IS NULL)
    AND "ci_pipelines"."sha" = 'c36b5a7137a25b64c35b1d5f8bfd8a7e60581bfb'
ORDER BY
    "ci_pipelines"."id" DESC
LIMIT 1

Makes a Gitaly call even if pipelines status is cached

GraphQL version

Only makes one SQL query to get project pipelines

Click to expand

SELECT
    "ci_pipelines".*
FROM
    "ci_pipelines"
WHERE ("ci_pipelines"."project_id" = 7
    AND "ci_pipelines"."sha" = 'c36b5a7137a25b64c35b1d5f8bfd8a7e60581bfb'
    OR "ci_pipelines"."project_id" = 8
    AND "ci_pipelines"."sha" = '588440f66559714280628a4f9799f0c4eb880a4a')

Only makes a Gitaly call if pipeline status is not cached

How to set up and validate locally

Open rails console (bin/rails console)
Clear pipeline cache

projects = Project.all
projects.each { |project| Gitlab::Cache::Ci::ProjectPipelineStatus.new(project).delete_from_cache }

Go to http://gdk.test:3000/-/graphql-explorer
Run this query (make sure you have a project with a pipeline)

query getProjects {
  projects(membership: true) {
    nodes {
      latestPipelineDetailedStatus {
        name
        label
      }
    }
  }
}

Copy the correlationId
Go to http://gdk.test:3000/
In the upper right corner of performance bar, click the + symbol and pasted the request ID
Choose request from dropdown
Run the query again without clearing pipeline cache to see cached result

Edited Nov 13, 2024 by Peter Hegman

Add pipeline status to GraphQL projects query

What does this MR do and why?

MR acceptance checklist

Database review

Query 1

Query 2

Query 3

Comparison to current HAML view queries

HAML version

GraphQL version

How to set up and validate locally

Merge request reports