04.sql
No OneTemporary
Actions

Size

1 KB

Subscribers

None

04.sql
View Options

	-- 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;