Viewing & Altering Schedule

[eangius]

Thanks for sharing this tool.

Over time we created a bunch of test jobs that are no longer valid that we'd like to discover & remove from the schedule.
Is there a way to list (all or a queried subset) of the scheduled jobs through the pg_timetable cli or similar? Where does the schedule live: in the target database or wherever pg_timetable client runs from? & would these jobs only be trashable via specific SELECT timetable.delete_job('my_job_name') sql commands or is there a bulk clear mode?

[pashagolub]

Hello, and thanks for your kind words!

Yes, there is a way. pg_timetable is entirely database-driven. That means all settings are stored in the database client is connecting to. Target databases (aka remote) are not affected.
To list all chains, you can examine the timetable.chain table, e.g.

timetable=> TABLE timetable.chain;
 chain_id |            chain_name            |  run_at   | max_instances | timeout | live | self_destruct | exclusive_execution | client_name
----------+----------------------------------+-----------+---------------+---------+------+---------------+---------------------+-------------
        1 | Download locations and aggregate |           |               |       0 | t    | f             | f                   |
        2 | notify every minute              | * * * * * |             1 |       0 | t    | f             | f                   |
        3 | many tasks                       | * * * * * |             1 |       0 | t    | f             | f                   |
(3 rows)

Then you may use whatever SQL to delete the chains, e.g.

timetable=> DELETE FROM timetable.chain WHERE chain_id < 3;
DELETE 2

Or you may use UPDATE to mark them as disabled, e.g.

timetable=> UPDATE timetable.chain SET live = FALSE WHERE client_name = 'foo';

Or you may even clear the whole table at once:

timetable=> TRUNCATE timetable.chain CASCADE;
NOTICE:  truncate cascades to table "task"
NOTICE:  truncate cascades to table "parameter"
TRUNCATE TABLE

The timetable.delete_job() is just a handy wrapper for a SQL:

CREATE OR REPLACE FUNCTION timetable.delete_job(IN job_name TEXT) RETURNS boolean AS $$
    WITH del_chain AS (DELETE FROM timetable.chain WHERE chain.chain_name = $1 RETURNING chain_id)
    SELECT EXISTS(SELECT 1 FROM del_chain)
$$ LANGUAGE SQL;

[eangius]

Perfect level of detail. Thanks