[go: up one dir, main page]
Skip to content

Add diff_note_positions sharding key

Eugenia Grieffrequested to merge
550693-define-a-sharding-key-for-diff_note_positions-table into master

What does this MR do and why?

Add sharding key for diff_note_positions

  • Add sharding key column namespace_id
  • Add trigger to populate sharding key on insert and update
  • Add NOT VALID NOT NULL constraint
  • Create index for sharding key synchronously
  • Add NOT VALID foreign key for namespace_id column

A DiffNotePosition record is associated with a Note. The notes table has 3 sharding key columns (project_id, namespace_id, organization_id), but one of them is temporary (project_id, see #444222 (comment 2765183660) for more context).

Given this information, the sharding key for diff_note_positions can be populated from the notes table using a trigger:

  • If notes.project_id column is populated, we find the project and populate diff_note_positions.namespace_id with the value in project.project_namespace_id
  • If notes.namespace_id column is populated, we set this value to diff_note_positions.namespace_id

This MR follows a similar process to Add `suggestions` sharding key (!207836 - merged) and reuses the trigger created in that MR.

References

Related to #572834

Migration output

UP 
main: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 18282
main: == 20251013100839 AddShardingKeyToDiffNotePositions: migrating ================
main: -- add_column(:diff_note_positions, :namespace_id, :bigint)
main:    -> 0.0135s
main: == 20251013100839 AddShardingKeyToDiffNotePositions: migrated (0.0271s) =======

main: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 18282
ci: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 18312
ci: == 20251013100839 AddShardingKeyToDiffNotePositions: migrating ================
ci: -- add_column(:diff_note_positions, :namespace_id, :bigint)
ci:    -> 0.0336s
ci: == 20251013100839 AddShardingKeyToDiffNotePositions: migrated (0.0548s) =======

ci: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 18312
main: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 18364
main: == 20251013100914 AddShardingKeyTriggerOnDiffNotePositions: migrating =========
main: -- execute("CREATE OR REPLACE FUNCTION sync_sharding_key_with_notes_table()\nRETURNS TRIGGER AS\n$$\nDECLARE\n  note_project_id BIGINT;\n  note_namespace_id BIGINT;\nBEGIN\n  IF NEW.\"note_id\" IS NULL OR NEW.\"namespace_id\" IS NOT NULL THEN\n    RETURN NEW;\n  END IF;\n\n  SELECT \"project_id\", \"namespace_id\"\n  INTO note_project_id, note_namespace_id\n  FROM \"notes\"\n  WHERE \"id\" = NEW.\"note_id\";\n\n  IF note_project_id IS NOT NULL THEN\n    SELECT \"project_namespace_id\" FROM \"projects\"\n    INTO NEW.\"namespace_id\" WHERE \"projects\".\"id\" = note_project_id;\n  ELSE\n    NEW.\"namespace_id\" := note_namespace_id;\n  END IF;\n\n  RETURN NEW;\nEND\n$$ LANGUAGE PLPGSQL;\n")
main:    -> 0.0132s
main: -- execute("CREATE TRIGGER set_sharding_key_for_diff_note_positions_on_insert_and_update\nBEFORE INSERT OR UPDATE ON diff_note_positions\nFOR EACH ROW\n\nEXECUTE FUNCTION sync_sharding_key_with_notes_table()\n")
main:    -> 0.0021s
main: == 20251013100914 AddShardingKeyTriggerOnDiffNotePositions: migrated (0.0300s)

main: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 18364
ci: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 18402
ci: == 20251013100914 AddShardingKeyTriggerOnDiffNotePositions: migrating =========
ci: -- execute("CREATE OR REPLACE FUNCTION sync_sharding_key_with_notes_table()\nRETURNS TRIGGER AS\n$$\nDECLARE\n  note_project_id BIGINT;\n  note_namespace_id BIGINT;\nBEGIN\n  IF NEW.\"note_id\" IS NULL OR NEW.\"namespace_id\" IS NOT NULL THEN\n    RETURN NEW;\n  END IF;\n\n  SELECT \"project_id\", \"namespace_id\"\n  INTO note_project_id, note_namespace_id\n  FROM \"notes\"\n  WHERE \"id\" = NEW.\"note_id\";\n\n  IF note_project_id IS NOT NULL THEN\n    SELECT \"project_namespace_id\" FROM \"projects\"\n    INTO NEW.\"namespace_id\" WHERE \"projects\".\"id\" = note_project_id;\n  ELSE\n    NEW.\"namespace_id\" := note_namespace_id;\n  END IF;\n\n  RETURN NEW;\nEND\n$$ LANGUAGE PLPGSQL;\n")
ci:    -> 0.0139s
ci: -- execute("CREATE TRIGGER set_sharding_key_for_diff_note_positions_on_insert_and_update\nBEFORE INSERT OR UPDATE ON diff_note_positions\nFOR EACH ROW\n\nEXECUTE FUNCTION sync_sharding_key_with_notes_table()\n")
ci:    -> 0.0017s
ci: == 20251013100914 AddShardingKeyTriggerOnDiffNotePositions: migrated (0.0356s)

ci: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 18402
main: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 18449
main: == 20251013100952 AddNotNullConstraintOnDiffNotePositionsShardingKey: migrating
main: -- current_schema(nil)
main:    -> 0.0007s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("ALTER TABLE diff_note_positions\nADD CONSTRAINT check_4c86140f48\nCHECK ( namespace_id IS NOT NULL )\nNOT VALID;\n")
main:    -> 0.0007s
main: == 20251013100952 AddNotNullConstraintOnDiffNotePositionsShardingKey: migrated (0.0402s)

main: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 18449
ci: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 18488
ci: == 20251013100952 AddNotNullConstraintOnDiffNotePositionsShardingKey: migrating
ci: -- current_schema(nil)
ci:    -> 0.0005s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE diff_note_positions\nADD CONSTRAINT check_4c86140f48\nCHECK ( namespace_id IS NOT NULL )\nNOT VALID;\n")
ci:    -> 0.0007s
ci: == 20251013100952 AddNotNullConstraintOnDiffNotePositionsShardingKey: migrated (0.0441s)

ci: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 18488
main: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 18530
main: == 20251013101021 AddShardingKeyIndexOnDiffNotePositions: migrating ===========
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0097s
main: -- index_exists?(:diff_note_positions, :namespace_id, {:name=>"index_diff_note_positions_on_namespace_id", :algorithm=>:concurrently})
main:    -> 0.0012s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- add_index(:diff_note_positions, :namespace_id, {:name=>"index_diff_note_positions_on_namespace_id", :algorithm=>:concurrently})
main:    -> 0.0024s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: == 20251013101021 AddShardingKeyIndexOnDiffNotePositions: migrated (0.0399s) ==

main: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 18530
ci: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 18568
ci: == 20251013101021 AddShardingKeyIndexOnDiffNotePositions: migrating ===========
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0104s
ci: -- index_exists?(:diff_note_positions, :namespace_id, {:name=>"index_diff_note_positions_on_namespace_id", :algorithm=>:concurrently})
ci:    -> 0.0013s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0006s
ci: -- add_index(:diff_note_positions, :namespace_id, {:name=>"index_diff_note_positions_on_namespace_id", :algorithm=>:concurrently})
ci:    -> 0.0025s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0003s
ci: == 20251013101021 AddShardingKeyIndexOnDiffNotePositions: migrated (0.0458s) ==

ci: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 18568
main: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 18609
main: == 20251013101048 AddDiffNotePositionsNamespaceIdForeignKey: migrating ========
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("LOCK TABLE namespaces, diff_note_positions IN SHARE ROW EXCLUSIVE MODE")
main:    -> 0.0006s
main: -- execute("ALTER TABLE diff_note_positions ADD CONSTRAINT fk_9ccec9c22a FOREIGN KEY (namespace_id) REFERENCES namespaces (id) ON DELETE CASCADE NOT VALID;")
main:    -> 0.0013s
main: == 20251013101048 AddDiffNotePositionsNamespaceIdForeignKey: migrated (0.0529s)

main: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 18609
ci: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 18648
ci: == 20251013101048 AddDiffNotePositionsNamespaceIdForeignKey: migrating ========
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- execute("LOCK TABLE namespaces, diff_note_positions IN SHARE ROW EXCLUSIVE MODE")
ci:    -> 0.0005s
ci: -- execute("ALTER TABLE diff_note_positions ADD CONSTRAINT fk_9ccec9c22a FOREIGN KEY (namespace_id) REFERENCES namespaces (id) ON DELETE CASCADE NOT VALID;")
ci:    -> 0.0022s
ci: == 20251013101048 AddDiffNotePositionsNamespaceIdForeignKey: migrated (0.0767s)

ci: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 18648
DOWN 
main: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 17267
main: == 20251013100839 AddShardingKeyToDiffNotePositions: reverting ================
main: -- remove_column(:diff_note_positions, :namespace_id, :bigint)
main:    -> 0.0125s
main: == 20251013100839 AddShardingKeyToDiffNotePositions: reverted (0.0296s) =======

main: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 17267
ci: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 17302
ci: == 20251013100839 AddShardingKeyToDiffNotePositions: reverting ================
ci: -- remove_column(:diff_note_positions, :namespace_id, :bigint)
ci:    -> 0.0125s
ci: == 20251013100839 AddShardingKeyToDiffNotePositions: reverted (0.0332s) =======

ci: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 17302

main: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 17654
main: == 20251013100914 AddShardingKeyTriggerOnDiffNotePositions: reverting =========
main: -- execute("DROP TRIGGER IF EXISTS set_sharding_key_for_diff_note_positions_on_insert_and_update ON diff_note_positions")
main:    -> 0.0114s
main: == 20251013100914 AddShardingKeyTriggerOnDiffNotePositions: reverted (0.0252s)

main: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 17654
ci: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 17703
ci: == 20251013100914 AddShardingKeyTriggerOnDiffNotePositions: reverting =========
ci: -- execute("DROP TRIGGER IF EXISTS set_sharding_key_for_diff_note_positions_on_insert_and_update ON diff_note_positions")
ci:    -> 0.0141s
ci: == 20251013100914 AddShardingKeyTriggerOnDiffNotePositions: reverted (0.0326s)

ci: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 17703

main: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 17819
main: == 20251013100952 AddNotNullConstraintOnDiffNotePositionsShardingKey: reverting
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("            ALTER TABLE diff_note_positions\n            DROP CONSTRAINT IF EXISTS check_4c86140f48\n")
main:    -> 0.0006s
main: == 20251013100952 AddNotNullConstraintOnDiffNotePositionsShardingKey: reverted (0.0249s)

main: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 17819
ci: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 17859
ci: == 20251013100952 AddNotNullConstraintOnDiffNotePositionsShardingKey: reverting
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- execute("            ALTER TABLE diff_note_positions\n            DROP CONSTRAINT IF EXISTS check_4c86140f48\n")
ci:    -> 0.0007s
ci: == 20251013100952 AddNotNullConstraintOnDiffNotePositionsShardingKey: reverted (0.0297s)

ci: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 17859

main: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 17977
main: == 20251013101021 AddShardingKeyIndexOnDiffNotePositions: reverting ===========
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0102s
main: -- indexes(:diff_note_positions)
main:    -> 0.0013s
main: -- current_schema(nil)
main:    -> 0.0001s
main: == 20251013101021 AddShardingKeyIndexOnDiffNotePositions: reverted (0.0331s) ==

main: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 17977
ci: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 18007
ci: == 20251013101021 AddShardingKeyIndexOnDiffNotePositions: reverting ===========
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0111s
ci: -- indexes(:diff_note_positions)
ci:    -> 0.0015s
ci: -- current_schema(nil)
ci:    -> 0.0001s
ci: == 20251013101021 AddShardingKeyIndexOnDiffNotePositions: reverted (0.0414s) ==

ci: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 18007

main: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 18121
main: == 20251013101048 AddDiffNotePositionsNamespaceIdForeignKey: reverting ========
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: == 20251013101048 AddDiffNotePositionsNamespaceIdForeignKey: reverted (0.0445s)

main: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 18121
ci: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 18158
ci: == 20251013101048 AddDiffNotePositionsNamespaceIdForeignKey: reverting ========
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: == 20251013101048 AddDiffNotePositionsNamespaceIdForeignKey: reverted (0.0497s)

ci: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 18158

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Eugenia Grieff

Merge request reports