Skip to content

Migrate cadence start date to automation start date

euko requested to merge 370939-migrate-cadence-start-date-to-automation-start-date into master

What does this MR do and why?

An iterations cadence - cadence for short - is a container for iterations. In other words, a cadence has many iterations. Note that an iteration can belong to a single cadence throughout its lifespan.

The start_date attribute of a cadence used to indicate the date on which its first iteration started. Based on the start date of the first iteration, future iterations were scheduled. For example, if the first iteration for a cadence started on Wed Aug 17 2022, future iterations would be generated to start on Wednesdays e.g., Wed Aug 24, Wed Aug 31, and so on.

After #367493 (closed), the start_date attribute stores the date from which iterations should be bulk-created according to some specified parameters by a background worker. From user perspective, this change has allowed users to update when future iterations would start - they are no longer fixed to start on the initial start date. Even if the first iteration started on Wed Aug 17, the start_date attribute can be updated to a future date like Mon Sep 5 and future iterations would be generated on Mondays. In addition, validations have been added so that all scheduled iterations start on the same weekday as the start_date of a cadence.

To ensure that all cadence records observe this constraint, we want to perform a data migration to set the start_date attribute of the existing cadence records in this MR. To achieve this, we can simply set the start_date of cadences to to their first upcoming iteration start date.

To illustrate, suppose a cadence had the following iterations for a certain cadence:

  • Iteration 1 (past) started on Monday
  • Iteration 2 (current) started on Tuesday
  • Iteration 3 (upcoming) will start on Monday
  • Iteration 4 (upcoming) will start on Monday

We can set the cadence's start_date to the start date of Iteration 3, the first upcoming iteration for the cadence so that all iterations created on or after the start date are created on the same weekday.

Resolves #370939 (closed)

Database Review

This data migration is completely reversible. There will be no loss of data.

Migration Up

                                                                                       QUERY PLAN                                                                                        
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Update on iterations_cadences  (cost=14.44..34906.89 rows=1657 width=149) (actual time=89.135..89.140 rows=0 loops=1)
   Buffers: shared hit=46473 dirtied=25 written=16
   ->  Nested Loop  (cost=14.44..34906.89 rows=1657 width=149) (actual time=0.187..58.896 rows=1433 loops=1)
         Buffers: shared hit=35821
         ->  Nested Loop  (cost=0.85..12364.38 rows=1657 width=129) (actual time=0.102..30.371 rows=1433 loops=1)
               Buffers: shared hit=17882
               ->  Merge Join  (cost=0.56..801.07 rows=1657 width=97) (actual time=0.049..11.972 rows=1464 loops=1)
                     Merge Cond: (iterations_cadences.id = ic.id)
                     Buffers: shared hit=8925
                     ->  Index Scan using iterations_cadences_pkey on iterations_cadences  (cost=0.28..383.14 rows=5634 width=83) (actual time=0.014..5.245 rows=4979 loops=1)
                           Buffers: shared hit=4463
                     ->  Index Scan using iterations_cadences_pkey on iterations_cadences ic  (cost=0.28..383.14 rows=1657 width=14) (actual time=0.030..4.885 rows=1464 loops=1)
                           Filter: automatic
                           Rows Removed by Filter: 3515
                           Buffers: shared hit=4462
               ->  Subquery Scan on sprints1  (cost=0.29..6.97 rows=1 width=32) (actual time=0.011..0.012 rows=1 loops=1464)
                     Buffers: shared hit=8957
                     ->  Limit  (cost=0.29..6.96 rows=1 width=4) (actual time=0.010..0.011 rows=1 loops=1464)
                           Buffers: shared hit=8957
                           ->  Index Scan using index_sprints_iterations_cadence_id on sprints  (cost=0.29..13.63 rows=2 width=4) (actual time=0.010..0.010 rows=1 loops=1464)
                                 Index Cond: (iterations_cadence_id = ic.id)
                                 Filter: ((start_date <= CURRENT_DATE) AND (due_date >= CURRENT_DATE))
                                 Rows Removed by Filter: 5
                                 Buffers: shared hit=8957
         ->  Subquery Scan on sprints2  (cost=13.58..13.59 rows=1 width=32) (actual time=0.019..0.019 rows=1 loops=1433)
               Buffers: shared hit=17939
               ->  Limit  (cost=13.58..13.58 rows=1 width=4) (actual time=0.018..0.018 rows=1 loops=1433)
                     Buffers: shared hit=17939
                     ->  Sort  (cost=13.58..13.59 rows=3 width=4) (actual time=0.017..0.017 rows=1 loops=1433)
                           Sort Key: sprints_1.start_date
                           Sort Method: quicksort  Memory: 25kB
                           Buffers: shared hit=17939
                           ->  Index Scan using index_sprints_iterations_cadence_id on sprints sprints_1  (cost=0.29..13.57 rows=3 width=4) (actual time=0.003..0.014 rows=5 loops=1433)
                                 Index Cond: (iterations_cadence_id = ic.id)
                                 Filter: (start_date > sprints1.current_iteration_due_date)
                                 Rows Removed by Filter: 10
                                 Buffers: shared hit=17939
 Planning Time: 1.331 ms
 Execution Time: 89.299 ms
(39 rows)

Migration Down

                                                                                        QUERY PLAN                                                                                        
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Update on iterations_cadences  (cost=14.02..22423.66 rows=1606 width=121) (actual time=59.539..59.542 rows=0 loops=1)
   Buffers: shared hit=36870 dirtied=19 written=18
   ->  Nested Loop  (cost=14.02..22423.66 rows=1606 width=121) (actual time=0.140..38.066 rows=1440 loops=1)
         Buffers: shared hit=26210
         ->  Merge Join  (cost=0.56..786.39 rows=1606 width=97) (actual time=0.040..10.072 rows=1440 loops=1)
               Merge Cond: (iterations_cadences.id = ic.id)
               Buffers: shared hit=8847
               ->  Index Scan using iterations_cadences_pkey on iterations_cadences  (cost=0.28..376.22 rows=5546 width=83) (actual time=0.007..4.669 rows=4961 loops=1)
                     Buffers: shared hit=4424
               ->  Index Scan using iterations_cadences_pkey on iterations_cadences ic  (cost=0.28..376.22 rows=1606 width=14) (actual time=0.027..3.901 rows=1440 loops=1)
                     Filter: automatic
                     Rows Removed by Filter: 3521
                     Buffers: shared hit=4423
         ->  Subquery Scan on sprints  (cost=13.45..13.46 rows=1 width=32) (actual time=0.018..0.019 rows=1 loops=1440)
               Buffers: shared hit=17363
               ->  Limit  (cost=13.45..13.45 rows=1 width=4) (actual time=0.018..0.018 rows=1 loops=1440)
                     Buffers: shared hit=17363
                     ->  Sort  (cost=13.45..13.47 rows=8 width=4) (actual time=0.017..0.017 rows=1 loops=1440)
                           Sort Key: sprints_1.start_date
                           Sort Method: top-N heapsort  Memory: 25kB
                           Buffers: shared hit=17363
                           ->  Index Scan using index_sprints_iterations_cadence_id on sprints sprints_1  (cost=0.29..13.41 rows=8 width=4) (actual time=0.003..0.013 rows=14 loops=1440)
                                 Index Cond: (iterations_cadence_id = ic.id)
                                 Buffers: shared hit=17363
 Planning Time: 1.497 ms
 Execution Time: 59.644 ms
(26 rows)

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 euko

Merge request reports