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)