Skip to content

Commit a0bcbeb

Browse files
author
Vladimir Ershov
committed
Merge commit '5fbc1181076c9dc1e8c9aadd4b07c657320616f3' into PGPROEE9_6_scheduler
2 parents b92ad70 + 5fbc118 commit a0bcbeb

File tree

9 files changed

+168
-79
lines changed

9 files changed

+168
-79
lines changed

‎contrib/pgpro_scheduler/README.md

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -38,7 +38,7 @@ help to handle scheduler configuration.
3838
* **schedule.database** - text, list of database names on which scheduler
3939
is enabled. Database names should be separated by comma.
4040
Default value: empty string.
41-
* **schedule.scheme** - text, the `scheme` name where scheduler store its
41+
* **schedule.schema** - text, the `schema` name where scheduler store its
4242
tables and functions. To change this value restart required. Normally
4343
you should not change this variable but it could be useful if you
4444
want run scheduled jobs on hot-standby database. So you can define
@@ -114,7 +114,7 @@ where:
114114

115115
## SQL Scheme
116116

117-
The extension uses SQL scheme `schedule` to store its internal tables and
117+
The extension uses SQL schema `schedule` to store its internal tables and
118118
functions. Direct access to tables is forbidden. All manipulations should
119119
be performed by means of functions defined by extension.
120120

‎contrib/pgpro_scheduler/README.rus.md

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -41,7 +41,7 @@ pgpro_scheduler это расширение PostgreSQL и не требует н
4141
* **schedule.database** - строковая переменная, указывает с какими базам может
4242
работать планировщик. Что бы указать несколько баз, нужно перечислить их
4343
имена через запятую. По умолчанию - пустая строка.
44-
* **schedule.scheme** - строковая переменная, указывает в какой `scheme`
44+
* **schedule.schema** - строковая переменная, указывает в какой `schema`
4545
находятся служебные таблицы планировщика. Для изменения требуется
4646
перезагрузка. Обычно ее не надо менять. Может использоваться для работы
4747
на реплике, если используется foreign data wrapper. По умолчанию -

‎contrib/pgpro_scheduler/expected/cron_string.out

