Use deferrable constraint to bulk update iterations

What does this MR do and why?

An iteration cadence is a container for iterations and has many iterations. Within an iteration cadence, iterations cannot have overlapping dates. Here's a short illustration:

iteration 1	iteration 2	valid?
Apr 1 ~ 5	Apr 6 ~ 10	✅
Apr 1 ~ 5	Apr 3 ~ 7	❌

We validate and enforce that iterations don't have overlapping dates using AR validations and a PG exclusion constraint iteration_start_and_due_date_iterations_cadence_id_constraint.

Sometimes we want to defer the enforcement of the PG constraint to the end of a transaction when we need to bulk update iterations. For example, if an iteration's start date gets pushed to a later date we want to also reschedule the ensuing iterations. Currently, because we don't use a deferrable constraint, we have to carefully coordinate the bulk update of iterations and this adds to the code complexity (the bulk update is done in CreateIterationsInAdvanceService. See !85249 (comment 913642905).)

In this MR, we are setting the PG exclusion constraint iteration_start_and_due_date_iterations_cadence_id_constraint to be deferrable so we can bulk update iterations more easily.

No feature change is included in this MR. Moreover the affected part of the application code is behind a non-default FF :iteration_cadences.

How to test

If you want to see how iterations are updated in bulk:

1. Enable the feature flag :iteration_cadences.

2. Visit any group and navigate to Iteration page via Issues menu on the left sidebar.

3. Create a new cadence.

4. Edit the cadence duration to observe the bulk update.

⚠ It may take time for the worker to queue the create/update service. You can directly execute the service in the rails console:

cadence = Iterations::Cadence.last
Iterations::Cadences::CreateIterationsInAdvanceService.new(User.automation_bot, cadence).execute

demo

Screen_Recording_2022-04-15_at_1.30.04_PM

Database review

Migration

• Dropping iteration_start_and_due_date_iterations_cadence_id_constraint took 227.814 ms.
• Adding the updated version of the same constraint (with DEFERRABLE INITIALLY DEFERRED) took 1133.898 ms.

(both on cold cache)

Updated bulk update query

CreateIterationsInAdvanceService class used to update iterations separately. Now they are updated at once inside a transaction.

Before: to update 10 iterations in bulk (10 is the upper limit), we currently run each of the ten updates in a separate transactions:

Only a single transaction shown for readability.

