Implement triggers to keep the `vulnerability_reads table` in sync
Why are we doing this work
As we are constantly getting time-out errors while loading the group&instance-level vulnerability reports pages, we wanted to implement a performant solution to query our complex data model. We are basically creating a new table to denormalize our data to make it easier to query which will improve the performance. This approach is called "Command Query Responsibility Segregation" which in short is CQRS.
The first implementation task of having a separate read model is defining & creating the read model which will be done within the scope of this issue.
Relevant links
- An example timeout issue: #327607 (closed)
Implementation plan
-
database Implement triggers for the following scenarios to keep the vulnerability_reads
table in synch;-
A trigger to insert into this table whenever we have a new record created in the vulnerabilities
table -
A trigger to update the related record when a duplicated column of a vulnerability gets updated in the vulnerabilities
table -
A trigger to update the has_issues
flag, when we create an issue for a vulnerability
-
Trigger & function 1
create or replace function insert_vulnerability_reads() returns trigger
language plpgsql
as $$
declare
scanner_id bigint;
has_issues boolean;
begin
select
vulnerability_scanners.id into scanner_id
from
vulnerability_occurrences
inner join vulnerability_scanners on vulnerability_scanners.id = vulnerability_occurrences.scanner_id
where
vulnerability_occurrences.vulnerability_id = new.id;
select exists(
select
1
from
vulnerability_issue_links
where vulnerability_issue_links.vulnerability_id = new.id
) into has_issues;
insert into vulnerability_reads (vulnerability_id, projects_id, report_type, scanner_id, severity, state, has_issues, resolved_on_default_branch)
values (new.id, new.project_id, new.report_type, scanner_id, new.severity, new.state, has_issues, new.resolved_on_default_branch);
return null;
end
$$;
create trigger insert_vulnerability_reads after insert on vulnerabilities for each row execute procedure insert_vulnerability_reads();
Trigger & function 2
create or replace 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 update_vulnerability_reads_from_vulnerability after update on vulnerabilities for each row execute procedure update_vulnerability_reads_from_vulnerability();
Trigger & function 3
create or replace 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;
return null;
end
$$;
create trigger set_has_issues_on_vulnerability_reads after update on vulnerability_issue_links for each row execute procedure set_has_issues_on_vulnerability_reads();
Edited by Thiago Figueiró