Migrate namespaces delayed_project_removal to namespace_settings [RUN ALL RSPEC] [RUN AS-IF-FOSS] (!53916) · Merge requests · GitLab.org / GitLab

Drew Blessing requested to merge dblessing_migrate_delayed_project_removal into master Feb 10, 2021

What does this MR do?

Based on the work in #206913 (closed) to migrate another column.

The POC for cascading settings identified that we want namespace settings isolated to the namespace_settings table so the namespaces table doesn't become too wide with lock columns.

Migrating this column to namespace_settings will clear the way for us to implement cascading settings with the delayed_project_removal setting.

The setting defaults to false and on GitLab.com there are only 134 out of 3+ million namespace records so a post migration should be just fine. Self-managed instances can also change this setting but it's a one-off setting for each namespace and the post migrate should be sufficient in this case, too.

Migrations

Up

== 20210214201118 AddDelayedProjectRemovalToNamespaceSettings: migrating ======
-- add_column(:namespace_settings, :delayed_project_removal, :boolean, {:default=>false, :null=>false})
   -> 0.0044s
== 20210214201118 AddDelayedProjectRemovalToNamespaceSettings: migrated (0.0045s)

== 20210214205155 AddIndexToNamespacesDelayedProjectRemoval: migrating ========
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:namespaces, :delayed_project_removal, {:name=>"tmp_idx_on_namespaces_delayed_project_removal", :where=>"delayed_project_removal = TRUE", :algorithm=>:concurrently})
   -> 0.0068s
-- add_index(:namespaces, :delayed_project_removal, {:name=>"tmp_idx_on_namespaces_delayed_project_removal", :where=>"delayed_project_removal = TRUE", :algorithm=>:concurrently})
   -> 0.0103s
== 20210214205155 AddIndexToNamespacesDelayedProjectRemoval: migrated (0.0180s)

== 20210215095328 MigrateDelayedProjectRemovalFromNamespacesToNamespaceSettings: migrating
-- execute("INSERT INTO namespace_settings (namespace_id, delayed_project_removal, created_at, updated_at)\nVALUES (1, TRUE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), (109, TRUE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), (110, TRUE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), (111, TRUE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), (112, TRUE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), (113, TRUE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), (114, TRUE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), (115, TRUE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), (116, TRUE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), (117, TRUE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), (118, TRUE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), (119, TRUE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), (120, TRUE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), (121, TRUE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), (122, TRUE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), (123, TRUE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), (124, TRUE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), (125, TRUE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), (126, TRUE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), (127, TRUE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), (128, TRUE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), (129, TRUE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), (130, TRUE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), (131, TRUE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), (132, TRUE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), (133, TRUE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), (134, TRUE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), (135, TRUE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), (136, TRUE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), (137, TRUE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), (138, TRUE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), (139, TRUE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), (140, TRUE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), (141, TRUE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), (142, TRUE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), (143, TRUE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), (144, TRUE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)\nON CONFLICT (namespace_id) DO UPDATE\n  SET delayed_project_removal = TRUE\n")
   -> 0.0106s
== 20210215095328 MigrateDelayedProjectRemovalFromNamespacesToNamespaceSettings: migrated (0.0824s)

Down

== 20210215095328 MigrateDelayedProjectRemovalFromNamespacesToNamespaceSettings: reverting
== 20210215095328 MigrateDelayedProjectRemovalFromNamespacesToNamespaceSettings: reverted (0.0000s)

== 20210214205155 AddIndexToNamespacesDelayedProjectRemoval: reverting ========
-- transaction_open?()
   -> 0.0000s
-- indexes(:namespaces)
   -> 0.0074s
-- execute("SET statement_timeout TO 0")
   -> 0.0004s
-- remove_index(:namespaces, {:algorithm=>:concurrently, :name=>"tmp_idx_on_namespaces_delayed_project_removal"})
   -> 0.0026s
-- execute("RESET ALL")
   -> 0.0005s
== 20210214205155 AddIndexToNamespacesDelayedProjectRemoval: reverted (0.0118s)

== 20210214201118 AddDelayedProjectRemovalToNamespaceSettings: reverting ======
-- remove_column(:namespace_settings, :delayed_project_removal, :boolean, {:default=>false, :null=>false})
   -> 0.0027s
== 20210214201118 AddDelayedProjectRemovalToNamespaceSettings: reverted (0.0052s)

Database

Index creation test in #database-lab:

exec create index concurrently index_on_namespaces_delayed_project_removal ON namespaces USING btree (delayed_project_removal) WHERE (delayed_project_removal = true);

The query has been executed. Duration: 75.124 s (estimated* for prod: 8.173...75.124 s)

% time      seconds wait_event
------ ------------ -----------------------------
88.85     66.747827 IO.DataFileRead
10.77      8.087504 Running
0.27       0.203061 IO.SLRURead
0.10       0.074703 IO.DataFileWrite
0.01       0.011115 IPC.BgWorkerShutdown
------ ------------ -----------------------------
100.00    75.124210

Note: These explains were not executed via #database-lab because the index doesn't currently exist and the new column doesn't exist.

Select Explain: https://explain.depesz.com/s/gJof

select id from namespaces where delayed_project_removal = TRUE;

Insert Explain: https://explain.depesz.com/s/rL7z

insert into namespace_settings (namespace_id, delayed_project_removal, created_at, updated_at) values (3000, TRUE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), (4000, TRUE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP) ON CONFLICT (namespace_id) DO UPDATE SET delayed_project_removal = TRUE;

Screenshots (strongly suggested)

Does this MR meet the acceptance criteria?

Conformity

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

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

Label as security and @ mention @gitlab-com/gl-security/appsec
The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
Security reports checked/validated by a reviewer from the AppSec team

Edited Feb 23, 2021 by Mark Chao

Migrate namespaces delayed_project_removal to namespace_settings [RUN ALL RSPEC] [RUN AS-IF-FOSS]