Fix backfill migration overlap on empty CI partitions

What does this MR do and why?

The post-deploy migration BackfillPipelinesIdRangeOnCiPartitions (introduced in 19.0, db/post_migrate/20260512150000_backfill_pipelines_id_range_on_ci_partitions.rb) can abort with PG::ExclusionViolation on the check_ci_partitions_pipelines_id_range_no_overlap constraint.

It happens when a ci_partitions row between the first partition and the current one has no rows in p_ci_builds. The migration set each partition's upper bound to the next partition's MIN(commit_id). If that next partition is empty, the bound is NULL, so a non-current partition ends up with an unbounded range like [1, ). That collides with the current partition's own unbounded range, and PostgreSQL rejects the insert.

Because the migration runs in a transaction, db:migrate aborts and every later migration stays pending. Retrying does not help, since ranges are recomputed from the same data each time.

This was observed on a 19.0.8 upgrade from 18.11.11 (Omnibus, external PostgreSQL, single database). Low-CI-usage instances are the likely population: any where a default partition (100, 101, 102) never got a build.

Closes #627999 (closed)

How the fix works

The fix is simpler than the earlier approach: skip the one write that would overlap. When a non-current partition's next neighbor is empty (NULL upper bound), we do not write a range for it at all. The current partition still gets its unbounded range as before.

This is safe because a partition with a NULL pipelines_id_range is simply left out of Gitlab::Ci::Pipeline::PartitionCache (it filters where.not(pipelines_id_range: nil)). When a lookup finds no cached range covering an id, Gitlab::Ci::Pipeline::ByIdLookup falls back to a full-table scan (scope.find_by(id: id)) and still returns the right pipeline. So the only cost of a missing range is losing partition pruning for that partition, not correctness. There is no need to compute a gap-spanning bound; the app works fine without a range there.

Before:

Partition.order(:id).to_a.push(Partition.new).each_cons(2) do |partition, next_partition|
  min = Build.where(partition_id: partition.id).minimum(:commit_id)
  next unless min

  upper = Build.where(partition_id: next_partition.id).minimum(:commit_id)
  partition.update!(pipelines_id_range: min...upper)
  break if partition.current?
end

This walks partitions in pairs. Each range runs from the partition's own lowest build commit_id up to (but not including) the next partition's lowest. A Partition.new sentinel is appended so the current partition pairs with an empty upper and gets an unbounded [min, ). upper comes from next_partition, so if that neighbor is empty, upper is nil and a non-current partition wrongly becomes unbounded.

Failing case, three partitions, middle one empty:

id	status	builds (commit_id)
100	active	1
101	active	none
102	current	2

• (100,101): min=1, upper=MIN(101)=nil would write [1, ) on 100. Unbounded, and 100 is not current.
• (101,102): MIN(101)=nil, skipped by next unless min.
• (102, sentinel): 102 is current, upper=nil, writes [2, ).

[2, ) overlaps [1, ), so the constraint rejects it and the migration rolls back.

After:

Partition.order(:id).to_a.push(Partition.new).each_cons(2) do |partition, next_partition|
  min = Build.where(partition_id: partition.id).minimum(:commit_id)
  next unless min

  upper = Build.where(partition_id: next_partition.id).minimum(:commit_id)

  next if upper.nil? && !partition.current?

  partition.update!(pipelines_id_range: min...upper)
  break if partition.current?
end

The only change is the next if upper.nil? && !partition.current? guard. For the same data: 100 is skipped and left NULL, 101 is skipped (no builds), 102 (current) gets [2, ). No two ranges overlap.

Note for reviewers: this edits a migration that shipped in 19.0, which is normally avoided. It is safe here: on affected instances the migration aborted inside its own transaction, so nothing was committed, and the corrected version just succeeds on the next db:migrate. Instances that already ran it cleanly recompute the same ranges. No new db/schema_migrations checksum is needed because only the migration body changed, not its timestamp.

How to test

A migration spec example sets up a populated partition, an empty intermediate partition, and a current populated partition. Against the old code it reproduces the PG::ExclusionViolation; with the fix it passes and the first partition is left without a range.

Verified locally with:

RUN_ALL_MIGRATION_TESTS=1 bundle exec rspec spec/migrations/20260512150000_backfill_pipelines_id_range_on_ci_partitions_spec.rb

Related issues

#608165 (closed) and #618475 are related but are being fixed separately at runtime. This migration is left unchanged with respect to those two issues.