Drop stuck builds that waiting for runner ack

What does this MR do and why?

• Drops builds that are pending, but the runner is assigned to the build and timed out waiting for acknowledgement.

follow-up from this discussion

References

Resolves #571474

How to set up and validate locally

There is no UI or console-based validation for this service. Please go through and run spec/services/ci/stuck_builds/drop_pending_service_spec.rb to validate.

Query Plan:

Note: Simplified the query using *

Existing query

Query:

SELECT * FROM "p_ci_builds" WHERE "p_ci_builds"."type" = 'Ci::Build' AND ("p_ci_builds"."status" IN ('pending')) AND ("p_ci_builds".created_at < '2025-10-08 08:56:43.193197') AND ("p_ci_builds".updated_at < '2025-10-08 08:56:43.193197') ORDER BY "p_ci_builds"."created_at" ASC, "p_ci_builds"."project_id" ASC LIMIT 100

Query Plan:

https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/44228/commands/135496

New query

Query:

SELECT * FROM "p_ci_builds" WHERE "p_ci_builds"."type" = 'Ci::Build' AND "p_ci_builds"."id" IN 
(SELECT "p_ci_builds"."id" FROM "p_ci_builds" WHERE "p_ci_builds"."type" = 'Ci::Build' AND ("p_ci_builds"."status" IN ('pending')) 
AND "p_ci_builds"."runner_id" IS NOT NULL) AND ("p_ci_builds".created_at < '2025-10-10 11:06:41.203519') 
AND ("p_ci_builds".updated_at < '2025-10-10 11:06:41.203519') ORDER BY "p_ci_builds"."created_at" ASC, "p_ci_builds"."project_id" ASC LIMIT 100

Query Plan:

https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/44372/commands/136038

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.