Change column type of vulnerability_reads.cluster_agent_id to bigint

What does this MR do and why?

In GraphQL filter for vulnerabilities by container... (#358763 - closed) we want to add filter for vulnerabilities by container images with vulnerabilities, to do so we need to change the column type that was erroneously set to text instead of bigint. This MR is changing that. We're doing it by introducing new column casted_cluster_agent_id, needed indexes and foreign key. In this MR we are taking care only of creating new column, in next MR we will add background migration to handle backfilling casted_cluster_agent_id with data from cluster_agent_id column. The last MR will remove old column and rename casted_cluster_agent_id to cluster_agent_id. This is first MR of planned 3.

Migrations

⋊> env VERBOSE=true bundle exec rake db:migrate:up:main VERSION=20220523162734
main: == 20220523162734 AddVulnerabilityReadsCastedClusterAgentColumn: migrating ====
main: -- column_exists?(:vulnerability_reads, :casted_cluster_agent_id)
main:    -> 0.0065s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- add_column(:vulnerability_reads, :casted_cluster_agent_id, :bigint)
main:    -> 0.0024s
main: == 20220523162734 AddVulnerabilityReadsCastedClusterAgentColumn: migrated (0.0167s) 

⋊> env VERBOSE=true bundle exec rake db:migrate:up:main VERSION=20220523163734
main: == 20220523163734 UpdateVulnerabilityReadsTriggerFunctions: migrating =========
main: -- execute("CREATE OR REPLACE FUNCTION insert_or_update_vulnerability_reads()\nRETURNS TRIGGER\nLANGUAGE plpgsql\nAS $$\nDECLARE\n  severity smallint;\n  state smallint;\n  report_type smallint;\n  resolved_on_default_branch boolean;\nBEGIN\n  IF (NEW.vulnerability_id IS NULL AND (TG_OP = 'INSERT' OR TG_OP = 'UPDATE')) THEN\n    RETURN NULL;\n  END IF;\n\n  IF (TG_OP = 'UPDATE' AND OLD.vulnerability_id IS NOT NULL AND NEW.vulnerability_id IS NOT NULL) THEN\n    RETURN NULL;\n  END IF;\n\n  SELECT\n    vulnerabilities.severity, vulnerabilities.state, vulnerabilities.report_type, vulnerabilities.resolved_on_default_branch\n  INTO\n    severity, state, report_type, resolved_on_default_branch\n  FROM\n     vulnerabilities\n  WHERE\n    vulnerabilities.id = NEW.vulnerability_id;\n\n  INSERT INTO vulnerability_reads (vulnerability_id, project_id, scanner_id, report_type, severity, state, resolved_on_default_branch, uuid, location_image, cluster_agent_id, casted_cluster_agent_id)\n    VALUES (NEW.vulnerability_id, NEW.project_id, NEW.scanner_id, report_type, severity, state, resolved_on_default_branch, NEW.uuid::uuid, NEW.location->>'image', NEW.location->'kubernetes_resource'->>'agent_id', CAST(NEW.location->'kubernetes_resource'->>'agent_id' AS bigint))\n    ON CONFLICT(vulnerability_id) DO NOTHING;\n  RETURN NULL;\nEND\n$$;\n")
main:    -> 0.0057s
main: -- execute("CREATE OR REPLACE FUNCTION update_location_from_vulnerability_occurrences()\nRETURNS trigger\nLANGUAGE plpgsql\nAS $$\nBEGIN\nUPDATE\n  vulnerability_reads\nSET\n  location_image = NEW.location->>'image',\n  casted_cluster_agent_id = CAST(NEW.location->'kubernetes_resource'->>'agent_id' AS bigint),\n  cluster_agent_id = NEW.location->'kubernetes_resource'->>'agent_id'\nWHERE\n  vulnerability_id = NEW.vulnerability_id;\nRETURN NULL;\n\nEND\n$$;\n")
main:    -> 0.0010s
main: == 20220523163734 UpdateVulnerabilityReadsTriggerFunctions: migrated (0.0079s) 

⋊> env VERBOSE=true bundle exec rake db:migrate:up:main VERSION=20220523164734
main: == 20220523164734 AddForeignKeyToVulnerabilityReadsCastedClusterAgentId: migrating 
main: -- transaction_open?()
main:    -> 0.0000s
main: -- foreign_keys(:vulnerability_reads)
main:    -> 0.0059s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("ALTER TABLE vulnerability_reads\nADD CONSTRAINT fk_aee839e611\nFOREIGN KEY (casted_cluster_agent_id)\nREFERENCES cluster_agents (id)\nON DELETE SET NULL\nNOT VALID;\n")
main:    -> 0.0043s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0004s
main: -- execute("ALTER TABLE vulnerability_reads VALIDATE CONSTRAINT fk_aee839e611;")
main:    -> 0.0166s
main: -- execute("RESET statement_timeout")
main:    -> 0.0006s
main: == 20220523164734 AddForeignKeyToVulnerabilityReadsCastedClusterAgentId: migrated (0.0372s) 

⋊> env VERBOSE=true bundle exec rake db:migrate:up:main VERSION=20220523165734
main: == 20220523165734 AddIndexToVulnerabilityReadsCastedClusterAgentId: migrating =
main: -- transaction_open?()
main:    -> 0.0000s
main: -- index_exists?(:vulnerability_reads, :casted_cluster_agent_id, {:name=>"index_cis_vulnerability_reads_on_cluster_agent_id", :where=>"report_type = 7", :algorithm=>:concurrently})
main:    -> 0.0115s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0005s
main: -- add_index(:vulnerability_reads, :casted_cluster_agent_id, {:name=>"index_cis_vulnerability_reads_on_cluster_agent_id", :where=>"report_type = 7", :algorithm=>:concurrently})
main:    -> 0.0084s
main: -- execute("RESET statement_timeout")
main:    -> 0.0005s
main: == 20220523165734 AddIndexToVulnerabilityReadsCastedClusterAgentId: migrated (0.0327s) 

⋊>  env VERBOSE=true bundle exec rake db:migrate:down:main VERSION=20220523165734
main: == 20220523165734 AddIndexToVulnerabilityReadsCastedClusterAgentId: reverting =
main: -- transaction_open?()
main:    -> 0.0000s
main: -- indexes(:vulnerability_reads)
main:    -> 0.0102s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0004s
main: -- remove_index(:vulnerability_reads, {:algorithm=>:concurrently, :name=>"index_cis_vulnerability_reads_on_cluster_agent_id"})
main:    -> 0.0032s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: == 20220523165734 AddIndexToVulnerabilityReadsCastedClusterAgentId: reverted (0.0221s) 

⋊>  env VERBOSE=true bundle exec rake db:migrate:down:main VERSION=20220523164734
main: == 20220523164734 AddForeignKeyToVulnerabilityReadsCastedClusterAgentId: reverting 
main: -- transaction_open?()
main:    -> 0.0000s
main: -- remove_foreign_key(:vulnerability_reads, :cluster_agents, {:column=>:casted_cluster_agent_id})
main:    -> 0.0090s
main: == 20220523164734 AddForeignKeyToVulnerabilityReadsCastedClusterAgentId: reverted (0.0192s) 

⋊>  env VERBOSE=true bundle exec rake db:migrate:down:main VERSION=20220523163734
main: == 20220523163734 UpdateVulnerabilityReadsTriggerFunctions: reverting =========
main: -- execute("CREATE OR REPLACE FUNCTION insert_or_update_vulnerability_reads()\nRETURNS TRIGGER\nLANGUAGE plpgsql\nAS $$\nDECLARE\n  severity smallint;\n  state smallint;\n  report_type smallint;\n  resolved_on_default_branch boolean;\nBEGIN\n  IF (NEW.vulnerability_id IS NULL AND (TG_OP = 'INSERT' OR TG_OP = 'UPDATE')) THEN\n    RETURN NULL;\n  END IF;\n\n  IF (TG_OP = 'UPDATE' AND OLD.vulnerability_id IS NOT NULL AND NEW.vulnerability_id IS NOT NULL) THEN\n    RETURN NULL;\n  END IF;\n\n  SELECT\n    vulnerabilities.severity, vulnerabilities.state, vulnerabilities.report_type, vulnerabilities.resolved_on_default_branch\n  INTO\n    severity, state, report_type, resolved_on_default_branch\n  FROM\n     vulnerabilities\n  WHERE\n    vulnerabilities.id = NEW.vulnerability_id;\n\n  INSERT INTO vulnerability_reads (vulnerability_id, project_id, scanner_id, report_type, severity, state, resolved_on_default_branch, uuid, location_image, cluster_agent_id)\n    VALUES (NEW.vulnerability_id, NEW.project_id, NEW.scanner_id, report_type, severity, state, resolved_on_default_branch, NEW.uuid::uuid, NEW.location->>'image', NEW.location->'kubernetes_resource'->>'agent_id')\n    ON CONFLICT(vulnerability_id) DO NOTHING;\n  RETURN NULL;\nEND\n$$;\n")
main:    -> 0.0033s
main: -- execute("CREATE OR REPLACE FUNCTION update_location_from_vulnerability_occurrences()\nRETURNS trigger\nLANGUAGE plpgsql\nAS $$\nBEGIN\nUPDATE\n  vulnerability_reads\nSET\n  location_image = NEW.location->>'image',\n  \n  cluster_agent_id = NEW.location->'kubernetes_resource'->>'agent_id'\nWHERE\n  vulnerability_id = NEW.vulnerability_id;\nRETURN NULL;\n\nEND\n$$;\n")
main:    -> 0.0008s
main: == 20220523163734 UpdateVulnerabilityReadsTriggerFunctions: reverted (0.0051s) 

⋊>  env VERBOSE=true bundle exec rake db:migrate:down:main VERSION=20220523162734
main: == 20220523162734 AddVulnerabilityReadsCastedClusterAgentColumn: reverting ====
main: -- column_exists?(:vulnerability_reads, :casted_cluster_agent_id)
main:    -> 0.0054s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- remove_column(:vulnerability_reads, :casted_cluster_agent_id)
main:    -> 0.0011s
main: == 20220523162734 AddVulnerabilityReadsCastedClusterAgentColumn: reverted (0.0147s) 

MR acceptance checklist

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

• I have evaluated the MR acceptance checklist for this MR.

Related to GraphQL filter for vulnerabilities by container... (#358763 - closed)