Set `projects.has_external_wiki` to `DEFAULT false` and `NOT NULL`

What does this MR do?

This change contains migrations for projects.has_external_wiki to become a NOT NULL column that defaults to FALSE.

This is possible after the PG Trigger added in !49916 (merged) that always maintains projects.has_external_wiki as a boolean and never a NULL.

It includes a data migration to set all historical NULL columns to be either TRUE or FALSE.

The data migration also fixes the historical bad data for has_external_wiki #273574 (closed).

The issue for the validation of the NOT NULL constraint in %13.10 is #296719 (closed).

Related issues: #273574 (closed) #290715 (closed).

Migration output

Up

20210105025900

== 20210105025900 AddDefaultProjectsHasExternalWiki: migrating ================
-- change_column_default(:projects, :has_external_wiki, {:from=>nil, :to=>false})
   -> 0.0073s
== 20210105025900 AddDefaultProjectsHasExternalWiki: migrated (0.0134s) =======

20210105025903

== 20210105025903 AddNotNullConstraintToProjectsHasExternalWiki: migrating ====
-- current_schema()
   -> 0.0003s
-- transaction_open?()
   -> 0.0000s
-- current_schema()
   -> 0.0002s
-- execute("ALTER TABLE projects\nADD CONSTRAINT check_421d399b70\nCHECK ( has_external_wiki IS NOT NULL )\nNOT VALID;\n")
   -> 0.0030s
== 20210105025903 AddNotNullConstraintToProjectsHasExternalWiki: migrated (0.0243s)

20210105030124

== 20210105030124 CleanupProjectsWithNullHasExternalWiki: migrating ===========
-- execute("WITH project_ids_to_update (id) AS (\n  SELECT \"services\".\"project_id\" FROM \"services\" INNER JOIN \"projects\" ON \"projects\".\"id\" = \"services\".\"project_id\" WHERE \"services\".\"active\" = TRUE AND \"services\".\"type\" = 'ExternalWikiService' AND \"services\".\"project_id\" IS NOT NULL AND \"services\".\"id\" >= 53 AND (\"projects\".\"has_external_wiki\" = FALSE OR \"projects\".\"has_external_wiki\" IS NULL) AND \"projects\".\"pending_delete\" = FALSE AND \"projects\".\"archived\" = FALSE\n)\nUPDATE projects SET has_external_wiki = true WHERE id IN (SELECT id FROM project_ids_to_update)\n")
   -> 0.0308s
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:projects, :id, {:where=>"(\n  \"projects\".\"has_external_wiki\" = TRUE\n  OR \"projects\".\"has_external_wiki\" IS NULL\n)\nAND \"projects\".\"pending_delete\" = FALSE\nAND \"projects\".\"archived\" = FALSE\n", :name=>"tmp_index_projects_on_id_where_has_external_wiki_is_true_null", :algorithm=>:concurrently})
   -> 0.0166s
-- execute("SET statement_timeout TO 0")
   -> 0.0002s
-- add_index(:projects, :id, {:where=>"(\n  \"projects\".\"has_external_wiki\" = TRUE\n  OR \"projects\".\"has_external_wiki\" IS NULL\n)\nAND \"projects\".\"pending_delete\" = FALSE\nAND \"projects\".\"archived\" = FALSE\n", :name=>"tmp_index_projects_on_id_where_has_external_wiki_is_true_null", :algorithm=>:concurrently})
   -> 0.0076s
-- execute("RESET ALL")
   -> 0.0002s
-- execute("WITH project_ids_to_update (id) AS (\n  SELECT \"projects\".\"id\" FROM \"projects\" WHERE ((\n  \"projects\".\"has_external_wiki\" = TRUE\n  OR \"projects\".\"has_external_wiki\" IS NULL\n)\nAND \"projects\".\"pending_delete\" = FALSE\nAND \"projects\".\"archived\" = FALSE\n) AND \"projects\".\"id\" >= 19 AND (NOT EXISTS (SELECT 1 FROM \"services\" WHERE (services.project_id = projects.id) AND \"services\".\"type\" = 'ExternalWikiService' AND \"services\".\"active\" = TRUE))\n)\nUPDATE projects SET has_external_wiki = false WHERE id IN (SELECT id FROM project_ids_to_update)\n")
   -> 0.0010s
