Staging swh-scheduler db dump mounted on docker db:
```
time psql -e service=swh-scheduler-dev -c '\i sql/updates/33.sql'
Pager usage is off.
Null display is "(null)".
Timing is on.
Border style is 2.
Current Host Server Date Time : Wed 12 Jan 2022 10:58:53 AM CET
Administrative queries:
:settings -- Server Settings
:conninfo -- Server connections
:activity -- Server activity
:locks -- Lock info
:waits -- Waiting queires
:dbsize -- Database Size
:tablesize -- Tables Size
:uselesscol -- Useless columns
:uptime -- Server uptime
:menu -- Help Menu
\h -- Help with SQL commands
\? -- Help with psql commands
Development queries:
:sp -- Current Search Path
:clear -- Clear screen
:ll -- List
Expanded display is used automatically.
rollback;
psql:sql/updates/33.sql:6: WARNING: there is no transaction in progress
ROLLBACK
Time: 0.152 ms
begin;
BEGIN
Time: 0.100 ms
insert into dbversion (version, release, description)
values (33, now(), 'Work In Progress');
INSERT 0 1
Time: 0.501 ms
create table new_task (
like task
including all
excluding indexes
excluding constraints
);
CREATE TABLE
Time: 3.948 ms
create table new_task_run (
like task_run
including all
excluding indexes
excluding constraints
);
CREATE TABLE
Time: 2.324 ms
insert into new_task(id, type, arguments, next_run, current_interval, status, policy,
retries_left, priority)
select id, type, arguments, next_run, current_interval, status, policy,
retries_left, priority
from task
where (
policy='recurring' and (type = 'load-nixguix' or
type like 'list-%' or
type like 'index-%')
) or (
policy = 'oneshot' and next_run > now() - interval '2 months'
);
INSERT 0 634838
Time: 6245.212 ms (00:06.245)
insert into new_task_run
select id, task, backend_id, scheduled, started, ended, metadata, status
from task_run where task in (
select distinct id from new_task
);
INSERT 0 961657
Time: 4541.407 ms (00:04.541)
select last_value from task_id_seq;
+------------+
| last_value |
+------------+
| 29197936 |
+------------+
(1 row)
Time: 0.371 ms
select last_value from task_run_id_seq;
+------------+
| last_value |
+------------+
| 72602306 |
+------------+
(1 row)
Time: 0.219 ms
alter table task rename to archive_task;
ALTER TABLE
Time: 0.276 ms
alter table task_run rename to archive_task_run;
ALTER TABLE
Time: 0.190 ms
alter table new_task rename to task;
ALTER TABLE
Time: 0.197 ms
alter table new_task_run rename to task_run;
ALTER TABLE
Time: 0.132 ms
select last_value from task_id_seq;
+------------+
| last_value |
+------------+
| 29197936 |
+------------+
(1 row)
Time: 0.085 ms
select last_value from task_run_id_seq;
+------------+
| last_value |
+------------+
| 72602306 |
+------------+
(1 row)
Time: 0.074 ms
alter table task
add primary key(id);
ALTER TABLE
Time: 219.584 ms
alter table task
alter column type set not null;
ALTER TABLE
Time: 0.251 ms
alter table task
add constraint task_type_fk
foreign key (type) references task_type (type);
ALTER TABLE
Time: 157.253 ms
alter table task
add constraint task_priority_fk
foreign key (priority) references priority_ratio (id);
ALTER TABLE
Time: 74.358 ms
alter table task
add constraint task_check_policy
check (policy <> 'recurring' or current_interval is not null)
not valid;
ALTER TABLE
Time: 0.586 ms
alter table task
validate constraint task_check_policy;
ALTER TABLE
Time: 72.120 ms
alter table task_run
add primary key(id);
ALTER TABLE
Time: 358.370 ms
alter table task_run
alter column status set not null,
alter column status set default 'scheduled';
ALTER TABLE
Time: 0.573 ms
alter table task_run
alter column task set not null,
add constraint task_id_fk
foreign key (task) references task (id);
ALTER TABLE
Time: 459.268 ms
create index on task(type);
CREATE INDEX
Time: 400.429 ms
create index on task(next_run);
CREATE INDEX
Time: 214.107 ms
create index on task using btree(type, md5(arguments::text));
CREATE INDEX
Time: 1850.221 ms (00:01.850)
create index on task(priority);
CREATE INDEX
Time: 232.837 ms
create index on task_run(task);
CREATE INDEX
Time: 367.688 ms
create index on task_run(backend_id);
CREATE INDEX
Time: 1741.770 ms (00:01.742)
create index on task_run(task asc, started asc);
CREATE INDEX
Time: 494.755 ms
create index on task(type, next_run)
where status = 'next_run_not_scheduled'::task_status;
CREATE INDEX
Time: 65.994 ms
create or replace function swh_scheduler_update_task_on_task_end ()
returns trigger
language plpgsql
as $$
declare
cur_task task%rowtype;
cur_task_type task_type%rowtype;
adjustment_factor float;
new_interval interval;
begin
select * from task where id = new.task into cur_task;
select * from task_type where type = cur_task.type into cur_task_type;
case
when new.status = 'permfailed' then
update task
set status = 'disabled'
where id = cur_task.id;
when new.status in ('eventful', 'uneventful') then
case
when cur_task.policy = 'oneshot' then
update task
set status = 'completed'
where id = cur_task.id;
when cur_task.policy = 'recurring' then
if new.status = 'uneventful' then
adjustment_factor := 1/cur_task_type.backoff_factor;
else
adjustment_factor := 1/cur_task_type.backoff_factor;
end if;
new_interval := greatest(
cur_task_type.min_interval,
least(
cur_task_type.max_interval,
adjustment_factor * cur_task.current_interval));
update task
set status = 'next_run_not_scheduled',
next_run = new.ended + new_interval,
current_interval = new_interval,
retries_left = coalesce(cur_task_type.num_retries, 0)
where id = cur_task.id;
end case;
else -- new.status in 'failed', 'lost'
if cur_task.retries_left > 0 then
update task
set status = 'next_run_not_scheduled',
next_run = new.ended + coalesce(cur_task_type.retry_delay, interval '1 hour'),
retries_left = cur_task.retries_left - 1
where id = cur_task.id;
else -- no retries left
case
when cur_task.policy = 'oneshot' then
update task
set status = 'disabled'
where id = cur_task.id;
when cur_task.policy = 'recurring' then
update task
set status = 'next_run_not_scheduled',
next_run = new.ended + cur_task.current_interval,
retries_left = coalesce(cur_task_type.num_retries, 0)
where id = cur_task.id;
end case;
end if; -- retries
end case;
return null;
end;
$$;
CREATE FUNCTION
Time: 1.079 ms
create trigger update_task_on_task_end
after update of status on task_run
for each row
when (new.status NOT IN ('scheduled', 'started'))
execute procedure swh_scheduler_update_task_on_task_end ();
CREATE TRIGGER
Time: 0.432 ms
psql -e service=swh-scheduler-dev -c '\i sql/updates/33.sql' 0.01s user 0.00s system 0% cpu 17.516 total
```