Backfill slack_integrations_scopes sharding key, upsert slack_api_scopes
What does this MR do and why?
Setting project_id, group_id, organization_id
based on the associated integrations record.
Not using the closer slack_integrations record
as that backfill hasn't been finalized on that table yet.
At the same time this BBM moves records in the slack_integrations_scopes to be associated with new upserted records in the slack_api_scopes table. These new upserted records will all have a sharding key set (organization_id). When the backfill is finalized, we can DELETE all records in the slack_api_scopes table that don't have an organization_i before validating the NOT NULL constraint on that column.
Only 3 records exist today in the slack_api_scopes table. And unless there's some test data for other organizations, we should only create 3 new records in total (all with organization_id=1). But the BBM can handle any scenario, and should work fine in the separate test cell we already have.
Query plans
Update sharindg key and upsert slack_api_scope
https://console.postgres.ai/gitlab/gitlab-production-main/sessions/46455/commands/141696
WITH relation AS MATERIALIZED (
SELECT
"slack_integrations_scopes"."id",
"slack_integrations_scopes"."slack_integration_id",
"slack_integrations_scopes"."slack_api_scope_id"
FROM
"slack_integrations_scopes"
WHERE
"slack_integrations_scopes"."id" BETWEEN 1
AND 3647
AND "slack_integrations_scopes"."id" >= 1
AND "slack_integrations_scopes"."id" < 194
LIMIT
100
), required_scopes AS MATERIALIZED (
SELECT
"relation"."id",
"integrations"."project_id",
"integrations"."group_id",
"integrations"."organization_id",
COALESCE(
"integrations"."organization_id",
"namespaces"."organization_id",
"projects"."organization_id"
) AS "computed_organization_id",
"slack_api_scopes"."name"
FROM
"relation"
JOIN "slack_integrations" ON "slack_integrations"."id" = "relation"."slack_integration_id"
JOIN "integrations" ON "integrations"."id" = "slack_integrations"."integration_id"
LEFT JOIN "namespaces" ON "namespaces"."id" = "integrations"."group_id"
LEFT JOIN "projects" ON "projects"."id" = "integrations"."project_id"
JOIN "slack_api_scopes" ON "slack_api_scopes"."id" = "relation"."slack_api_scope_id"
),
upserted_api_scopes AS MATERIALIZED (
INSERT INTO "slack_api_scopes" ("organization_id", "name")
SELECT
DISTINCT ON (
"computed_organization_id", "name"
) "computed_organization_id",
"name"
FROM
"required_scopes" ON CONFLICT ("organization_id", "name") DO
UPDATE
SET
"name" = EXCLUDED."name" RETURNING *
)
UPDATE
"slack_integrations_scopes"
SET
"project_id" = "required_scopes"."project_id",
"group_id" = "required_scopes"."group_id",
"organization_id" = "required_scopes"."organization_id",
"slack_api_scope_id" = "upserted_api_scopes"."id"
FROM
"required_scopes"
JOIN "upserted_api_scopes" ON "upserted_api_scopes"."organization_id" = "required_scopes"."computed_organization_id"
AND "upserted_api_scopes"."name" = "required_scopes"."name"
WHERE
"slack_integrations_scopes"."id" = "required_scopes"."id"