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
drop trigger update_task_on_task_end on archive_task_run;
DROP TRIGGER
Time: 0.366 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.364 ms
psql -e service=swh-scheduler-dev -c '\i sql/updates/33.sql' 0.01s user 0.00s system 0% cpu 17.302 total
```