-- SWH DB schema upgrade
-- from_version: 32
-- to_version: 33
-- description: Archive old new_task/new_task-run tasks
rollback;
begin;
insert into dbversion (version, release, description)
values (33, now(), 'Work In Progress');
-- Schema
create table new_task (
id bigserial, -- primary key,
type text, -- not null references task_type(type),
arguments jsonb not null,
next_run timestamptz not null,
current_interval interval,
status task_status not null,
policy task_policy not null default 'recurring',
retries_left bigint not null default 0,
priority task_priority -- references priority_ratio(id)
-- check (policy <> 'recurring' or current_interval is not null)
);
comment on table new_task is 'Schedule of recurring tasks';
comment on column new_task.arguments is 'Arguments passed to the underlying job scheduler. '
'Contains two keys, ''args'' (list) and ''kwargs'' (object).';
comment on column new_task.next_run is 'The next run of this new_task should be run on or after that time';
comment on column new_task.current_interval is 'The interval between two runs of this new_task, '
'taking into account the backoff factor';
comment on column new_task.policy is 'Whether the new_task is one-shot or recurring';
comment on column new_task.retries_left is 'The number of "short delay" retries of the new_task in case of '
'transient failure';
comment on column new_task.priority is 'Policy of the given new_task';
comment on column new_task.id is 'Task Identifier';
comment on column new_task.type is 'References task_type table';
comment on column new_task.status is 'Task status (''next_run_not_scheduled'', ''next_run_scheduled'', ''completed'', ''disabled'')';
create table new_task_run (
id bigserial, -- primary key,
task bigint, -- not null references task(id),
backend_id text,
scheduled timestamptz,
started timestamptz,
ended timestamptz,
metadata jsonb,
status task_run_status -- not null default 'scheduled'
);
comment on table new_task_run is 'History of new_task runs sent to the job-running backend';
comment on column new_task_run.backend_id is 'id of the new_task run in the job-running backend';
comment on column new_task_run.metadata is 'Useful metadata for the given new_task run. '
'For instance, the worker that took on the job, '
'or the logs for the run.';
comment on column new_task_run.id is 'Task run identifier';
comment on column new_task_run.task is 'References new_task table';
comment on column new_task_run.scheduled is 'Scheduled run time for new_task';
comment on column new_task_run.started is 'Task starting time';
comment on column new_task_run.ended is 'Task ending time';
--------------------------
-- Actual migration script
--------------------------
-- Keep only the necessary recurring task runs (from tasks lister & load-nixguix)
COPY new_task_run(id, task, backend_id, scheduled, started, ended, metadata, status)
(insert into new_task_run
select id, task, backend_id, scheduled, started, ended, metadata, status
from task_run where task in (
select id from task
where (type = 'load-nixguix' and policy='recurring') or type like 'list-%'
)
);
-- Keep only the latest oneshot tasks (2 months old)
COPY new_task_run(id, task, backend_id, scheduled, started, ended, metadata, status)
(
select id, task, backend_id, scheduled, started, ended, metadata, statusdistinct id from task
from task_run where task in (
select id from taskpolicy='recurring' and (type = 'load-nixguix' or type like 'list-%' or type like 'index-%')
where policy = 'oneshot') or (
policy = 'oneshot' and next_run > now() - interval '2 months'
)
);
COPYinsert into new_task(id, type, arguments, next_run, current_interval, status, policy, retries_left, priority)
(
seleselect distinct id, type, arguments, next_run, current_interval, status, policy, retries_left, priority
from task
where where (policy='recurring' and (type = 'load-nixguix' and policy='recurringor type like 'list-%' or type like 'list-%'index-%'))
);
COPY new_task(id, type, arguments, or (policy = 'oneshot' and next_run, current_ > now() - interval, status, policy, retries_left, priority '2 months')
(;
-- -----------
select id, type, arguments, next_run, current_interval, status, policy, retries_left, priority-- -- renaming
from task-- -----------
-- -- Rename current tables to archive_ prefixed names tables
where policy='oneshot' and next_run > now() - interval '2 months'alter table task rename to archive_task;
);alter table task_run rename to archive_task_run;
--------------- -- Rename new tables to standard tables
-- pk and fksalter table new_task rename to task;
-------------alter table new_task_run rename to task_run;
alter table task-- -- -------------
-- -- -- pk and fks
add primary key id;-- -- -------------
alter table task
alter column type set not null;
alter table task
add constraint task_type_fk
foreign key (type) references task_type (type);
alter table task
add constraint task_priority_fk
foreign key (task_priority) references priority_ratio (id);
alter table task
add constraint task_check_policy (type)
check (policy <> 'recurring' or current_interval is not null)
not valid;
alter table task
validate constraint task_check_policy;
alter table task_run
add primary key id;
aalter tablecolumn task_run
alter column set task set not null,
add constraint task_id_fk
foreign key (task) references task (id);
alter table task_run
alter column status set not null,
alter column status set default 'scheduled';
--------
-- index
--------
create index on task(type);
create index on task(next_run);
create index on task using btree(type, md5(arguments::text));
create index on task(priority);
create index on task_run(task);
create index on task_run(backend_id);
create index task_run_id_asc_idx on task_run(task asc, started asc);
create index on task(type, next_run)
where status = 'next_run_not_scheduled'::task_status;
-- Rename current tables to archive_ prefixed names tables
alter table task rename to archive_task;
alter table task_run rename to archive_task_run;
-- Rename new tables to standard tables
alter table new_task rename to task;
alter table new_task_run rename to task_run;