Retry dropping tmp bigint FK for deployments with longer locks

What does this MR do and why?

A bigint conversion on deployments left behind a temporary foreign key, fk_b9a3851b82_tmp, on deployments.project_id_convert_to_bigint referencing projects.id with ON DELETE CASCADE. Migration 20260731085558 (DropTmpBigintIndexesAndFkForDeploymentsPhaseTwo) was supposed to drop it along with the tmp indexes. The index drops landed; the foreign key removal did not.

While the foreign key exists, every project delete fires the cascade referential integrity trigger against deployments.project_id_convert_to_bigint. That column has no index, so the check is a sequential scan.

Two attempts to remove it have already failed, and both are recorded in schema_migrations, so neither will run again:

• 20260805084521 (RemoveTmpBigintFkForDeploymentsPhaseTwo) is guarded by Gitlab::Database::MigrationHelpers::WraparoundAutovacuum#can_execute_on?. A wraparound prevention vacuum was running on deployments, so the migration returned early. An early return counts as a successful run.
• 20260810085131 (RemoveTmpBigintFkForDeploymentsPhaseTwoRetry) exhausted its lock retries and raised.

This MR adds a third attempt and reduces 20260810085131 to a bare no-op: milestone '19.3', empty up and down, and a comment explaining what happened and pointing at the new retry. Its WraparoundAutovacuum include and disable_ddl_transaction! are removed.

db/structure.sql is unchanged.

The new migration

db/post_migrate/20260811082241_remove_tmp_bigint_fk_for_deployments_phase_two_retry_two.rb defines RemoveTmpBigintFkForDeploymentsPhaseTwoRetryTwo. It is a verbatim copy of retry 2's body with one change: it passes an explicit timing_configuration to with_lock_retries.

Gitlab::Database::WithLockRetries::DEFAULT_TIMING_CONFIGURATION tops out at a 2 second lock_timeout per attempt, which was not enough to acquire ACCESS EXCLUSIVE on both projects and deployments. The new configuration escalates lock_timeout through 1s, 2s, 3s, 5s, and 7s with a 1 minute sleep between attempts, looped 3 times, for 15 attempts total.

Worth flagging for reviewers: worst case is roughly 15 minutes (about 14 minutes of sleep plus 54 seconds of lock waits) before AttemptsExhaustedError is raised. That is above the usual ~10 minute guidance for post-deployment migrations, and it is deliberate.

Everything else carries over from retry 2: the WraparoundAutovacuum include and guard, disable_ddl_transaction!, milestone '19.3', raise_on_exhaustion: true, and both up and down bodies.

up is idempotent. remove_foreign_key_if_exists returns early when the constraint is absent, so the migration does nothing where an earlier attempt already succeeded, including on self-managed instances.

down recreates the foreign key as NOT VALID, matching how AddBigintFkForDeploymentsPhaseTwo originally added it, so a rollback does not pay for a full table validation.

Verification

• The migration timestamp and the db/schema_migrations/20260811082241 checksum were generated by bin/rails generate post_deployment_migration against the development cluster, not written by hand. A reviewer flagged a hand-written checksum on the previous MR; this addresses that.
• Ran bin/rails db:migrate against the development cluster. It completed in 0.23 seconds. The foreign key does not exist there, so the existence guard short-circuited, which is the expected idempotent no-op path.
• rubocop -A reports no offenses on either migration file.
• reek reports only IrresponsibleModule on both files, which every migration in that directory also trips.
• No migration spec exists for this class. Neither of the two previous retries had one.
• Migration testing on !248679 (merged) measured this same migration at roughly 4 to 6 seconds across the main, ci, and sec databases, with no change in database size.

References

• Issue: #551602
• Retry 2: !249303 (merged)
• Retry 1: !248679 (merged)
• Earlier related MR: !248030 (merged)

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.