Undo adding foreign key on geo_event_log.hashed_storage_attachments_event_id
What does this MR do?
Before we can add the foreign key constraint, we need to make sure all the foreign keys actually exist in the target table. So the foreign keys that no longer exist should be deleted before adding the foreign key constraint.
Because removing the orphaned rows just before adding the foreign key can run into racing conditions, this MR now removes adding the FK so it can be solved properly in a separate MR.
Query plan
I ran the query plan on a production like database:
EXPLAIN ANALYZE SELECT *
FROM geo_event_log
WHERE (geo_event_log.hashed_storage_attachments_event_id IS NOT NULL)
AND NOT EXISTS (SELECT 1
FROM geo_hashed_storage_attachments_events
WHERE geo_hashed_storage_attachments_events.id = geo_event_log.hashed_storage_attachments_event_id);
Resulting in this plan:
QUERY PLAN
-------------------
Merge Anti Join (cost=2.21..177.88 rows=1 width=112) (actual time=224.264..224.264 rows=0 loops=1)
Merge Cond: (geo_event_log.hashed_storage_attachments_event_id = geo_hashed_storage_attachments_events.id)
-> Index Scan using index_geo_event_log_on_hashed_storage_attachments_event_id on geo_event_log (cost=0.28..153.56 rows=467 widt
h=112) (actual time=1.523..222.225 rows=730 loops=1)
-> Index Only Scan using geo_hashed_storage_attachments_events_pkey on geo_hashed_storage_attachments_events (cost=0.28..20.73 r
ows=964 width=8) (actual time=0.801..1.178 rows=730 loops=1)
Heap Fetches: 0
Planning time: 21.323 ms
Execution time: 224.543 ms
(7 rows)
What are the relevant issue numbers?
Originally introduced in: https://gitlab.com/gitlab-org/gitlab-ee/merge_requests/7990
Closes https://gitlab.com/gitlab-org/gitlab-ee/issues/8302
Does this MR meet the acceptance criteria?
-
Changelog entry added, if necessary -
Documentation created/updated -
Tests added for this feature/bug -
Conforms to the code review guidelines -
Conforms to the merge request performance guidelines -
Conforms to the style guides -
Conforms to the database guides -
Link to e2e tests MR added if this MR has Requires e2e tests label. See the Test Planning Process. -
EE specific content should be in the top level /ee
folder -
For a paid feature, have we considered GitLab.com plans, how it works for groups, and is there a design for promoting it to users who aren't on the correct plan?
Edited by Toon Claes