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

ER-Diagram