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:
Download files from a server
Import files
Run aggregations
Build report
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 disabledtask_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');