Delete orphaned project group links

What does this MR do and why?

project_group_links rows can have a group_id that does not resolve to a group:

• the group_id foreign key to namespaces (fk_28a1244b01) is NOT VALID, so rows that predate it can reference a namespace that no longer exists, and
• ProjectGroupLink belongs_to :group goes through the Group STI class, so a group_id that points at a namespace that is not a group (for example a user namespace) also resolves to nil.

These rows caused the 500 in the groups API fixed by !258506 (merged). As discussed there, GitLab.com has no such rows, but other installations may, so this MR adds a post-deployment migration that deletes them.

The migration walks the distinct group_id values in batches of 500 (recursive loose index scan) and deletes every link whose group_id has no matching namespace of type Group. The table is small (table_size: small in db/docs/project_group_links.yml), so a regular post-deployment migration is enough. An orphaned link grants no access, because there is no group membership behind it, so deleting it does not change anyone's permissions. down is a no-op.

Validating fk_28a1244b01 follows in #633386 once these rows are gone.

Database

Per batch, the migration runs three queries. Batching uses distinct_each_batch(column: :group_id, of: 500), a recursive loose index scan over the distinct group_id values:

-- 1. batch iterator: recursive loose index scan CTE, 500 distinct group_ids per batch
-- (plus an OFFSET 500 probe of the same CTE to find the next batch start)
WITH RECURSIVE loose_index_scan_cte AS (
  (SELECT project_group_links.group_id FROM project_group_links
   WHERE project_group_links.group_id >= 1
   ORDER BY group_id ASC LIMIT 1)
  UNION
  (SELECT (SELECT project_group_links.group_id FROM project_group_links
   WHERE project_group_links.group_id > loose_index_scan_cte.group_id
   ORDER BY group_id ASC LIMIT 1) AS group_id)
)
SELECT group_id FROM loose_index_scan_cte AS project_group_links
WHERE group_id IS NOT NULL AND group_id >= 1 AND group_id < 501;

-- 2. namespaces lookup for the batch (up to 500 ids)
SELECT namespaces.id FROM namespaces
WHERE namespaces.type = 'Group' AND namespaces.id IN (1, 2, ...);

-- 3. delete every link for the orphaned ids in one statement (skipped when empty)
DELETE FROM project_group_links WHERE project_group_links.group_id IN (...);

The scan is covered by the unique (group_id, project_id) index and the lookup uses the namespaces primary key. On GitLab.com this deletes nothing, as checked by @smaglangit in !258506 (comment 3936928071). @reza-marandi is confirming the DELETE plan on postgres.ai with a few fake ids.

Data deletion

• Recovery: the deleted rows can be restored from a database backup. Restoring them would not bring anything back for users, because each one references a namespace that is missing or is not a group, so it grants no access and the application cannot load its group.
• Records affected: none on GitLab.com, as checked by @smaglangit in !258506 (comment 3936928071). On other installations it is only the links left behind by deleted or non-group namespaces, which should be a handful at most. Note each batch covers up to 500 distinct group_ids but the DELETE removes every link for the orphaned ids in that batch in a single statement, so the number of rows deleted per batch is not bounded (orphans are frozen historical data).
• User experience impact: none expected. The deleted links grant no access and are not shown as working links anywhere, and they are the rows that made the groups API return a 500 before !258506 (merged).

References

• Closes #631625
• Follow-up from !258506 (merged)
• Follow-up (FK validation): #633386

Screenshots or screen recordings

Not applicable, database migration.

How to set up and validate locally

1. In a Rails console, create an orphaned link, for example one that points at a user namespace:

   project = Project.first
   link = ProjectGroupLink.create!(project: project, group: Group.where.not(id: project.namespace_id).first, group_access: Gitlab::Access::DEVELOPER)
   link.update_column(:group_id, User.first.namespace.id)

2. Run bin/rails db:migrate.

3. ProjectGroupLink.exists?(link.id) returns false, and links to real groups are kept.

Specs: bin/rspec spec/migrations/20261001140134_delete_orphaned_project_group_links_spec.rb

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.