Add triggers to sync vulnerability_reads (!75230) · Merge requests · GitLab.org / GitLab

Sashi Kumar Kumaresan requested to merge sk/341393-create-vuln-reads-trigger into master Nov 24, 2021

What does this MR do and why?

This MR creates triggers to create/update rows in vulnerability_reads which is used to increase the performance of vulnerabilities filtering.

Database Changes

Migrate Up

bundle exec rake db:migrate:up VERSION=20220106111958
== 20220106111958 AddInsertOrUpdateVulnerabilityReadsTrigger: migrating =======
-- 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")
   -> 0.0055s
-- execute("CREATE TRIGGER trigger_insert_or_update_vulnerability_reads_from_occurrences\nAFTER INSERT OR UPDATE ON vulnerability_occurrences\nFOR EACH ROW\nEXECUTE PROCEDURE insert_or_update_vulnerability_reads();\n")
   -> 0.0011s
== 20220106111958 AddInsertOrUpdateVulnerabilityReadsTrigger: migrated (0.0071s) 

bundle exec rake db:migrate:up VERSION=20220106112043
== 20220106112043 AddUpdateVulnerabilityReadsTrigger: migrating ===============
-- execute("CREATE OR REPLACE FUNCTION update_vulnerability_reads_from_vulnerability()\nRETURNS TRIGGER AS\n$$\nBEGIN\nUPDATE\n  vulnerability_reads\nSET\n  severity = NEW.severity,\n  state = NEW.state,\n  resolved_on_default_branch = NEW.resolved_on_default_branch\nWHERE vulnerability_id = NEW.id;\nRETURN NULL;\n\nEND\n$$ LANGUAGE PLPGSQL\n")
   -> 0.0074s
-- execute("CREATE TRIGGER trigger_update_vulnerability_reads_on_vulnerability_update\nAFTER UPDATE ON vulnerabilities\nFOR EACH ROW\nWHEN (\n  OLD.severity IS DISTINCT FROM NEW.severity OR\n  OLD.state IS DISTINCT FROM NEW.state OR\n  OLD.resolved_on_default_branch IS DISTINCT FROM NEW.resolved_on_default_branch\n)\nEXECUTE PROCEDURE update_vulnerability_reads_from_vulnerability();\n")
   -> 0.0039s
== 20220106112043 AddUpdateVulnerabilityReadsTrigger: migrated (0.0115s) ======

bundle exec rake db:migrate:up VERSION=20220106112085
== 20220106112085 AddUpdateVulnerabilityReadsLocationTrigger: migrating =======
-- execute("CREATE OR REPLACE FUNCTION update_location_from_vulnerability_occurrences()\nRETURNS TRIGGER AS\n$$\nBEGIN\nUPDATE\n  vulnerability_reads\nSET\n  location_image = NEW.location->>'image',\n  cluster_agent_id = NEW.location->'kubernetes_resource'->>'agent_id'\nWHERE\n  vulnerability_id = NEW.vulnerability_id;\nRETURN NULL;\n\nEND\n$$ LANGUAGE PLPGSQL\n")
   -> 0.0091s
-- execute("CREATE TRIGGER trigger_update_location_on_vulnerability_occurrences_update\nAFTER UPDATE ON vulnerability_occurrences\nFOR EACH ROW\nWHEN (\n  NEW.report_type IN (2, 7) AND (\n    OLD.location->>'image' IS DISTINCT FROM NEW.location->>'image' OR\n    OLD.location->'kubernetes_resource'->>'agent_id' IS DISTINCT FROM NEW.location->'kubernetes_resource'->>'agent_id'\n  )\n)\nEXECUTE PROCEDURE update_location_from_vulnerability_occurrences();\n")
   -> 0.0042s
== 20220106112085 AddUpdateVulnerabilityReadsLocationTrigger: migrated (0.0135s) 

bundle exec rake db:migrate:up VERSION=20220106163326
== 20220106163326 AddHasIssuesOnVulnerabilityReadsTrigger: migrating ==========
-- execute("CREATE OR REPLACE FUNCTION set_has_issues_on_vulnerability_reads()\nRETURNS TRIGGER AS\n$$\nBEGIN\nUPDATE\n  vulnerability_reads\nSET\n  has_issues = true\nWHERE\n  vulnerability_id = NEW.vulnerability_id AND has_issues IS FALSE;\nRETURN NULL;\n\nEND\n$$ LANGUAGE PLPGSQL\n")
   -> 0.0100s
-- execute("CREATE OR REPLACE FUNCTION unset_has_issues_on_vulnerability_reads()\nRETURNS TRIGGER\nLANGUAGE plpgsql\nAS $$\nDECLARE\n  has_issues_link integer;\n  vulnerability_reads_id bigint;\nBEGIN\n  SELECT 1 INTO has_issue_links FROM vulnerability_issue_links WHERE vulnerability_id = OLD.vulnerability_id;\n\n  IF (has_issue_links = 1) THEN\n    RETURN NULL;\n  END IF;\n\n  SELECT\n    id INTO vulnerability_reads_id\n  FROM\n    vulnerability_reads\n  WHERE\n    vulnerability_id = OLD.vulnerability_id AND has_issues IS TRUE\n  FOR UPDATE;\n\n  UPDATE\n    vulnerability_reads\n  SET\n    has_issues = false\n  WHERE\n    id = vulnerability_reads_id;\n  RETURN NULL;\nEND\n$$;\n")
   -> 0.0018s
