Fix update_all when FROM is a subquery
What does this MR do?
After !45529 (merged), we have found a strange bug in the staging environment. It's not happening all the time but sometimes when propagating a group integration to the descendants.
PG::UniqueViolation: ERROR: duplicate key value violates unique constraint "index_services_on_unique_group_id_and_type"https://sentry.gitlab.net/gitlab/staginggitlabcom/issues/2057034/
After some investigation, we have realized that the batch object contains an ActiveRecord::Relation which translates to different SQL code depending if the batch is created using from_union or ActiveRecord::QueryMethods#or.
batch object is created in PropagateIntegrationInheritDescendantWorker#L14. batch records are updated in BulkUpdateIntegrationService#L12.
Before: batch.update_all(service_hash)
UPDATE "services"
SET "created_at" = $1,
"updated_at" = $2,
"active" = $3,
"properties" = $4,
"push_events" = $5,
"issues_events" = $6,
"merge_requests_events" = $7,
"tag_push_events" = $8,
"note_events" = $9,
"category" = $10,
"wiki_page_events" = $11,
"pipeline_events" = $12,
"confidential_issues_events" = $13,
"commit_events" = $14,
"job_events" = $15,
"confidential_note_events" = $16,
"deployment_events" = $17,
"comment_on_event_enabled" = $18,
"comment_detail" = $19,
"inherit_from_id" = $20,
"alert_events" = $21,
"type" = $22
WHERE "services"."id" BETWEEN $23 AND $24
After: Service.where(id: batch.select(:id)).update_all(service_hash)
UPDATE "services"
SET "created_at" = $1,
"updated_at" = $2,
"active" = $3,
"properties" = $4,
"push_events" = $5,
"issues_events" = $6,
"merge_requests_events" = $7,
"tag_push_events" = $8,
"note_events" = $9,
"category" = $10,
"wiki_page_events" = $11,
"pipeline_events" = $12,
"confidential_issues_events" = $13,
"commit_events" = $14,
"job_events" = $15,
"confidential_note_events" = $16,
"deployment_events" = $17,
"comment_on_event_enabled" = $18,
"comment_detail" = $19,
"inherit_from_id" = $20,
"alert_events" = $21,
"type" = $22
WHERE "services"."id" IN
(SELECT "services"."id"
FROM (
(SELECT "services".*
FROM "services"
WHERE "services"."type" = 'JiraService'
AND "services"."inherit_from_id" IN
(SELECT "services"."id"
FROM "services"
WHERE "services"."type" = 'JiraService'
AND ("services"."group_id" IN
(WITH RECURSIVE "base_and_ancestors" AS (
(SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" = 2)
UNION
(SELECT "namespaces".*
FROM "namespaces",
"base_and_ancestors"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" = "base_and_ancestors"."parent_id")) SELECT id
FROM "base_and_ancestors" AS "namespaces")
OR "services"."instance" = TRUE))
AND "services"."group_id" IN
(WITH RECURSIVE "base_and_descendants" AS (
(SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = 2)
UNION
(SELECT "namespaces".*
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"))
UNION
(SELECT "services".*
FROM "services"
WHERE "services"."type" = 'JiraService'
AND "services"."inherit_from_id" IN
(SELECT "services"."id"
FROM "services"
WHERE "services"."type" = 'JiraService'
AND ("services"."group_id" IN
(WITH RECURSIVE "base_and_ancestors" AS (
(SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" = 2)
UNION
(SELECT "namespaces".*
FROM "namespaces",
"base_and_ancestors"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" = "base_and_ancestors"."parent_id")) SELECT id
FROM "base_and_ancestors" AS "namespaces")
OR "services"."instance" = TRUE))
AND "services"."project_id" IN
(SELECT "projects"."id"
FROM "projects"
WHERE "projects"."namespace_id" IN
(WITH RECURSIVE "base_and_descendants" AS (
(SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" = 2)
UNION
(SELECT "namespaces".*
FROM "namespaces",
"base_and_descendants"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT "namespaces"."id"
FROM "base_and_descendants" AS "namespaces")))) services
WHERE "services"."id" BETWEEN $23 AND $24)Group-level integrations are under group_level_integrations feature flag, so there is no impact on the users.
Related to #273176 (closed)
Edited by Arturo Herrero