-- transaction_open?()
   -> 0.0000s
-- indexes(:projects)
   -> 0.0139s
-- remove_index(:projects, {:algorithm=>:concurrently, :name=>"tmp_index_projects_on_id_where_has_external_wiki_is_true_null"})
   -> 0.0022s
== 20210105030124 CleanupProjectsWithNullHasExternalWiki: migrated (0.1960s) ==

Down

20210105030124

Note, this is a no-op.

== 20210105030124 CleanupProjectsWithNullHasExternalWiki: reverting ===========
== 20210105030124 CleanupProjectsWithNullHasExternalWiki: reverted (0.0000s) ==

20210105025903

== 20210105025903 AddNotNullConstraintToProjectsHasExternalWiki: reverting ====
-- execute("ALTER TABLE projects\nDROP CONSTRAINT IF EXISTS check_421d399b70\n")
   -> 0.0020s
== 20210105025903 AddNotNullConstraintToProjectsHasExternalWiki: reverted (0.0067s)

20210105025900

== 20210105025900 AddDefaultProjectsHasExternalWiki: reverting ================
-- change_column_default(:projects, :has_external_wiki, {:from=>false, :to=>nil})
   -> 0.0035s
== 20210105025900 AddDefaultProjectsHasExternalWiki: reverted (0.0081s) =======

SQL queries in CleanupProjectsWithNullHasExternalWiki

These are the formatted outputs of the queries that ran on my local environment within 20210105030124. The queries would be batched. Some discussion about the queries can be found in !50916 (comment 488575274).

Update projects.has_external_wiki to be TRUE.

Note, query is operated on batches of 100 services.

WITH project_ids_to_update (id) AS 
(
   SELECT
      "services"."project_id" 
   FROM
      "services" 
      INNER JOIN
         "projects" 
         ON "projects"."id" = "services"."project_id" 
   WHERE
      "services"."active" = TRUE 
      AND "services"."type" = 'ExternalWikiService' 
      AND "services"."project_id" IS NOT NULL 
      AND "services"."id" >= 100
      AND "services"."id" < 200 
      AND 
      (
         "projects"."has_external_wiki" = FALSE 
         OR "projects"."has_external_wiki" IS NULL
      )
      AND "projects"."pending_delete" = FALSE 
      AND "projects"."archived" = FALSE 
)
UPDATE
   projects 
SET
   has_external_wiki = true 
WHERE
   id IN 
   (
      SELECT
         id 
      FROM
         project_ids_to_update
   )

Temporary index (gets removed during the migration)

CREATE INDEX tmp_index_projects_on_id_where_has_external_wiki_is_true_null 
ON projects USING btree (id) 
WHERE
   (
((has_external_wiki = true) 
      OR 
      (
         has_external_wiki IS NULL
      )
) 
      AND 
      (
         pending_delete = false
      )
      AND 
      (
         archived = false
      )
   )
;

Update projects.has_external_wiki to be FALSE.

Note, query is operated on batches of 100 projects.

WITH project_ids_to_update (id) AS 
(
   SELECT
      "projects"."id" 
   FROM
      "projects" 
   WHERE
      (
( "projects"."has_external_wiki" = TRUE 
         OR "projects"."has_external_wiki" IS NULL ) 
         AND "projects"."pending_delete" = FALSE 
         AND "projects"."archived" = FALSE 
      )
      AND "projects"."id" >= 100
      AND "projects"."id" < 200 
      AND 
      (
         NOT EXISTS 
         (
            SELECT
               1 
            FROM
               "services" 
            WHERE
               (
                  services.project_id = projects.id
               )
               AND "services"."type" = 'ExternalWikiService' 
               AND "services"."active" = TRUE
         )
      )
)
UPDATE
   projects 
SET
   has_external_wiki = false 
WHERE
   id IN 
   (
      SELECT
         id 
      FROM
         project_ids_to_update
   )

Does this MR meet the acceptance criteria?

Conformity

• Changelog entry
• [-] Documentation (if required)
• Code review guidelines
• Merge request performance guidelines
• Style guides
• Database guides
• [-] Separation of EE specific content

Availability and Testing

• Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.
• [-] Tested in all supported browsers
• [-] Informed Infrastructure department of a default or new setting change, if applicable per definition of done

Related to #290715 (closed) #273574 (closed)