Skip to content

Rename ml_candidates.iid to eid

Eduardo Bonet requested to merge 398170-add-iid-to-ml_candidates into master

What does this MR do and why?

Column iid on ml_candidates is not really an iid like in other tables. Renaming it to eid (external id, as it is created by a third party tool) allows future creation of an actual iid. Database changes are kept at minimum to not break the application.

This is the first part of adding internal id to ml_candidates

Database

Migrations

Up 
bin/rails db:migrate RAILS_ENV=development
main: == 20230321153304 RenameMlCandidatesIidToEid: migrating =======================
main: -- transaction_open?()
main:    -> 0.0000s
main: -- columns(:ml_candidates)
main:    -> 0.1937s
main: -- column_exists?(:ml_candidates, :id)
main:    -> 0.0014s
main: -- column_exists?(:ml_candidates, :eid)
main:    -> 0.0011s
main: -- columns(:ml_candidates)
main:    -> 0.0009s
main: -- add_column(:ml_candidates, :eid, :uuid, {:limit=>nil, :precision=>nil, :scale=>nil})
main:    -> 0.0015s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- exec_query("SELECT COUNT(*) AS count FROM \"ml_candidates\"")
main:    -> 0.0009s
main: -- current_schema()
main:    -> 0.0003s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("ALTER TABLE ml_candidates\nADD CONSTRAINT check_cd160587d4\nCHECK ( eid IS NOT NULL )\nNOT VALID;\n")
main:    -> 0.0015s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0005s
main: -- execute("ALTER TABLE ml_candidates VALIDATE CONSTRAINT check_cd160587d4;")
main:    -> 0.0025s
main: -- execute("RESET statement_timeout")
main:    -> 0.0010s
main: -- indexes(:ml_candidates)
main:    -> 0.0040s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0017s
main: -- index_exists?(:ml_candidates, ["experiment_id", "eid"], {:unique=>true, :name=>"index_ml_candidates_on_experiment_id_and_eid", :length=>{}, :order=>{}, :using=>:btree, :algorithm=>:concurrently})
main:    -> 0.0017s
main: -- add_index(:ml_candidates, ["experiment_id", "eid"], {:unique=>true, :name=>"index_ml_candidates_on_experiment_id_and_eid", :length=>{}, :order=>{}, :using=>:btree, :algorithm=>:concurrently})
main:    -> 0.0013s
main: -- foreign_keys(:ml_candidates)
main:    -> 0.0021s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- column_exists?(:ml_candidates, :iid)
main:    -> 0.0011s
main: -- column_exists?(:ml_candidates, :eid)
main:    -> 0.0010s
main: -- current_schema()
main:    -> 0.0002s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- quote_table_name(:ml_candidates)
main:    -> 0.0000s
main: -- quote_column_name(:iid)
main:    -> 0.0000s
main: -- quote_column_name(:eid)
main:    -> 0.0000s
main: -- columns(:ml_candidates)
main:    -> 0.0012s
main: -- execute("CREATE OR REPLACE FUNCTION function_for_trigger_d4408fb9ca78()\nRETURNS trigger\nLANGUAGE plpgsql\nAS $$\nBEGIN\n  IF NEW.\"iid\" IS NOT DISTINCT FROM NULL AND NEW.\"eid\" IS DISTINCT FROM NULL THEN\n    NEW.\"iid\" = NEW.\"eid\";\n  END IF;\n\n  IF NEW.\"eid\" IS NOT DISTINCT FROM NULL AND NEW.\"iid\" IS DISTINCT FROM NULL THEN\n    NEW.\"eid\" = NEW.\"iid\";\n  END IF;\n\n  RETURN NEW;\nEND\n$$;\n\nDROP TRIGGER IF EXISTS trigger_d4408fb9ca78\nON \"ml_candidates\";\n\nCREATE TRIGGER trigger_d4408fb9ca78\nBEFORE INSERT ON \"ml_candidates\"\nFOR EACH ROW EXECUTE FUNCTION function_for_trigger_d4408fb9ca78();\n")
main:    -> 0.0035s
main: -- execute("CREATE OR REPLACE FUNCTION function_for_trigger_c7041d62c85e()\nRETURNS trigger\nLANGUAGE plpgsql\nAS $$\nBEGIN\n  NEW.\"iid\" := NEW.\"eid\";\n  RETURN NEW;\nEND\n$$;\n\nDROP TRIGGER IF EXISTS trigger_c7041d62c85e\nON \"ml_candidates\";\n\nCREATE TRIGGER trigger_c7041d62c85e\nBEFORE UPDATE OF \"eid\" ON \"ml_candidates\"\nFOR EACH ROW EXECUTE FUNCTION function_for_trigger_c7041d62c85e();\n")
main:    -> 0.0008s
main: -- execute("CREATE OR REPLACE FUNCTION function_for_trigger_a4c33b508978()\nRETURNS trigger\nLANGUAGE plpgsql\nAS $$\nBEGIN\n  NEW.\"eid\" := NEW.\"iid\";\n  RETURN NEW;\nEND\n$$;\n\nDROP TRIGGER IF EXISTS trigger_a4c33b508978\nON \"ml_candidates\";\n\nCREATE TRIGGER trigger_a4c33b508978\nBEFORE UPDATE OF \"iid\" ON \"ml_candidates\"\nFOR EACH ROW EXECUTE FUNCTION function_for_trigger_a4c33b508978();\n")
main:    -> 0.0005s
main: == 20230321153304 RenameMlCandidatesIidToEid: migrated (0.2766s) ==============

