Skip to content

Create partitioned merge_request_diff_commits copy

Kerri Miller requested to merge 422764/create-partitioned-merge_request_diff_commits-copy into master

What does this MR do and why?

Create partitioned merge_request_diff_commits copy

This is the first step of partitioning the merge_request_diff_commits table by int range.

Related to #422764 (closed) as part of &11271

DB

Up

main: == [advisory_lock_connection] object_id: 117480, pg_backend_pid: 86428
main: == [advisory_lock_connection] object_id: 119340, pg_backend_pid: 86452
main: == 20240205215337 CreatePartitionedMergeRequestDiffCommitsCopy: migrating =====
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction(nil)
main: -- execute("CREATE TABLE merge_request_diff_commits_b5377a7a34 (\n  LIKE merge_request_diff_commits INCLUDING ALL EXCLUDING INDEXES,\n   merge_request_diff_id_tmp bigint NOT NULL, relative_order_tmp integer NOT NULL,\n  PRIMARY KEY (merge_request_diff_id_tmp, relative_order_tmp)\n) PARTITION BY RANGE (merge_request_diff_id_tmp)\n")
main:    -> 0.0041s
main: -- remove_column("merge_request_diff_commits_b5377a7a34", "merge_request_diff_id")
main:    -> 0.0012s
main: -- rename_column("merge_request_diff_commits_b5377a7a34", "merge_request_diff_id_tmp", "merge_request_diff_id")
main:    -> 0.0027s
main: -- remove_column("merge_request_diff_commits_b5377a7a34", "relative_order")
main:    -> 0.0012s
main: -- rename_column("merge_request_diff_commits_b5377a7a34", "relative_order_tmp", "relative_order")
main:    -> 0.0038s
main:    -> 0.0133s
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 (10000001)\n")
main:    -> 0.0053s
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 (20000001)\n")
main:    -> 0.0039s
main: -- execute("CREATE TABLE gitlab_partitions_dynamic.merge_request_diff_commits_b5377a7a34_20000001 PARTITION OF merge_request_diff_commits_b5377a7a34\nFOR VALUES FROM (20000001) TO (30000001)\n")
main:    -> 0.0042s
main: -- execute("CREATE TABLE gitlab_partitions_dynamic.merge_request_diff_commits_b5377a7a34_30000001 PARTITION OF merge_request_diff_commits_b5377a7a34\nFOR VALUES FROM (30000001) TO (40000001)\n")
main:    -> 0.0035s
main: -- execute("CREATE TABLE gitlab_partitions_dynamic.merge_request_diff_commits_b5377a7a34_40000001 PARTITION OF merge_request_diff_commits_b5377a7a34\nFOR VALUES FROM (40000001) TO (50000001)\n")
main:    -> 0.0034s
main: -- execute("CREATE TABLE gitlab_partitions_dynamic.merge_request_diff_commits_b5377a7a34_50000001 PARTITION OF merge_request_diff_commits_b5377a7a34\nFOR VALUES FROM (50000001) TO (60000001)\n")
main:    -> 0.0042s
main: -- execute("CREATE TABLE gitlab_partitions_dynamic.merge_request_diff_commits_b5377a7a34_60000001 PARTITION OF merge_request_diff_commits_b5377a7a34\nFOR VALUES FROM (60000001) TO (70000001)\n")
main:    -> 0.0043s
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 where merge_request_diff_id = OLD.merge_request_diff_id AND relative_order = OLD.relative_order;\nELSIF (TG_OP = 'UPDATE') THEN\n  UPDATE merge_request_diff_commits_b5377a7a34\n  SET authored_date = NEW.authored_date,\n    committed_date = NEW.committed_date,\n    sha = NEW.sha,\n    message = NEW.message,\n    trailers = NEW.trailers,\n    commit_author_id = NEW.commit_author_id,\n    committer_id = NEW.committer_id\n  WHERE merge_request_diff_commits_b5377a7a34.merge_request_diff_id = NEW.merge_request_diff_id AND merge_request_diff_commits_b5377a7a34.relative_order = NEW.relative_order;\nELSIF (TG_OP = 'INSERT') THEN\n  INSERT INTO merge_request_diff_commits_b5377a7a34 (authored_date,\n    committed_date,\n    sha,\n    message,\n    trailers,\n    commit_author_id,\n    committer_id,\n    merge_request_diff_id,\n    relative_order)\n  VALUES (NEW.authored_date,\n    NEW.committed_date,\n    NEW.sha,\n    NEW.message,\n    NEW.trailers,\n    NEW.commit_author_id,\n    NEW.committer_id,\n    NEW.merge_request_diff_id,\n    NEW.relative_order);\nEND IF;\nRETURN NULL;\n\nEND\n$$ LANGUAGE PLPGSQL\n")
main:    -> 0.0020s
main: -- execute("COMMENT ON FUNCTION table_sync_function_0992e728d3 IS 'Partitioning migration: table sync for merge_request_diff_commits table'")
main:    -> 0.0008s
main: -- current_schema(nil)
main:    -> 0.0002s
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.0009s
main: == 20240205215337 CreatePartitionedMergeRequestDiffCommitsCopy: migrated (0.1004s)

main: == [advisory_lock_connection] object_id: 119340, pg_backend_pid: 86452

Down

main: == [advisory_lock_connection] object_id: 117480, pg_backend_pid: 86428
main: == 20240205215337 CreatePartitionedMergeRequestDiffCommitsCopy: 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.0006s
main: -- execute("DROP FUNCTION IF EXISTS table_sync_function_0992e728d3()")
main:    -> 0.0003s
main: -- drop_table("merge_request_diff_commits_b5377a7a34")
main:    -> 0.0105s
main: == 20240205215337 CreatePartitionedMergeRequestDiffCommitsCopy: reverted (0.0230s)

Merge request reports