Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commita0bcbeb

Browse files
author
Vladimir Ershov
committed
Merge commit '5fbc1181076c9dc1e8c9aadd4b07c657320616f3' into PGPROEE9_6_scheduler
2 parentsb92ad70 +5fbc118 commita0bcbeb

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 SQLscheme`schedule` to store its internal tables and
117+
The extension uses SQLschema`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-
selectid,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 longtask could be executed, NULL - infinite',
198+
'max_run_time','how longjob 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
CREATEFUNCTIONschedule._get_cron_from_attrs(params jsonb) RETURNS jsonbAS
242242
$BODY$
243243
DECLARE
244-
tdatestext[];
245-
datestext[];
246-
cron jsonb;
244+
datestext[];
245+
cron jsonb;
246+
clean_cron jsonb;
247+
Ninteger;
248+
nametext;
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 abouttask''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 aboutjob''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 cronIS NOT NULLAND 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 cronIS 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 nameINSELECT*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+
CREATEFUNCTIONschedule._get_array_from_jsonb(dsttext[], value jsonb) RETURNStext[]AS
298+
$BODY$
299+
DECLARE
300+
vtypetext;
301+
BEGIN
302+
IF value ISNULL THEN
303+
RETURN dst;
304+
END IF;
278305

279-
IF tdatesIS NOT NULLAND 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
CREATEFUNCTIONschedule._get_commands_from_attrs(params jsonb) RETURNStext[]AS
291328
$BODY$
292329
DECLARE
293-
commandstext[];
330+
commandstext[];
331+
Ninteger;
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 isNULLor 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-
CREATEFUNCTIONschedule.get_user_owned_cron() RETURNS SETOFschedule.cron_recAS
807+
CREATEFUNCTIONschedule.get_owned_cron() RETURNS SETOFschedule.cron_recAS
768808
$BODY$
769809
DECLARE
770810
iischedule.cron;
@@ -780,7 +820,8 @@ $BODY$
780820
LANGUAGE plpgsql
781821
SECURITY DEFINER;
782822

783-
CREATEFUNCTIONschedule.get_user_owned_cron(usenametext) RETURNS SETOFschedule.cron_recAS
823+
824+
CREATEFUNCTIONschedule.get_owned_cron(usenametext) RETURNS SETOFschedule.cron_recAS
784825
$BODY$
785826
DECLARE
786827
iischedule.cron;
@@ -800,6 +841,26 @@ $BODY$
800841
LANGUAGE plpgsql
801842
SECURITY DEFINER;
802843

844+
CREATEFUNCTIONschedule.get_user_owned_cron() RETURNS SETOFschedule.cron_recAS
845+
$BODY$
846+
BEGIN
847+
RETURN QUERYSELECT*fromschedule.get_owned_cron();
848+
END
849+
$BODY$
850+
LANGUAGE plpgsql
851+
SECURITY DEFINER;
852+
853+
CREATEFUNCTIONschedule.get_user_owned_cron(usenametext) RETURNS SETOFschedule.cron_recAS
854+
$BODY$
855+
BEGIN
856+
RETURN QUERYSELECT*fromschedule.get_owned_cron(usename);
857+
END
858+
$BODY$
859+
LANGUAGE plpgsql
860+
SECURITY DEFINER;
861+
862+
863+
803864
CREATEFUNCTIONschedule.get_user_cron() RETURNS SETOFschedule.cron_recAS
804865
$BODY$
805866
DECLARE
@@ -870,6 +931,16 @@ $BODY$
870931
LANGUAGE plpgsql
871932
SECURITY DEFINER;
872933

934+
CREATEFUNCTIONschedule.get_active_jobs(usenametext) RETURNS SETOFschedule.cron_jobAS
935+
$BODY$
936+
DECLARE
937+
BEGIN
938+
RETURN QUERYSELECT*FROMschedule.get_user_active_jobs(usename);
939+
END
940+
$BODY$
941+
LANGUAGE plpgsql
942+
SECURITY DEFINER;
943+
873944
CREATEFUNCTIONschedule.get_active_jobs() RETURNS SETOFschedule.cron_jobAS
874945
$BODY$
875946
DECLARE
@@ -943,6 +1014,15 @@ $BODY$
9431014
LANGUAGE plpgsql
9441015
SECURITY DEFINER;
9451016

1017+
CREATEFUNCTIONschedule.get_log(usenametext) RETURNS SETOFschedule.cron_jobAS
1018+
$BODY$
1019+
BEGIN
1020+
RETURN QUERYSELECT*FROMschedule.get_user_log(usename);
1021+
END
1022+
$BODY$
1023+
LANGUAGE plpgsql
1024+
SECURITY DEFINER;
1025+
9461026
CREATEFUNCTIONschedule.get_log() RETURNS SETOFschedule.cron_jobAS
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
createuser__temp_robot;
2+
createuser__temp_root WITH SUPERUSER;
3+
4+
SET SESSION AUTHORIZATION __temp_root;
5+
26
selectschedule.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,retryfromschedule.cronorder by id;
3943

4044

45+
RESET SESSION AUTHORIZATION;
46+
dropuser __temp_root;
4147
dropuser __temp_robot;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp