CYPEX is in charge of handling everything from rapid prototyping to full application development.

When building a full application, it can become necessary to schedule jobs. CYPEX offers the means to make that happen using pg_timetable, a job scheduler developed by CYBERTEC. It’s able to handle all kinds of job execution tasks.

Let’s take a look at a sample use case:

• When a contract is entered, somebody else should be notified
• If there is no response, try again in two weeks

The way to integrate job scheduling with CYPEX is by using standard SQL tables. In pg_timetable, every job is stored in tables. By writing database-side code, you have a transactional way of scheduling jobs.

Before you explore further, you need to get familiar with the basic architecture of pg_timetable:

All configuration data is stored in tables, which allows you to model fairly complex operations:

Note that shell operations are only possible when running CYPEX outside of a cloud context. When starting pg_timetable, you can set a switch to control this behavior.

If you want to learn more about pg_timetable, please see the official pg_timetable documentation to get more information about the basic processes.

Notifications and job scheduling often go hand-in-hand. In CYPEX all notifications are stored in tables. What we want to achieve are:

• Full transactional semantics
• Being able to have everything in one backup
• Easy integration.

The data structure looks as follows:

To send a notification to an end user, all you have to do is to call a server-side function:

CREATE FUNCTION cypex.create_notification (
  recipient int8,
  message text,
  level text DEFAULT 'info',
  target text DEFAULT 'gui'
)
RETURNS void
AS $$
BEGIN
  INSERT INTO cypex.t_notification(recipient, message, level, target)
  	VALUES (recipient, message, level, target);
END;
$$ LANGUAGE plpgsql;

The notification will be sent to the notification table and then displayed in the graphical user interface:

When the message is selected it will be marked as “read”. However, you can easily mark it unread using SQL queries (= UPDATE statement).

pg_timetable is an Open Source job scheduler for PostgreSQL. It’s fully transactional, offers the ability to handle complex tasks and can be fully configured using standard database tables. pg_timetable is a core component of CYPEX - all configuration tables are automatically pre-installed and are therefore ready-to-use.

Why is pg_timetable part of CYPEX in the first place?

The reason is that CYPEX needs scheduling capabilities to handle various important things such as but not limited to:

• Asynchronous execution
• Notifications
• Sending emails
• Job scheduling

Let’s discuss those tasks in more detail:

Often users want to run long operations. Just imagine some data pre-aggregation which might take 20 minutes to complete. The problem is: If you have a button in a CYPEX UI you’ll face timeouts and many other usability-related issues which can cause inconvenience. The solution to the problem is asynchronous execution.

How can you do that? pg_timetable has a feature which allows for the execution of “self-destructing chains”. This type of chain is executed only once and is then removed by the system. In case the execution is interrupted, pg_timetable will try again. All you have to do to run a chain asynchronously (single execution) is to write a server-side function which schedules a pg_timetable job. Your GUI will then simply call this quick function and wait for pg_timetable to handle things asynchronously.

Your server side function can do whatever is needed. It can schedule the task to execute what your business logic requires, send an email or issue a notification when the task is complete.

On the GUI side all you need is a button, a state change or some other operation capable of scheduling a job.

The following example contains a simple method to create a self-destructing chain:

CREATE OR REPLACE FUNCTION raise_func(text)
  RETURNS void LANGUAGE plpgsql AS
$BODY$
BEGIN
   RAISE NOTICE '%', $1;
END;
$BODY$;

SELECT timetable.add_job(
    job_name          => 'notify then destruct',
    job_schedule      => '* * * * *',
    job_command       => 'SELECT raise_func($1)',
    job_parameters    => '[ "Ahoj from self destruct task" ]'::jsonb,
    job_kind          => 'SQL'::timetable.command_kind,
    job_live          => TRUE,
    job_self_destruct => TRUE
) as chain_id;

This leads us directly to the next important topic: notifications. As you have already seen all your chain has to do is to send a simple INSERT:

INSERT INTO cypex.t_notification(recipient, message, level, target)
  	VALUES (recipient, message, level, target);

This is enough to send a notification. Note that in PostgreSQL all notifications are fully transactional. For all practical purposes, this means that the notification is issued on COMMIT to ensure that the message isn’t seen too early and to avoid race conditions.

