Skip to content

Add milestone date sourcing foreign key

charlie ablett requested to merge 32326-cablett-epic-source-fks into master

What does this MR do?

Add a non-validating foreign key constraint to epics table (see this discussion)

This MR also finds any Epic records with invalid start_date_sourcing_milestone_id or due_date_sourcing_milestone_id nullifies the invalid fields.

52 `epic` records with an invalid `start_date_sourcing_milestone_id`

Query (run 8 Jan 2020):

SELECT ID, start_date_sourcing_milestone_id 
FROM epics 
WHERE start_date_sourcing_milestone_id NOT IN (SELECT id FROM milestones)
Seq Scan on epics  (cost=26840.64..32597.32 rows=7388 width=8) (actual time=648.303..681.843 rows=52 loops=1)
  Filter: (NOT (hashed SubPlan 1))
  Rows Removed by Filter: 14723
  Buffers: shared hit=81376 read=1173
  I/O Timings: read=65.473
  SubPlan 1
    ->  Index Only Scan using milestones_pkey on milestones  (cost=0.42..24928.12 rows=765006 width=4) (actual time=0.134..303.454 rows=764958 loops=1)
          Heap Fetches: 90877
          Buffers: shared hit=80119 read=1037
          I/O Timings: read=48.848
Planning time: 2.424 ms
Execution time: 689.134 ms
54 `epic` records with an invalid `due_date_sourcing_milestone_id`

Query (run 10 January 2020):

SELECT ID, start_date_sourcing_milestone_id 
FROM epics 
WHERE start_date_sourcing_milestone_id NOT IN (SELECT id FROM milestones)
Seq Scan on epics  (cost=21276.90..27035.29 rows=7456 width=8) (actual time=638.795..677.006 rows=54 loops=1)
  Filter: (NOT (hashed SubPlan 1))
  Rows Removed by Filter: 14862
  Buffers: shared hit=52479 read=1258
  I/O Timings: read=99.349
  SubPlan 1
    ->  Index Only Scan using milestones_pkey on milestones  (cost=0.42..19361.65 rows=766103 width=4) (actual time=0.144..300.796 rows=766758 loops=1)
          Heap Fetches: 57514
          Buffers: shared hit=51237 read=1107
          I/O Timings: read=78.703
Planning time: 2.218 ms
Execution time: 683.582 ms

Screenshots

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

  • [-] 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

Related to #32326 (closed)

Edited by 🤖 GitLab Bot 🤖

Merge request reports