Draft: Add project_id to merge_request_diff_commits

Kerri Millerrequested to merge
kerrizor/501473-add-projectid-to-mrdiffcommits into master

What does this MR do and why?

Add project_id to merge_request_diff_commits

  • Add column
  • Update table sync trigger function

References

Please include cross links to any resources that are relevant to this MR. This will give reviewers and future readers helpful context to give an efficient review of the changes introduced.

Database

Up

main: == [advisory_lock_connection] object_id: 131040, pg_backend_pid: 96666
main: == [advisory_lock_connection] object_id: 146760, pg_backend_pid: 96720
main: == 20241208000435 AddProjectIdToMergeRequestDiffCommits: migrating ============
main: -- add_column(:merge_request_diff_commits, :project_id, :bigint, {:if_not_exists=>true})
main:    -> 0.0033s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("ALTER TABLE merge_request_diff_commits ADD CONSTRAINT fk_73c47295fb FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE CASCADE NOT VALID;")
main:    -> 0.0018s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0004s
main: -- execute("ALTER TABLE merge_request_diff_commits VALIDATE CONSTRAINT fk_73c47295fb;")
main:    -> 0.0032s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0006s
main: -- index_exists?(:merge_request_diff_commits, :project_id, {:name=>"index_merge_request_diff_commits_on_project_id", :algorithm=>:concurrently})
main:    -> 0.0040s
main: -- add_index(:merge_request_diff_commits, :project_id, {:name=>"index_merge_request_diff_commits_on_project_id", :algorithm=>:concurrently})
main:    -> 0.0023s
main: -- execute("DROP TRIGGER IF EXISTS table_sync_trigger_57c8465cd7 ON merge_request_diff_commits")
main:    -> 0.0012s
main: -- execute("DROP FUNCTION IF EXISTS table_sync_function_0992e728d3()")
main:    -> 0.0006s
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    \"project_id\" = NEW.\"project_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    \"project_id\")\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\",\n    NEW.\"project_id\");\nEND IF;\nRETURN NULL;\n\nEND\n$$ LANGUAGE PLPGSQL\n")
main:    -> 0.0025s
main: -- execute("COMMENT ON FUNCTION table_sync_function_0992e728d3 IS 'Partitioning migration: table sync for merge_request_diff_commits table'")
main:    -> 0.0006s
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.0004s
main: == 20241208000435 AddProjectIdToMergeRequestDiffCommits: migrated (0.0667s) ===

Down

main: == [advisory_lock_connection] object_id: 131040, pg_backend_pid: 96666
main: == 20241208000435 AddProjectIdToMergeRequestDiffCommits: reverting ============
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- remove_foreign_key(:merge_request_diff_commits, {:column=>:project_id})
main:    -> 0.0202s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0005s
main: -- indexes(:merge_request_diff_commits)
main:    -> 0.0022s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- remove_index(:merge_request_diff_commits, {:algorithm=>:concurrently, :name=>"index_merge_request_diff_commits_on_project_id"})
main:    -> 0.0017s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: -- remove_column(:merge_request_diff_commits, :project_id)
main:    -> 0.0009s
main: -- execute("DROP TRIGGER IF EXISTS table_sync_trigger_57c8465cd7 ON merge_request_diff_commits")
main:    -> 0.0005s
main: -- execute("DROP FUNCTION IF EXISTS table_sync_function_0992e728d3()")
main:    -> 0.0007s
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.0007s
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.0008s
main: == 20241208000435 AddProjectIdToMergeRequestDiffCommits: reverted (0.0622s) ===

Related to Add sharding key to merge_request_diff_commits ... (#501473 - closed)

Edited by Kerri Miller

Merge request reports