Skip to content

Replace runners token indices in projects table

Pedro Pombeiro requested to merge pedropombeiro/355977/replace-runner_token_indices-with-uniq into master

What does this MR do and why?

Describe in detail what your merge request does and why.

Having ensured that no duplicates remain in the projects table with !81991 (merged), we should now:

  1. add a migration to create unique indices
  2. drop the existing non-unique ones (index_projects_on_runners_token and index_projects_on_runners_token_encrypted)
  3. drop the temporary indices created in !81991 (merged) (tmp_index_projects_on_id_and_runners_token and tmp_index_projects_on_id_and_runners_token_encrypted).

NOTE: I'm assuming that any admin of a self-managed instance will have already gone through the %14.9 migrations before they run these. Please let me know if there is a flaw in my reasoning, or if there is a process I need to follow in order to ensure that happens (e.g. through https://docs.gitlab.com/ee/update/#version-specific-upgrading-instructions).

Closes #355977 (closed)

Screenshots or screen recordings

These are strongly recommended to assist reviewers and reduce the time to merge your change.

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

Database migration logs

Migrate 
main: == 20220617123022 AddUniqueIndexOnProjectsOnRunnersToken: migrating ===========
main: -- transaction_open?()
main:    -> 0.0000s
main: -- index_exists?(:projects, :runners_token, {:name=>"index_uniq_projects_on_runners_token", :unique=>true, :algorithm=>:concurrently})
main:    -> 0.0141s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0004s
main: -- add_index(:projects, :runners_token, {:name=>"index_uniq_projects_on_runners_token", :unique=>true, :algorithm=>:concurrently})
main:    -> 0.0017s
main: -- execute("RESET statement_timeout")
main:    -> 0.0002s
main: == 20220617123022 AddUniqueIndexOnProjectsOnRunnersToken: migrated (0.0387s) ==

main: == 20220617123034 AddUniqueIndexOnProjectsOnRunnersTokenEncrypted: migrating ==
main: -- transaction_open?()
main:    -> 0.0000s
main: -- index_exists?(:projects, :runners_token_encrypted, {:name=>"index_uniq_projects_on_runners_token_encrypted", :unique=>true, :algorithm=>:concurrently})
main:    -> 0.0117s
main: -- add_index(:projects, :runners_token_encrypted, {:name=>"index_uniq_projects_on_runners_token_encrypted", :unique=>true, :algorithm=>:concurrently})
main:    -> 0.0012s
main: == 20220617123034 AddUniqueIndexOnProjectsOnRunnersTokenEncrypted: migrated (0.0143s)

main: == 20220617123105 DropIndexOnProjectsOnRunnersToken: migrating ================
main: -- transaction_open?()
main:    -> 0.0000s
main: -- indexes(:projects)
main:    -> 0.0109s
main: -- remove_index(:projects, {:algorithm=>:concurrently, :name=>"index_projects_on_runners_token"})
main:    -> 0.0015s
main: == 20220617123105 DropIndexOnProjectsOnRunnersToken: migrated (0.0135s) =======

main: == 20220617123113 DropIndexOnProjectsOnRunnersTokenEncrypted: migrating =======
main: -- transaction_open?()
main:    -> 0.0000s
main: -- indexes(:projects)
main:    -> 0.0108s
main: -- remove_index(:projects, {:algorithm=>:concurrently, :name=>"index_projects_on_runners_token_encrypted"})
main:    -> 0.0008s
main: == 20220617123113 DropIndexOnProjectsOnRunnersTokenEncrypted: migrated (0.0128s)

main: == 20220617123135 DropTempIndexOnProjectsOnIdAndRunnersToken: migrating =======
main: -- transaction_open?()
main:    -> 0.0000s
main: -- indexes(:projects)
main:    -> 0.0108s
main: -- remove_index(:projects, {:algorithm=>:concurrently, :name=>"tmp_index_projects_on_id_and_runners_token"})
main:    -> 0.0008s
main: == 20220617123135 DropTempIndexOnProjectsOnIdAndRunnersToken: migrated (0.0127s)