Lines changed: 11 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,6 @@
11
create user __temp_robot;
2+
create user __temp_root WITH SUPERUSER;
3+
SET SESSION AUTHORIZATION __temp_root;
24
select schedule.create_job(
35
'{
46
"name": "Test @reboot",
@@ -51,13 +53,15 @@ select schedule.create_job(
5153
4
5254
(1 row)
5355

54-
select id,node,name,rule,do_sql,same_transaction, postpone,retry from schedule.cron order by id;
55-
id | node | name | rule | do_sql | same_transaction | postpone | retry
56-
----+--------+----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------+------------------+-----------+-------
57-
1 | master | Test @reboot | {"crontab": "@reboot", "onstart": 1} | {"show all"} | f | | 0
58-
2 | master | Test 1 | {"days": [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31], "hours": [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23], "wdays": [0, 1, 2, 3, 4, 5, 6], "months": [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12], "crontab": "* * * * *", "minutes": [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59]} | {"select 'this is every minute job'"} | f | @ 2 hours | 0
59-
3 | master | Test 2 4/4 2/4 * * * | {"days": [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31], "hours": [2, 6, 10, 14, 18, 22], "wdays": [0, 1, 2, 3, 4, 5, 6], "months": [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12], "crontab": "4/4 2/4 * * *", "minutes": [4, 8, 12, 16, 20, 24, 28, 32, 36, 40, 44, 48, 52, 56]} | {"select pg_sleep(10)"} | f | | 0
60-
4 | master | Test 3 | {"days": [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31], "hours": [1], "wdays": [0, 4], "months": [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12], "crontab": "23 1 * * THU,SUN", "minutes": [23]} | {"select 'ok' as ok"} | f | | 0
56+
select * from schedule.cron order by id;
57+
id | node | name | comments | rule | next_time_statement | do_sql | same_transaction | onrollback_statement | active | broken | executor | owner | postpone | retry | max_run_time | max_instances | start_date | end_date | reason | _next_exec_time
58+
----+--------+----------------------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+---------------------------------------+------------------+----------------------+--------+--------+--------------+-------------+-----------+-------+--------------+---------------+------------+----------+--------+-----------------
59+
1 | master | Test @reboot | | {"crontab": "@reboot", "onstart": 1} | | {"show all"} | f | | t | f | __temp_robot | __temp_root | | 0 | | 1 | | | |
60+
2 | master | Test 1 | | {"days": [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31], "hours": [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23], "wdays": [0, 1, 2, 3, 4, 5, 6], "months": [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12], "crontab": "* * * * *", "minutes": [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59]} | | {"select 'this is every minute job'"} | f | | t | f | __temp_robot | __temp_root | @ 2 hours | 0 | | 1 | | | |
61+
3 | master | Test 2 4/4 2/4 * * * | | {"days": [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31], "hours": [2, 6, 10, 14, 18, 22], "wdays": [0, 1, 2, 3, 4, 5, 6], "months": [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12], "crontab": "4/4 2/4 * * *", "minutes": [4, 8, 12, 16, 20, 24, 28, 32, 36, 40, 44, 48, 52, 56]} | | {"select pg_sleep(10)"} | f | | t | f | __temp_robot | __temp_root | | 0 | | 1 | | | |
62+
4 | master | Test 3 | | {"days": [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31], "hours": [1], "wdays": [0, 4], "months": [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12], "crontab": "23 1 * * THU,SUN", "minutes": [23]} | | {"select 'ok' as ok"} | f | | t | f | __temp_root | __temp_root | | 0 | | 1 | | | |
6163
(4 rows)
6264

65+
RESET SESSION AUTHORIZATION;
66+
drop user __temp_root;
6367
drop user __temp_robot;

‎contrib/pgpro_scheduler/pgpro_scheduler--1.0.sql

Lines changed: 131 additions & 51 deletions
Original file line numberDiff line numberDiff line change
@@ -195,7 +195,7 @@ BEGIN
195195
'dates', 'Set of exact dates when comman will be executed',
196196
'use_same_transaction', 'if set of commans should be executed within the same transaction',
197197
'last_start_available', 'for how long could command execution be postponed in format of interval type' ,
198-
'max_run_time', 'how long task could be executed, NULL - infinite',
198+
'max_run_time', 'how long job could be executed, NULL - infinite',
199199
'max_instances', 'the number of instances run at the same time',
200200
'onrollback', 'statement to be executed after rollback if one occured',
201201
'next_time_statement', 'statement to be executed last to calc next execution time'
@@ -241,68 +241,108 @@ LANGUAGE plpgsql;
241241
CREATE FUNCTION schedule._get_cron_from_attrs(params jsonb) RETURNS jsonb AS
242242
$BODY$
243243
DECLARE
244-
tdates text[];
245-
dates text[];
246-
cron jsonb;
244+
dates text[];
245+
cron jsonb;
246+
clean_cron jsonb;
247+
N integer;
248+
name text;
247249
BEGIN
248250

249-
IF params?'cron' THEN
250-
EXECUTE 'SELECT schedule.cron2jsontext($1::cstring)::jsonb'
251-
INTO cron
252-
USING params->>'cron';
253-
ELSIF params?'rule' THEN
254-
cron := params->'rule';
255-
ELSIF NOT params?'date' AND NOT params?'dates' THEN
256-
RAISE EXCEPTION 'There is no information about task''s schedule'
257-
USING HINT = 'Use ''cron'' - cron string, ''rule'' - json to set schedule rules or ''date'' and ''dates'' to set exact date(s)';
258-
END IF;
251+
IF params?'cron' THEN
252+
EXECUTE 'SELECT schedule.cron2jsontext($1::cstring)::jsonb'
253+
INTO cron
254+
USING params->>'cron';
255+
ELSIF params?'rule' THEN
256+
cron := params->'rule';
257+
ELSIF NOT params?'date' AND NOT params?'dates' THEN
258+
RAISE EXCEPTION 'There is no information about job''s schedule'
259+
USING HINT = 'Use ''cron'' - cron string, ''rule'' - json to set schedule rules or ''date'' and ''dates'' to set exact date(s)';
260+
END IF;
259261

260-
IF cron IS NOT NULL AND cron?'dates' THEN
261-
EXECUTE 'SELECT array_agg(value)::text[] from jsonb_array_elements_text($1) as X'
262-
INTO tdates
263-
USING cron->'dates';
264-
ELSE
265-
tdates := '{}'::text[];
266-
END IF;
262+
IF cron IS NOT NULL THEN
263+
IF cron?'date' THEN
264+
dates := schedule._get_array_from_jsonb(dates, cron->'date');
265+
END IF;
266+
IF cron?'dates' THEN
267+
dates := schedule._get_array_from_jsonb(dates, cron->'dates');
268+
END IF;
269+
END IF;
267270

268-
IF params?'date' THEN
269-
tdates := array_append(tdates, params->>'date');
270-
END IF;
271+
IF params?'date' THEN
272+
dates := schedule._get_array_from_jsonb(dates, params->'date');
273+
END IF;
274+
IF params?'dates' THEN
275+
dates := schedule._get_array_from_jsonb(dates, params->'dates');
276+
END IF;
277+
N := array_length(dates, 1);
278+
279+
IF N > 0 THEN
280+
EXECUTE 'SELECT array_agg(lll) FROM (SELECT distinct(date_trunc(''min'', unnest::timestamp with time zone)) as lll FROM unnest($1) ORDER BY date_trunc(''min'', unnest::timestamp with time zone)) as Z'
281+
INTO dates USING dates;
282+
cron := COALESCE(cron, '{}'::jsonb) || json_build_object('dates', array_to_json(dates))::jsonb;
283+
END IF;
284+
285+
clean_cron := '{}'::jsonb;
286+
FOR name IN SELECT * FROM unnest('{dates, crontab, onstart, days, hours, wdays, months, minutes}'::text[])
287+
LOOP
288+
IF cron?name THEN
289+
clean_cron := jsonb_set(clean_cron, array_append('{}'::text[], name), cron->name);
290+
END IF;
291+
END LOOP;
292+
RETURN clean_cron;
293+
END
294+
$BODY$
295+
LANGUAGE plpgsql;
271296

272-
IF params?'dates' THEN
273-
EXECUTE 'SELECT array_agg(value)::text[] from jsonb_array_elements_text($1) as X'
274-
INTO dates
275-
USING params->'dates';
276-
tdates := array_cat(tdates, dates);
277-
END IF;
297+
CREATE FUNCTION schedule._get_array_from_jsonb(dst text[], value jsonb) RETURNS text[] AS
298+
$BODY$
299+
DECLARE
300+
vtype text;
301+
BEGIN
302+
IF value IS NULL THEN
303+
RETURN dst;
304+
END IF;
278305

279-
IF tdates IS NOT NULL AND array_length(tdates, 1) > 0 THEN
280-
EXECUTE 'SELECT array_agg(lll) FROM (SELECT distinct(date_trunc(''min'', unnest::timestamp with time zone)) as lll FROM unnest($1) ORDER BY date_trunc(''min'', unnest::timestamp with time zone)) as Z'
281-
INTO dates
282-
USING tdates;
283-
cron := COALESCE(cron, '{}'::jsonb) || json_build_object('dates', array_to_json(dates))::jsonb;
284-
END IF;
285-
RETURN cron;
306+
EXECUTE 'SELECT jsonb_typeof($1)'
307+
INTO vtype
308+
USING value;
309+
IF vtype = 'string' THEN
310+
-- EXECUTE 'SELECT array_append($1, jsonb_set(''{"a":""}''::jsonb, ''{a}'', $2)->>''a'')'
311+
EXECUTE 'SELECT array_append($1, $2->>0)'
312+
INTO dst
313+
USING dst, value;
314+
ELSIF vtype = 'array' THEN
315+
EXECUTE 'SELECT $1 || array_agg(value)::text[] from jsonb_array_elements_text($2)'
316+
INTO dst
317+
USING dst, value;
318+
ELSE
319+
RAISE EXCEPTION 'The value could be only ''string'' or ''array'' type';
320+
END IF;
321+
322+
RETURN dst;
286323
END
287324
$BODY$
288325
LANGUAGE plpgsql;
289326

290327
CREATE FUNCTION schedule._get_commands_from_attrs(params jsonb) RETURNS text[] AS
291328
$BODY$
292329
DECLARE
293-
commands text[];
330+
commands text[];
331+
N integer;
294332
BEGIN
295-
IF params?'command' THEN
296-
EXECUTE 'SELECT array_append(''{}''::text[], $1)'
297-
INTO commands
298-
USING params->>'command';
299-
ELSIF params?'commands' THEN
300-
EXECUTE 'SELECT array_agg(value)::text[] from jsonb_array_elements_text($1) as X'
301-
INTO commands
302-
USING params->'commands';
303-
ELSE
304-
RAISE EXCEPTION 'There is no information about what task to execute'
305-
USING HINT = 'Use ''command'' or ''commands'' key to transmit information';
333+
N := 0;
334+
IF params?'command' THEN
335+
commands := schedule._get_array_from_jsonb(commands, params->'command');
336+
END IF;
337+
338+
IF params?'commands' THEN
339+
commands := schedule._get_array_from_jsonb(commands, params->'commands');
340+
END IF;
341+
342+
N := array_length(commands, 1);
343+
IF N is NULL or N = 0 THEN
344+
RAISE EXCEPTION 'There is no information about what job to execute'
345+
USING HINT = 'Use ''command'' or ''commands'' key to transmit information';
306346
END IF;
307347

308348
RETURN commands;
@@ -764,7 +804,7 @@ $BODY$
764804
LANGUAGE plpgsql
765805
SECURITY DEFINER;
766806

767-
CREATE FUNCTION schedule.get_user_owned_cron() RETURNS SETOF schedule.cron_rec AS
807+
CREATE FUNCTION schedule.get_owned_cron() RETURNS SETOF schedule.cron_rec AS
768808
$BODY$
769809
DECLARE
770810
ii schedule.cron;
@@ -780,7 +820,8 @@ $BODY$
780820
LANGUAGE plpgsql
781821
SECURITY DEFINER;
782822

783-
CREATE FUNCTION schedule.get_user_owned_cron(usename text) RETURNS SETOF schedule.cron_rec AS
823+
824+
CREATE FUNCTION schedule.get_owned_cron(usename text) RETURNS SETOF schedule.cron_rec AS
784825
$BODY$
785826
DECLARE
786827
ii schedule.cron;
@@ -800,6 +841,26 @@ $BODY$
800841
LANGUAGE plpgsql
801842
SECURITY DEFINER;
802843

844+
CREATE FUNCTION schedule.get_user_owned_cron() RETURNS SETOF schedule.cron_rec AS
845+
$BODY$
846+
BEGIN
847+
RETURN QUERY SELECT * from schedule.get_owned_cron();
848+
END
849+
$BODY$
850+
LANGUAGE plpgsql
851+
SECURITY DEFINER;
852+
853+
CREATE FUNCTION schedule.get_user_owned_cron(usename text) RETURNS SETOF schedule.cron_rec AS
854+
$BODY$
855+
BEGIN
856+
RETURN QUERY SELECT * from schedule.get_owned_cron(usename);
857+
END
858+
$BODY$
859+
LANGUAGE plpgsql
860+
SECURITY DEFINER;
861+
862+
863+
803864
CREATE FUNCTION schedule.get_user_cron() RETURNS SETOF schedule.cron_rec AS
804865
$BODY$
805866
DECLARE
@@ -870,6 +931,16 @@ $BODY$
870931
LANGUAGE plpgsql
871932
SECURITY DEFINER;
872933

934+
CREATE FUNCTION schedule.get_active_jobs(usename text) RETURNS SETOF schedule.cron_job AS
935+
$BODY$
936+
DECLARE
937+
BEGIN
938+
RETURN QUERY SELECT * FROM schedule.get_user_active_jobs(usename);
939+
END
940+
$BODY$
941+
LANGUAGE plpgsql
942+
SECURITY DEFINER;
943+
873944
CREATE FUNCTION schedule.get_active_jobs() RETURNS SETOF schedule.cron_job AS
874945
$BODY$
875946
DECLARE
@@ -943,6 +1014,15 @@ $BODY$
9431014
LANGUAGE plpgsql
9441015
SECURITY DEFINER;
9451016

1017+
CREATE FUNCTION schedule.get_log(usename text) RETURNS SETOF schedule.cron_job AS
1018+
$BODY$
1019+
BEGIN
1020+
RETURN QUERY SELECT * FROM schedule.get_user_log(usename);
1021+
END
1022+
$BODY$
1023+
LANGUAGE plpgsql
1024+
SECURITY DEFINER;
1025+
9461026
CREATE FUNCTION schedule.get_log() RETURNS SETOF schedule.cron_job AS
9471027
$BODY$
9481028
BEGIN

‎contrib/pgpro_scheduler/sql/cron_string.sql

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,8 @@
11
create user __temp_robot;
2+
create user __temp_root WITH SUPERUSER;
3+
4+
SET SESSION AUTHORIZATION __temp_root;
5+
26
select schedule.create_job(
37
'{
48
"name": "Test @reboot",
@@ -38,4 +42,6 @@ select schedule.create_job(
3842
select id,node,name,rule,do_sql,same_transaction, postpone,retry from schedule.cron order by id;
3943

4044

45+
RESET SESSION AUTHORIZATION;
46+
drop user __temp_root;
4147
drop user __temp_robot;

0 commit comments

Comments
 (0)