Add notes.namespace_id index and foreign key
What does this MR do and why?
Add notes.namespace_id index and foreign key
The index has been created on GitLab.com asynchronously:
gitlabhq_dblab=# \d index_notes_on_namespace_id
Index "public.index_notes_on_namespace_id"
Column | Type | Key? | Definition
--------------+--------+------+--------------
namespace_id | bigint | yes | namespace_id
btree, for table "public.notes"
Migration Output
Up:
main: == [advisory_lock_connection] object_id: 224740, pg_backend_pid: 1468
main: == 20230731090319 AddNotesNamespaceIdForeignKey: migrating ====================
main: -- transaction_open?()
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.0974s
main: -- index_exists?(:notes, :namespace_id, {:name=>"index_notes_on_namespace_id", :algorithm=>:concurrently})
main: -> 0.0048s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0002s
main: -- add_index(:notes, :namespace_id, {:name=>"index_notes_on_namespace_id", :algorithm=>:concurrently})
main: -> 0.0086s
main: -- execute("RESET statement_timeout")
main: -> 0.0003s
main: -- transaction_open?()
main: -> 0.0000s
main: -- transaction_open?()
main: -> 0.0000s
main: -- execute("ALTER TABLE notes ADD CONSTRAINT fk_76db6d50c6 FOREIGN KEY (namespace_id) REFERENCES namespaces (id) ON DELETE CASCADE NOT VALID;")
main: -> 0.0031s
main: -- execute("ALTER TABLE notes VALIDATE CONSTRAINT fk_76db6d50c6;")
main: -> 0.0088s
main: == 20230731090319 AddNotesNamespaceIdForeignKey: migrated (0.1702s) ===========
main: == [advisory_lock_connection] object_id: 224740, pg_backend_pid: 1468
ci: == [advisory_lock_connection] object_id: 225140, pg_backend_pid: 1471
ci: == 20230731090319 AddNotesNamespaceIdForeignKey: migrating ====================
ci: -- transaction_open?()
ci: -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci: -> 0.0006s
ci: -- index_exists?(:notes, :namespace_id, {:name=>"index_notes_on_namespace_id", :algorithm=>:concurrently})
ci: -> 0.0056s
ci: -- execute("SET statement_timeout TO 0")
ci: -> 0.0002s
ci: -- add_index(:notes, :namespace_id, {:name=>"index_notes_on_namespace_id", :algorithm=>:concurrently})
ci: -> 0.0029s
ci: -- execute("RESET statement_timeout")
ci: -> 0.0002s
ci: -- transaction_open?()
ci: -> 0.0000s
ci: -- transaction_open?()
ci: -> 0.0000s
ci: -- execute("ALTER TABLE notes ADD CONSTRAINT fk_76db6d50c6 FOREIGN KEY (namespace_id) REFERENCES namespaces (id) ON DELETE CASCADE NOT VALID;")
ci: -> 0.0036s
ci: -- execute("ALTER TABLE notes VALIDATE CONSTRAINT fk_76db6d50c6;")
ci: -> 0.0091s
ci: == 20230731090319 AddNotesNamespaceIdForeignKey: migrated (0.0536s) ===========
Down:
ci: == [advisory_lock_connection] object_id: 224460, pg_backend_pid: 2312
ci: == 20230731090319 AddNotesNamespaceIdForeignKey: reverting ====================
ci: -- remove_foreign_key(:notes, {:column=>:namespace_id})
ci: -> 0.0772s
ci: -- transaction_open?()
ci: -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci: -> 0.0060s
ci: -- indexes(:notes)
ci: -> 0.0718s
ci: -- execute("SET statement_timeout TO 0")
ci: -> 0.0003s
ci: -- remove_index(:notes, {:algorithm=>:concurrently, :name=>"index_notes_on_namespace_id"})
ci: -> 0.0063s
ci: -- execute("RESET statement_timeout")
ci: -> 0.0010s
ci: == 20230731090319 AddNotesNamespaceIdForeignKey: reverted (0.5520s) ===========
ci: == [advisory_lock_connection] object_id: 224460, pg_backend_pid: 2312
main: == [advisory_lock_connection] object_id: 224460, pg_backend_pid: 2884
main: == 20230731090319 AddNotesNamespaceIdForeignKey: reverting ====================
main: -- remove_foreign_key(:notes, {:column=>:namespace_id})
main: -> 0.0040s
main: -- transaction_open?()
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.0007s
main: -- indexes(:notes)
main: -> 0.0096s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0006s
main: -- remove_index(:notes, {:algorithm=>:concurrently, :name=>"index_notes_on_namespace_id"})
main: -> 0.0024s
main: -- execute("RESET statement_timeout")
main: -> 0.0006s
main: == 20230731090319 AddNotesNamespaceIdForeignKey: reverted (0.2499s) ===========
main: == [advisory_lock_connection] object_id: 224460, pg_backend_pid: 2884
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.
Related to #416127 (closed)
Edited by Heinrich Lee Yu