-- execute("CREATE TRIGGER trigger_update_has_issues_on_vulnerability_issue_links_update\nAFTER INSERT ON vulnerability_issue_links\nFOR EACH ROW\nEXECUTE FUNCTION set_has_issues_on_vulnerability_reads();\n")
   -> 0.0037s
-- execute("CREATE TRIGGER trigger_update_has_issues_on_vulnerability_issue_links_delete\nAFTER DELETE ON vulnerability_issue_links\nFOR EACH ROW\nEXECUTE FUNCTION unset_has_issues_on_vulnerability_reads();\n")
   -> 0.0017s
== 20220106163326 AddHasIssuesOnVulnerabilityReadsTrigger: migrated (0.0175s) =

Migrate Down

bundle exec rake db:migrate:down VERSION=20220106111958
== 20220106111958 AddInsertOrUpdateVulnerabilityReadsTrigger: reverting =======
-- execute("DROP TRIGGER IF EXISTS trigger_insert_or_update_vulnerability_reads_from_occurrences ON vulnerability_occurrences")
   -> 0.0032s
-- execute("DROP FUNCTION IF EXISTS insert_or_update_vulnerability_reads()")
   -> 0.0007s
== 20220106111958 AddInsertOrUpdateVulnerabilityReadsTrigger: reverted (0.0040s) 

bundle exec rake db:migrate:down VERSION=20220106112043
== 20220106112043 AddUpdateVulnerabilityReadsTrigger: reverting ===============
-- execute("DROP TRIGGER IF EXISTS trigger_update_vulnerability_reads_on_vulnerability_update ON vulnerabilities")
   -> 0.0031s
-- execute("DROP FUNCTION IF EXISTS update_vulnerability_reads_from_vulnerability()")
   -> 0.0009s
== 20220106112043 AddUpdateVulnerabilityReadsTrigger: reverted (0.0040s) ======

bundle exec rake db:migrate:down VERSION=20220106112085
== 20220106112085 AddUpdateVulnerabilityReadsLocationTrigger: reverting =======
-- execute("DROP TRIGGER IF EXISTS trigger_update_location_on_vulnerability_occurrences_update ON vulnerability_occurrences")
   -> 0.0028s
-- execute("DROP FUNCTION IF EXISTS update_location_from_vulnerability_occurrences()")
   -> 0.0006s
== 20220106112085 AddUpdateVulnerabilityReadsLocationTrigger: reverted (0.0035s) 

bundle exec rake db:migrate:down VERSION=20220106163326
== 20220106163326 AddHasIssuesOnVulnerabilityReadsTrigger: reverting ==========
-- execute("DROP TRIGGER IF EXISTS trigger_update_has_issues_on_vulnerability_issue_links_update ON vulnerability_issue_links")
   -> 0.0034s
-- execute("DROP FUNCTION IF EXISTS set_has_issues_on_vulnerability_reads()")
   -> 0.0008s
-- execute("DROP TRIGGER IF EXISTS trigger_update_has_issues_on_vulnerability_issue_links_delete ON vulnerability_issue_links")
   -> 0.0007s
-- execute("DROP FUNCTION IF EXISTS unset_has_issues_on_vulnerability_reads()")
   -> 0.0007s
== 20220106163326 AddHasIssuesOnVulnerabilityReadsTrigger: reverted (0.0057s) =

Trigger 1

CREATE FUNCTION insert_or_update_vulnerability_reads() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
  severity smallint;
  state smallint;
  report_type smallint;
  resolved_on_default_branch boolean;
BEGIN
  IF (NEW.vulnerability_id IS NULL AND (TG_OP = 'INSERT' OR TG_OP = 'UPDATE')) THEN
    RETURN NULL;
  END IF;

  IF (TG_OP = 'UPDATE' AND OLD.vulnerability_id IS NOT NULL AND NEW.vulnerability_id IS NOT NULL) THEN
    RETURN NULL;
  END IF;

  SELECT
    vulnerabilities.severity, vulnerabilities.state, vulnerabilities.report_type, vulnerabilities.resolved_on_default_branch
  INTO
    severity, state, report_type, resolved_on_default_branch
  FROM
     vulnerabilities
  WHERE
    vulnerabilities.id = NEW.vulnerability_id;

  INSERT INTO vulnerability_reads (vulnerability_id, project_id, scanner_id, report_type, severity, state, resolved_on_default_branch, uuid, location_image, cluster_agent_id)
    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')
    ON CONFLICT(vulnerability_id) DO NOTHING;
  RETURN NULL;
