Skip to content

Delete orphan redirect_routes rows

Tomasz Skoruparequested to merge
tskorupa/delete-orphan-redirect_routes-rows into master

What does this MR do and why?

Delete orphan redirect route rows

Addresses #561339

There are a number of rows that refer to projects that have been deleted and are no longer present in the database. These rows now cannot be attributed to a namespace (through a project).

SQL query (perform)

DELETE FROM "redirect_routes" WHERE ("redirect_routes"."id") IN (
  SELECT "redirect_routes"."id"
  FROM "redirect_routes"
    LEFT OUTER JOIN projects ON redirect_routes.source_id = projects.id
  WHERE
    "redirect_routes"."id" BETWEEN 3 AND 4
    AND "redirect_routes"."source_type" = 'Project'
    AND "redirect_routes"."namespace_id" IS NULL
    AND "redirect_routes"."id" >= 3
    AND "redirect_routes"."id" < 4
    AND "projects"."id" IS NULL
)

https://postgres.ai/console/gitlab/gitlab-production-main/sessions/43349/commands/132441

SQL query (each_batch)

SELECT
  "redirect_routes"."id"
FROM "redirect_routes"
WHERE
  "redirect_routes"."id" BETWEEN 3 AND 4
  AND "redirect_routes"."source_type" = 'Project'
  AND "redirect_routes"."namespace_id" IS NULL
ORDER BY "redirect_routes"."id" ASC
LIMIT 1

SELECT
  "redirect_routes"."id"
FROM "redirect_routes"
WHERE
  "redirect_routes"."id" BETWEEN 3 AND 4
  AND "redirect_routes"."source_type" = 'Project'
  AND "redirect_routes"."namespace_id" IS NULL
  AND "redirect_routes"."id" >= 3
ORDER BY "redirect_routes"."id" ASC
LIMIT 1
OFFSET 1

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Tomasz Skorupa

Merge request reports