|
| 1 | +#pgpro_scheduler internals |
| 2 | + |
| 3 | +Extention creates 3 tables in schema`schedule`. They are not accessable |
| 4 | +by public. |
| 5 | + |
| 6 | +**schedule.cron** - table contains records to be scheduled to porces job. |
| 7 | +Analog for crontab. |
| 8 | + |
| 9 | +CREATE TABLE schedule.cron( |
| 10 | +id SERIAL PRIMARY KEY, |
| 11 | +name text,-- name of job |
| 12 | +node text,-- name of node |
| 13 | +comments text,-- comments on job |
| 14 | +rule jsonb,-- json object with shedule, see description below |
| 15 | +next_time_statement text,-- sql statement to be executed to |
| 16 | +-- calculate next execution time |
| 17 | +do_sql text[],-- SQL statements to be executed |
| 18 | +same_transaction boolean DEFAULT false,-- if sequence in do_sql will be |
| 19 | +-- executed in one transaction |
| 20 | +onrollback_statement text,-- sql statement to be executed after ROLLBACK |
| 21 | +active boolean DEFAULT true,-- is job active |
| 22 | +broken boolean DEFAULT false,-- is job broken |
| 23 | +executor text,-- name of executor user |
| 24 | +owner text,-- neme of user who owns (created) job |
| 25 | +postpone interval,-- on what time execution could be delayed if there |
| 26 | +-- are no free session to execute it in time |
| 27 | +retry integer default 0,-- number of retrys if error |
| 28 | +max_run_time interval,-- how long job can be processed |
| 29 | +max_instances integer default 1,-- how much instances of the same |
| 30 | +-- job could be executed simultaneously |
| 31 | +start_date timestamp,-- begin of time period within job can |
| 32 | +-- be performed, can be NULL |
| 33 | +end_date timestamp,-- end of time period within job can |
| 34 | +-- be performed, can be NULL |
| 35 | +reason text-- text reason why job marked as broken |
| 36 | +); |
| 37 | + |
| 38 | +**schedule.at** - table stores nearest jobs to be executed or |
| 39 | +being executed at the moment. Each record contains information about |
| 40 | +time the job must begin, reference to cron table, time of last start allowed |
| 41 | +(if specified), time of actual start (if being performed), state - waiting |
| 42 | +execution or executing. |
| 43 | + |
| 44 | +CREATE TABLE schedule.at( |
| 45 | +start_at timestamp,-- time job will start |
| 46 | +last_start_available timestamp,-- time last start allowed |
| 47 | +retry integer, |
| 48 | +cron integer REFERENCES schedule.cron (id), -- cron table reference |
| 49 | +node text, |
| 50 | +started timestamp,-- time of actual start |
| 51 | +active boolean-- true - execution, false - waiting |
| 52 | +); |
| 53 | + |
| 54 | +**scedule.log** - table with job executed. When job has been performed |
| 55 | +it moved from**schedule.at** to this table, so tables has about the same |
| 56 | +structure except this table has information about result of execution. |
| 57 | + |
| 58 | +CREATE TABLE schedule.log( |
| 59 | +start_at timestamp,-- time at job were to be started |
| 60 | +last_start_available timestamp,-- time of last start available |
| 61 | +retry integer, |
| 62 | +cron integer,-- reference to cron table |
| 63 | +node text,-- reference to cron table node |
| 64 | +started timestamp,-- time job has been started |
| 65 | +finished timestamp,-- time job has been finished |
| 66 | +status boolean,-- true - success, false - failure |
| 67 | +message text-- error message |
| 68 | +); |
| 69 | + |