Add ci_sources-scoped Commit.latestPipeline GraphQL field

Stan Hurequested to merge
commit-status-exclude-dangling-pipelines into master

What does this MR do and why?

Adds a latestPipeline field to the Commit GraphQL type, backed by the ci_sources-scoped project.ci_pipelines (batch loaded).

Background. The commits list badge derives a commit's CI status from the newest of commit.pipelines, which resolves through Ci::PipelinesFinder onto project.all_pipelines. That association is unscoped, so it includes dangling-source pipelines (e.g. security_orchestration_policy scans). When such a pipeline is newer than the commit's real CI pipeline, the badge shows the dangling pipeline's status — for example a green check on a commit whose actual CI pipeline failed (see #579690).

Every other surface already scopes to ci_sources and ignores dangling pipelines: the commit page, the API commit status field, and Ci::Ref#update_status_by! (next if pipeline.dangling?).

This MR adds the correctly-scoped field only.

Release sequencing — this is step 1 of 2

The commit list query is switched to this field in a follow-up shipped one release later (!243958 (merged), targeting 19.3), so the field exists in the previous release (19.2) that self-managed instances upgrade from. That guarantees no stripped/blank commit statuses during a rolling upgrade, and on GitLab.com the field is deployed before any frontend depends on it. See multi-version compatibility.

  • 19.2 (this MR): add the Commit.latestPipeline field.
  • 19.3: !243958 (merged) switches the commit list badge to it.

Related

MR acceptance checklist

  • bundle exec rubocop on changed files.
  • Backend spec added (commit_type_spec.rb: dangling pipeline excluded).
  • GraphQL docs regenerated.
  • Milestone: 19.2.

Database query plans

The latestPipeline resolver batch-loads a page of commits into one query (Ci::Pipeline.latest_pipeline_per_commit(..., in_current_partition: true)), replacing the current commit-list badge path (commit.pipelines(first: 1)), which runs one query per commit (N+1). It also scopes to ci_sources (excludes dangling sources such as security_orchestration_policy), unlike the current query which excludes only child pipelines (source != 12).

Partition scoping. p_ci_pipelines is partitioned by partition_id. To avoid the cross-partition locking flagged by Database/AvoidUnpartitionedCiRelations, the resolver queries the current partition first (... AND partition_id = <current>) and only falls back to a cross-partition scan for the SHAs whose latest pipeline predates it. A newer pipeline for a SHA always has both a higher id and a partition_id >= any older pipeline's, so the latest found in the current partition is the global latest. For a page of recent commits this prunes to a single partition.

Note: the current partition was 113 at measurement time (SELECT id FROM ci_partitions WHERE status = 2;). The "After" plans below are the partition-scoped queries; the "Before" plan is the existing per-commit query, unchanged.

Run against the ci Database Lab clone. SHAs are real gitlab-org/gitlab@master commits (project 278964).

1. After — new batched query (recent page, 20 commits, one execution)

Recent commits resolve entirely in the current partition, so the resolver issues a single partition-pruned query and the fallback never runs:

EXPLAIN
SELECT DISTINCT ON (sha) *
FROM p_ci_pipelines
WHERE project_id = 278964
  AND partition_id = 113   -- current partition first
  AND (source IN (1,2,3,4,5,6,7,8,10,11) OR source IS NULL)
  AND sha IN (
    '8b1c831c415227f2f21959d5f7fa882d0fc7826b', 'a18c1077e06763d54af15431fa322aaf65188c20',
    'ad931779d90d117c841eca6cdd569f748f826254', '9117eddda7c352a9d90d5fe13f1a474db66e0587',
    'e7fb3dc4b1b38fa16b39b7b106d2298b386d50d2', 'd63b339b8bb0da0fe80471d6703dc1137f428fd3',
    '5e351c99925ebef9cc7ec2d0c706e876e91ffc34', 'e17352d7aea8f56b9194af420d758bf47beb3575',
    '65be1f73b5d57d090ee05755a90d91b0ec8e49dc', 'fe8d33147909fa97604039524916753850256907',
    '4850b39a313b7207ab0fd14d60ef71616ab6d6ff', '4c702d721652850fe7c0335d8cbc0c4cae8312b9',
    'c2214384fdadf15002fbf90bdfe1d4abe280bd0b', '423ad1ed25266b0e53d533dba54edb3a4038fbc2',
    'bcd6c028b5e4109c7e9e01d86c7eba5987d06bb9', '0b4d4c1e64add719ae85ca95fd6f72a41995ec70',
    '32448c7802dd293c86525cf4fbded284edf39b39', 'ed7c52b6539029e6598dd8a7b807527b0a06d077',
    '8f452eab2547499ea609d15b5211325affc76771', 'd777101839a910064a48705b9806e8265915eddf'
  )