main: == 20230321153337 CleanupMlCandidatesIidRename: migrating =====================
main: -- column_exists?(:ml_candidates, :iid)
main:    -> 0.0011s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- quote_table_name(:ml_candidates)
main:    -> 0.0000s
main: -- execute("DROP TRIGGER IF EXISTS trigger_d4408fb9ca78\nON \"ml_candidates\";\n\nDROP FUNCTION IF EXISTS function_for_trigger_d4408fb9ca78;\n")
main:    -> 0.0007s
main: -- execute("DROP TRIGGER IF EXISTS trigger_c7041d62c85e\nON \"ml_candidates\";\n\nDROP FUNCTION IF EXISTS function_for_trigger_c7041d62c85e;\n")
main:    -> 0.0004s
main: -- execute("DROP TRIGGER IF EXISTS trigger_a4c33b508978\nON \"ml_candidates\";\n\nDROP FUNCTION IF EXISTS function_for_trigger_a4c33b508978;\n")
main:    -> 0.0005s
main: -- remove_column(:ml_candidates, :iid)
main:    -> 0.0008s
main: == 20230321153337 CleanupMlCandidatesIidRename: migrated (0.0089s) ============

ci: == 20230321153304 RenameMlCandidatesIidToEid: migrating =======================
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- columns(:ml_candidates)
ci:    -> 0.0124s
ci: -- column_exists?(:ml_candidates, :id)
ci:    -> 0.0098s
ci: -- column_exists?(:ml_candidates, :eid)
ci:    -> 0.0088s
ci: -- columns(:ml_candidates)
ci:    -> 0.0122s
ci: -- add_column(:ml_candidates, :eid, :uuid, {:limit=>nil, :precision=>nil, :scale=>nil})
ci:    -> 0.0030s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- exec_query("SELECT COUNT(*) AS count FROM \"ml_candidates\"")
ci:    -> 0.0077s
ci: -- current_schema()
ci:    -> 0.0035s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- transaction_open?()
ci:    -> 0.0026s
ci: -- execute("ALTER TABLE ml_candidates\nADD CONSTRAINT check_cd160587d4\nCHECK ( eid IS NOT NULL )\nNOT VALID;\n")
ci:    -> 0.0025s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0024s
ci: -- execute("ALTER TABLE ml_candidates VALIDATE CONSTRAINT check_cd160587d4;")
ci:    -> 0.0028s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0034s
ci: -- indexes(:ml_candidates)
ci:    -> 0.0197s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0083s
ci: -- index_exists?(:ml_candidates, ["experiment_id", "eid"], {:unique=>true, :name=>"index_ml_candidates_on_experiment_id_and_eid", :length=>{}, :order=>{}, :using=>:btree, :algorithm=>:concurrently})
ci:    -> 0.0159s
ci: -- add_index(:ml_candidates, ["experiment_id", "eid"], {:unique=>true, :name=>"index_ml_candidates_on_experiment_id_and_eid", :length=>{}, :order=>{}, :using=>:btree, :algorithm=>:concurrently})
ci:    -> 0.0025s
ci: -- foreign_keys(:ml_candidates)
ci:    -> 0.0101s
ci: -- transaction_open?()
ci:    -> 0.0001s
ci: -- column_exists?(:ml_candidates, :iid)
ci:    -> 0.0097s
ci: -- column_exists?(:ml_candidates, :eid)
ci:    -> 0.0138s
ci: -- current_schema()
ci:    -> 0.0055s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- quote_table_name(:ml_candidates)
ci:    -> 0.0000s
ci: -- quote_column_name(:iid)
ci:    -> 0.0000s
ci: -- quote_column_name(:eid)
ci:    -> 0.0000s
ci: -- columns(:ml_candidates)
ci:    -> 0.0018s
ci: -- execute("CREATE OR REPLACE FUNCTION function_for_trigger_d4408fb9ca78()\nRETURNS trigger\nLANGUAGE plpgsql\nAS $$\nBEGIN\n  IF NEW.\"iid\" IS NOT DISTINCT FROM NULL AND NEW.\"eid\" IS DISTINCT FROM NULL THEN\n    NEW.\"iid\" = NEW.\"eid\";\n  END IF;\n\n  IF NEW.\"eid\" IS NOT DISTINCT FROM NULL AND NEW.\"iid\" IS DISTINCT FROM NULL THEN\n    NEW.\"eid\" = NEW.\"iid\";\n  END IF;\n\n  RETURN NEW;\nEND\n$$;\n\nDROP TRIGGER IF EXISTS trigger_d4408fb9ca78\nON \"ml_candidates\";\n\nCREATE TRIGGER trigger_d4408fb9ca78\nBEFORE INSERT ON \"ml_candidates\"\nFOR EACH ROW EXECUTE FUNCTION function_for_trigger_d4408fb9ca78();\n")
ci:    -> 0.0038s
ci: -- execute("CREATE OR REPLACE FUNCTION function_for_trigger_c7041d62c85e()\nRETURNS trigger\nLANGUAGE plpgsql\nAS $$\nBEGIN\n  NEW.\"iid\" := NEW.\"eid\";\n  RETURN NEW;\nEND\n$$;\n\nDROP TRIGGER IF EXISTS trigger_c7041d62c85e\nON \"ml_candidates\";\n\nCREATE TRIGGER trigger_c7041d62c85e\nBEFORE UPDATE OF \"eid\" ON \"ml_candidates\"\nFOR EACH ROW EXECUTE FUNCTION function_for_trigger_c7041d62c85e();\n")
ci:    -> 0.0006s
ci: -- execute("CREATE OR REPLACE FUNCTION function_for_trigger_a4c33b508978()\nRETURNS trigger\nLANGUAGE plpgsql\nAS $$\nBEGIN\n  NEW.\"eid\" := NEW.\"iid\";\n  RETURN NEW;\nEND\n$$;\n\nDROP TRIGGER IF EXISTS trigger_a4c33b508978\nON \"ml_candidates\";\n\nCREATE TRIGGER trigger_a4c33b508978\nBEFORE UPDATE OF \"iid\" ON \"ml_candidates\"\nFOR EACH ROW EXECUTE FUNCTION function_for_trigger_a4c33b508978();\n")
ci:    -> 0.0006s
ci: == 20230321153304 RenameMlCandidatesIidToEid: migrated (0.2705s) ==============

