pg_timetable: asynchronous chain execution

Intro - asynchronous chain execution in pg_timetable

I wrote about the new pg_timetable 3 major release not so long ago. Three essential features were highlighted:

• new session locking implementation;
• new jackc/pgx Golang library used;
• exclusive chain execution.

Meanwhile, two minor releases have come out, and the current version is v3.2. It focuses on the completely new and fantastic feature asynchronous control over job execution:

• you can start jobs manually,
• you can stop jobs manually.

This machinery opens many possibilities, e.g.,

• starting multiple chains simultaneously,
• emulating conditional logic inside tasks,
• jobs debugging,
• cancelling long-running and/or frozen tasks etc.

Experimental environment: tutorial in asynchronous chain execution using pg_timetable

🔔 Notice!: In this tutorial, I will use two consoles. First for pg_timetable and the second for psql. You may use whatever SQL client you prefer.

Let's try to set up with a clean database named timetable, running on a local host. Run pg_timetable in the first console:

Now with any sql client (I prefer psql), execute the content of the samples/basic.sql script:

In a minute or less, you will see to following output in the pg_timetable console:

That means our chain is present in the system and the configuration is active. Let's check what it is. From the psql console (or any other sql client):

We see our chain with chain_id = 1 has a single execution configuration. According to this configuration:

• The chain should be executed each minute (run_at = '* * * * *');
• There is only one instance of this chain allowed to be running (max_instances = 1);
• This configuration is enabled and active (live = TRUE);
• It's not supposed to delete itself after successful execution (self_destruct = FALSE);
• The chain can be executed in parallel with other jobs (exclusive_execution = FALSE);
• The chain can be executed by any client (client_name IS NULL).

Let's now disable this chain for the purpose of the experiment. From the psql console (or any other sql client):

Right after this, you will see the following lines in the pg_timetable console:

This means we have neither regularly scheduled chains nor interval enabled chains.

Starting a chain manually

To start a chain, one should use the newly introduced function

There are two crucial moments here: Despite the fact that we are starting a chain, we need to specify a chain configuration entry. This is because each chain may have several different configurations and different input arguments for each task in it.

From the psql console (or any other sql client):

In less than a minute (because the main loop is 1 minute) you will see the following output in the pg_timetable console:

Hooray! 🍾🎉 It's working!!!!

🔔 Attention!

1. We can start any chain configuration, even a disabled one!
2. But it runs only once, even if the run_at field states it should be invoked each minute!
3. You must specify the client name responsible for the execution!

For simplicity and performance reasons, the function doesn't check if there is an active session with such a client name active. You, however, can always easily check this:

So, for example, to start chain execution on each connected client, one may use this query:

Pay attention, we used DISTINCT to avoid duplicated execution for the same client session. As I described in the previous post, one pg_timetable session usually has several opened database connections.

Stopping a chain manually

To stop a chain, one should use the newly introduced function

We need to specify a chain configuration entry and pg_timetable client name affected.

For the purpose of the experiment, let's change our chain a little. From the psql console (or any other sql client):

First, we list all tasks in our chain with chain_id = 1.

Our chain consists only of one task with task_id = 6:

• it's a simple SQL task
• it calls the pg_notify function with arguments provided by the timetable.chain_execution_parameters table.

🔔 Notice! You can see the ER-schema of the timetable schema here.

So I added a pg_sleep_for() function call to the script to emulate the frozen or long-running process.

Now let's try to start the chain. From psql:

After some time we will see the chain started in the pg_timetable console:

As you can see, there is no information about successful execution. That means our chain is still in progress. Let's stop it:

And in the pg_timetable log we can see:

What a weird message, you might be thinking. One of the lines says: "ERROR: Task execution failed...". And the final line says: "LOG: Executed successfully...". How is that even possible?!

The explanation is simple. Chains can ignore errors. There is a chain property for that. And that's precisely the case! Even if some steps of the chain failed, it's considered successful.

Finally

This was the third in a series of posts dedicated to the new pg_timetable v3 features. Stay tuned for the components to be highlighted:

• Debug mode for developing!

The previous posts can be found here:

• new session locking implementation;
• new jackc/pgx Golang library used;
• exclusive chain execution.

Love! Peace! Stay safe! ❤