RUN AS-IF-FOSS Background migration to replace blocked_by links
What does this MR do?
Replaces blocked_by
links with blocks
link where source and target is swapped.
There are currently 47600 blocked_by links.
DB queries
- migration:
$ bundle exec rake db:migrate
== 20201015073808 MigrateBlockedBy: migrating =================================
== 20201015073808 MigrateBlockedBy: migrated (0.0182s) ========================
$ bundle exec rake db:rollback
== 20201015073808 MigrateBlockedBy: reverting =================================
== 20201015073808 MigrateBlockedBy: reverted (0.0000s) ========================
- scheduling query (basic relation): https://explain.depesz.com/s/jDfE
SELECT "issue_links".* FROM "issue_links" WHERE "issue_links"."link_type" = 2
- query to delete "bidirectional" links: https://explain.depesz.com/s/jr2x
DELETE FROM "issue_links" WHERE "issue_links"."id" IN (SELECT "issue_links"."id" FROM "issue_links" INNER JOIN issue_links as opposite_links ON issue_links.source_id = opposite_links.target_id AND issue_links.target_id = opposite_links.source_id WHERE "issue_links"."id" BETWEEN 0 AND 1000 AND "issue_links"."link_type" = 2)
- query which replaces blocked_by with block: https://explain.depesz.com/s/pGV7
UPDATE "issue_links" SET source_id=target_id,target_id=source_id,link_type=1 WHERE "issue_links"."id" BETWEEN 0 AND 1000 AND "issue_links"."link_type" = 2
Estimated times
- 900ms for select statement with 1000 items
- 3ms for delete statement with 1000 items
- 4ms for update statement with 1000 items
- 47600 items to update
- 47600 / 1000 = 48 loops
- 2 mins delay per loop => 100 mins to run the all scheduled batches
Related to #225919 (closed)
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 -
Informed Infrastructure department of a default or new setting change, if applicable per definition of done
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
Edited by Jan Provaznik