ORDER BY sha ASC, id DESC;

Warm cache

https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/53584/commands/155749

Time: 4.319 ms  
  - planning: 3.928 ms  
  - execution: 0.391 ms  
    - I/O read: 0.000 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 170 (~1.30 MiB) from the buffer pool  
  - reads: 0 from the OS file cache, including disk I/O  
  - dirtied: 0  
  - writes: 0

2. Before — old per-commit query (runs once per commit; multiply cost by page size)

EXPLAIN
SELECT *
FROM p_ci_pipelines
WHERE project_id = 278964
  AND source != 12
  AND sha = '8b1c831c415227f2f21959d5f7fa882d0fc7826b'
ORDER BY id DESC
LIMIT 2;

https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/53584/commands/155709:

Time: 28.930 ms  
  - planning: 28.308 ms  
  - execution: 0.622 ms  
    - I/O read: 0.000 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 52 (~416.00 KiB) from the buffer pool  
  - reads: 0 from the OS file cache, including disk I/O  
  - dirtied: 0  
  - writes: 0

Given there are 20 queries, this extrapolates to 578.6 ms total time.

3. After — worst case: deep-history page (older partitions, Dec 2022 commits)

For a page whose commits predate the current partition, the current-partition probe returns nothing, so the resolver falls back to a single cross-partition scan for the missing SHAs (two queries total):

-- Step 1: current-partition probe (returns no rows for these old SHAs, then the fallback runs)
EXPLAIN
SELECT DISTINCT ON (sha) *
FROM p_ci_pipelines
WHERE project_id = 278964
  AND partition_id = 113
  AND (source IN (1,2,3,4,5,6,7,8,10,11) OR source IS NULL)
  AND sha IN (   
    '12c3532250b2021816798ee0b52e5122ae1cd9c6', '3855402ae370c868e507de96a1c447c36bb234b7',
    '1c27a71a92b31de10955c766eea3b936b63c0e62', 'f2b900e7d13db91bc7406f9647ec2d947bc1a863',
    '6e3584b1a0e6b48f901a17316b90ee0cb547638d', 'aabfff5a5aec0cb326a9f992aff77821ec23edfa',
    'b7fa86207fed9d044d2f4aa7c3defa45ea01cef7', '1405a7985226aabd8a99d87e6886acec52098fad',
    'ef1ebb76daf64df48e7415dd964c518e105262c8', '0f98bfa9043127fb9c62778fb5fbb5e6c63d653c',
    'fc07dd920a938bf4e44596939f24b2ac2d414b3b', '634486f64f91a65a7fb280e4b903373b7d9ca589',
    '7ddbae53d786cad923b7006d1a91116d92ee9d4a', '12ddef2470272ed3e44e0e53515d1c74099c7dc1',
    'cf87ba353a752a836288ea36f7c4076a5ff92171', 'bd88e6325d0092c2665babd8ed078f9870fbe4c3',
    '44b272890435e1e909499a4a1d30369b4a2602d2', 'c29f27cbee1cc958917b357bb333d6713ce1cedb',
    '6d3a35037a7011ba7a748346249e9d54c5112f47', '890a430cac7912fea96b37ce6c8161c747ce7b0f'
  );

