Skip to content

Add `namespace_id` to `vulnerability_reads` table

Mehmet Emin INAC requested to merge 367300_add_namespace_id_to_vulnerability_reads_table into master

What does this MR do and why?

This MR introduces another denormalization for the vulnerability_reads table to make it faster to filter by namespace_id. Related triggers are also updated to set the new column value.

Related to [Proposal] Add `namespace_id` to `vulnerability... (#367300 - closed).

Database review

Adding the new column
rake db:migrate:up 
main: == 20220720161816 AddNamespaceIdToVulnerabilityReadsTable: migrating ==========
main: -- add_column(:vulnerability_reads, :namespace_id, :bigint)
main:    -> 0.0039s
main: == 20220720161816 AddNamespaceIdToVulnerabilityReadsTable: migrated (0.0059s) =
rake db:migrate:down 
main: == 20220720161816 AddNamespaceIdToVulnerabilityReadsTable: reverting ==========
main: -- remove_column(:vulnerability_reads, :namespace_id, :bigint)
main:    -> 0.0086s
main: == 20220720161816 AddNamespaceIdToVulnerabilityReadsTable: reverted (0.0444s) =
Changing the triggers
rake db:migrate:up 
main: == 20220720162123 UpdateVulnerabilityReadsTriggersToSetNamespaceId: migrating =
main: -- execute("CREATE OR REPLACE FUNCTION insert_or_update_vulnerability_reads() RETURNS trigger\n    LANGUAGE plpgsql\n    AS $$\nDECLARE\n  severity smallint;\n  state smallint;\n  report_type smallint;\n  resolved_on_default_branch boolean;\n  present_on_default_branch boolean;\n  namespace_id bigint;\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, vulnerabilities.present_on_default_branch\n  INTO\n    severity, state, report_type, resolved_on_default_branch, present_on_default_branch\n  FROM\n     vulnerabilities\n  WHERE\n    vulnerabilities.id = NEW.vulnerability_id;\n\n  IF present_on_default_branch IS NOT true THEN\n    RETURN NULL;\n  END IF;\n\n  SELECT\n    projects.namespace_id\n  INTO\n    namespace_id\n  FROM\n    projects\n  WHERE\n    projects.id = NEW.project_id;\n\n  INSERT INTO vulnerability_reads (vulnerability_id, namespace_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, namespace_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.0061s
main: -- execute("CREATE OR REPLACE FUNCTION insert_vulnerability_reads_from_vulnerability() RETURNS trigger\n    LANGUAGE plpgsql\n    AS $$\nDECLARE\n  scanner_id bigint;\n  uuid uuid;\n  location_image text;\n  cluster_agent_id text;\n  casted_cluster_agent_id bigint;\n  namespace_id bigint;\nBEGIN\n  SELECT\n    v_o.scanner_id, v_o.uuid, v_o.location->>'image', v_o.location->'kubernetes_resource'->>'agent_id', CAST(v_o.location->'kubernetes_resource'->>'agent_id' AS bigint), projects.namespace_id\n  INTO\n    scanner_id, uuid, location_image, cluster_agent_id, casted_cluster_agent_id, namespace_id\n  FROM\n    vulnerability_occurrences v_o\n  INNER JOIN projects ON projects.id = v_o.project_id\n  WHERE\n    v_o.vulnerability_id = NEW.id\n  LIMIT 1;\n\n  INSERT INTO vulnerability_reads (vulnerability_id, namespace_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.id, namespace_id, NEW.project_id, scanner_id, NEW.report_type, NEW.severity, NEW.state, NEW.resolved_on_default_branch, uuid::uuid, location_image, cluster_agent_id, casted_cluster_agent_id)\n    ON CONFLICT(vulnerability_id) DO NOTHING;\n  RETURN NULL;\nEND\n$$;\n")
main:    -> 0.0008s
main: == 20220720162123 UpdateVulnerabilityReadsTriggersToSetNamespaceId: migrated (0.0072s)
rake db:migrate:down 
main: == 20220720162123 UpdateVulnerabilityReadsTriggersToSetNamespaceId: reverting =
main: -- execute("CREATE OR REPLACE FUNCTION insert_or_update_vulnerability_reads() RETURNS trigger\n    LANGUAGE plpgsql\n    AS $$\nDECLARE\n  severity smallint;\n  state smallint;\n  report_type smallint;\n  resolved_on_default_branch boolean;\n  present_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, vulnerabilities.present_on_default_branch\n  INTO\n    severity, state, report_type, resolved_on_default_branch, present_on_default_branch\n  FROM\n     vulnerabilities\n  WHERE\n    vulnerabilities.id = NEW.vulnerability_id;\n\n  IF present_on_default_branch IS NOT true THEN\n    RETURN NULL;\n  END IF;\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.0068s
main: -- execute("CREATE OR REPLACE FUNCTION insert_vulnerability_reads_from_vulnerability() RETURNS trigger\n    LANGUAGE plpgsql\n    AS $$\nDECLARE\n  scanner_id bigint;\n  uuid uuid;\n  location_image text;\n  cluster_agent_id text;\n  casted_cluster_agent_id bigint;\nBEGIN\n  SELECT\n    v_o.scanner_id, v_o.uuid, v_o.location->>'image', v_o.location->'kubernetes_resource'->>'agent_id', CAST(v_o.location->'kubernetes_resource'->>'agent_id' AS bigint)\n  INTO\n    scanner_id, uuid, location_image, cluster_agent_id, casted_cluster_agent_id\n  FROM\n    vulnerability_occurrences v_o\n  WHERE\n    v_o.vulnerability_id = NEW.id\n  LIMIT 1;\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.id, NEW.project_id, scanner_id, NEW.report_type, NEW.severity, NEW.state, NEW.resolved_on_default_branch, uuid::uuid, location_image, cluster_agent_id, casted_cluster_agent_id)\n    ON CONFLICT(vulnerability_id) DO NOTHING;\n  RETURN NULL;\nEND\n$$;\n")
main:    -> 0.0032s
main: == 20220720162123 UpdateVulnerabilityReadsTriggersToSetNamespaceId: reverted (0.0136s)
Adding the index
rake db:migrate:up 
main: == 20220720171102 AddIndexOnVulnerabilityReadsNamespaceId: migrating ==========
main: -- transaction_open?()
main:    -> 0.0000s
main: -- index_exists?(:vulnerability_reads, [:namespace_id, :state, :severity, :vulnerability_id], {:name=>"index_vuln_reads_on_namespace_id_state_severity_and_vuln_id", :order=>{:vulnerability_id=>:desc}, :algorithm=>:concurrently})
main:    -> 0.0189s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0006s
main: -- add_index(:vulnerability_reads, [:namespace_id, :state, :severity, :vulnerability_id], {:name=>"index_vuln_reads_on_namespace_id_state_severity_and_vuln_id", :order=>{:vulnerability_id=>:desc}, :algorithm=>:concurrently})
main:    -> 0.0866s
main: -- execute("RESET statement_timeout")
main:    -> 0.0007s
main: == 20220720171102 AddIndexOnVulnerabilityReadsNamespaceId: migrated (0.1200s) =
rake db:migrate:down 
main: == 20220720171102 AddIndexOnVulnerabilityReadsNamespaceId: reverting ==========
main: -- transaction_open?()
main:    -> 0.0000s
main: -- indexes(:vulnerability_reads)
main:    -> 0.0373s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0010s
main: -- remove_index(:vulnerability_reads, {:algorithm=>:concurrently, :name=>"index_vuln_reads_on_namespace_id_state_severity_and_vuln_id"})
main:    -> 0.0078s
main: -- execute("RESET statement_timeout")
main:    -> 0.0008s
main: == 20220720171102 AddIndexOnVulnerabilityReadsNamespaceId: reverted (0.0702s) =
Adding the foreign key
rake db:migrate:up 
main: == 20220720171801 AddForeignKeyOnVulnerabilityReadsNamespaceId: migrating =====
main: -- transaction_open?()
main:    -> 0.0000s
main: -- foreign_keys(:vulnerability_reads)
main:    -> 0.1570s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("ALTER TABLE vulnerability_reads\nADD CONSTRAINT fk_4f593f6c62\nFOREIGN KEY (namespace_id)\nREFERENCES namespaces (id)\nON DELETE CASCADE\nNOT VALID;\n")
main:    -> 0.0067s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0006s
main: -- execute("ALTER TABLE vulnerability_reads VALIDATE CONSTRAINT fk_4f593f6c62;")
main:    -> 0.0185s
main: -- execute("RESET statement_timeout")
main:    -> 0.0007s
main: == 20220720171801 AddForeignKeyOnVulnerabilityReadsNamespaceId: migrated (0.2127s)
rake db:migrate:down 
main: == 20220720171801 AddForeignKeyOnVulnerabilityReadsNamespaceId: reverting =====
main: -- transaction_open?()
main:    -> 0.0000s
main: -- foreign_keys(:vulnerability_reads)
main:    -> 0.0103s
main: -- remove_foreign_key(:vulnerability_reads, {:column=>:namespace_id})
main:    -> 0.0066s
main: == 20220720171801 AddForeignKeyOnVulnerabilityReadsNamespaceId: reverted (0.0409s)

The new trigger versions will read namespace_id from the projects table by filtering them by the PK of the table. I can provide the execution plans if needed.

MR acceptance checklist

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

Edited by Mehmet Emin INAC

Merge request reports