Wait longer for locks when finalizing pending detach partitions

Leaminn Marequested to merge
ci-finalize-pending-detach-longer-lock-waits into master

What does this MR do and why?

Context

On GitLab.com, ci_build_trace_metadata_102 is still pending detach. Every attempt by Database::FinalizePendingDetachPartitionsWorker to finalize it times out on the locks on p_ci_builds and p_ci_job_artifacts. Running the worker more often overnight (!263720 (merged)) didn't help, because the partitioning lock retries never wait longer than 1s. In #631599 (comment 4105946969), it's suggested we try a longer lock timeout.

This MR

  • Each round of the finalizer's lock retries now ends with longer attempts: 3s, then 4.5s. We stop just under GitLab.com's 5s deadlock_timeout, because a longer wait would cancel any regular autovacuum blocking it.
  • Writes to p_ci_builds and p_ci_job_artifacts queue behind each wait. A failing 3s or 4.5s attempt can stall them for up to about 6s or 9s, which stays under the 15s statement_timeout. The worker runs only between 23:00 and 04:45 UTC.
  • The dropper's own finalize attempt uses the new ladder too. Other WithPartitioningLockRetries callers keep the default ladder.

This gives the finalizer a chance to get past blockers that clear within a few seconds.

References

How to set up and validate locally

  1. Run the specs: 
    bin/rspec spec/lib/gitlab/database/partitioning/pending_detach_partition_finalizer_spec.rb spec/lib/gitlab/database/partitioning/with_partitioning_lock_retries_spec.rb
    All examples pass.

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.

Merge request reports