Background Migration to truncate overlong vulnerability titles

What does this MR do and why?

Per #324117 (closed), there are vulnerabilities wherein the title_html attribute has exceeded the 800 character expected size for the schema, resulting in request failures when attempting to request them. This MR creates a temporary index on these vulnerabilities to rapidly select them, and then enqueues a background migration which will use this index to grab these incorrect records and truncate their titles to the expected length.

Query Impact

The temporary index should make the select queries highly selective and more efficient than they appear in postgres.ai.

Index Apply

CREATE INDEX CONCURRENTLY "tmp_index_vulnerability_overlong_title_html" ON "vulnerabilities" ("id")
WHERE
    LENGTH(title_html) > 800

The query has been executed. Duration: 5.494 min

1

SELECT
    "vulnerabilities"."id"
FROM
    "vulnerabilities"
WHERE
    "vulnerabilities"."id" BETWEEN 1 AND 500
    AND (LENGTH(title_html) > 800)
ORDER BY
    "vulnerabilities"."id" ASC
LIMIT 1

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/13791/commands/48383

2

SELECT
    "vulnerabilities"."id"
FROM
    "vulnerabilities"
WHERE
    "vulnerabilities"."id" BETWEEN 1 AND 500
    AND (LENGTH(title_html) > 800)
    AND "vulnerabilities"."id" >= 1
ORDER BY
    "vulnerabilities"."id" ASC
LIMIT 1 OFFSET 200

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/13791/commands/48384

3

SELECT
    "vulnerabilities".*
FROM
    "vulnerabilities"
WHERE
    "vulnerabilities"."id" BETWEEN 1 AND 500
    AND (LENGTH(title_html) > 800)
    AND "vulnerabilities"."id" >= 1

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/13791/commands/48385

4

SELECT
    "vulnerabilities".*
FROM
    "vulnerabilities"
WHERE
    "vulnerabilities"."id" = 1
LIMIT 1

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/13791/commands/48387

5

UPDATE
    "vulnerabilities"
SET
    "updated_at" = '2022-12-07 10:53:26.211237',
    "title_html" = 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa...'
WHERE
    "vulnerabilities"."id" = 1

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/13791/commands/48388

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

• I have evaluated the MR acceptance checklist for this MR.

Related to #324117 (closed)