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 SCHEMA timetable;
  2
  3-- define migrations you need to apply
  4-- every change to this file should populate this table.
  5-- Version value should contain issue number zero padded followed by
  6-- short description of the issue\feature\bug implemented\resolved
  7CREATE TABLE timetable.migration(
  8    id INT8 NOT NULL,
  9    version TEXT NOT NULL,
 10    PRIMARY KEY (id)
 11);
 12
 13INSERT INTO
 14    timetable.migration (id, version)
 15VALUES
 16    (0, '00259 Restart migrations for v4'),
 17    (1, '00305 Fix timetable.is_cron_in_time'),
 18    (2, '00323 Append timetable.delete_job function'),
 19    (3, '00329 Migration required for some new added functions'),
 20    (4, '00334 Refactor timetable.task as plain schema without tree-like dependencies'),
 21    (5, '00381 Rewrite active chain handling'),
 22    (6, '00394 Add started_at column to active_session and active_chain tables'),
 23    (7, '00417 Rename LOG database log level to INFO'),
 24    (8, '00436 Add txid column to timetable.execution_log');
 25
 26CREATE DOMAIN timetable.cron AS TEXT CHECK(
 27    substr(VALUE, 1, 6) IN ('@every', '@after') AND (substr(VALUE, 7) :: INTERVAL) IS NOT NULL
 28    OR VALUE = '@reboot'
 29    OR VALUE ~ '^(((\d+,)+\d+|(\d+(\/|-)\d+)|(\*(\/|-)\d+)|\d+|\*) +){4}(((\d+,)+\d+|(\d+(\/|-)\d+)|(\*(\/|-)\d+)|\d+|\*) ?)$'
 30);
 31
 32COMMENT ON DOMAIN timetable.cron IS 'Extended CRON-style notation with support of interval values';
 33
 34CREATE TABLE timetable.chain (
 35    chain_id            BIGSERIAL   PRIMARY KEY,
 36    chain_name          TEXT        NOT NULL UNIQUE,
 37    run_at              timetable.cron,
 38    max_instances       INTEGER,
 39    timeout             INTEGER     DEFAULT 0,
 40    live                BOOLEAN     DEFAULT FALSE,
 41    self_destruct       BOOLEAN     DEFAULT FALSE,
 42    exclusive_execution BOOLEAN     DEFAULT FALSE,
 43    client_name         TEXT
 44);
 45
 46COMMENT ON TABLE timetable.chain IS
 47    'Stores information about chains schedule';
 48COMMENT ON COLUMN timetable.chain.run_at IS
 49    'Extended CRON-style time notation the chain has to be run at';
 50COMMENT ON COLUMN timetable.chain.max_instances IS
 51    'Number of instances (clients) this chain can run in parallel';
 52COMMENT ON COLUMN timetable.chain.timeout IS
 53    'Abort any chain that takes more than the specified number of milliseconds';
 54COMMENT ON COLUMN timetable.chain.live IS
 55    'Indication that the chain is ready to run, set to FALSE to pause execution';
 56COMMENT ON COLUMN timetable.chain.self_destruct IS
 57    'Indication that this chain will delete itself after successful run';
 58COMMENT ON COLUMN timetable.chain.exclusive_execution IS
 59    'All parallel chains should be paused while executing this chain';
 60COMMENT ON COLUMN timetable.chain.client_name IS
 61    'Only client with this name is allowed to run this chain, set to NULL to allow any client';    
 62
 63CREATE TYPE timetable.command_kind AS ENUM ('SQL', 'PROGRAM', 'BUILTIN');
 64
 65CREATE TABLE timetable.task (
 66    task_id             BIGSERIAL               PRIMARY KEY,
 67    chain_id            BIGINT                  REFERENCES timetable.chain(chain_id) ON UPDATE CASCADE ON DELETE CASCADE,
 68    task_order          DOUBLE PRECISION        NOT NULL,
 69    task_name           TEXT,
 70    kind                timetable.command_kind  NOT NULL DEFAULT 'SQL',
 71    command             TEXT                    NOT NULL,
 72    run_as              TEXT,
 73    database_connection TEXT,
 74    ignore_error        BOOLEAN                 NOT NULL DEFAULT FALSE,
 75    autonomous          BOOLEAN                 NOT NULL DEFAULT FALSE,
 76    timeout             INTEGER                 DEFAULT 0
 77);          
 78
 79COMMENT ON TABLE timetable.task IS
 80    'Holds information about chain elements aka tasks';
 81COMMENT ON COLUMN timetable.task.chain_id IS
 82    'Link to the chain, if NULL task considered to be disabled';
 83COMMENT ON COLUMN timetable.task.task_order IS
 84    'Indicates the order of task within a chain';    
 85COMMENT ON COLUMN timetable.task.run_as IS
 86    'Role name to run task as. Uses SET ROLE for SQL commands';
 87COMMENT ON COLUMN timetable.task.ignore_error IS
 88    'Indicates whether a next task in a chain can be executed regardless of the success of the current one';
 89COMMENT ON COLUMN timetable.task.kind IS
 90    'Indicates whether "command" is SQL, built-in function or an external program';
 91COMMENT ON COLUMN timetable.task.command IS
 92    'Contains either an SQL command, or command string to be executed';
 93COMMENT ON COLUMN timetable.task.timeout IS
 94    'Abort any task within a chain that takes more than the specified number of milliseconds';
 95
 96-- parameter passing for a chain task
 97CREATE TABLE timetable.parameter(
 98    task_id     BIGINT  REFERENCES timetable.task(task_id)
 99                        ON UPDATE CASCADE ON DELETE CASCADE,
100    order_id    INTEGER CHECK (order_id > 0),
101    value       JSONB,
102    PRIMARY KEY (task_id, order_id)
103);
104
105COMMENT ON TABLE timetable.parameter IS
106    'Stores parameters passed as arguments to a chain task';
107
108CREATE UNLOGGED TABLE timetable.active_session(
109    client_pid  BIGINT  NOT NULL,
110    client_name TEXT    NOT NULL,
111    server_pid  BIGINT  NOT NULL,
112    started_at  TIMESTAMPTZ DEFAULT now()
113);
114
115COMMENT ON TABLE timetable.active_session IS
116    'Stores information about active sessions';
117
118CREATE TYPE timetable.log_type AS ENUM ('DEBUG', 'NOTICE', 'INFO', 'ERROR', 'PANIC', 'USER');
119
120CREATE OR REPLACE FUNCTION timetable.get_client_name(integer) RETURNS TEXT AS
121$$
122    SELECT client_name FROM timetable.active_session WHERE server_pid = $1 LIMIT 1
123$$
124LANGUAGE sql;
125
126CREATE TABLE timetable.log
127(
128    ts              TIMESTAMPTZ         DEFAULT now(),
129    pid             INTEGER             NOT NULL,
130    log_level       timetable.log_type  NOT NULL,
131    client_name     TEXT                DEFAULT timetable.get_client_name(pg_backend_pid()),
132    message         TEXT,
133    message_data    jsonb
134);
135
136COMMENT ON TABLE timetable.log IS
137    'Stores log entries of active sessions';
138
139CREATE TABLE timetable.execution_log (
140    chain_id    BIGINT,
141    task_id     BIGINT,
142    txid        INTEGER NOT NULL,
143    last_run    TIMESTAMPTZ DEFAULT now(),
144    finished    TIMESTAMPTZ,
145    pid         BIGINT,
146    returncode  INTEGER,
147    kind        timetable.command_kind,
148    command     TEXT,
149    output      TEXT,
150    client_name TEXT        NOT NULL
151);
152
153COMMENT ON TABLE timetable.execution_log IS
154    'Stores log entries of executed tasks and chains';
155
156CREATE UNLOGGED TABLE timetable.active_chain(
157    chain_id    BIGINT  NOT NULL,
158    client_name TEXT    NOT NULL,
159    started_at  TIMESTAMPTZ DEFAULT now()
160);
161
162COMMENT ON TABLE timetable.active_chain IS
163    'Stores information about active chains within session';
164
165CREATE OR REPLACE FUNCTION timetable.try_lock_client_name(worker_pid BIGINT, worker_name TEXT)
166RETURNS bool AS
167$CODE$
168BEGIN
169    IF pg_is_in_recovery() THEN
170        RAISE NOTICE 'Cannot obtain lock on a replica. Please, use the primary node';
171        RETURN FALSE;
172    END IF;
173    -- remove disconnected sessions
174    DELETE
175        FROM timetable.active_session
176        WHERE server_pid NOT IN (
177            SELECT pid
178            FROM pg_catalog.pg_stat_activity
179            WHERE application_name = 'pg_timetable'
180        );
181    DELETE 
182        FROM timetable.active_chain 
183        WHERE client_name NOT IN (
184            SELECT client_name FROM timetable.active_session
185        );
186    -- check if there any active sessions with the client name but different client pid
187    PERFORM 1
188        FROM timetable.active_session s
189        WHERE
190            s.client_pid <> worker_pid
191            AND s.client_name = worker_name
192        LIMIT 1;
193    IF FOUND THEN
194        RAISE NOTICE 'Another client is already connected to server with name: %', worker_name;
195        RETURN FALSE;
196    END IF;
197    -- insert current session information
198    INSERT INTO timetable.active_session(client_pid, client_name, server_pid) VALUES (worker_pid, worker_name, pg_backend_pid());
199    RETURN TRUE;
200END;
201$CODE$
202STRICT
203LANGUAGE plpgsql;
204

9.4. ER-Diagram

Database Schema

ER-Diagram