TRANSACTION (0.2ms)  COMMIT /*application:sidekiq,correlation_id:01G0NMWCVBNXX1W17FZNSS5QJN,jid:82a7f1b57a9825463b1ddc30,endpoint_id:Iterations::Cadences::CreateIterationsWorker,db_config_name:main,line:/lib/gitlab/database.rb:356:in `commit'*/
  ↳ lib/gitlab/database.rb:356:in `commit'
  Iteration Load (0.3ms)  SELECT "sprints".* FROM "sprints" WHERE "sprints"."id" = 7296 LIMIT 1 /*application:sidekiq,correlation_id:01G0NMWCVBNXX1W17FZNSS5QJN,jid:82a7f1b57a9825463b1ddc30,endpoint_id:Iterations::Cadences::CreateIterationsWorker,db_config_name:main,line:/ee/app/services/iterations/cadences/create_iterations_in_advance_service.rb:139:in `block in update_existing_iterations!'*/
  ↳ config/initializers/forbid_sidekiq_in_transactions.rb:58:in `block in committed!'
  TRANSACTION (0.1ms)  BEGIN /*application:sidekiq,correlation_id:01G0NMWCVBNXX1W17FZNSS5QJN,jid:82a7f1b57a9825463b1ddc30,endpoint_id:Iterations::Cadences::CreateIterationsWorker,db_config_name:main,line:/ee/app/services/iterations/cadences/create_iterations_in_advance_service.rb:139:in `block in update_existing_iterations!'*/
  ↳ ee/app/services/iterations/cadences/create_iterations_in_advance_service.rb:139:in `block in update_existing_iterations!'
  CACHE Route Load (0.0ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 126 AND "routes"."source_type" = 'Namespace' LIMIT 1
  ↳ app/models/concerns/routable.rb:118:in `full_path'
  Group Load (0.4ms)  SELECT "namespaces"."id", "namespaces"."name", "namespaces"."path", "namespaces"."owner_id", "namespaces"."created_at", "namespaces"."updated_at", "namespaces"."type", "namespaces"."description", "namespaces"."avatar", "namespaces"."membership_lock", "namespaces"."share_with_group_lock", "namespaces"."visibility_level", "namespaces"."request_access_enabled", "namespaces"."ldap_sync_status", "namespaces"."ldap_sync_error", "namespaces"."ldap_sync_last_update_at", "namespaces"."ldap_sync_last_successful_update_at", "namespaces"."ldap_sync_last_sync_at", "namespaces"."description_html", "namespaces"."lfs_enabled", "namespaces"."parent_id", "namespaces"."shared_runners_minutes_limit", "namespaces"."repository_size_limit", "namespaces"."require_two_factor_authentication", "namespaces"."two_factor_grace_period", "namespaces"."cached_markdown_version", "namespaces"."project_creation_level", "namespaces"."runners_token", "namespaces"."file_template_project_id", "namespaces"."saml_discovery_token", "namespaces"."runners_token_encrypted", "namespaces"."custom_project_templates_group_id", "namespaces"."auto_devops_enabled", "namespaces"."extra_shared_runners_minutes_limit", "namespaces"."last_ci_minutes_notification_at", "namespaces"."last_ci_minutes_usage_notification_level", "namespaces"."subgroup_creation_level", "namespaces"."emails_disabled", "namespaces"."max_pages_size", "namespaces"."max_artifacts_size", "namespaces"."mentions_disabled", "namespaces"."default_branch_protection", "namespaces"."unlock_membership_to_ldap", "namespaces"."max_personal_access_token_lifetime", "namespaces"."push_rule_id", "namespaces"."shared_runners_enabled", "namespaces"."allow_descendants_override_disabled_shared_runners", "namespaces"."traversal_ids" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 126 LIMIT 1 /*application:sidekiq,correlation_id:01G0NMWCVBNXX1W17FZNSS5QJN,jid:82a7f1b57a9825463b1ddc30,endpoint_id:Iterations::Cadences::CreateIterationsWorker,db_config_name:main,line:/ee/app/services/iterations/cadences/create_iterations_in_advance_service.rb:139:in `block in update_existing_iterations!'*/
  ↳ ee/app/services/iterations/cadences/create_iterations_in_advance_service.rb:139:in `block in update_existing_iterations!'
  Iteration Exists? (0.3ms)  SELECT 1 AS one FROM "sprints" WHERE "sprints"."iterations_cadence_id" = 8086 AND "sprints"."id" != 7295 AND (sprints.start_date <= '2022-06-05') AND (sprints.due_date >= '2022-05-16') LIMIT 1 /*application:sidekiq,correlation_id:01G0NMWCVBNXX1W17FZNSS5QJN,jid:82a7f1b57a9825463b1ddc30,endpoint_id:Iterations::Cadences::CreateIterationsWorker,db_config_name:main,line:/ee/app/models/ee/iteration.rb:281:in `dates_do_not_overlap'*/
  ↳ ee/app/models/ee/iteration.rb:281:in `dates_do_not_overlap'
  Iteration Update (0.5ms)  UPDATE "sprints" SET "updated_at" = '2022-04-15 03:39:48.455018', "start_date" = '2022-05-16', "due_date" = '2022-06-05', "cached_markdown_version" = 1900544, "title_html" = '', "description_html" = '' WHERE "sprints"."id" = 7295 /*application:sidekiq,correlation_id:01G0NMWCVBNXX1W17FZNSS5QJN,jid:82a7f1b57a9825463b1ddc30,endpoint_id:Iterations::Cadences::CreateIterationsWorker,db_config_name:main,line:/ee/app/services/iterations/cadences/create_iterations_in_advance_service.rb:139:in `block in update_existing_iterations!'*/
  ↳ ee/app/services/iterations/cadences/create_iterations_in_advance_service.rb:139:in `block in update_existing_iterations!'
   (0.4ms)  UPDATE sprints SET sequence=t.row_number
FROM (
  SELECT id, row_number() OVER (ORDER BY start_date) FROM sprints
  WHERE iterations_cadence_id = 8086
) as t
WHERE t.id=sprints.id AND (sprints.sequence IS DISTINCT FROM t.row_number)
 /*application:sidekiq,correlation_id:01G0NMWCVBNXX1W17FZNSS5QJN,jid:82a7f1b57a9825463b1ddc30,endpoint_id:Iterations::Cadences::CreateIterationsWorker,db_config_name:main,line:/ee/app/models/iterations/cadence.rb:80:in `update_iteration_sequences'*/
  ↳ ee/app/models/iterations/cadence.rb:80:in `update_iteration_sequences'
  TRANSACTION (0.2ms)  COMMIT /*application:sidekiq,correlation_id:01G0NMWCVBNXX1W17FZNSS5QJN,jid:82a7f1b57a9825463b1ddc30,endpoint_id:Iterations::Cadences::CreateIterationsWorker,db_config_name:main,line:/lib/gitlab/database.rb:356:in `commit'*/

