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

Database Schema

ER-Diagram