Differential D6812 Diff 24688 swh/scheduler/sql/40-func.sql

Changeset View

Standalone View

swh/scheduler/sql/40-func.sql

Show First 20 Lines • Show All 357 Lines • ▼ Show 20 Lines	create trigger update_task_on_task_end
after update of status on task_run		after update of status on task_run
for each row		for each row
when (new.status NOT IN ('scheduled', 'started'))		when (new.status NOT IN ('scheduled', 'started'))
execute procedure swh_scheduler_update_task_on_task_end ();		execute procedure swh_scheduler_update_task_on_task_end ();


create or replace function update_metrics(lister_id uuid default NULL, ts timestamptz default now())		create or replace function update_metrics(lister_id uuid default NULL, ts timestamptz default now())
returns setof scheduler_metrics		returns setof scheduler_metrics
language sql		language plpgsql
as $$		as $$
insert into scheduler_metrics (		begin
lister_id, visit_type, last_update,		-- If we do the following select as a subquery in the insert statement below,
origins_known, origins_enabled,		-- PostgreSQL prevents the use of parallel queries. So we do the select into a
origins_never_visited, origins_with_pending_changes		-- temporary table, which doesn't suffer this limitation.
)
select		create temporary table tmp_update_metrics
		on commit drop
		as select
lo.lister_id, lo.visit_type, coalesce(ts, now()) as last_update,		lo.lister_id, lo.visit_type, coalesce(ts, now()) as last_update,
count(*) as origins_known,		count(*) as origins_known,
count(*) filter (where enabled) as origins_enabled,		count(*) filter (where enabled) as origins_enabled,
count(*) filter (where		count(*) filter (where
enabled and last_snapshot is NULL		enabled and last_snapshot is NULL
) as origins_never_visited,		) as origins_never_visited,
count(*) filter (where		count(*) filter (where
enabled and lo.last_update > last_successful		enabled and lo.last_update > last_successful
) as origins_with_pending_changes		) as origins_with_pending_changes
from listed_origins lo		from listed_origins lo
left join origin_visit_stats ovs using (url, visit_type)		left join origin_visit_stats ovs using (url, visit_type)
where		where
-- update only for the requested lister		-- update only for the requested lister
update_metrics.lister_id = lo.lister_id		update_metrics.lister_id = lo.lister_id
-- or for all listers if the function argument is null		-- or for all listers if the function argument is null
or update_metrics.lister_id is null		or update_metrics.lister_id is null
group by (lister_id, visit_type)		group by (lo.lister_id, lo.visit_type);
on conflict (lister_id, visit_type) do update
		return query
		insert into scheduler_metrics (
		lister_id, visit_type, last_update,
		origins_known, origins_enabled,
		origins_never_visited, origins_with_pending_changes
		)
		select * from tmp_update_metrics
		on conflict on constraint scheduler_metrics_pkey do update
set		set
last_update = EXCLUDED.last_update,		last_update = EXCLUDED.last_update,
origins_known = EXCLUDED.origins_known,		origins_known = EXCLUDED.origins_known,
origins_enabled = EXCLUDED.origins_enabled,		origins_enabled = EXCLUDED.origins_enabled,
origins_never_visited = EXCLUDED.origins_never_visited,		origins_never_visited = EXCLUDED.origins_never_visited,
origins_with_pending_changes = EXCLUDED.origins_with_pending_changes		origins_with_pending_changes = EXCLUDED.origins_with_pending_changes
returning *		returning *;
		end;
$$;		$$;

comment on function update_metrics(uuid, timestamptz) is 'Update metrics for the given lister_id';		comment on function update_metrics(uuid, timestamptz) is 'Update metrics for the given lister_id';