Alexandru Croitor requested to merge automate-create-iterations-in-cadence into master May 22, 2021

What does this MR do?

This MR addresses automation of creating iteration in advance for a cadence set to be automatic

This happens in 3 scenarios:
- Cron job running on a daily basis checking for cadences that would potentially need to generate more iterations in advance.
- Trigger iteration in advance generation asynchronously when a cadence is created
- Trigger iteration in advance generation synchronously when an iteration is being closed and roll-over issues is being attempted. We'll check if the cadence corresponding to the iteration that needs to roll-over issues, has a future iteration in place and if not we'll attempt to create needed iterations in advance so that roll-over succeeds.

Database

The main query that determines if a cadence might be behind with iterations it needs to have in advance. So the query will fetch cadences for which automation was last run(last_run_date) more than a single iteration duration ago. So if for instance duration for iterations for a given cadence were set to 2 weeks and automation did not run at all yet or was run more than(or equal) 2 weeks ago we probably need to create a new iteration.

query generated by for_automated_iterations scope

SELECT
    "iterations_cadences".*
FROM
    "iterations_cadences"
WHERE
    "iterations_cadences"."automatic" = TRUE
    AND "iterations_cadences"."duration_in_weeks" IS NOT NULL
    AND "iterations_cadences"."duration_in_weeks" > 0
    AND DATE((COALESCE("iterations_cadences"."last_run_date", DATE('01-01-1970')) + "iterations_cadences"."duration_in_weeks" * INTERVAL '1 week')) <= CURRENT_DATE

 Seq Scan on public.iterations_cadences  (cost=0.00..229.59 rows=1 width=111) (actual time=29.497..29.498 rows=0 loops=1)
   Filter: (iterations_cadences.automatic AND (iterations_cadences.duration_in_weeks IS NOT NULL) AND (iterations_cadences.duration_in_weeks > 0) AND (date((COALESCE(iterations_cadences.last_run_date, '1970-01-01'::date) + ((iterations_cadences.duration_in_weeks)::double precision * '7 days'::interval))) <= CURRENT_DATE))
   Rows Removed by Filter: 2386
   Buffers: shared read=41 dirtied=16
   I/O Timings: read=17.457 write=0.000

