Draft: Create partitioned version of merge_request_diff_commits
What does this MR do and why?
This creates the partitioned version of merge_request_diff_commits table which will later on replace the existing one. This table has fewer columns since most of the columns will be moved to merge_request_commits_metadata table. A trigger is added so deletion, updates, and inserts coming from the old table will also happen on this new table. The table will have fewer records at first and will be backfilled in a separate MR.
We cannot use the existing helpers to create a partitioned copy of merge_request_diff_commits because this partitioned version is not a direct copy of the old one (fewer columns).
Some spec contexts were removed in this MR since they're not needed (they're failing but we don't need to fix). At the point wherein this MR can be merged, phase 1 should be done so the dedup FF will be gone. These removals will be likely removed from this MR once we get to that point.
DO NOT MERGE YET UNTIL PHASE 1 IS COMPLETE
db:migrate
Click to expand
$ bundle exec rake db:migrate
main: == [advisory_lock_connection] object_id: 129920, pg_backend_pid: 81085
main: == 20251015070354 CreatePartitionedMergeRequestDiffCommits: migrating =========
main: -- create_table("merge_request_diff_commits_b5377a7a34", {:options=>"PARTITION BY RANGE(project_id)", :primary_key=>[:merge_request_diff_id, :relative_order, :project_id]})
main: -> 0.0126s
main: -- execute("CREATE TABLE gitlab_partitions_dynamic.merge_request_diff_commits_b5377a7a34_1 PARTITION OF merge_request_diff_commits_b5377a7a34\nFOR VALUES FROM (1) TO (2000001)\n")
main: -> 0.0063s
main: -- execute("CREATE TABLE gitlab_partitions_dynamic.merge_request_diff_commits_b5377a7a34_2000001 PARTITION OF merge_request_diff_commits_b5377a7a34\nFOR VALUES FROM (2000001) TO (4000001)\n")
main: -> 0.0046s
main: -- execute("CREATE TABLE gitlab_partitions_dynamic.merge_request_diff_commits_b5377a7a34_4000001 PARTITION OF merge_request_diff_commits_b5377a7a34\nFOR VALUES FROM (4000001) TO (6000001)\n")
main: -> 0.0032s
main: -- execute("CREATE TABLE gitlab_partitions_dynamic.merge_request_diff_commits_b5377a7a34_6000001 PARTITION OF merge_request_diff_commits_b5377a7a34\nFOR VALUES FROM (6000001) TO (8000001)\n")
main: -> 0.0031s
main: -- execute("CREATE TABLE gitlab_partitions_dynamic.merge_request_diff_commits_b5377a7a34_8000001 PARTITION OF merge_request_diff_commits_b5377a7a34\nFOR VALUES FROM (8000001) TO (10000001)\n")
main: -> 0.0038s
main: -- execute("CREATE TABLE gitlab_partitions_dynamic.merge_request_diff_commits_b5377a7a34_10000001 PARTITION OF merge_request_diff_commits_b5377a7a34\nFOR VALUES FROM (10000001) TO (12000001)\n")
main: -> 0.0038s
main: -- execute("CREATE TABLE gitlab_partitions_dynamic.merge_request_diff_commits_b5377a7a34_12000001 PARTITION OF merge_request_diff_commits_b5377a7a34\nFOR VALUES FROM (12000001) TO (14000001)\n")
main: -> 0.0029s
main: -- execute("CREATE FUNCTION table_sync_function_0992e728d3()\nRETURNS TRIGGER AS\n$$\nBEGIN\nIF (TG_OP = 'DELETE') THEN\n DELETE FROM merge_request_diff_commits_b5377a7a34\n WHERE \"merge_request_diff_id\" = OLD.\"merge_request_diff_id\"\n AND \"relative_order\" = OLD.\"relative_order\"\n AND \"project_id\" = COALESCE(OLD.\"project_id\", (SELECT mrd.project_id FROM merge_request_diffs mrd WHERE mrd.id = OLD.\"merge_request_diff_id\"));\nELSIF (TG_OP = 'UPDATE') THEN\n UPDATE merge_request_diff_commits_b5377a7a34\n SET \"merge_request_commits_metadata_id\" = NEW.\"merge_request_commits_metadata_id\"\n WHERE merge_request_diff_commits_b5377a7a34.\"merge_request_diff_id\" = NEW.\"merge_request_diff_id\"\n AND merge_request_diff_commits_b5377a7a34.\"relative_order\" = NEW.\"relative_order\"\n AND merge_request_diff_commits_b5377a7a34.\"project_id\" = COALESCE(NEW.\"project_id\", (SELECT mrd.project_id FROM merge_request_diffs mrd WHERE mrd.id = NEW.\"merge_request_diff_id\"));\nELSIF (TG_OP = 'INSERT') THEN\n INSERT INTO merge_request_diff_commits_b5377a7a34 (\"merge_request_commits_metadata_id\",\n \"project_id\",\n \"merge_request_diff_id\",\n \"relative_order\")\n VALUES (NEW.\"merge_request_commits_metadata_id\",\n COALESCE(NEW.\"project_id\", (SELECT mrd.project_id FROM merge_request_diffs mrd WHERE mrd.id = NEW.\"merge_request_diff_id\")),\n NEW.\"merge_request_diff_id\",\n NEW.\"relative_order\");\nEND IF;\nRETURN NULL;\n\nEND\n$$ LANGUAGE PLPGSQL\n")
main: -> 0.0025s
main: -- execute("CREATE TRIGGER table_sync_trigger_57c8465cd7\nAFTER INSERT OR UPDATE OR DELETE ON merge_request_diff_commits\nFOR EACH ROW\n\nEXECUTE FUNCTION table_sync_function_0992e728d3()\n")
main: -> 0.0010s
main: == 20251015070354 CreatePartitionedMergeRequestDiffCommits: migrated (0.1189s)
main: == [advisory_lock_connection] object_id: 129920, pg_backend_pid: 81085
ci: == [advisory_lock_connection] object_id: 129920, pg_backend_pid: 81101
ci: == 20251015070354 CreatePartitionedMergeRequestDiffCommits: migrating =========
ci: -- create_table("merge_request_diff_commits_b5377a7a34", {:options=>"PARTITION BY RANGE(project_id)", :primary_key=>[:merge_request_diff_id, :relative_order, :project_id]})
ci: -> 0.0131s
ci: -- execute("CREATE TABLE gitlab_partitions_dynamic.merge_request_diff_commits_b5377a7a34_1 PARTITION OF merge_request_diff_commits_b5377a7a34\nFOR VALUES FROM (1) TO (2000001)\n")
ci: -> 0.0040s
ci: -- execute("CREATE TABLE gitlab_partitions_dynamic.merge_request_diff_commits_b5377a7a34_2000001 PARTITION OF merge_request_diff_commits_b5377a7a34\nFOR VALUES FROM (2000001) TO (4000001)\n")
ci: -> 0.0023s
ci: -- execute("CREATE TABLE gitlab_partitions_dynamic.merge_request_diff_commits_b5377a7a34_4000001 PARTITION OF merge_request_diff_commits_b5377a7a34\nFOR VALUES FROM (4000001) TO (6000001)\n")
ci: -> 0.0044s
ci: -- execute("CREATE TABLE gitlab_partitions_dynamic.merge_request_diff_commits_b5377a7a34_6000001 PARTITION OF merge_request_diff_commits_b5377a7a34\nFOR VALUES FROM (6000001) TO (8000001)\n")
ci: -> 0.0025s
ci: -- execute("CREATE TABLE gitlab_partitions_dynamic.merge_request_diff_commits_b5377a7a34_8000001 PARTITION OF merge_request_diff_commits_b5377a7a34\nFOR VALUES FROM (8000001) TO (10000001)\n")
ci: -> 0.0025s
ci: -- execute("CREATE TABLE gitlab_partitions_dynamic.merge_request_diff_commits_b5377a7a34_10000001 PARTITION OF merge_request_diff_commits_b5377a7a34\nFOR VALUES FROM (10000001) TO (12000001)\n")
ci: -> 0.0021s
ci: -- execute("CREATE FUNCTION table_sync_function_0992e728d3()\nRETURNS TRIGGER AS\n$$\nBEGIN\nIF (TG_OP = 'DELETE') THEN\n DELETE FROM merge_request_diff_commits_b5377a7a34\n WHERE \"merge_request_diff_id\" = OLD.\"merge_request_diff_id\"\n AND \"relative_order\" = OLD.\"relative_order\"\n AND \"project_id\" = COALESCE(OLD.\"project_id\", (SELECT mrd.project_id FROM merge_request_diffs mrd WHERE mrd.id = OLD.\"merge_request_diff_id\"));\nELSIF (TG_OP = 'UPDATE') THEN\n UPDATE merge_request_diff_commits_b5377a7a34\n SET \"merge_request_commits_metadata_id\" = NEW.\"merge_request_commits_metadata_id\"\n WHERE merge_request_diff_commits_b5377a7a34.\"merge_request_diff_id\" = NEW.\"merge_request_diff_id\"\n AND merge_request_diff_commits_b5377a7a34.\"relative_order\" = NEW.\"relative_order\"\n AND merge_request_diff_commits_b5377a7a34.\"project_id\" = COALESCE(NEW.\"project_id\", (SELECT mrd.project_id FROM merge_request_diffs mrd WHERE mrd.id = NEW.\"merge_request_diff_id\"));\nELSIF (TG_OP = 'INSERT') THEN\n INSERT INTO merge_request_diff_commits_b5377a7a34 (\"merge_request_commits_metadata_id\",\n \"project_id\",\n \"merge_request_diff_id\",\n \"relative_order\")\n VALUES (NEW.\"merge_request_commits_metadata_id\",\n COALESCE(NEW.\"project_id\", (SELECT mrd.project_id FROM merge_request_diffs mrd WHERE mrd.id = NEW.\"merge_request_diff_id\")),\n NEW.\"merge_request_diff_id\",\n NEW.\"relative_order\");\nEND IF;\nRETURN NULL;\n\nEND\n$$ LANGUAGE PLPGSQL\n")
ci: -> 0.0015s
ci: -- execute("CREATE TRIGGER table_sync_trigger_57c8465cd7\nAFTER INSERT OR UPDATE OR DELETE ON merge_request_diff_commits\nFOR EACH ROW\n\nEXECUTE FUNCTION table_sync_function_0992e728d3()\n")
ci: -> 0.0007s
ci: == 20251015070354 CreatePartitionedMergeRequestDiffCommits: migrated (0.0819s)
ci: == [advisory_lock_connection] object_id: 129920, pg_backend_pid: 81101db:migrate:down
Click to expand
$ VERSION=20251015070354 bundle exec rake db:migrate:down:main db:migrate:down:ci
main: == [advisory_lock_connection] object_id: 129600, pg_backend_pid: 31786
main: == 20251015070354 CreatePartitionedMergeRequestDiffCommits: reverting =========
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- execute("DROP TRIGGER IF EXISTS table_sync_trigger_57c8465cd7 ON merge_request_diff_commits")
main: -> 0.0017s
main: -- execute("DROP FUNCTION IF EXISTS table_sync_function_0992e728d3()")
main: -> 0.0006s
main: -- drop_table("merge_request_diff_commits_b5377a7a34")
main: -> 0.0183s
main: == 20251015070354 CreatePartitionedMergeRequestDiffCommits: reverted (0.0637s)
main: == [advisory_lock_connection] object_id: 129600, pg_backend_pid: 31786
ci: == [advisory_lock_connection] object_id: 129600, pg_backend_pid: 31907
ci: == 20251015070354 CreatePartitionedMergeRequestDiffCommits: reverting =========
ci: -- transaction_open?(nil)
ci: -> 0.0000s
ci: -- transaction_open?(nil)
ci: -> 0.0000s
ci: -- execute("DROP TRIGGER IF EXISTS table_sync_trigger_57c8465cd7 ON merge_request_diff_commits")
ci: -> 0.0008s
ci: -- execute("DROP FUNCTION IF EXISTS table_sync_function_0992e728d3()")
ci: -> 0.0004s
ci: -- drop_table("merge_request_diff_commits_b5377a7a34")
ci: -> 0.0131s
ci: == 20251015070354 CreatePartitionedMergeRequestDiffCommits: reverted (0.0259s)
ci: == [advisory_lock_connection] object_id: 129600, pg_backend_pid: 31907References
https://gitlab.com/gitlab-org/gitlab/-/issues/527228
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 Patrick Bajao