Introducing pg_timetable v5.6: delayed chains and error handling

We're excited to unveil pg_timetable v5.6, a significant release that takes your PostgreSQL job scheduling to a new level. In this release, we've introduced several key enhancements and features that empower you to manage and automate your database tasks precisely and flexibly.

Table of Contents

Start async chains with delay

One of the standout features in pg_timetable v5.6 is the addition of the chain delay start functionality. Now, you can asynchronously initiate chains of tasks with a time delay:

SELECT timetable.notify_chain_start(
        chain_id => 42, 
        worker_name => 'vacuumer',
        start_delay => '1 hour'::interval)

1

2

3

4

SELECT timetable.notify_chain_start(

chain_id => 42,

worker_name => 'vacuumer',

start_delay => '1 hour'::interval)

The code above means to send the "vacuumer" worker command to start the chain with ID=42 in 1 hour from this moment.

The most obvious situation when you want to use this functionality is the following. Imagine you have a high load on the system at the moment. And you have scheduled soon or even running jobs already. To manage resources efficiently by giving the system some breathing room, you can cancel some chains right now and execute them later. Just notify a worker about a delayed start without applying any changes to job settings.

Chain Error Handling

Another game-changing addition is the enhanced chain error handling. From now you can execute any arbitrary SQL command if the chain fails. Either you want to write a log entry about failure or restart the whole process; it's up to you now!

SELECT timetable.add_job(
  job_name            => 'fail',
  job_schedule        => '* * * * *',
  job_command         => 'SELECT 42/0',
  job_kind            => 'SQL'::timetable.command_kind,
  job_live            => TRUE,
  job_ignore_errors   => FALSE,
  job_on_error        => $$SELECT pg_notify('monitoring', 
    format('{'ChainID': %s, 'Executor': %s, 'Message': 'Something bad happened'}', 
    current_setting('pg_timetable.current_chain_id')::bigint,
    current_setting('pg_timetable.current_client_name')::text))$$
)

1

2

3

4

5

6

7

8

9

10

11

12

SELECT timetable.add_job(

job_name => 'fail',

job_schedule => '* * * * *',

job_command => 'SELECT 42/0',

job_kind => 'SQL'::timetable.command_kind,

job_live => TRUE,

job_ignore_errors => FALSE,

job_on_error => $$SELECT pg_notify('monitoring',

format('{'ChainID': %s, 'Executor': %s, 'Message': 'Something bad happened'}',

current_setting('pg_timetable.current_chain_id')::bigint,

current_setting('pg_timetable.current_client_name')::text))$$

)

Other Changes

But that's not all! In pg_timetable v5.6, we've included a range of other improvements and updates to enhance your experience:

Go Version Bump: We've bumped the Go version to v1.21, ensuring that pg_timetable stays current with the latest developments in the Go ecosystem.
Refactored Task Execution: We've refactored autonomous and remote task execution. This refinement ensures smoother and more efficient execution of your tasks, reducing the risk of interruptions and failures.
Bug Fix: We've addressed a rare bug by resetting the session before returning a connection to the pool. This fix ensures the stability and integrity of your parallel processes.