-- Step 2: cross-partition fallback for the SHAs not found in step 1 (this is the plan measured below)
EXPLAIN
SELECT DISTINCT ON (sha) *
FROM p_ci_pipelines
WHERE project_id = 278964
  AND (source IN (1,2,3,4,5,6,7,8,10,11) OR source IS NULL)
  AND sha IN (
    '12c3532250b2021816798ee0b52e5122ae1cd9c6', '3855402ae370c868e507de96a1c447c36bb234b7',
    '1c27a71a92b31de10955c766eea3b936b63c0e62', 'f2b900e7d13db91bc7406f9647ec2d947bc1a863',
    '6e3584b1a0e6b48f901a17316b90ee0cb547638d', 'aabfff5a5aec0cb326a9f992aff77821ec23edfa',
    'b7fa86207fed9d044d2f4aa7c3defa45ea01cef7', '1405a7985226aabd8a99d87e6886acec52098fad',
    'ef1ebb76daf64df48e7415dd964c518e105262c8', '0f98bfa9043127fb9c62778fb5fbb5e6c63d653c',
    'fc07dd920a938bf4e44596939f24b2ac2d414b3b', '634486f64f91a65a7fb280e4b903373b7d9ca589',
    '7ddbae53d786cad923b7006d1a91116d92ee9d4a', '12ddef2470272ed3e44e0e53515d1c74099c7dc1',
    'cf87ba353a752a836288ea36f7c4076a5ff92171', 'bd88e6325d0092c2665babd8ed078f9870fbe4c3',
    '44b272890435e1e909499a4a1d30369b4a2602d2', 'c29f27cbee1cc958917b357bb333d6713ce1cedb',
    '6d3a35037a7011ba7a748346249e9d54c5112f47', '890a430cac7912fea96b37ce6c8161c747ce7b0f'
  )
ORDER BY sha ASC, id DESC;

Step 1 plan — https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/53584/commands/155750:

Time: 4.140 ms  
  - planning: 3.967 ms  
  - execution: 0.173 ms  
    - I/O read: 0.000 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 64 (~512.00 KiB) from the buffer pool  
  - reads: 0 from the OS file cache, including disk I/O  
  - dirtied: 0  
  - writes: 0

Fallback (step 2) plan — https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/53584/commands/155751:

Time: 38.727 ms  
  - planning: 35.534 ms  
  - execution: 3.193 ms  
    - I/O read: 0.000 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 1124 (~8.80 MiB) from the buffer pool  
  - reads: 0 from the OS file cache, including disk I/O  
  - dirtied: 0  
  - writes: 0

The step-1 probe adds one lightweight partition-pruned query on top of this; it only touches the current partition and returns no rows for these SHAs.

Summary

Measured on the ci Database Lab clone (gitlab-org/gitlab, 20-commit page):

Queries Planning Execution (warm) Buffers
Before — pipelines(first: 1) 20 (one per commit) ~28 ms each (~570 ms total) ~0.6 ms each ~52 × 20 ≈ 1040
After — recent page 1 ~4 ms ~0.4 ms 170
After — deep-history (probe + fallback) 2 ~4 ms + ~36 ms ~0.2 ms + ~3 ms 64 + 1124 ≈ 1188
  • N+1 collapses to one query. The 20 per-commit queries (~570 ms of planning in aggregate) become a single batched query for a recent page, or two for a deep-history page: 18–19 fewer round-trips, and one planning pass instead of one per query (Database Lab plans each of the 20; partitioned tables commonly use custom, re-planned plans, so this multiplies in the N+1 path).
  • Partition pruning is the big win. Scoping the recent-page query to the current partition (partition_id = 113) brings it to ~4 ms planning / 0.4 ms execution / 170 buffers — roughly 8× less planning and 6× fewer buffers than the same query unscoped (~34 ms / ~1069 buffers), and cheaper than even a single old per-commit query (~29 ms). No cross-partition locking on the hot path.
  • Deep-history worst case stays bounded. When a page predates the current partition, the partition-pruned probe (~4 ms, 64 buffers) misses and the resolver runs one cross-partition fallback (~36 ms planning, 3 ms execution, 1124 buffers) — total ~43 ms / ~1188 buffers for the whole page. That's about one old per-commit query's planning cost, incurred only for old pages, and the ~1188 buffers are comparable to the ~1040 the 20 per-commit queries touch in aggregate. The fallback holds up via the p_ci_pipelines_project_id_sha_idx (project_id, sha) index.
  • Planning-dominated; execution is trivial. All warm executions are sub-3 ms. The ~36 ms planning cost only appears on the cross-partition fallback (deep-history pages); recent pages avoid it entirely thanks to partition pruning. Absolute milliseconds aren't comparable across warm/cold runs — buffer counts and query count are the robust metrics.
  • Caveat: absolute milliseconds aren't comparable across warm/cold runs; buffer counts and query count are the robust metrics. The fixed ~30 ms planning is inherent to this partitioned table and is paid once by the batched query. The postgres.ai plans predate the partition_id scoping and should be re-run.
Edited by Stan Hu

Merge request reports