04.sql
-- SWH Scheduler Schema upgrade
-- from_version: 03
-- to_version: 04
-- description: Add a maximum queue length to the task types in the scheduler
begin;
insert into dbversion (version, release, description)
values (4, now(), 'Work In Progress');
alter table task_type add column max_queue_length bigint;
comment on column task_type.max_queue_length is 'Maximum length of the queue for this type of tasks';
drop function swh_scheduler_peek_ready_tasks (timestamptz, bigint);
drop function swh_scheduler_grab_ready_tasks (timestamptz, bigint);
create or replace function swh_scheduler_peek_ready_tasks (task_type text, ts timestamptz default now(),
num_tasks bigint default NULL)
returns setof task
language sql
stable
as $$
select * from task
where next_run <= ts
and type = task_type
and status = 'next_run_not_scheduled'
order by next_run
limit num_tasks;
$$;
create or replace function swh_scheduler_grab_ready_tasks (task_type text, ts timestamptz default now(),
num_tasks bigint default NULL)
returns setof task
language sql
as $$
update task
set status='next_run_scheduled'
from (
select id from task
where next_run <= ts
and type = task_type
and status='next_run_not_scheduled'
order by next_run
limit num_tasks
for update skip locked
) next_tasks
where task.id = next_tasks.id
returning task.*;
$$;
commit;