Skip to content

Undo adding foreign key on geo_event_log.hashed_storage_attachments_event_id

Toon Claes requested to merge tc-geo-remove-missing-fk-rows into master

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?

Edited by Toon Claes

Merge request reports