Validate foreign key on GroupHooks

What does this MR do?

In %13.11, we added a NOT VALID foreign key constraint to the column to ensure GitLab doesn’t create inconsistent records !57735 (merged).

We also added a data migration, to fix or clean up existing records !57863 (merged).

We can now validate the foreign key. Reference https://docs.gitlab.com/ee/development/database/add_foreign_key_to_existing_column.html

But

The migration to validate the foreign keys !60266 (merged) failed in production and we reverted !60280 (merged).

We have 473 invalid records in production WHERE "web_hooks"."group_id" IS NOT NULL AND "web_hooks"."project_id" IS NOT NULL. We have to fix the invalid records before being able to validate the foreign key, more details in !60266 (comment 561648867).

SQL statement

UPDATE "web_hooks"
SET "group_id" = NULL
WHERE "web_hooks"."type" = 'ProjectHook'
  AND "web_hooks"."project_id" IS NOT NULL
  AND "web_hooks"."group_id" IS NOT NULL
  AND "web_hooks"."id" BETWEEN 280001 AND 290000;

Time: 451.605 ms
  - planning: 0.202 ms
  - execution: 451.403 ms (estimated* for prod: 0.036...0.405 s)
    - I/O read: 426.403 ms
    - I/O write: N/A

https://console.postgres.ai/shared/d1afaf8a-4027-4586-ba40-7c8bc894f6df

SQL execution time

Executing the query in #database-lab give the following numbers:

EXEC UPDATE "web_hooks"
SET "group_id" = NULL
WHERE "web_hooks"."type" = 'ProjectHook'
  AND "web_hooks"."project_id" IS NOT NULL
  AND "web_hooks"."group_id" IS NOT NULL
  AND "web_hooks"."id" BETWEEN 280001 AND 290000;

The query has been executed. Duration: 0.234 s (estimated* for prod: 0.022...0.214 s)

I know we are just over the limit that specifies that any single query must stay below 1 second execution time with cold caches, so I'm not sure if we should use each_batch instead. Any advice is welcome.

The foreign key validation is now working compared to what we saw before !60266 (comment 560457544).

EXEC ALTER TABLE web_hooks VALIDATE CONSTRAINT fk_rails_d35697648e;

The query has been executed. Duration: 5.083 s (estimated* for prod: 0.446...4.661 s)

Migration script

$ rails db:migrate
== 20210428151144 UpdateInvalidWebHooks: migrating ============================
== 20210428151144 UpdateInvalidWebHooks: migrated (0.0170s) ===================

== 20210428151238 ValidateForeignKeyOnGroupHooks: migrating ===================
-- foreign_keys(:web_hooks)
   -> 0.0069s
-- execute("SET statement_timeout TO 0")
   -> 0.0006s
-- execute("ALTER TABLE web_hooks VALIDATE CONSTRAINT fk_rails_d35697648e;")
   -> 0.0117s
-- execute("RESET ALL")
   -> 0.0007s
== 20210428151238 ValidateForeignKeyOnGroupHooks: migrated (0.0209s) ==========

Related to #202423 (closed)