scheduler.sql

create or replace function scheduler.run_tasks() returns void as $$
    declare
        _function_name     varchar;
        _sqlstate          text;
        _message           text;
        _exception_detail  text;
        _exception_hint    text;
        _exception_context text;
        _run_tasks_counter bigint;
        _start_time        timestamp with time zone;
    begin

        _run_tasks_counter = nextval('scheduler.run_tasks_counter');

        FOR _function_name IN
            SELECT
                function_name
            FROM scheduler.tasks
            WHERE last_run < CURRENT_TIMESTAMP - run_interval
            ORDER BY POSITION (task_operation::varchar IN 'aggregate,snapshot,analyze,purge') 
        LOOP

            BEGIN

                _start_time = clock_timestamp();

                UPDATE scheduler.tasks SET last_run = CURRENT_TIMESTAMP WHERE function_name = _function_name;

                EXECUTE 'SELECT ' || _function_name || '()';

                INSERT INTO scheduler.tasks_stats (
                    function_name,
                    execution_time,
                    run_tasks_counter,
                    run_at
                ) VALUES (
                    _function_name,
                    extract(milliseconds from  clock_timestamp()) - extract(milliseconds from  _start_time),
                    _run_tasks_counter,
                    _start_time
                );

            EXCEPTION
                WHEN OTHERS THEN
                    GET STACKED DIAGNOSTICS
                        _sqlstate          = RETURNED_SQLSTATE,
                        _message           = MESSAGE_TEXT,
                        _exception_detail  = PG_EXCEPTION_DETAIL,
                        _exception_hint    = PG_EXCEPTION_HINT,
                        _exception_context = PG_EXCEPTION_CONTEXT;
                    RAISE WARNING 'scheduler.run(): function "%" failed:
                        state  : %
                        message: %
                        detail : %
                        hint   : %
                        context: %',
                        _function_name,
                        _sqlstate,
                        _message,
                        _exception_detail,
                        _exception_hint,
                        _exception_context;
            END;
        END LOOP;

    end;
$$ language plpgsql;