Sending email is of great importance. pg_timetable and consequently CYPEX offer this vital capability.

The following example shows how such a job can be scheduled by server-side code:

DO $$
	-- An example for using the SendMail task.
DECLARE
	v_mail_task_id bigint;
	v_log_task_id bigint;
	v_chain_id bigint;
BEGIN
	-- Get the chain id
	INSERT INTO timetable.chain (chain_name, max_instances, live) VALUES ('Send Mail', 1, TRUE)
	RETURNING chain_id INTO v_chain_id;

	-- Add SendMail task
	INSERT INTO timetable.task (chain_id, task_order, kind, command)
	SELECT v_chain_id, 10, 'BUILTIN', 'SendMail'
	RETURNING task_id INTO v_mail_task_id;

	-- Create the parameters for the SensMail task
		-- "username":	      The username used for authenticating on the mail server
		-- "password":        The password used for authenticating on the mail server
		-- "serverhost":      The IP address or hostname of the mail server
		-- "serverport":      The port of the mail server
		-- "senderaddr":      The email that will appear as the sender
		-- "ccaddr":	      String array of the recipients(Cc) email addresses
		-- "bccaddr":	      String array of the recipients(Bcc) email addresses
		-- "toaddr":          String array of the recipients(To) email addresses
		-- "subject":	      Subject of the email
		-- "attachment":      String array of the attachments (local file)
		-- "attachmentdata":  Pairs of name and base64-encoded content
		-- "msgbody":	      The body of the email

	INSERT INTO timetable.parameter (task_id, order_id, value)
		VALUES (v_mail_task_id, 1, '{
				"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":   ["D:\\Go stuff\\Books\\Concurrency in Go.pdf","D:\\Go stuff\\Books\\The Way To Go.pdf"],
                "attachmentdata": [{"name": "File.txt", "base64data": "RmlsZSBDb250ZW50"}],
				"msgbody":		"<b>Hello User,</b> <p>I got some Go books for you enjoy</p> <i>pg_timetable</i>!"
				}'::jsonb);


	-- Add Log task and make it the last task using `task_order` column (=30)
	INSERT INTO timetable.task (chain_id, task_order, kind, command)
	SELECT v_chain_id, 30, 'BUILTIN', 'Log'
	RETURNING task_id INTO v_log_task_id;

	-- Add housekeeping task, that will delete sent mail and update parameter for the
-- previous logging task
	-- Since we're using a special add_task() function we don't need to specify the `chain_id`.
	-- Function will take the same `chain_id` from the parent task, SendMail in this particular case
	PERFORM timetable.add_task(
		kind => 'SQL',
		parent_id => v_mail_task_id,
		command => format(
$query$ WITH sent_mail(toaddr) AS (DELETE FROM timetable.parameter WHERE task_id = %s
RETURNING value->>'username')
INSERT INTO timetable.parameter (task_id, order_id, value)
SELECT %s, 1, to_jsonb('Sent emails to: ' || string_agg(sent_mail.toaddr, ';'))
FROM sent_mail
ON CONFLICT (task_id, order_id) DO UPDATE SET value = EXCLUDED.value$query$,
				v_mail_task_id, v_log_task_id
			),
		order_delta => 10
	);

-- In the end we should have something like this. Note, that even Log task was created earlier
-- it will be executed later
-- due to the `task_order` column.

-- timetable=> SELECT task_id, chain_id, kind, left(command, 50) FROM timetable.task ORDER BY task_order;
--  task_id | chain_id | task_order |  kind   |                             left
-- ---------+----------+------------+---------+---------------------------------------------------------------
--       45 |       24 |         10 | BUILTIN | SendMail
--       47 |       24 |         20 | SQL     | WITH sent_mail(toaddr) AS (DELETE FROM timetable.p
--       46 |       24 |         30 | BUILTIN | Log
-- (3 rows)

END;
$$
LANGUAGE PLPGSQL;

You can schedule normal jobs which are to be executed repeatedly or at a given point in time. We recommend checking out the pg_timetable documentation to learn more about job scheduling.