ci: == 20230321153337 CleanupMlCandidatesIidRename: migrating =====================
ci: -- column_exists?(:ml_candidates, :iid)
ci:    -> 0.0013s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- quote_table_name(:ml_candidates)
ci:    -> 0.0000s
ci: -- execute("DROP TRIGGER IF EXISTS trigger_d4408fb9ca78\nON \"ml_candidates\";\n\nDROP FUNCTION IF EXISTS function_for_trigger_d4408fb9ca78;\n")
ci:    -> 0.0010s
ci: -- execute("DROP TRIGGER IF EXISTS trigger_c7041d62c85e\nON \"ml_candidates\";\n\nDROP FUNCTION IF EXISTS function_for_trigger_c7041d62c85e;\n")
ci:    -> 0.0005s
ci: -- execute("DROP TRIGGER IF EXISTS trigger_a4c33b508978\nON \"ml_candidates\";\n\nDROP FUNCTION IF EXISTS function_for_trigger_a4c33b508978;\n")
ci:    -> 0.0005s
ci: -- remove_column(:ml_candidates, :iid)
ci:    -> 0.0008s
ci: == 20230321153337 CleanupMlCandidatesIidRename: migrated (0.0176s) ============
Down 
❯ rake db:migrate:down:main VERSION=20230321153337
main: == 20230321153337 CleanupMlCandidatesIidRename: reverting =====================
main: -- transaction_open?()
main:    -> 0.0000s
main: -- columns(:ml_candidates)
main:    -> 0.1432s
main: -- column_exists?(:ml_candidates, :id)
main:    -> 0.0047s
main: -- column_exists?(:ml_candidates, :iid)
main:    -> 0.0065s
main: -- columns(:ml_candidates)
main:    -> 0.0079s
main: -- add_column(:ml_candidates, :iid, :uuid, {:limit=>nil, :precision=>nil, :scale=>nil})
main:    -> 0.0040s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- exec_query("SELECT COUNT(*) AS count FROM \"ml_candidates\"")
main:    -> 0.0027s
main: -- indexes(:ml_candidates)
main:    -> 0.0110s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0035s
main: -- index_exists?(:ml_candidates, ["experiment_id", "iid"], {:unique=>true, :name=>"index_ml_candidates_on_experiment_id_and_iid", :length=>{}, :order=>{}, :using=>:btree, :algorithm=>:concurrently})
main:    -> 0.0206s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0004s
main: -- add_index(:ml_candidates, ["experiment_id", "iid"], {:unique=>true, :name=>"index_ml_candidates_on_experiment_id_and_iid", :length=>{}, :order=>{}, :using=>:btree, :algorithm=>:concurrently})
main:    -> 0.0062s
main: -- execute("RESET statement_timeout")
main:    -> 0.0015s
main: -- foreign_keys(:ml_candidates)
main:    -> 0.0036s
main: -- transaction_open?()
main:    -> 0.0001s
main: -- column_exists?(:ml_candidates, :eid)
main:    -> 0.0013s
main: -- column_exists?(:ml_candidates, :iid)
main:    -> 0.0011s
main: -- current_schema()
main:    -> 0.0003s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("ALTER TABLE ml_candidates\nADD CONSTRAINT check_a622dca80a\nCHECK ( (iid IS NOT NULL) )\nNOT VALID;\n")
main:    -> 0.0013s
main: -- execute("ALTER TABLE ml_candidates VALIDATE CONSTRAINT check_a622dca80a;")
main:    -> 0.0006s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- quote_table_name(:ml_candidates)
main:    -> 0.0000s
main: -- quote_column_name(:iid)
main:    -> 0.0000s
main: -- quote_column_name(:eid)
main:    -> 0.0000s
main: -- columns(:ml_candidates)
main:    -> 0.0011s
main: -- execute("CREATE OR REPLACE FUNCTION function_for_trigger_d4408fb9ca78()\nRETURNS trigger\nLANGUAGE plpgsql\nAS $$\nBEGIN\n  IF NEW.\"iid\" IS NOT DISTINCT FROM NULL AND NEW.\"eid\" IS DISTINCT FROM NULL THEN\n    NEW.\"iid\" = NEW.\"eid\";\n  END IF;\n\n  IF NEW.\"eid\" IS NOT DISTINCT FROM NULL AND NEW.\"iid\" IS DISTINCT FROM NULL THEN\n    NEW.\"eid\" = NEW.\"iid\";\n  END IF;\n\n  RETURN NEW;\nEND\n$$;\n\nDROP TRIGGER IF EXISTS trigger_d4408fb9ca78\nON \"ml_candidates\";\n\nCREATE TRIGGER trigger_d4408fb9ca78\nBEFORE INSERT ON \"ml_candidates\"\nFOR EACH ROW EXECUTE FUNCTION function_for_trigger_d4408fb9ca78();\n")
main:    -> 0.0054s
main: -- execute("CREATE OR REPLACE FUNCTION function_for_trigger_c7041d62c85e()\nRETURNS trigger\nLANGUAGE plpgsql\nAS $$\nBEGIN\n  NEW.\"iid\" := NEW.\"eid\";\n  RETURN NEW;\nEND\n$$;\n\nDROP TRIGGER IF EXISTS trigger_c7041d62c85e\nON \"ml_candidates\";\n\nCREATE TRIGGER trigger_c7041d62c85e\nBEFORE UPDATE OF \"eid\" ON \"ml_candidates\"\nFOR EACH ROW EXECUTE FUNCTION function_for_trigger_c7041d62c85e();\n")
main:    -> 0.0007s
main: -- execute("CREATE OR REPLACE FUNCTION function_for_trigger_a4c33b508978()\nRETURNS trigger\nLANGUAGE plpgsql\nAS $$\nBEGIN\n  NEW.\"eid\" := NEW.\"iid\";\n  RETURN NEW;\nEND\n$$;\n\nDROP TRIGGER IF EXISTS trigger_a4c33b508978\nON \"ml_candidates\";\n\nCREATE TRIGGER trigger_a4c33b508978\nBEFORE UPDATE OF \"iid\" ON \"ml_candidates\"\nFOR EACH ROW EXECUTE FUNCTION function_for_trigger_a4c33b508978();\n")
main:    -> 0.0005s
main: == 20230321153337 CleanupMlCandidatesIidRename: reverted (0.3726s) ============