END
$$;

CREATE TRIGGER
  trigger_insert_or_update_vulnerability_reads_from_occurrences
AFTER INSERT OR UPDATE ON
  vulnerability_occurrences
FOR EACH ROW EXECUTE FUNCTION insert_or_update_vulnerability_reads();

Trigger 2

CREATE FUNCTION set_has_issues_on_vulnerability_reads() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
UPDATE
  vulnerability_reads
SET
  has_issues = true
WHERE
  vulnerability_id = NEW.vulnerability_id AND has_issues IS FALSE;
RETURN NULL;

END
$$;

CREATE FUNCTION unset_has_issues_on_vulnerability_reads() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
  has_issue_links integer;
  vulnerability_reads_id bigint;
BEGIN
  SELECT 1 INTO has_issue_links FROM vulnerability_issue_links WHERE vulnerability_id = OLD.vulnerability_id;

  IF (has_issue_links = 1) THEN
    RETURN NULL;
  END IF;

  SELECT
    id INTO vulnerability_reads_id
  FROM
    vulnerability_reads
  WHERE
    vulnerability_id = OLD.vulnerability_id AND has_issues IS TRUE
  FOR UPDATE;

  UPDATE
    vulnerability_reads
  SET
    has_issues = false
  WHERE
    id = vulnerability_reads_id;
  RETURN NULL;
END
$$;

CREATE TRIGGER
  trigger_update_has_issues_on_vulnerability_issue_links_update
AFTER INSERT ON
  vulnerability_issue_links
FOR EACH ROW EXECUTE FUNCTION set_has_issues_on_vulnerability_reads();

CREATE TRIGGER
  trigger_update_has_issues_on_vulnerability_issue_links_delete
AFTER DELETE ON
  vulnerability_issue_links
FOR EACH ROW EXECUTE FUNCTION unset_has_issues_on_vulnerability_reads();

Trigger 3

CREATE FUNCTION update_location_from_vulnerability_occurrences() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
UPDATE
  vulnerability_reads
SET
  location_image = NEW.location->>'image',
  cluster_agent_id = NEW.location->'kubernetes_resource'->>'agent_id'
WHERE
  vulnerability_id = NEW.vulnerability_id;
RETURN NULL;

END
$$;

CREATE TRIGGER
  trigger_update_location_on_vulnerability_occurrences_update
AFTER UPDATE ON
  vulnerability_occurrences
FOR EACH ROW WHEN (
  ((new.report_type = ANY (ARRAY[2, 7])) AND (
    ((old.location ->> 'image'::text) IS DISTINCT FROM (new.location ->> 'image'::text)) OR 
    (((old.location -> 'kubernetes_resource'::text) ->> 'agent_id'::text) IS DISTINCT FROM ((new.location -> 'kubernetes_resource'::text) ->> 'agent_id'::text)))
  )
)
EXECUTE FUNCTION
  update_location_from_vulnerability_occurrences();

Trigger 4

CREATE FUNCTION update_vulnerability_reads_from_vulnerability() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
UPDATE
  vulnerability_reads
SET
  severity = NEW.severity,
  state = NEW.state,
  resolved_on_default_branch = NEW.resolved_on_default_branch
WHERE vulnerability_id = NEW.id;
RETURN NULL;

END
$$;

CREATE TRIGGER
  trigger_update_vulnerability_reads_on_vulnerability_update
AFTER UPDATE ON
  vulnerabilities
FOR EACH ROW EXECUTE FUNCTION update_vulnerability_reads_from_vulnerability();

📈 Inserts/Updates/Deletes metrics

Dashboard(Internal)

This table shows the maximum number of operations per second for the last 7 days duration.

Table	Inserts	Updates	Deletes
`vulnerabilities`	27.9 ops/s	1540 ops/s	267 ops/s
`vulnerability_occurrences`	27.9 ops/s	61.3 ops/s	183 ops/s
`vulnerability_issue_links`	0.332 ops/s	-	0.0120 ops/s

⚠ Incident Mitigation

In case of any incident related to this change, we need to delete the triggers:

DROP TRIGGER IF EXISTS trigger_insert_or_update_vulnerability_reads_from_occurrences ON vulnerability_occurrences
DROP TRIGGER IF EXISTS trigger_update_vulnerability_reads_on_vulnerability_update ON vulnerabilities
DROP TRIGGER IF EXISTS trigger_update_location_on_vulnerability_occurrences_update ON vulnerability_occurrences
DROP TRIGGER IF EXISTS trigger_update_has_issues_on_vulnerability_issue_links_update ON vulnerability_issue_links
DROP TRIGGER IF EXISTS trigger_update_has_issues_on_vulnerability_issue_links_delete ON vulnerability_issue_links

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.

Edited Jan 28, 2022 by Sashi Kumar Kumaresan

Add triggers to sync vulnerability_reads

What does this MR do and why?

Database Changes

📈 Inserts/Updates/Deletes metrics

⚠ Incident Mitigation

MR acceptance checklist

Merge request reports