main: == 20220617123144 DropTempIndexOnProjectsOnIdAndRunnersTokenEncrypted: migrating
main: -- transaction_open?()
main:    -> 0.0000s
main: -- indexes(:projects)
main:    -> 0.0102s
main: -- remove_index(:projects, {:algorithm=>:concurrently, :name=>"tmp_index_projects_on_id_and_runners_token_encrypted"})
main:    -> 0.0007s
main: == 20220617123144 DropTempIndexOnProjectsOnIdAndRunnersTokenEncrypted: migrated (0.0120s)

ci: == 20220617123022 AddUniqueIndexOnProjectsOnRunnersToken: migrating ===========
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- index_exists?(:projects, :runners_token, {:name=>"index_uniq_projects_on_runners_token", :unique=>true, :algorithm=>:concurrently})
ci:    -> 0.0119s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0002s
ci: -- add_index(:projects, :runners_token, {:name=>"index_uniq_projects_on_runners_token", :unique=>true, :algorithm=>:concurrently})
ci:    -> 0.0015s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0002s
ci: == 20220617123022 AddUniqueIndexOnProjectsOnRunnersToken: migrated (0.0154s) ==

ci: == 20220617123034 AddUniqueIndexOnProjectsOnRunnersTokenEncrypted: migrating ==
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- index_exists?(:projects, :runners_token_encrypted, {:name=>"index_uniq_projects_on_runners_token_encrypted", :unique=>true, :algorithm=>:concurrently})
ci:    -> 0.0139s
ci: -- add_index(:projects, :runners_token_encrypted, {:name=>"index_uniq_projects_on_runners_token_encrypted", :unique=>true, :algorithm=>:concurrently})
ci:    -> 0.0017s
ci: == 20220617123034 AddUniqueIndexOnProjectsOnRunnersTokenEncrypted: migrated (0.0182s)

ci: == 20220617123105 DropIndexOnProjectsOnRunnersToken: migrating ================
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- indexes(:projects)
ci:    -> 0.0154s
ci: -- remove_index(:projects, {:algorithm=>:concurrently, :name=>"index_projects_on_runners_token"})
ci:    -> 0.0018s
ci: == 20220617123105 DropIndexOnProjectsOnRunnersToken: migrated (0.0220s) =======

ci: == 20220617123113 DropIndexOnProjectsOnRunnersTokenEncrypted: migrating =======
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- indexes(:projects)
ci:    -> 0.0154s
ci: -- remove_index(:projects, {:algorithm=>:concurrently, :name=>"index_projects_on_runners_token_encrypted"})
ci:    -> 0.0010s
ci: == 20220617123113 DropIndexOnProjectsOnRunnersTokenEncrypted: migrated (0.0183s)

ci: == 20220617123135 DropTempIndexOnProjectsOnIdAndRunnersToken: migrating =======
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- indexes(:projects)
ci:    -> 0.0149s
ci: -- remove_index(:projects, {:algorithm=>:concurrently, :name=>"tmp_index_projects_on_id_and_runners_token"})
ci:    -> 0.0011s
ci: == 20220617123135 DropTempIndexOnProjectsOnIdAndRunnersToken: migrated (0.0178s)

ci: == 20220617123144 DropTempIndexOnProjectsOnIdAndRunnersTokenEncrypted: migrating
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- indexes(:projects)
ci:    -> 0.0119s
ci: -- remove_index(:projects, {:algorithm=>:concurrently, :name=>"tmp_index_projects_on_id_and_runners_token_encrypted"})
ci:    -> 0.0009s
ci: == 20220617123144 DropTempIndexOnProjectsOnIdAndRunnersTokenEncrypted: migrated (0.0142s)
Rollback 
main: == 20220617123144 DropTempIndexOnProjectsOnIdAndRunnersTokenEncrypted: reverting
main: -- transaction_open?()
main:    -> 0.0000s
main: -- index_exists?(:projects, [:id, :runners_token_encrypted], {:where=>"runners_token_encrypted IS NOT NULL", :unique=>false, :name=>"tmp_index_projects_on_id_and_runners_token_encrypted", :algorithm=>:concurrently})
main:    -> 0.0410s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0001s
main: -- add_index(:projects, [:id, :runners_token_encrypted], {:where=>"runners_token_encrypted IS NOT NULL", :unique=>false, :name=>"tmp_index_projects_on_id_and_runners_token_encrypted", :algorithm=>:concurrently})
main:    -> 0.0023s
main: -- execute("RESET statement_timeout")
main:    -> 0.0002s
main: == 20220617123144 DropTempIndexOnProjectsOnIdAndRunnersTokenEncrypted: reverted (0.0494s)

