Add new scopes to security_policy_dismissals

What does this MR do and why?

This adds a new scope to security_policy_dismissals to support filtering for dependencies dismissed by warn-mode policies.

The new scope will be used here.

Query

SELECT DISTINCT
    unnest(license_occurrence_uuids)
FROM
    "security_policy_dismissals"
WHERE
    "security_policy_dismissals"."status" = 1
    AND (license_occurrence_uuids && ARRAY['f174724a-8b42-56e5-8927-8d69ef906d75', 'a28a055c-7074-5ac6-9c4d-ba7d4ead9948', '1f042a22-63c1-5885-9884-7b2caa822a48', 'a80aab63-3bbf-5c1b-85f1-96ffe4583232']::text[])

query plan without index

https://postgres.ai/console/gitlab/gitlab-production-main/sessions/45817/commands/140243

query plan with index

https://postgres.ai/console/gitlab/gitlab-production-main/sessions/45817/commands/140245

Migration up

main: == [advisory_lock_connection] object_id: 130440, pg_backend_pid: 47030
main: == 20251125150714 AddLicenseOccurrenceUuidsIndexToSecurityPolicyDismissals: migrating 
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0915s
main: -- index_exists?(:security_policy_dismissals, :license_occurrence_uuids, {:using=>:gin, :name=>"idx_security_policy_dismissals_license_occurrence_uuids", :where=>"status = 1", :algorithm=>:concurrently})
main:    -> 0.0083s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0006s
main: -- add_index(:security_policy_dismissals, :license_occurrence_uuids, {:using=>:gin, :name=>"idx_security_policy_dismissals_license_occurrence_uuids", :where=>"status = 1", :algorithm=>:concurrently})
main:    -> 0.0116s
main: -- execute("RESET statement_timeout")
main:    -> 0.0007s
main: == 20251125150714 AddLicenseOccurrenceUuidsIndexToSecurityPolicyDismissals: migrated (0.2115s) 

Migration down

main: == [advisory_lock_connection] object_id: 130660, pg_backend_pid: 54980
main: == 20251125150714 AddLicenseOccurrenceUuidsIndexToSecurityPolicyDismissals: reverting 
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0530s
main: -- index_exists?(:security_policy_dismissals, :license_occurrence_uuids, {:name=>"idx_security_policy_dismissals_license_occurrence_uuids", :algorithm=>:concurrently})
main:    -> 0.0064s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0010s
main: -- remove_index(:security_policy_dismissals, {:name=>"idx_security_policy_dismissals_license_occurrence_uuids", :algorithm=>:concurrently, :column=>:license_occurrence_uuids})
main:    -> 0.0095s
main: -- execute("RESET statement_timeout")
main:    -> 0.0007s
main: == 20251125150714 AddLicenseOccurrenceUuidsIndexToSecurityPolicyDismissals: reverted (0.1268s) 

main: == [advisory_lock_connection] object_id: 130660, pg_backend_pid: 54980

References

Related to: #580545 (closed)

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.