Asynchronous Chain Execution: PG Timetable

Article

Intro

Not long ago, I wrote an article about the brand-new pg_timetable 3 major release. Three key characteristics were emphasized:

• implement new session locking;
• use of a new jackc/pgx Golang library;
• execution of an exclusive chain.

Two minor updates have since been made, bringing the version up to v3.2. The most important thing is the great and brand-new feature of asynchronous control over job execution.

• You can begin tasks manually,
• Jobs can be manually terminated.

This equipment creates a variety of opportunities, including

• launching several chains simultaneously,
• creating tasks that mimic conditional logic,
• debugging jobs,
• halting frozen or long-running tasks, etc.

experimental setting

Please note that I’ll be using two consoles for this tutorial. Pg_timetable comes first, followed by psql. Use whichever SQL client you like best.

Let’s try to set up with a clean database named timetable, running on a local host. In the first console, type pg_timetable:

$ pg_timetable --clientname=loader postgresql://scheduler@localhost/timetable [ 2021-01-25 11:38:29.780 | LOG ]: Connection established... [ 2021-01-25 11:38:29.826 | LOG ]: Proceeding as 'loader' with client PID 11560 [ 2021-01-25 11:38:29.827 | LOG ]: Executing script: DDL [ 2021-01-25 11:38:29.956 | LOG ]: Schema file executed: DDL [ 2021-01-25 11:38:29.956 | LOG ]: Executing script: JSON Schema [ 2021-01-25 11:38:29.962 | LOG ]: Schema file executed: JSON Schema [ 2021-01-25 11:38:29.962 | LOG ]: Executing script: Built-in Tasks [ 2021-01-25 11:38:29.964 | LOG ]: Schema file executed: Built-in Tasks [ 2021-01-25 11:38:29.964 | LOG ]: Executing script: Job Functions [ 2021-01-25 11:38:29.969 | LOG ]: Schema file executed: Job Functions [ 2021-01-25 11:38:29.969 | LOG ]: Configuration schema created... [ 2021-01-25 11:38:29.971 | LOG ]: Accepting asynchronous chains execution requests... [ 2021-01-25 11:38:29.972 | LOG ]: Checking for @reboot task chains... [ 2021-01-25 11:38:29.973 | LOG ]: Number of chains to be executed: 0 [ 2021-01-25 11:38:29.974 | LOG ]: Checking for task chains... [ 2021-01-25 11:38:29.974 | LOG ]: Checking for interval task chains... [ 2021-01-25 11:38:29.976 | LOG ]: Number of chains to be executed: 0 [ 2021-01-25 11:38:30.025 | LOG ]: Number of active interval chains: 0 ...

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

 $ psql postgresql://scheduler@localhost/timetable psql (13.0) Type "help" for help. timetable=> \i samples/basic.sql INSERT 0 1

The output listed below will appear in the pg_timetable console in about a minute:

... [ 2021-01-25 11:41:30.068 | LOG ]: Number of chains to be executed: 1 [ 2021-01-25 11:41:30.075 | LOG ]: Starting chain ID: 1; configuration ID: 1 [ 2021-01-25 11:41:30.093 | LOG ]: Executed successfully chain ID: 1; configuration ID: 1 ...

That means that the configuration is working and that our chain is in the system. Let us investigate. Any other sql client, or the psql console:

timetable=> SELECT * FROM timetable.chain_execution_config; -[ RECORD 1 ]--------------+-------------------- chain_execution_config | 1 chain_id | 1 chain_name | notify every minute run_at | * * * * * max_instances | 1 live | t self_destruct | f exclusive_execution | f excluded_execution_configs | client_name |

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).

For the sake of the experiment, let’s now disable this chain. When using the psql console (or another sql client),

timetable=> UPDATE timetable.chain_execution_config SET live = FALSE WHERE chain_execution_config = 1; UPDATE 1

Immediately after that, the pg_timetable console will display the lines below:

... [ 2021-01-25 11:53:30.183 | LOG ]: Checking for task chains... [ 2021-01-25 11:53:30.189 | LOG ]: Checking for interval task chains... [ 2021-01-25 11:53:30.190 | LOG ]: Number of chains to be executed: 0 [ 2021-01-25 11:53:30.191 | LOG ]: Number of active interval chains: 0 ...

This indicates that neither regularly scheduled chains nor chains with interval functionality exist.

manually starting a chain

One should use the newly introduced function to begin a chain.

 timetable.notify_chain_start(chain_config_id BIGINT, worker_name TEXT)

Here, there are two critical instances: Even though we are starting a chain, we still need to add a chain configuration entry. This is because it’s possible that each task in a chain will have different configurations and arguments.

Any other sql client, or the psql console:

 timetable=> SELECT timetable.notify_chain_start(1, 'loader');

The output will appear in the pg_timetable console in less than a minute (since the main loop lasts one minute) as follows:

... [ 2021-01-25 11:57:30.228 | LOG ]: Adding asynchronous chain to working queue: {1 START 1611572244} [ 2021-01-25 11:57:30.230 | LOG ]: Starting chain ID: 1; configuration ID: 1 [ 2021-01-25 11:57:30.305 | LOG ]: Executed successfully chain ID: 1; configuration ID: 1 ...

Hooray! It’s working!!!!

Attention!

