Unable to delete projects because of "PG::QueryCanceled:" - setting source_project_id to nil

Summary

The user is intermittently unable to delete projects on gitlab.com. After reviewing logs in Kibana, the support team has found these errors:

Deletion failed on <projectname> with the following message: PG::QueryCanceled: ERROR: canceling statement due to statement timeout
CONTEXT: SQL statement "UPDATE ONLY "public"."merge_requests" SET "source_project_id" = NULL WHERE $1 OPERATOR(pg_catalog.=) "source_project_id""

Reported by the customer in ZenDesk ticket https://gitlab.zendesk.com/agent/tickets/183662 [internal]. Similar issue, with the same symptoms: #230949 (closed)

Steps to reproduce

Unknown

Example Project

What is the current bug behavior?

After confirming project removal, a blue banner appears at the top that says it is in the process of being deleted. The project is not deleted after that. The repository is gone, however.

What is the expected correct behavior?

The project should be completely removed.

Relevant logs and/or screenshots

https://log.gprd.gitlab.net/goto/5ae28b00eb1f0d62d8d2e7429de9bc4e [internal]

Problem analysis

Scenarios when the SQL timeout can occur:

Forked project:

For a forked project, the MRs can have source_project_id = forked project, and target_project_id = parent fork project. When the forked project is deleted, such MRs are closed and their source_project_id is set to nil (via ON DELETE SET NULL condition).

Non fork project:

For a non-forked project, the source_project_id and target_project_id will be same as the project in which the MR is created. source_project_id has the condition ON DELETE SET NULL and target_project_id has the condition ON DELETE CASCADE. When the project gets deleted, it could so happen that the source_project_id is first set to nil and then the cascade deletion happens and all records are deleted.

Implementation details

Re-create the foreign key constraint for source_project_id to be positioned after target_project_id. This will ensure the projects are deleted before the updation of nil is triggered.

Note: This solves only for non-forked project. For forked projects, please refer to: #329919 (closed)