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;