ci: == 20220617123144 DropTempIndexOnProjectsOnIdAndRunnersTokenEncrypted: reverting
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- index_exists?(:projects, [:id, :runners_token_encrypted], {:where=>"runners_token_encrypted IS NOT NULL", :unique=>false, :name=>"tmp_index_projects_on_id_and_runners_token_encrypted", :algorithm=>:concurrently})
ci:    -> 0.0388s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0001s
ci: -- add_index(:projects, [:id, :runners_token_encrypted], {:where=>"runners_token_encrypted IS NOT NULL", :unique=>false, :name=>"tmp_index_projects_on_id_and_runners_token_encrypted", :algorithm=>:concurrently})
ci:    -> 0.0026s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0002s
ci: == 20220617123144 DropTempIndexOnProjectsOnIdAndRunnersTokenEncrypted: reverted (0.0468s)

main: == 20220617123135 DropTempIndexOnProjectsOnIdAndRunnersToken: reverting =======
main: -- transaction_open?()
main:    -> 0.0000s
main: -- index_exists?(:projects, [:id, :runners_token], {:where=>"runners_token IS NOT NULL", :unique=>false, :name=>"tmp_index_projects_on_id_and_runners_token", :algorithm=>:concurrently})
main:    -> 0.0368s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0001s
main: -- add_index(:projects, [:id, :runners_token], {:where=>"runners_token IS NOT NULL", :unique=>false, :name=>"tmp_index_projects_on_id_and_runners_token", :algorithm=>:concurrently})
main:    -> 0.0150s
main: -- execute("RESET statement_timeout")
main:    -> 0.0002s
main: == 20220617123135 DropTempIndexOnProjectsOnIdAndRunnersToken: reverted (0.0566s)

ci: == 20220617123135 DropTempIndexOnProjectsOnIdAndRunnersToken: reverting =======
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- index_exists?(:projects, [:id, :runners_token], {:where=>"runners_token IS NOT NULL", :unique=>false, :name=>"tmp_index_projects_on_id_and_runners_token", :algorithm=>:concurrently})
ci:    -> 0.0391s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0001s
ci: -- add_index(:projects, [:id, :runners_token], {:where=>"runners_token IS NOT NULL", :unique=>false, :name=>"tmp_index_projects_on_id_and_runners_token", :algorithm=>:concurrently})
ci:    -> 0.0018s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0002s
ci: == 20220617123135 DropTempIndexOnProjectsOnIdAndRunnersToken: reverted (0.0460s)

main: == 20220617123113 DropIndexOnProjectsOnRunnersTokenEncrypted: reverting =======
main: -- transaction_open?()
main:    -> 0.0000s
main: -- index_exists?(:projects, :runners_token_encrypted, {:name=>"index_projects_on_runners_token_encrypted", :algorithm=>:concurrently})
main:    -> 0.0155s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0001s
main: -- add_index(:projects, :runners_token_encrypted, {:name=>"index_projects_on_runners_token_encrypted", :algorithm=>:concurrently})
main:    -> 0.0019s
main: -- execute("RESET statement_timeout")
main:    -> 0.0002s
main: == 20220617123113 DropIndexOnProjectsOnRunnersTokenEncrypted: reverted (0.0227s)

