4. Components

The scheduling in pg_timetable encompasses three different abstraction levels to facilitate the reuse with other parameters or additional schedules.

Command:

The base level, command, defines what to do.

Task:

The second level, task, represents a chain element (step) to run one of the commands. With tasks we define order of commands, arguments passed (if any), and how errors are handled.

Chain:

The third level represents a connected tasks forming a chain of tasks. Chain defines if, when, and how often a job should be executed.

4.1. Command

Currently, there are three different kinds of commands:

SQL

SQL snippet. Starting a cleanup, refreshing a materialized view or processing data.

PROGRAM

External Command. Anything that can be called as an external binary, including shells, e.g. bash, pwsh, etc. The external command will be called using golang’s exec.CommandContext.

BUILTIN

Internal Command. A prebuilt functionality included in pg_timetable. These include:

  • NoOp,

  • Sleep,

  • Log,

  • SendMail,

  • Download,

  • CopyFromFile,

  • CopyToFile,

  • Shutdown.

4.2. Task

The next building block is a task, which simply represents a step in a list of chain commands. An example of tasks combined in a chain would be:

  1. Download files from a server

  2. Import files

  3. Run aggregations

  4. Build report

  5. Remove the files from disk

Note

All tasks of the chain in pg_timetable are executed within one transaction. However, please, pay attention there is no opportunity to rollback PROGRAM and BUILTIN tasks.

4.2.1. Table timetable.task

chain_id bigint

Link to the chain, if NULL task considered to be disabled

task_order DOUBLE PRECISION

Indicates the order of task within a chain.

kind timetable.command_kind

The type of the command. Can be SQL (default), PROGRAM or BUILTIN.

command text

Contains either a SQL command, a path to application or name of the BUILTIN command which will be executed.

run_as text

The role as which the task should be executed as.

database_connection text

The connection string for the external database that should be used.

ignore_error boolean

Specify if the next task should proceed after encountering an error (default: false).

autonomous boolean

Specify if the task should be executed out of the chain transaction. Useful for VACUUM, CREATE DATABASE, CALL etc.

timeout integer

Abort any task within a chain that takes more than the specified number of milliseconds.

Warning

If the task has been configured with ignore_error set to true (the default value is false), the worker process will report a success on execution even if the task within the chain fails.

As mentioned above, commands are simple skeletons (e.g. send email, vacuum, etc.). In most cases, they have to be brought to live by passing input parameters to the execution.

4.2.2. Table timetable.parameter

task_id bigint

The ID of the task.

order_id integer

The order of the parameter. Several parameters are processed one by one according to the order.

value jsonb

A JSON value containing the parameters.

4.2.3. Parameter value format

Depending on the command kind argument can be represented by different JSON values.

Kind
Schema

Example

SQL
array
'[ "one", 2, 3.14, false ]'::jsonb
PROGRAM
array of strings
'["-x", "Latin-ASCII", "-o", "orte_ansi.txt", "orte.txt"]'::jsonb
BUILTIN: Sleep
integer
'5' :: jsonb
BUILTIN: Log
any
'"WARNING"'::jsonb
'{"Status": "WARNING"}'::jsonb
BUILTIN: SendMail
object
'{
    "username":     "user@example.com",
    "password":     "password",
    "serverhost":   "smtp.example.com",
    "serverport":   587,
    "senderaddr":   "user@example.com",
    "ccaddr":       ["recipient_cc@example.com"],
    "bccaddr":      ["recipient_bcc@example.com"],
    "toaddr":       ["recipient@example.com"],
    "subject":      "pg_timetable - No Reply",
    "attachment":   ["/temp/attachments/Report.pdf","config.yaml"],
    "attachmentdata": [{"name": "File.txt", "base64data": "RmlsZSBDb250ZW50"}],
    "msgbody":      "<h2>Hello User,</h2> <p>check some attachments!</p>",
    "contenttype":   "text/html; charset=UTF-8"
}'::jsonb
BUILTIN: Download
object
'{
    "workersnum": 2,
    "fileurls": ["http://example.com/foo.gz", "https://example.com/bar.csv"],
    "destpath": "."
}'::jsonb
BUILTIN: CopyFromFile
object
'{
    "sql": "COPY location FROM STDIN",
    "filename": "download/orte_ansi.txt"
}'::jsonb
BUILTIN: CopyToFile
object
'{
    "sql": "COPY location TO STDOUT",
    "filename": "download/location.txt"
}'::jsonb
BUILTIN: Shutdown

value ignored

BUILTIN: NoOp

value ignored

4.3. Chain

Once tasks have been arranged, they have to be scheduled as a chain. For this, pg_timetable builds upon the enhanced cron-string, all the while adding multiple configuration options.

4.3.1. Table timetable.chain

chain_name text

The unique name of the chain.

run_at timetable.cron

Standard cron-style value at Postgres server time zone or @after, @every, @reboot clause.

max_instances integer

The amount of instances that this chain may have running at the same time.

timeout integer

Abort any chain that takes more than the specified number of milliseconds.

live boolean

Control if the chain may be executed once it reaches its schedule.

self_destruct boolean

Self destruct the chain after successful execution. Failed chains will be executed according to the schedule one more time.

exclusive_execution boolean

Specifies whether the chain should be executed exclusively while all other chains are paused.

client_name text

Specifies which client should execute the chain. Set this to NULL to allow any client.

Note

All chains in pg_timetable are scheduled at the PostgreSQL server time zone. You can change the timezone for the current session when adding new chains, e.g.

SET TIME ZONE 'UTC';

-- Run VACUUM at 00:05 every day in August UTC
SELECT timetable.add_job('execute-func', '5 0 * 8 *', 'VACUUM');