Time: 30.225 ms
  - planning: 0.683 ms
  - execution: 29.542 ms
    - I/O read: 17.457 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 0 from the buffer pool
  - reads: 41 (~328.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 16 (~128.00 KiB)
  - writes: 0

Adding an index:

CREATE INDEX cadence_create_iterations_automation
    ON public.iterations_cadences USING btree
    (automatic ASC NULLS LAST, duration_in_weeks ASC NULLS LAST, date(COALESCE(last_run_date, '1970-01-01') + duration_in_weeks precision * '7 days') ASC NULLS LAST)
    WHERE duration_in_weeks IS NOT NULL;

The query has been executed. Duration: 60.085 ms

query and plan for for_automated_iterations scope after index

--same sql
explain
SELECT
    "iterations_cadences".*
FROM
    "iterations_cadences"
WHERE
    "iterations_cadences"."automatic" = TRUE
    AND "iterations_cadences"."duration_in_weeks" IS NOT NULL
    AND "iterations_cadences"."duration_in_weeks" > 0
    AND DATE((COALESCE("iterations_cadences"."last_run_date", DATE('01-01-1970')) + "iterations_cadences"."duration_in_weeks" * INTERVAL '1 week')) <= CURRENT_DATE

--different plan

Index Scan using cadence_create_iterations_automation on public.iterations_cadences  (cost=0.28..2.46 rows=1 width=111) (actual time=0.128..0.128 rows=0 loops=1)
   Index Cond: ((iterations_cadences.automatic = true) AND (iterations_cadences.duration_in_weeks > 0) AND (date((COALESCE(iterations_cadences.last_run_date, '1970-01-01'::date) + ((iterations_cadences.duration_in_weeks)::double precision * '7 days'::interval))) <= CURRENT_DATE))
   Buffers: shared read=2
   I/O Timings: read=0.068 write=0.000

Time: 1.471 ms
  - planning: 1.306 ms
  - execution: 0.165 ms
    - I/O read: 0.068 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 0 from the buffer pool
  - reads: 2 (~16.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Migrations

UP

== 20210527065005 AddIndexForCadenceIterationsAutomation: migrating ===========
-- indexes(:iterations_cadences)
   -> 0.0055s
-- current_schema()
   -> 0.0003s
-- execute("        CREATE INDEX CONCURRENTLY cadence_create_iterations_automation ON iterations_cadences\n        USING BTREE(automatic, duration_in_weeks, (DATE ((COALESCE(\"iterations_cadences\".\"last_run_date\", DATE('01-01-1970')) + \"iterations_cadences\".\"duration_in_weeks\" * INTERVAL '1 week')))) \n        WHERE duration_in_weeks IS NOT NULL\n")
   -> 0.0847s
== 20210527065005 AddIndexForCadenceIterationsAutomation: migrated (0.0951s) ==

== 20210517221612 AddDefaultValueToMergeRequestsAuthorApprovalOnProjects: migrating
-- change_column_default(:projects, :merge_requests_author_approval, false)
   -> 0.0127s
== 20210517221612 AddDefaultValueToMergeRequestsAuthorApprovalOnProjects: migrated (0.0237s)

== 20210529164247 ChangeIterationsTitleUniquenessIndex: migrating =============
-- transaction_open?()
   -> 0.0000s
-- indexes(:sprints)
   -> 0.0083s
-- execute("SET statement_timeout TO 0")
   -> 0.0009s
-- remove_index(:sprints, {:algorithm=>:concurrently, :name=>"index_sprints_on_group_id_and_title"})
   -> 0.0031s
-- execute("RESET ALL")
   -> 0.0007s
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:sprints, [:iterations_cadence_id, :title], {:name=>"index_sprints_on_iterations_cadence_id_and_title", :unique=>true, :algorithm=>:concurrently})
   -> 0.0038s
-- add_index(:sprints, [:iterations_cadence_id, :title], {:name=>"index_sprints_on_iterations_cadence_id_and_title", :unique=>true, :algorithm=>:concurrently})
   -> 0.0066s
== 20210529164247 ChangeIterationsTitleUniquenessIndex: migrated (0.0258s) ====

DOWN

== 20210527065005 AddIndexForCadenceIterationsAutomation: reverting ===========
-- transaction_open?()
   -> 0.0000s
-- indexes(:iterations_cadences)
   -> 0.0040s
-- execute("SET statement_timeout TO 0")
   -> 0.0007s
-- remove_index(:iterations_cadences, {:algorithm=>:concurrently, :name=>"cadence_create_iterations_automation"})
   -> 0.0051s
-- execute("RESET ALL")
   -> 0.0006s
== 20210527065005 AddIndexForCadenceIterationsAutomation: reverted (0.0115s) ==

== 20210529164247 ChangeIterationsTitleUniquenessIndex: reverting =============
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:sprints, [:group_id, :title], {:name=>"index_sprints_on_group_id_and_title", :unique=>true, :where=>"group_id IS NOT NULL", :algorithm=>:concurrently})
   -> 0.0057s
-- transaction_open?()
   -> 0.0000s
-- indexes(:sprints)
   -> 0.0040s
-- execute("SET statement_timeout TO 0")
   -> 0.0006s
-- remove_index(:sprints, {:algorithm=>:concurrently, :name=>"index_sprints_on_iterations_cadence_id_and_title"})
   -> 0.0053s
-- execute("RESET ALL")
   -> 0.0006s
== 20210529164247 ChangeIterationsTitleUniquenessIndex: reverted (0.0185s) ====

Screenshots (strongly suggested)

Does this MR meet the acceptance criteria?

Conformity

I have included a changelog entry, or it's not needed. (Does this MR need a changelog?)
I have added/updated documentation, or it's not needed. (Is documentation required?)
I have properly separated EE content from FOSS, or this MR is FOSS only. (Where should EE code go?)
I have added information for database reviewers in the MR description, or it's not needed. (Does this MR have database related changes?)
I have self-reviewed this MR per code review guidelines.
This MR does not harm performance, or I have asked a reviewer to help assess the performance impact. (Merge request performance guidelines)
I have followed the style guides.

Availability and Testing

I have added/updated tests following the Testing Guide, or it's not needed. (Consider all test levels. See the Test Planning Process.)
I have tested this MR in all supported browsers, or it's not needed.
I have informed the Infrastructure department of a default or new setting change per definition of done, or it's not needed.

Security

Does this MR contain changes to processing or storing of credentials or tokens, authorization and authentication methods or other items described in the security review guidelines? If not, then delete this Security section.

Label as security and @ mention @gitlab-com/gl-security/appsec
The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
Security reports checked/validated by a reviewer from the AppSec team

Edited May 30, 2021 by Alexandru Croitor

Add auto-generation of iterations for iteration cadences