ci: == 20220617123113 DropIndexOnProjectsOnRunnersTokenEncrypted: reverting =======
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- index_exists?(:projects, :runners_token_encrypted, {:name=>"index_projects_on_runners_token_encrypted", :algorithm=>:concurrently})
ci:    -> 0.0402s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0002s
ci: -- add_index(:projects, :runners_token_encrypted, {:name=>"index_projects_on_runners_token_encrypted", :algorithm=>:concurrently})
ci:    -> 0.0017s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0002s
ci: == 20220617123113 DropIndexOnProjectsOnRunnersTokenEncrypted: reverted (0.0469s)

main: == 20220617123105 DropIndexOnProjectsOnRunnersToken: reverting ================
main: -- transaction_open?()
main:    -> 0.0000s
main: -- index_exists?(:projects, :runners_token, {:name=>"index_projects_on_runners_token", :algorithm=>:concurrently})
main:    -> 0.0152s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0001s
main: -- add_index(:projects, :runners_token, {:name=>"index_projects_on_runners_token", :algorithm=>:concurrently})
main:    -> 0.0019s
main: -- execute("RESET statement_timeout")
main:    -> 0.0002s
main: == 20220617123105 DropIndexOnProjectsOnRunnersToken: reverted (0.0216s) =======

ci: == 20220617123105 DropIndexOnProjectsOnRunnersToken: reverting ================
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- index_exists?(:projects, :runners_token, {:name=>"index_projects_on_runners_token", :algorithm=>:concurrently})
ci:    -> 0.0416s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0002s
ci: -- add_index(:projects, :runners_token, {:name=>"index_projects_on_runners_token", :algorithm=>:concurrently})
ci:    -> 0.0026s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0002s
ci: == 20220617123105 DropIndexOnProjectsOnRunnersToken: reverted (0.0494s) =======

main: == 20220617123034 AddUniqueIndexOnProjectsOnRunnersTokenEncrypted: reverting ==
main: -- transaction_open?()
main:    -> 0.0000s
main: -- indexes(:projects)
main:    -> 0.0367s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0001s
main: -- remove_index(:projects, {:algorithm=>:concurrently, :name=>"index_uniq_projects_on_runners_token_encrypted"})
main:    -> 0.0017s
main: -- execute("RESET statement_timeout")
main:    -> 0.0001s
main: == 20220617123034 AddUniqueIndexOnProjectsOnRunnersTokenEncrypted: reverted (0.0425s)

ci: == 20220617123034 AddUniqueIndexOnProjectsOnRunnersTokenEncrypted: reverting ==
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- indexes(:projects)
ci:    -> 0.0154s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0002s
ci: -- remove_index(:projects, {:algorithm=>:concurrently, :name=>"index_uniq_projects_on_runners_token_encrypted"})
ci:    -> 0.0017s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0002s
ci: == 20220617123034 AddUniqueIndexOnProjectsOnRunnersTokenEncrypted: reverted (0.0220s)

main: == 20220617123022 AddUniqueIndexOnProjectsOnRunnersToken: reverting ===========
main: -- transaction_open?()
main:    -> 0.0000s
main: -- indexes(:projects)
main:    -> 0.0377s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0001s
main: -- remove_index(:projects, {:algorithm=>:concurrently, :name=>"index_uniq_projects_on_runners_token"})
main:    -> 0.0017s
main: -- execute("RESET statement_timeout")
main:    -> 0.0002s
main: == 20220617123022 AddUniqueIndexOnProjectsOnRunnersToken: reverted (0.0441s) ==

ci: == 20220617123022 AddUniqueIndexOnProjectsOnRunnersToken: reverting ===========
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- indexes(:projects)
ci:    -> 0.0388s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0001s
ci: -- remove_index(:projects, {:algorithm=>:concurrently, :name=>"index_uniq_projects_on_runners_token"})
ci:    -> 0.0019s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0002s
ci: == 20220617123022 AddUniqueIndexOnProjectsOnRunnersToken: reverted (0.0454s) ==
## MR acceptance checklist

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

Edited by Pedro Pombeiro

Merge request reports