1. Any chain configuration, even one that is disabled, can be started!
2. But it runs only once, even if the run_at field states it should be invoked each minute!
3. The client name in charge of the execution must be specified!

For the sake of simplicity and performance, the function doesn’t check to see if there is an active session with a client name matching that one. You can, however, easily verify this:

timetable=> SELECT * FROM timetable.active_session; client_pid | client_name | server_pid ------------+-------------+------------ 11560 | loader | 10448 11560 | loader | 15920 11560 | loader | 13816 (3 rows)

Therefore, one could use the following example to begin chain execution on each connected client:

timetable=> SELECT DISTINCT ON (client_name) timetable.notify_chain_start(1, client_name) FROM timetable.active_session;

Pay attention, we used DISTINCT to avoid duplicated execution for the same client session. As I said in the last post, one pg_timetable session usually has more than one open database connection.

manually stopping a chain

Use the newly introduced function to stop a chain.

timetable.notify_chain_stop(chain_config_id BIGINT, worker_name TEXT)

We must specify a chain configuration entry as well as the pg_timetable client name that is affected.

Let’s adjust our chain a little bit for the experiment. Any other sql client, or the psql console:

timetable=> SELECT * FROM timetable.task_chain WHERE chain_id = 1; chain_id | parent_id | task_id | run_uid | database_connection | ignore_error | autonomous ----------+-----------+---------+---------+---------------------+--------------+------------ 1 | | 6 | | | t | f (1 row) timetable=> SELECT * FROM timetable.base_task WHERE task_id = 6; task_id | name | kind | script ---------+-----------------------------+------+-------------------------- 6 | notify channel with payload | SQL | SELECT pg_notify($1, $2) (1 row) timetable=> UPDATE timetable.base_task SET script = $$SELECT pg_sleep_for('5 minutes'), pg_notify($1, $2)$$ WHERE task_id = 6; UPDATE 1 timetable=> SELECT * FROM timetable.base_task WHERE task_id = 6; task_id | name | kind | script ---------+-----------------------------+------+---------------------------------- 6 | notify channel with payload | SQL | SELECT pg_sleep_for('5 minutes'), pg_notify($1, $2) (1 row)

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

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

• Simple SQL operation;
• it calls the pg_notify function with arguments provided by the timetable.chain_execution_parameters table.

The ER-schema of the timetable schema is displayed here.

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

Let’s try to begin the chain now. From psql:

timetable=> SELECT timetable.notify_chain_start(1, 'loader'); notify_chain_start -------------------- (1 row)

The chain will eventually start appearing in the pg_timetable console:

... [ 2021-01-25 12:32:30.885 | LOG ]: Adding asynchronous chain to working queue: {1 START 1611574297} [ 2021-01-25 12:32:30.887 | LOG ]: Starting chain ID: 1; configuration ID: 1 ...

As you can see, nothing about successful execution is mentioned. That indicates that our chain is still being built. Let’s halt this:

timetable=> SELECT timetable.notify_chain_stop(1, 'loader'); notify_chain_start -------------------- (1 row)

And this is what we see in the pg_timetable log:

... [ 2021-01-25 12:36:31.108 | LOG ]: Adding asynchronous chain to working queue: {1 STOP 1611574582} [ 2021-01-25 12:36:31.108 | ERROR ]: Exec {"args":["TT_CHANNEL","Ahoj from SQL base task"],"err":{"Op":"read","Net":"tcp","Source":{"IP":"::1","Port":61437,"Zone":""},"Addr":{"IP":"::1","Port":5432,"Zone":""},"Err":{}},"pid":19824,"sql":"SELECT pg_sleep_for('5 minutes'), pg_notify($1, $2)"} [ 2021-01-25 12:36:31.109 | ERROR ]: Exec {"args":[],"err":{},"pid":19824,"sql":"rollback"} [ 2021-01-25 12:36:31.114 | ERROR ]: Task execution failed: {"ChainConfig":1,"ChainID":1,"TaskID":6,"TaskName":"notify channel with payload","Script":"SELECT pg_sleep_for('5 minutes'), pg_notify($1, $2)","Kind":"SQL","RunUID":{"String":"","Valid":false},"IgnoreError":true,"Autonomous":false,"DatabaseConnection":{"String":"","Valid":false},"ConnectString":{"String":"","Valid":false},"StartedAt":"2021-01-25T12:32:30.9494341+01:00","Duration":240160068}; Error: read tcp [::1]:61437->[::1]:5432: i/o timeout [ 2021-01-25 12:36:31.115 | LOG ]: Executed successfully chain ID: 1; configuration ID: 1 ...

You might be thinking, “What a strange message.” The text “ERROR: Task execution failed…” appears in one line. The last line of the message reads, “LOG: Executed successfully…” What’s even possible about that?

This can be explained easily. Errors are tolerant by chains. For that, there is a chain property. And indeed, that is the situation! The chain is regarded as successful even if some of the links were unsuccessful.

Finally

This was the third post in a series about the new things in pg_timetable v3. Watch for the following elements to be highlighted:

• Debug mode for developing!

About Enteros

​​​​Enteros offers a patented database performance management SaaS platform. It finds the root causes of complex database scalability and performance problems that affect business across a growing number of cloud, RDBMS, NoSQL, and machine learning database platforms.