After: The updates are done in bulk in a single transaction.

  Iteration Load (0.3ms)  SELECT "sprints".* FROM "sprints" WHERE "sprints"."iterations_cadence_id" = 8086 AND (start_date > '2022-04-18') ORDER BY "sprints"."due_date" ASC /*application:sidekiq,correlation_id:01G0NMJKYDMT8X5FNJXGB5ZASW,jid:5d0300edbbd566365bb574e4,endpoint_id:Iterations::Cadences::CreateIterationsWorker,db_config_name:main,line:/ee/app/services/iterations/cadences/create_iterations_in_advance_service.rb:81:in `existing_iterations_in_advance'*/
  ↳ ee/app/services/iterations/cadences/create_iterations_in_advance_service.rb:81:in `existing_iterations_in_advance'
  TRANSACTION (0.2ms)  BEGIN /*application:sidekiq,correlation_id:01G0NMJKYDMT8X5FNJXGB5ZASW,jid:5d0300edbbd566365bb574e4,endpoint_id:Iterations::Cadences::CreateIterationsWorker,db_config_name:main,line:/ee/app/services/iterations/cadences/create_iterations_in_advance_service.rb:135:in `block (2 levels) in update_existing_iterations!'*/
  ↳ ee/app/services/iterations/cadences/create_iterations_in_advance_service.rb:135:in `block (2 levels) in update_existing_iterations!'
  Iteration Update (0.5ms)  UPDATE "sprints" SET "start_date" = '2022-04-25', "due_date" = '2022-05-08' WHERE "sprints"."id" = 7294 /*application:sidekiq,correlation_id:01G0NMJKYDMT8X5FNJXGB5ZASW,jid:5d0300edbbd566365bb574e4,endpoint_id:Iterations::Cadences::CreateIterationsWorker,db_config_name:main,line:/ee/app/services/iterations/cadences/create_iterations_in_advance_service.rb:135:in `block (2 levels) in update_existing_iterations!'*/
  ↳ ee/app/services/iterations/cadences/create_iterations_in_advance_service.rb:135:in `block (2 levels) in update_existing_iterations!'
  Iteration Update (0.3ms)  UPDATE "sprints" SET "start_date" = '2022-05-09', "due_date" = '2022-05-22' WHERE "sprints"."id" = 7295 /*application:sidekiq,correlation_id:01G0NMJKYDMT8X5FNJXGB5ZASW,jid:5d0300edbbd566365bb574e4,endpoint_id:Iterations::Cadences::CreateIterationsWorker,db_config_name:main,line:/ee/app/services/iterations/cadences/create_iterations_in_advance_service.rb:135:in `block (2 levels) in update_existing_iterations!'*/
  ↳ ee/app/services/iterations/cadences/create_iterations_in_advance_service.rb:135:in `block (2 levels) in update_existing_iterations!'
  Iteration Update (0.4ms)  UPDATE "sprints" SET "start_date" = '2022-05-23', "due_date" = '2022-06-05' WHERE "sprints"."id" = 7296 /*application:sidekiq,correlation_id:01G0NMJKYDMT8X5FNJXGB5ZASW,jid:5d0300edbbd566365bb574e4,endpoint_id:Iterations::Cadences::CreateIterationsWorker,db_config_name:main,line:/ee/app/services/iterations/cadences/create_iterations_in_advance_service.rb:135:in `block (2 levels) in update_existing_iterations!'*/
  ↳ ee/app/services/iterations/cadences/create_iterations_in_advance_service.rb:135:in `block (2 levels) in update_existing_iterations!'
  Iteration Update (0.3ms)  UPDATE "sprints" SET "start_date" = '2022-06-06', "due_date" = '2022-06-19' WHERE "sprints"."id" = 7297 /*application:sidekiq,correlation_id:01G0NMJKYDMT8X5FNJXGB5ZASW,jid:5d0300edbbd566365bb574e4,endpoint_id:Iterations::Cadences::CreateIterationsWorker,db_config_name:main,line:/ee/app/services/iterations/cadences/create_iterations_in_advance_service.rb:135:in `block (2 levels) in update_existing_iterations!'*/
  ↳ ee/app/services/iterations/cadences/create_iterations_in_advance_service.rb:135:in `block (2 levels) in update_existing_iterations!'
  Iteration Update (0.3ms)  UPDATE "sprints" SET "start_date" = '2022-06-20', "due_date" = '2022-07-03' WHERE "sprints"."id" = 7298 /*application:sidekiq,correlation_id:01G0NMJKYDMT8X5FNJXGB5ZASW,jid:5d0300edbbd566365bb574e4,endpoint_id:Iterations::Cadences::CreateIterationsWorker,db_config_name:main,line:/ee/app/services/iterations/cadences/create_iterations_in_advance_service.rb:135:in `block (2 levels) in update_existing_iterations!'*/
  ↳ ee/app/services/iterations/cadences/create_iterations_in_advance_service.rb:135:in `block (2 levels) in update_existing_iterations!'
  Iteration Update (0.5ms)  UPDATE "sprints" SET "start_date" = '2022-07-04', "due_date" = '2022-07-17' WHERE "sprints"."id" = 7299 /*application:sidekiq,correlation_id:01G0NMJKYDMT8X5FNJXGB5ZASW,jid:5d0300edbbd566365bb574e4,endpoint_id:Iterations::Cadences::CreateIterationsWorker,db_config_name:main,line:/ee/app/services/iterations/cadences/create_iterations_in_advance_service.rb:135:in `block (2 levels) in update_existing_iterations!'*/
  ↳ ee/app/services/iterations/cadences/create_iterations_in_advance_service.rb:135:in `block (2 levels) in update_existing_iterations!'
  Iteration Update (1.3ms)  UPDATE "sprints" SET "start_date" = '2022-07-18', "due_date" = '2022-07-31' WHERE "sprints"."id" = 7300 /*application:sidekiq,correlation_id:01G0NMJKYDMT8X5FNJXGB5ZASW,jid:5d0300edbbd566365bb574e4,endpoint_id:Iterations::Cadences::CreateIterationsWorker,db_config_name:main,line:/ee/app/services/iterations/cadences/create_iterations_in_advance_service.rb:135:in `block (2 levels) in update_existing_iterations!'*/
  ↳ ee/app/services/iterations/cadences/create_iterations_in_advance_service.rb:135:in `block (2 levels) in update_existing_iterations!'
  Iteration Update (0.4ms)  UPDATE "sprints" SET "start_date" = '2022-08-01', "due_date" = '2022-08-14' WHERE "sprints"."id" = 7301 /*application:sidekiq,correlation_id:01G0NMJKYDMT8X5FNJXGB5ZASW,jid:5d0300edbbd566365bb574e4,endpoint_id:Iterations::Cadences::CreateIterationsWorker,db_config_name:main,line:/ee/app/services/iterations/cadences/create_iterations_in_advance_service.rb:135:in `block (2 levels) in update_existing_iterations!'*/
  ↳ ee/app/services/iterations/cadences/create_iterations_in_advance_service.rb:135:in `block (2 levels) in update_existing_iterations!'
  Iteration Update (0.3ms)  UPDATE "sprints" SET "start_date" = '2022-08-15', "due_date" = '2022-08-28' WHERE "sprints"."id" = 7302 /*application:sidekiq,correlation_id:01G0NMJKYDMT8X5FNJXGB5ZASW,jid:5d0300edbbd566365bb574e4,endpoint_id:Iterations::Cadences::CreateIterationsWorker,db_config_name:main,line:/ee/app/services/iterations/cadences/create_iterations_in_advance_service.rb:135:in `block (2 levels) in update_existing_iterations!'*/
  ↳ ee/app/services/iterations/cadences/create_iterations_in_advance_service.rb:135:in `block (2 levels) in update_existing_iterations!'
  TRANSACTION (0.2ms)  COMMIT /*application:sidekiq,correlation_id:01G0NMJKYDMT8X5FNJXGB5ZASW,jid:5d0300edbbd566365bb574e4,endpoint_id:Iterations::Cadences::CreateIterationsWorker,db_config_name:main,line:/lib/gitlab/database.rb:356:in `commit'*/

Sample plan for a single update (provided as FYI, the query plan didn't change)

explain (analyze, buffers) UPDATE "sprints" SET "start_date" = '2022-08-01', "due_date" = '2022-08-14' WHERE "sprints"."id" = 1;
                                                          QUERY PLAN                                                          
------------------------------------------------------------------------------------------------------------------------------
 Update on sprints  (cost=0.29..3.31 rows=1 width=322) (actual time=3.509..3.510 rows=0 loops=1)
   Buffers: shared hit=29 read=3 dirtied=15
   I/O Timings: read=2.604
   ->  Index Scan using sprints_pkey on sprints  (cost=0.29..3.31 rows=1 width=322) (actual time=0.043..0.046 rows=1 loops=1)
         Index Cond: (id = 1)
         Buffers: shared hit=3
 Planning Time: 0.518 ms
 Execution Time: 3.604 ms
(8 rows)

Related to #354977 (closed)