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        INTEGER 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

Database Schema

ER-Diagram