Ci::Minutes::RefreshCachedDataService generates cross-database query

The query

  Ci::PendingBuild Update All (1.2ms)  UPDATE "ci_pending_builds" SET "minutes_exceeded" = FALSE WHERE "ci_pending_builds"."namespace_id" IN (WITH RECURSIVE "base_and_descendants" AS ((SELECT "namespaces"."id", "namespaces"."name", "namespaces"."path", "namespaces"."owner_id", "namespaces"."created_at", "namespaces"."updated_at", "namespaces"."type", "namespaces"."description", "namespaces"."avatar", "namespaces"."membership_lock", "namespaces"."share_with_group_lock", "namespaces"."visibility_level", "namespaces"."request_access_enabled", "namespaces"."ldap_sync_status", "namespaces"."ldap_sync_error", "namespaces"."ldap_sync_last_update_at", "namespaces"."ldap_sync_last_successful_update_at", "namespaces"."ldap_sync_last_sync_at", "namespaces"."description_html", "namespaces"."lfs_enabled", "namespaces"."parent_id", "namespaces"."shared_runners_minutes_limit", "namespaces"."repository_size_limit", "namespaces"."require_two_factor_authentication", "namespaces"."two_factor_grace_period", "namespaces"."cached_markdown_version", "namespaces"."project_creation_level", "namespaces"."runners_token", "namespaces"."file_template_project_id", "namespaces"."saml_discovery_token", "namespaces"."runners_token_encrypted", "namespaces"."custom_project_templates_group_id", "namespaces"."auto_devops_enabled", "namespaces"."extra_shared_runners_minutes_limit", "namespaces"."last_ci_minutes_notification_at", "namespaces"."last_ci_minutes_usage_notification_level", "namespaces"."subgroup_creation_level", "namespaces"."emails_disabled", "namespaces"."max_pages_size", "namespaces"."max_artifacts_size", "namespaces"."mentions_disabled", "namespaces"."default_branch_protection", "namespaces"."unlock_membership_to_ldap", "namespaces"."max_personal_access_token_lifetime", "namespaces"."push_rule_id", "namespaces"."shared_runners_enabled", "namespaces"."allow_descendants_override_disabled_shared_runners", "namespaces"."traversal_ids" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 1)
UNION
(SELECT "namespaces"."id", "namespaces"."name", "namespaces"."path", "namespaces"."owner_id", "namespaces"."created_at", "namespaces"."updated_at", "namespaces"."type", "namespaces"."description", "namespaces"."avatar", "namespaces"."membership_lock", "namespaces"."share_with_group_lock", "namespaces"."visibility_level", "namespaces"."request_access_enabled", "namespaces"."ldap_sync_status", "namespaces"."ldap_sync_error", "namespaces"."ldap_sync_last_update_at", "namespaces"."ldap_sync_last_successful_update_at", "namespaces"."ldap_sync_last_sync_at", "namespaces"."description_html", "namespaces"."lfs_enabled", "namespaces"."parent_id", "namespaces"."shared_runners_minutes_limit", "namespaces"."repository_size_limit", "namespaces"."require_two_factor_authentication", "namespaces"."two_factor_grace_period", "namespaces"."cached_markdown_version", "namespaces"."project_creation_level", "namespaces"."runners_token", "namespaces"."file_template_project_id", "namespaces"."saml_discovery_token", "namespaces"."runners_token_encrypted", "namespaces"."custom_project_templates_group_id", "namespaces"."auto_devops_enabled", "namespaces"."extra_shared_runners_minutes_limit", "namespaces"."last_ci_minutes_notification_at", "namespaces"."last_ci_minutes_usage_notification_level", "namespaces"."subgroup_creation_level", "namespaces"."emails_disabled", "namespaces"."max_pages_size", "namespaces"."max_artifacts_size", "namespaces"."mentions_disabled", "namespaces"."default_branch_protection", "namespaces"."unlock_membership_to_ldap", "namespaces"."max_personal_access_token_lifetime", "namespaces"."push_rule_id", "namespaces"."shared_runners_enabled", "namespaces"."allow_descendants_override_disabled_shared_runners", "namespaces"."traversal_ids" FROM "namespaces", "base_and_descendants" WHERE "namespaces"."type" = 'Group' AND "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT "id" FROM "base_and_descendants" AS "namespaces") /*application:test,correlation_id:139d41a3c025a841a20b255a290355be,db_config_name:ci*/
  ↳ ee/app/services/ci/minutes/refresh_cached_data_service.rb:33:in `update_pending_builds!'

How to reproduce

1. Remove ee/spec/services/ci/minutes/additional_packs/change_namespace_service_spec.rb from spec/support/database/cross-join-allowlist.yml
2. Comment out the rescue in https://gitlab.com/gitlab-org/gitlab/-/blob/master/ee/app/services/ci/minutes/refresh_cached_data_service.rb#L15-19
3. Run the following command on the PoC branch

bin/rspec -f d --fail-fast ./ee/spec/services/ci/minutes/additional_packs/change_namespace_service_spec.rb

Note: This needs ci_pending_builds_maintain_ci_minutes_data to be enabled (see also #332951 (closed))

Potential solution

1. It seems we already use self_and_descendant_ids is probably using a denormalized form of the query but it is dependent on the linear
2. Force all_namespaces to be ids, then update_all in batches. Something like:

      # rubocop: disable CodeReuse/ActiveRecord
      def update_pending_builds!
        return unless ::Feature.enabled?(:ci_pending_builds_maintain_ci_minutes_data, @root_namespace, type: :development, default_enabled: :yaml)

        ::Ci::PendingBuild.transaction do
          minutes_exceeded = @root_namespace.ci_minutes_quota.minutes_used_up?
          all_namespace_ids = @root_namespace.self_and_descendant_ids.ids

          all_namespace_ids.in_groups_of(1_000, false) do |namespace_ids|
            ::Ci::PendingBuild.where(namespace: namespace_ids).update_all(minutes_exceeded: minutes_exceeded)
          end
        end
      end
      # rubocop: enable CodeReuse/ActiveRecord