Add milestone date sourcing foreign key
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
- [-] Changelog entry
- [-] Documentation (if required)
-
Code review guidelines -
Merge request performance guidelines -
Style guides -
Database guides - [-] Separation of EE specific content
Availability and Testing
- [-] Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.
- [-] Tested in all supported browsers
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 🤖