9. Database Schema
pg_timetable is a database driven application. During the first start the necessary schema is created if absent.
9.1. Main tables and objects
1CREATE TABLE timetable.chain (
2 chain_id BIGSERIAL PRIMARY KEY,
3 chain_name TEXT NOT NULL UNIQUE,
4 run_at timetable.cron,
5 max_instances INTEGER,
6 timeout INTEGER DEFAULT 0,
7 live BOOLEAN DEFAULT FALSE,
8 self_destruct BOOLEAN DEFAULT FALSE,
9 exclusive_execution BOOLEAN DEFAULT FALSE,
10 client_name TEXT,
11 on_error TEXT
12);
13
14COMMENT ON TABLE timetable.chain IS
15 'Stores information about chains schedule';
16COMMENT ON COLUMN timetable.chain.run_at IS
17 'Extended CRON-style time notation the chain has to be run at';
18COMMENT ON COLUMN timetable.chain.max_instances IS
19 'Number of instances (clients) this chain can run in parallel';
20COMMENT ON COLUMN timetable.chain.timeout IS
21 'Abort any chain that takes more than the specified number of milliseconds';
22COMMENT ON COLUMN timetable.chain.live IS
23 'Indication that the chain is ready to run, set to FALSE to pause execution';
24COMMENT ON COLUMN timetable.chain.self_destruct IS
25 'Indication that this chain will delete itself after successful run';
26COMMENT ON COLUMN timetable.chain.exclusive_execution IS
27 'All parallel chains should be paused while executing this chain';
28COMMENT ON COLUMN timetable.chain.client_name IS
29 'Only client with this name is allowed to run this chain, set to NULL to allow any client';
30
31CREATE TYPE timetable.command_kind AS ENUM ('SQL', 'PROGRAM', 'BUILTIN');
32
33CREATE TABLE timetable.task (
34 task_id BIGSERIAL PRIMARY KEY,
35 chain_id BIGINT REFERENCES timetable.chain(chain_id) ON UPDATE CASCADE ON DELETE CASCADE,
36 task_order DOUBLE PRECISION NOT NULL,
37 task_name TEXT,
38 kind timetable.command_kind NOT NULL DEFAULT 'SQL',
39 command TEXT NOT NULL,
40 run_as TEXT,
41 database_connection TEXT,
42 ignore_error BOOLEAN NOT NULL DEFAULT FALSE,
43 autonomous BOOLEAN NOT NULL DEFAULT FALSE,
44 timeout INTEGER DEFAULT 0
45);
46
47COMMENT ON TABLE timetable.task IS
48 'Holds information about chain elements aka tasks';
49COMMENT ON COLUMN timetable.task.chain_id IS
50 'Link to the chain, if NULL task considered to be disabled';
51COMMENT ON COLUMN timetable.task.task_order IS
52 'Indicates the order of task within a chain';
53COMMENT ON COLUMN timetable.task.run_as IS
54 'Role name to run task as. Uses SET ROLE for SQL commands';
55COMMENT ON COLUMN timetable.task.ignore_error IS
56 'Indicates whether a next task in a chain can be executed regardless of the success of the current one';
57COMMENT ON COLUMN timetable.task.kind IS
58 'Indicates whether "command" is SQL, built-in function or an external program';
59COMMENT ON COLUMN timetable.task.command IS
60 'Contains either an SQL command, or command string to be executed';
61COMMENT ON COLUMN timetable.task.timeout IS
62 'Abort any task within a chain that takes more than the specified number of milliseconds';
63
64-- parameter passing for a chain task
65CREATE TABLE timetable.parameter(
66 task_id BIGINT REFERENCES timetable.task(task_id)
67 ON UPDATE CASCADE ON DELETE CASCADE,
68 order_id INTEGER CHECK (order_id > 0),
69 value JSONB,
70 PRIMARY KEY (task_id, order_id)
71);
72
73COMMENT ON TABLE timetable.parameter IS
74 'Stores parameters passed as arguments to a chain task';
75
76CREATE UNLOGGED TABLE timetable.active_session(
77 client_pid BIGINT NOT NULL,
78 server_pid BIGINT NOT NULL,
79 client_name TEXT NOT NULL,
80 started_at TIMESTAMPTZ DEFAULT now()
81);
82
83COMMENT ON TABLE timetable.active_session IS
84 'Stores information about active sessions';
85
86CREATE TYPE timetable.log_type AS ENUM ('DEBUG', 'NOTICE', 'INFO', 'ERROR', 'PANIC', 'USER');
87
88CREATE OR REPLACE FUNCTION timetable.get_client_name(integer) RETURNS TEXT AS
89$$
90 SELECT client_name FROM timetable.active_session WHERE server_pid = $1 LIMIT 1
91$$
92LANGUAGE sql;
93
94CREATE TABLE timetable.log
95(
96 ts TIMESTAMPTZ DEFAULT now(),
97 pid INTEGER NOT NULL,
98 log_level timetable.log_type NOT NULL,
99 client_name TEXT DEFAULT timetable.get_client_name(pg_backend_pid()),
100 message TEXT,
101 message_data jsonb
102);
103
104COMMENT ON TABLE timetable.log IS
105 'Stores log entries of active sessions';
106
107CREATE TABLE timetable.execution_log (
108 chain_id BIGINT,
109 task_id BIGINT,
110 txid BIGINT NOT NULL,
111 last_run TIMESTAMPTZ DEFAULT now(),
112 finished TIMESTAMPTZ,
113 pid BIGINT,
114 returncode INTEGER,
115 kind timetable.command_kind,
116 command TEXT,
117 output TEXT,
118 client_name TEXT NOT NULL
119);
120
121COMMENT ON TABLE timetable.execution_log IS
122 'Stores log entries of executed tasks and chains';
123
124CREATE UNLOGGED TABLE timetable.active_chain(
125 chain_id BIGINT NOT NULL,
126 client_name TEXT NOT NULL,
127 started_at TIMESTAMPTZ DEFAULT now()
128);
129
130COMMENT ON TABLE timetable.active_chain IS
131 'Stores information about active chains within session';
132
133CREATE OR REPLACE FUNCTION timetable.try_lock_client_name(worker_pid BIGINT, worker_name TEXT)
134RETURNS bool AS
135$CODE$
136BEGIN
137 IF pg_is_in_recovery() THEN
138 RAISE NOTICE 'Cannot obtain lock on a replica. Please, use the primary node';
139 RETURN FALSE;
140 END IF;
141 -- remove disconnected sessions
142 DELETE
143 FROM timetable.active_session
144 WHERE server_pid NOT IN (
145 SELECT pid
146 FROM pg_catalog.pg_stat_activity
147 WHERE application_name = 'pg_timetable'
148 );
149 DELETE
150 FROM timetable.active_chain
151 WHERE client_name NOT IN (
152 SELECT client_name FROM timetable.active_session
153 );
154 -- check if there any active sessions with the client name but different client pid
155 PERFORM 1
156 FROM timetable.active_session s
157 WHERE
158 s.client_pid <> worker_pid
159 AND s.client_name = worker_name
160 LIMIT 1;
161 IF FOUND THEN
162 RAISE NOTICE 'Another client is already connected to server with name: %', worker_name;
163 RETURN FALSE;
164 END IF;
165 -- insert current session information
166 INSERT INTO timetable.active_session(client_pid, client_name, server_pid) VALUES (worker_pid, worker_name, pg_backend_pid());
167 RETURN TRUE;
168END;
169$CODE$
170STRICT
171LANGUAGE plpgsql;
172
9.4. ER-Diagram

ER-Diagram