Permalink
Fetching contributors…
Cannot retrieve contributors at this time
116 lines (95 sloc) 6.57 KB
\set ON_ERROR_ROLLBACK 1
\set ON_ERROR_STOP true
-- TODO Add test at the end running check_job_status to show it not finding any of the configured jobs
SELECT set_config('search_path', 'jobmon, dblink, public', false);
SELECT plan(2);
-- Cleanup from any previous testing that didn't clean up after itself properly
DELETE FROM job_log WHERE job_name IN ('PG_JOBMON TEST GOOD JOB', 'PG_JOBMON TEST WARNING JOB', 'PG_JOBMON TEST BAD JOB', 'PG_JOBMON TEST CANCELED JOB', 'PG_JOBMON TEST SQL JOB', 'PG_JOBMON TEST JOB NEVER FINISHED', 'PG_JOBMON TEST NON-CONFIG BAD JOB', 'PG_JOBMON TEST RUNNING JOB', 'PG_JOBMON TEST BLOCKED JOB');
DELETE FROM job_check_config WHERE job_name IN ('PG_JOBMON TEST GOOD JOB', 'PG_JOBMON TEST WARNING JOB', 'PG_JOBMON TEST BAD JOB', 'PG_JOBMON TEST CANCELED JOB', 'PG_JOBMON TEST SQL JOB', 'PG_JOBMON TEST JOB NEVER RUN', 'PG_JOBMON TEST JOB NEVER FINISHED', 'PG_JOBMON TEST NON-CONFIG BAD JOB', 'PG_JOBMON TEST RUNNING JOB', 'PG_JOBMON TEST BLOCKED JOB');
DELETE FROm job_check_log WHERE job_name IN ('PG_JOBMON TEST GOOD JOB', 'PG_JOBMON TEST WARNING JOB', 'PG_JOBMON TEST BAD JOB', 'PG_JOBMON TEST CANCELED JOB', 'PG_JOBMON TEST SQL JOB', 'PG_JOBMON TEST JOB NEVER FINISHED', 'PG_JOBMON TEST NON-CONFIG BAD JOB');
DROP FUNCTION IF EXISTS jobmon_test_jobs_normal();
-- Setup jobs to monitor for
INSERT INTO job_check_config (job_name, warn_threshold, error_threshold, active, sensitivity) VALUES ('PG_JOBMON TEST JOB NEVER RUN', '1 day', '2 days', true, 0);
INSERT INTO job_check_config (job_name, warn_threshold, error_threshold, active, sensitivity) VALUES ('PG_JOBMON TEST GOOD JOB', '30 seconds', '1 min', true, 0);
INSERT INTO job_check_config (job_name, warn_threshold, error_threshold, active, sensitivity) VALUES ('PG_JOBMON TEST BAD JOB', '1 day', '2 days', true, 0);
INSERT INTO job_check_config (job_name, warn_threshold, error_threshold, active, sensitivity) VALUES ('PG_JOBMON TEST WARNING JOB', '1 day', '2 days', true, 0);
INSERT INTO job_check_config (job_name, warn_threshold, error_threshold, active, sensitivity) VALUES ('PG_JOBMON TEST JOB NEVER FINISHED', '1 min', '10 mins', true, 1);
-- Setup for a job that was started but never had end_time set
INSERT INTO jobmon.job_log (owner, job_name, start_time, pid) VALUES ('keith', 'PG_JOBMON TEST JOB NEVER FINISHED', now() - '5 days'::interval, 1234);
CREATE FUNCTION jobmon_test_jobs_normal() RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
v_job_id bigint;
v_step_id bigint;
v_job_name text;
v_step_status boolean;
v_sql_job text;
BEGIN
EXECUTE 'CREATE TEMP TABLE tmp_test (col1 int, col2 text)';
EXECUTE 'INSERT INTO tmp_test VALUES (1, ''row1'')';
EXECUTE 'INSERT INTO tmp_test VALUES (2, ''row2'')';
v_job_name := 'PG_JOBMON TEST GOOD JOB';
SELECT INTO v_job_id jobmon.add_job(v_job_name);
SELECT INTO v_step_id jobmon.add_step(v_job_id, 'Test step 1');
PERFORM jobmon.update_step(v_step_id, 'OK', 'Successful Step 1');
v_step_status := jobmon.sql_step(v_job_id, 'Test step 2', 'UPDATE tmp_test SET col2 = ''changed''');
IF v_step_status = 'TRUE' THEN
RAISE NOTICE 'sql step succeeded as expected';
END IF;
SELECT INTO v_step_id jobmon.add_step(v_job_id, 'Test step 3');
PERFORM jobmon.update_step(v_step_id, 'OK', 'Successful Step 3');
PERFORM jobmon.close_job(v_job_id);
-- RAISE NOTICE 'Finished TEST GOOD JOB';
v_job_name := 'PG_JOBMON TEST WARNING JOB';
SELECT INTO v_job_id jobmon.add_job(v_job_name);
SELECT INTO v_step_id jobmon.add_step(v_job_id, 'Test step 1');
PERFORM jobmon.update_step(v_step_id, 'OK', 'Successful Step 1');
SELECT INTO v_step_id jobmon.add_step(v_job_id, 'Test step 2');
PERFORM jobmon.update_step(v_step_id, 'WARNING', 'Failed Step 2');
v_step_status := jobmon.sql_step(v_job_id, 'Test step 3', 'DELETE FROM tmp_test WHERE col3 = 0');
IF v_step_status = 'FALSE' THEN
RAISE NOTICE 'sql step failed as expected';
END IF;
PERFORM jobmon.fail_job(v_job_id, 2);
-- RAISE NOTICE 'Finished TEST WARNING JOB';
v_job_name := 'PG_JOBMON TEST BAD JOB';
SELECT INTO v_job_id jobmon.add_job(v_job_name);
SELECT INTO v_step_id jobmon.add_step(v_job_id, 'Test step 1');
PERFORM jobmon.update_step(v_step_id, 'OK', 'Successful Step 1');
SELECT INTO v_step_id jobmon.add_step(v_job_id, 'Test step 2');
PERFORM jobmon.update_step(v_step_id, 'CRITICAL', 'Failed Step 2');
v_step_status := jobmon.sql_step(v_job_id, 'Test step 3', 'DELETE FROM tmp_test WHERE col3 = 0');
IF v_step_status = 'FALSE' THEN
RAISE NOTICE 'sql step failed as expected';
END IF;
PERFORM jobmon.fail_job(v_job_id);
-- RAISE NOTICE 'Finished TEST BAD JOB';
v_sql_job := jobmon.sql_job('PG_JOBMON TEST SQL JOB', 'UPDATE tmp_test SET col2 = ''changed again''');
-- Cause 3 consecutive job failures for check_job_status() to catch
v_job_name := 'PG_JOBMON TEST NON-CONFIG BAD JOB';
SELECT INTO v_job_id jobmon.add_job(v_job_name);
SELECT INTO v_step_id jobmon.add_step(v_job_id, 'Test step 1');
PERFORM jobmon.update_step(v_step_id, 'CRITICAL', 'Testing repeated job failure');
PERFORM jobmon.fail_job(v_job_id);
v_job_name := 'PG_JOBMON TEST NON-CONFIG BAD JOB';
SELECT INTO v_job_id jobmon.add_job(v_job_name);
SELECT INTO v_step_id jobmon.add_step(v_job_id, 'Test step 1');
PERFORM jobmon.update_step(v_step_id, 'CRITICAL', 'Testing repeated job failure');
PERFORM jobmon.fail_job(v_job_id);
v_job_name := 'PG_JOBMON TEST NON-CONFIG BAD JOB';
SELECT INTO v_job_id jobmon.add_job(v_job_name);
SELECT INTO v_step_id jobmon.add_step(v_job_id, 'Test step 1');
PERFORM jobmon.update_step(v_step_id, 'CRITICAL', 'Testing repeated job failure');
PERFORM jobmon.fail_job(v_job_id);
END
$$;
SELECT results_eq('SELECT * FROM check_job_status()'
, $$VALUES(3,'MISSING','PG_JOBMON TEST BAD JOB','Has not completed a run since highest configured monitoring time period')
, (3,'MISSING','PG_JOBMON TEST GOOD JOB','Has not completed a run since highest configured monitoring time period')
, (3,'MISSING','PG_JOBMON TEST JOB NEVER FINISHED','Has not completed a run since highest configured monitoring time period')
, (3,'MISSING','PG_JOBMON TEST JOB NEVER RUN','Has not completed a run since highest configured monitoring time period')
, (3,'MISSING','PG_JOBMON TEST WARNING JOB','Has not completed a run since highest configured monitoring time period')$$
, 'Test for valid check_job_status() result when nothing has run yet');
SELECT pass('Cleanup and setup complete');
SELECT * FROM finish();