Sort commit statuses of a pipeline by id in the sort direction
What does this MR do and why?
Unquarantines the example API::CommitStatuses GET /projects/:id/repository/commits/:sha/statuses ci commit exists reporter user return commit statuses sort by desc pipeline_id behaves like get commit statuses returns all commit statuses (spec/requests/api/commit_statuses_spec.rb:130) and fixes its cause in lib/api/commit_statuses.rb.
Cause (flaky-testdataset-specific)
GET /projects/:id/repository/commits/:sha/statuses with order_by=pipeline_id orders only by that column:
def order_and_sort_statuses(statuses)
sort_direction = params[:sort].presence || DEFAULT_SORT_DIRECTION
order_column = ALLOWED_SORT_VALUES.include?(params[:order_by]) ? params[:order_by] : DEFAULT_SORT_VALUE
statuses.order(order_column => sort_direction)
endThere is no tie-breaker, so statuses that belong to the same pipeline come back in whatever order the query plan happens to produce. The spec expected ids ascending within each pipeline:
context 'return commit statuses sort by desc pipeline_id' do
let(:params) { { all: 1, order_by: "pipeline_id", sort: "desc" } }
let(:expected_statuses) { develop.statuses.order(id: :asc).ids + master.statuses.order(id: :asc).ids }A sequential scan plus in-memory sort keeps insertion (id) order for this tiny dataset, and the spec passes. When Postgres instead satisfies ORDER BY commit_id DESC by walking a (commit_id, …) index on p_ci_builds backwards, the ties come out in reverse id order and the eq assertion fails. The plan depends on table statistics, not on example order: in the 2025-08-11 failure the --only-failures retry (the example alone, fresh database) failed again with the same output, which is what broke master that day (rspec integration pg16 4/20, gitlab-org/quality/engineering-productivity/master-broken-incidents#15362 (closed)).
CI failure, identical shape in all three traces I checked (jobs 10982161876, 11257440076, 11605714336):
Failure/Error: expect(statuses_id).to eq(expected_statuses)
expected: [172, 174, 170, 171, 173, 175]
got: [174, 172, 175, 173, 171, 170]The two pipelines are in the right (descending) order; within each pipeline the ids are reversed.
This is also a real API bug, not only a test problem: without a deterministic tie-breaker, offset pagination of order_by=pipeline_id can skip or duplicate statuses between pages.
Fix
lib/api/commit_statuses.rb: whenorder_by=pipeline_id, break ties withidin the same direction assort(ORDER BY commit_id ASC, id ASC/commit_id DESC, id DESC).idis unique, so the order is fully determined by the data instead of the query plan.order_by=idis unchanged.- Behaviour change: with
sort=desc, statuses of the same pipeline now come back newest first, so the whole response followssort. Withsort=ascthey come back oldest first, which is also what the default plan returned before. This order was never defined: it depended on the query plan (see the revert check below). spec/requests/api/commit_statuses_spec.rb: expectiddescending within each pipeline in thesort by desc pipeline_idexample (thesort by asc pipeline_idexample already expectedidascending), remove thequarantine:metadata, and add an example that pages through thepipeline_idorder (per_page: 2) and checks every status comes back exactly once.doc/api/commits.md, theorder_byparameter description anddoc/api/openapi/openapi_v3.yaml(regenerated): say that withorder_by=pipeline_id, statuses of the same pipeline are also sorted byidin thesortdirection. The history block gets aChangedentry for 19.6.spec/support/rspec_order_todo.yml: remove the file, asscripts/rspec_check_order_dependencedoes when it passes.
Database
Changed query, generated with to_sql for order_by=pipeline_id&all=1, first page (LIMIT 20 OFFSET 0), select list shortened:
SELECT p_ci_builds.* FROM p_ci_builds
WHERE p_ci_builds.commit_id IN (
SELECT p_ci_pipelines.id FROM p_ci_pipelines
WHERE p_ci_pipelines.project_id = <project_id>
AND (p_ci_pipelines.source IN (1, 2, 3, 4, 5, 6, 7, 8, 10, 11) OR p_ci_pipelines.source IS NULL)
AND p_ci_pipelines.sha = '<sha>')
ORDER BY p_ci_builds.commit_id DESC, p_ci_builds.id DESC -- sort=asc: commit_id ASC, id ASC
LIMIT 20 OFFSET 0Plans from postgres.ai (gitlab-production-ci, EXPLAIN (ANALYZE, BUFFERS)) for gitlab-org/gitlab and a master commit from 2026-11-03 with 2 pipelines and 478 jobs. Every run returns rows.
| Query | Joe command | Execution | Buffers | Sort |
|---|---|---|---|---|
master, sort=asc: ORDER BY commit_id ASC |
576637 | 3.6 ms | 720 hit | top-N heapsort, 30 kB |
This MR, sort=asc: commit_id ASC, id ASC |
576633 / 576635 | 2.2 ms / 2.6 ms | 720 hit | top-N heapsort, 31 kB |
master, sort=desc: ORDER BY commit_id DESC |
576619 | 3.5 ms | 720 hit | top-N heapsort, 30 kB |
This MR, sort=desc: commit_id DESC, id DESC |
576617 | 3.4 ms | 720 hit | top-N heapsort, 35 kB |
This MR, sort=desc, last page (OFFSET 460) |
576621 | 3.1 ms | 720 hit | quicksort, 148 kB |
First query on a fresh clone (cold), master / this MR |
576631 / 576612 | 46 ms / 729 ms, almost all disk reads | 87 hit, 633 read | top-N heapsort |
Warm runs were taken in one sequence on the same clone, after a first query had read the rows into the cache. The cold first run is dominated by reading the same 478 rows, whichever query runs first.
The plan shape is the same as on master. master's ORDER BY commit_id already reads every status of the SHA's pipelines and sorts them in memory: no index leads with (commit_id, id) (p_ci_builds has (commit_id, stage_idx, created_at), (commit_id, status, type) and others), so it never streamed from an index. The id tie-breaker only adds a second sort key to that in-memory sort. Rows read and buffers are identical, and the extra sort cost is under 1 ms. The row set is bounded by the statuses of one commit's pipelines, and the default order_by=id path already sorts the same rows. No new index is needed.
Plan excerpt, `sort=desc` (warm, earlier run of the same `commit_id DESC, id DESC` order, command 573563, partitions without rows omitted)
Limit (actual time=2.473..2.487 rows=20 loops=1)
Buffers: shared hit=720
-> Sort (actual time=2.472..2.484 rows=20 loops=1)
Sort Key: p_ci_builds.commit_id DESC, p_ci_builds.id DESC
Sort Method: top-N heapsort Memory: 35kB
-> Nested Loop (actual time=0.637..1.670 rows=478 loops=1)
-> HashAggregate (actual time=0.393..0.400 rows=2 loops=1)
Group Key: p_ci_pipelines.id
-> Append (actual time=0.280..0.391 rows=2 loops=1)
-> Index Scan using ci_pipelines_117_project_id_sha_idx on ci_pipelines_117 (actual time=0.028..0.120 rows=2 loops=1)
Index Cond: ((project_id = <project_id>) AND ((sha)::text = '<sha>'::text))
-> Append (actual time=0.147..0.517 rows=239 loops=2)
-> Index Scan using ci_builds_117_commit_id_stage_idx_created_at_idx on ci_builds_117 (actual time=0.010..0.358 rows=239 loops=2)
Index Cond: (commit_id = p_ci_pipelines.id)Feature flag: skipped. This makes an undefined ordering deterministic; there is no new behaviour to roll out gradually.
References
Closes https://gitlab.com/gitlab-org/quality/test-failure-issues/-/issues/18755
Related: https://gitlab.com/gitlab-org/quality/test-failure-issues/-/issues/17012 (duplicate, same test hash) and the quarantine issue #525204 (104 flakiness reports between 2025-03 and 2025-10; quarantined in !209715 (merged) on 2025-10-23).
Guide: https://docs.gitlab.com/development/testing_guide/unhealthy_tests/
How to set up and validate locally
Tested against master at bd474164c255 (code with an explicit if order_column == 'pipeline_id' branch). The final code, statuses.order(order_column => sort_direction, id: sort_direction) on master at 6dee43bba3f7, produces byte-identical SQL for all four order_by/sort combinations (to_sql checked; ActiveRecord drops the duplicate id key when order_by=id), so the plans above apply unchanged. Re-run on the final code: the :130/:137/:144 loop, 15/15 with the default plan and 14/15 with the forced plan; the one failure was GRPC::DeadlineExceeded from the local test Gitaly (500 before the ordering code ran). The whole file passed every GET example in each run. The only failures were in POST /projects/:id/statuses/:sha examples, which fail the same way on master's code in this environment and are not touched by this MR. RuboCop is clean.
The CI failure needs the planner to pick a backward index scan, so locally it is forced with a small RSpec helper (not committed) that runs SET enable_seqscan = off; SET enable_sort = off on the CI connection before each example and resets it afterwards:
# tmp/force_index_scan.rb
RSpec.configure do |config|
config.before { Ci::ApplicationRecord.connection.execute('SET enable_seqscan = off; SET enable_sort = off') }
config.after { Ci::ApplicationRecord.connection.execute('RESET enable_seqscan; RESET enable_sort') }
endbin/rspec spec/requests/api/commit_statuses_spec.rb --seed 18755 --require ./tmp/force_index_scan.rbWithout a tie-breaker the result flips with the plan: on master's lib/api/commit_statuses.rb, the default plan returns ids ascending within each pipeline and the forced backward index scan returns them descending for sort=desc. With this MR both plans return id in the sort direction.
| Check | Result |
|---|---|
Whole file, --seed 18755, default plan and forced plan |
|
sort by desc pipeline_id (:130) and sort by asc pipeline_id (:137), each plan |
|
| CI seeds 27762 / 14248 / 38815 (whole file, no helper) | |
| Order dependence (forced plan): defined, reverse, random seeds 6762 / 13979 / 63010 / 34925 / 10235 | |
Pagination example (:144), each plan |
|
30-run loop of :130, :137 and :144 (forced plan) |
|
Revert check: master's lib/api/commit_statuses.rb |
sort by desc pipeline_id (:130) and the pagination example (:144) fail with the default plan. sort by asc pipeline_id (:137) passes on master too, because the natural order for commit_id ASC is already id ASC; it locks that order in. |
bundle exec rubocop lib/api/commit_statuses.rb spec/requests/api/commit_statuses_spec.rb |
MR acceptance checklist
Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.