Fix RescheduleBackfillImportedIssueSearchData migration

Merged Stan Hu requested to merge sh-fix-reschedule-backfill-migration into master

What does this MR do and why?

In GitLab 14.9, !71913 (merged) introduced a migration to backfill the issue search data.

In GitLab 15.2, !91979 (merged) rescheduled this migration. However, if between the two versions the maximum id of the issues table decreased below the max_value in the batched_background_migrations table for the 14.9 migration by an issue deletion, this new migration would fail due to a database constraint being violated.

To fix this, we need to take the minimum of the SELECT MAX(id) FROM issues and the max_value from the table.

Relates to #368631 (closed)

How to set up and validate locally

  1. On a test GitLab instance, delete the issues row that has the max ID:
gitlabhq_production=# SELECT * FROM batched_background_migrations WHERE job_class_name = 'BackfillIssueSearchData';
-[ RECORD 1 ]-----+------------------------------
id                | 43
created_at        | 2022-07-26 19:01:44.073334+00
updated_at        | 2022-07-26 19:01:44.073334+00
min_value         | 1
max_value         | 29
batch_size        | 100000
sub_batch_size    | 1000
interval          | 300
status            | 1
job_class_name    | BackfillIssueSearchData
batch_class_name  | PrimaryKeyBatchingStrategy
table_name        | issues
column_name       | id
job_arguments     | []
total_tuple_count | 30
pause_ms          | 100
max_batch_size    |
started_at        | 2022-07-26 19:01:44.071078+00
on_hold_until     |
gitlab_schema     | gitlab_main

gitlabhq_production=# select max(id) from issues;
-[ RECORD 1 ]
max | 29

gitlabhq_production=# delete from issues where id = 29;
DELETE 1
  1. Then run rake db:migrate:redo VERSION=20220707075300. In the previous version, you'll get something like:
Caused by:
PG::CheckViolation: ERROR:  new row for relation "batched_background_migrations" violates check constraint "check_max_value_in_range"
DETAIL:  Failing row contains (46, 2022-07-26 21:05:14.872769+00, 2022-07-26 21:05:14.872769+00, 28, 27, 50000, 1000, 120, 1, BackfillImportedIssueSearchData, PrimaryKeyBatchingStrategy, issues, id, [], 30, 100, null, 2022-07-26 21:05:14.869371+00, null, gitlab_main).
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:120:in `block in write_using_load_balancer'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/load_balancer.rb:115:in `block in read_write'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/load_balancer.rb:184:in `retry_with_backoff'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/load_balancer.rb:111:in `read_write'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:119:in `write_using_load_balancer'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:61:in `block (2 levels) in <class:ConnectionProxy>'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:120:in `block in write_using_load_balancer'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/load_balancer.rb:115:in `block in read_write'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/load_balancer.rb:184:in `retry_with_backoff'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/load_balancer.rb:111:in `read_write'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:119:in `write_using_load_balancer'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:71:in `transaction'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:120:in `block in write_using_load_balancer'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/load_balancer.rb:115:in `block in read_write'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/load_balancer.rb:184:in `retry_with_backoff'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/load_balancer.rb:111:in `read_write'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:119:in `write_using_load_balancer'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:71:in `transaction'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database.rb:299:in `block in transaction'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database.rb:298:in `transaction'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/batched_background_migration_helpers.rb:132:in `queue_batched_background_migration'
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20220707075300_reschedule_backfill_imported_issue_search_data.rb:21:in `up'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers/restrict_gitlab_schema.rb:33:in `block in exec_migration'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/query_analyzer.rb:37:in `within'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers/restrict_gitlab_schema.rb:30:in `exec_migration'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/lock_retry_mixin.rb:36:in `ddl_transaction'
/opt/gitlab/embedded/bin/bundle:23:in `load'
/opt/gitlab/embedded/bin/bundle:23:in `<main>'
Tasks: TOP => db:migrate:up

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Stan Hu