PostgreSQL and Greenplum job scheduler pg_task allows to execute any sql command at any specific time at background asynchronously
first
shared_preload_libraries = 'pg_task' # add pg_task to shared_preload_libraries
second
INSERT INTO task (input) VALUES ('SELECT now()'); -- to run sql more quickly use only input
INSERT INTO task (plan, input) VALUES (now() + '5 min':INTERVAL, 'SELECT now()'); -- to run sql after 5 minutes point plan(ned time)
INSERT INTO task (plan, input) VALUES ('2029-07-01 12:51:00', 'SELECT now()'); -- to run sql at specific time point it as plan(ned time)
INSERT INTO task (repeat, input) VALUES ('5 min', 'SELECT now()'); -- to repeat sql every 5 minutes point repeat( interval)
INSERT INTO task (input) VALUES ('SELECT 1/0'); -- exception is catched and writed in error as text
INSERT INTO task (group, max, input) VALUES ('group', 1, 'SELECT now()'); -- if some group needs concurently run only 2 parallel sqls then use max = 1
INSERT INTO task (group, max, input) VALUES ('group', 2, 'SELECT now()'); -- if in this group there are more sqls and they are executing concurently by 2 then passing max = 2 will execute sql as more early in this group (it is like priority)
INSERT INTO task (input, remote) VALUES ('SELECT now()', 'user=user host=host'); -- to run sql on remote database use remotepg_task creates folowing GUCs
| Name | Type | Default | Level | Description |
|---|---|---|---|---|
| pg_task.delete | bool | true | config, database, user, session | Auto delete task when both output and error are nulls |
| pg_task.drift | bool | false | config, database, user, session | Compute next repeat time by stop time instead by plan time |
| pg_task.header | bool | true | config, database, user, session | Show columns headers in output |
| pg_task.string | bool | true | config, database, user, session | Quote only strings |
| pg_conf.close | int | 60 * 1000 | config, database, superuser | Close conf, milliseconds |
| pg_conf.fetch | int | 10 | config, database, superuser | Fetch conf rows at once |
| pg_conf.restart | int | 60 | config, database, superuser | Restart conf interval, seconds |
| pg_task.count | int | 0 | config, database, user, session | Non-negative maximum count of tasks, are executed by current background worker process before exit |
| pg_task.fetch | int | 100 | config, database, user | Fetch task rows at once |
| pg_task.id | bigint | 0 | session | Current task id (for read only) |
| pg_task.limit | int | 1000 | config, database, user | Limit task rows at once |
| pg_task.max | int | 0 | config, database, user, session | Maximum count of concurrently executing tasks in group, negative value means pause between tasks in milliseconds |
| pg_task.run | int | 2147483647 | config, database, user, session | Maximum count of concurrently executing tasks in work |
| pg_task.sleep | int | 1000 | config, database, user | Check tasks every sleep milliseconds |
| pg_work.close | int | 60 * 1000 | config, database, superuser | Close work, milliseconds |
| pg_work.fetch | int | 100 | config, database, superuser | Fetch work rows at once |
| pg_work.restart | int | 60 | config, database, superuser | Restart work interval, seconds |
| pg_task.active | interval | 1 hour | config, database, user, session | Positive period after plan time, when task is active for executing |
| pg_task.data | text | postgres | config | Database name for tasks table |
| pg_task.delimiter | char | \t | config, database, user, session | Results columns delimiter |
| pg_task.escape | char | config, database, user, session | Results columns escape | |
| pg_task.group | text | group | config, database, user, session | Task grouping by name |
| pg_task.idle | int | 60 | config, database, user | Idle task count |
| pg_task.json | json | [{"data":"postgres"}] | config | Json configuration, available keys: data, reset, schema, table, sleep and user |
| pg_task.live | interval | 0 sec | config, database, user, session | Non-negative maximum time of live of current background worker process before exit |
| pg_task.null | text | \N | config, database, user, session | Null text value representation |
| pg_task.quote | char | config, database, user, session | Results columns quote | |
| pg_task.repeat | interval | 0 sec | config, database, user, session | Non-negative auto repeat tasks interval |
| pg_task.reset | interval | 1 hour | config, database, user | Interval of reset tasks |
| pg_task.schema | text | public | config, database, user | Schema name for tasks table |
| pg_task.table | text | task | config, database, user | Table name for tasks table |
| pg_task.timeout | interval | 0 sec | config, database, user, session | Non-negative allowed time for task run |
| pg_task.user | text | postgres | config | User name for tasks table |
pg_task creates table with folowing columns
| Name | Type | Nullable? | Default | Description |
|---|---|---|---|---|
| id | bigserial | NOT NULL | autoincrement | Primary key |
| parent | bigint | NULL | pg_task.id | Parent task id (if exists, like foreign key to id, but without constraint, for performance) |
| plan | timestamptz | NOT NULL | CURRENT_TIMESTAMP | Planned date and time of start |
| start | timestamptz | NULL | Actual date and time of start | |
| stop | timestamptz | NULL | Actual date and time of stop | |
| active | interval | NOT NULL | pg_task.active | Positive period after plan time, when task is active for executing |
| live | interval | NOT NULL | pg_task.live | Non-negative maximum time of live of current background worker process before exit |
| repeat | interval | NOT NULL | pg_task.repeat | Non-negative auto repeat tasks interval |
| timeout | interval | NOT NULL | pg_task.timeout | Non-negative allowed time for task run |
| count | int | NOT NULL | pg_task.count | Non-negative maximum count of tasks, are executed by current background worker process before exit |
| hash | int | NOT NULL | generated by group and remote | Hash for identifying tasks group |
| max | int | NOT NULL | pg_task.max | Maximum count of concurrently executing tasks in group, negative value means pause between tasks in milliseconds |
| pid | int | NULL | Id of process executing task | |
| state | enum state (PLAN, TAKE, WORK, DONE, STOP) | NOT NULL | PLAN | Task state |
| delete | bool | NOT NULL | pg_task.delete | Auto delete task when both output and error are nulls |
| drift | bool | NOT NULL | pg_task.drift | Compute next repeat time by stop time instead by plan time |
| header | bool | NOT NULL | pg_task.header | Show columns headers in output |
| string | bool | NOT NULL | pg_task.string | Quote only strings |
| delimiter | char | NOT NULL | pg_task.delimiter | Results columns delimiter |
| escape | char | NOT NULL | pg_task.escape | Results columns escape |
| quote | char | NOT NULL | pg_task.quote | Results columns quote |
| data | text | NULL | Some user data | |
| error | text | NULL | Catched error | |
| group | text | NOT NULL | pg_task.group | Task grouping by name |
| input | text | NOT NULL | Sql command(s) to execute | |
| null | text | NOT NULL | pg_task.null | Null text value representation |
| output | text | NULL | Received result(s) | |
| remote | text | NULL | Connect to remote database (if need) |
but you may add any needed colums and/or make partitions
by default pg_task runs on default database with default user with default schema with default table with default sleep
to run specific database and/or specific user and/or specific schema and/or specific table and/or specific sleep, set config (in json format)
pg_task.json = '[{"data":"database1"},{"data":"database2","user":"username2"},{"data":"database3","schema":"schema3"},{"data":"database4","table":"table4"},{"data":"database5","sleep":100}]'
if database and/or user and/or schema and/or table does not exist then pg_task create it/their