Changeset View
Changeset View
Standalone View
Standalone View
sql/updates/25.sql
- This file was added.
insert into dbversion (version, release, description) | |||||
values (25, now(), 'Work In Progress'); | |||||
create table scheduler_metrics ( | |||||
lister_id uuid not null references listers(id), | |||||
visit_type text not null, | |||||
last_update timestamptz not null, | |||||
origins_known int not null default 0, | |||||
origins_enabled int not null default 0, | |||||
origins_never_visited int not null default 0, | |||||
origins_with_pending_changes int not null default 0, | |||||
primary key (lister_id, visit_type) | |||||
); | |||||
comment on table scheduler_metrics is 'Cache of per-lister metrics for the scheduler, collated between the listed_origins and origin_visit_stats tables.'; | |||||
comment on column scheduler_metrics.lister_id is 'Lister instance on which metrics have been aggregated'; | |||||
comment on column scheduler_metrics.visit_type is 'Visit type on which metrics have been aggregated'; | |||||
comment on column scheduler_metrics.last_update is 'Last update of these metrics'; | |||||
comment on column scheduler_metrics.origins_known is 'Number of known (enabled or disabled) origins'; | |||||
comment on column scheduler_metrics.origins_enabled is 'Number of origins that were present in the latest listing'; | |||||
comment on column scheduler_metrics.origins_never_visited is 'Number of origins that have never been successfully visited'; | |||||
comment on column scheduler_metrics.origins_with_pending_changes is 'Number of enabled origins with known activity since our last visit'; | |||||
create or replace function update_metrics(lister_id uuid default NULL, ts timestamptz default now()) | |||||
returns setof scheduler_metrics | |||||
language sql | |||||
as $$ | |||||
insert into scheduler_metrics ( | |||||
lister_id, visit_type, last_update, | |||||
origins_known, origins_enabled, | |||||
origins_never_visited, origins_with_pending_changes | |||||
) | |||||
select | |||||
lo.lister_id, lo.visit_type, coalesce(ts, now()) as last_update, | |||||
count(*) as origins_known, | |||||
count(*) filter (where enabled) as origins_enabled, | |||||
count(*) filter (where | |||||
enabled and last_snapshot is NULL | |||||
) as origins_never_visited, | |||||
count(*) filter (where | |||||
enabled and lo.last_update > greatest(ovs.last_eventful, ovs.last_uneventful) | |||||
) as origins_with_pending_changes | |||||
from listed_origins lo | |||||
left join origin_visit_stats ovs using (url, visit_type) | |||||
where | |||||
-- update only for the requested lister | |||||
update_metrics.lister_id = lo.lister_id | |||||
-- or for all listers if the function argument is null | |||||
or update_metrics.lister_id is null | |||||
group by (lister_id, visit_type) | |||||
on conflict (lister_id, visit_type) do update | |||||
set | |||||
last_update = EXCLUDED.last_update, | |||||
origins_known = EXCLUDED.origins_known, | |||||
origins_enabled = EXCLUDED.origins_enabled, | |||||
origins_never_visited = EXCLUDED.origins_never_visited, | |||||
origins_with_pending_changes = EXCLUDED.origins_with_pending_changes | |||||
returning * | |||||
$$; | |||||
comment on function update_metrics(uuid, timestamptz) is 'Update metrics for the given lister_id'; |