❯ rake db:migrate:down:main VERSION=20230321153304
main: == 20230321153304 RenameMlCandidatesIidToEid: reverting =======================
main: -- column_exists?(:ml_candidates, :eid)
main:    -> 0.1205s
main: -- transaction_open?()
main:    -> 0.0001s
main: -- quote_table_name(:ml_candidates)
main:    -> 0.0001s
main: -- execute("DROP TRIGGER IF EXISTS trigger_d4408fb9ca78\nON \"ml_candidates\";\n\nDROP FUNCTION IF EXISTS function_for_trigger_d4408fb9ca78;\n")
main:    -> 0.0010s
main: -- execute("DROP TRIGGER IF EXISTS trigger_c7041d62c85e\nON \"ml_candidates\";\n\nDROP FUNCTION IF EXISTS function_for_trigger_c7041d62c85e;\n")
main:    -> 0.0006s
main: -- execute("DROP TRIGGER IF EXISTS trigger_a4c33b508978\nON \"ml_candidates\";\n\nDROP FUNCTION IF EXISTS function_for_trigger_a4c33b508978;\n")
main:    -> 0.0008s
main: -- remove_column(:ml_candidates, :eid)
main:    -> 0.0017s
main: == 20230321153304 RenameMlCandidatesIidToEid: reverted (0.1493s) ==============

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #398170 (closed)

Edited by Eduardo Bonet

Merge request reports