Batch Jobs / Jobs¶
Distribute execution time of a batch job evenly¶
Execute batch job nice2.dms.DeleteUnreferencedBinariesBatchJob
hourly at a random time but only between 06:00 and
21:59.
UPDATE nice_batch_job
SET
minutes = trunc(random()*60)::text,
hours = '6-21',
days_of_week = '*',
months = '*'
WHERE id = 'nice2.dms.DeleteUnreferencedBinariesBatchJob';
Jobs Grouped by Name and their Longest Duration¶
SELECT
name,
round(max(extract(epoch from end_date) - extract(epoch from start_date))) AS longest_duration,
round(avg(extract(epoch from end_date) - extract(epoch from start_date))) AS avg_duration,
max(start_date) AS last_execution
FROM nice_task_execution
WHERE start_date > now() - interval '10 days'
GROUP BY name;
Sort by duration:
sudo -u postgres n2sql-on-all-dbs -w --csv "$SQL" | sort -g -t , -k 3,3
List Jobs that Failed the Last Three Runs¶
WITH third AS (
SELECT
te.name,
(SELECT start_date FROM nice_task_execution WHERE te.name = name ORDER BY start_date DESC OFFSET 2 LIMIT 1) as start_date
FROM nice_task_execution as te
GROUP BY te.name
) SELECT te.name
FROM nice_task_execution AS te
LEFT OUTER JOIN nice_task_execution_status AS tes ON te.fk_task_execution_status = tes.pk
WHERE start_date >= (SELECT start_date FROM third WHERE name = te.name) and tes.unique_id = 'failed'
GROUP BY te.name
HAVING count(*) = 3
ORDER BY 1;