***schedule.scheme** - text, the`scheme` name where scheduler store its

***schedule.schema** - text, the`schema` name where scheduler store its

The extension uses SQLscheme`schedule` to store its internal tables and

The extension uses SQLschema`schedule` to store its internal tables and

functions. Direct access to tables is forbidden. All manipulations should

be performed by means of functions defined by extension.

***schedule.scheme** - строковая переменная, указывает в какой`scheme`

***schedule.schema** - строковая переменная, указывает в какой`schema`

create user __temp_robot;

create user __temp_root WITH SUPERUSER;

SET SESSION AUTHORIZATION __temp_root;

select schedule.create_job(

'{

"name": "Test @reboot",

4

(1 row)

select id,node,name,rule,do_sql,same_transaction, postpone,retry from schedule.cron order by id;

select id,node,name,rule,do_sql,same_transaction, postpone,retry from schedule.cron order by id;

id | node | name | rule | do_sql | same_transaction | postpone | retry

----+--------+----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------+------------------+-----------+-------

1 | master | Test @reboot | {"crontab": "@reboot", "onstart": 1} | {"show all"} | f | | 0

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

(4 rows)

RESET SESSION AUTHORIZATION;

drop user __temp_root;

drop user __temp_robot;

'dates','Set of exact dates when comman will be executed',

'use_same_transaction','if set of commans should be executed within the same transaction',

'last_start_available','for how long could command execution be postponed in format of interval type' ,

'max_run_time','how longtask could be executed, NULL - infinite',

'max_run_time','how longjob could be executed, NULL - infinite',

'max_instances','the number of instances run at the same time',

'onrollback','statement to be executed after rollback if one occured',

'next_time_statement','statement to be executed last to calc next execution time'

CREATEFUNCTIONschedule._get_cron_from_attrs(params jsonb) RETURNS jsonbAS

$BODY$

DECLARE

tdatestext[];

datestext[];

cron jsonb;

datestext[];

cron jsonb;

clean_cron jsonb;

Ninteger;

nametext;

IF params?'cron' THEN

EXECUTE'SELECT schedule.cron2jsontext($1::cstring)::jsonb'

INTO cron

USING params->>'cron';

ELSIF params?'rule' THEN

cron := params->'rule';

ELSIF NOT params?'date'AND NOT params?'dates' THEN

RAISE EXCEPTION'There is no information abouttask''s schedule'

USING HINT='Use''cron'' - cron string,''rule'' - json to set schedule rules or''date'' and''dates'' to set exact date(s)';

END IF;

IF params?'cron' THEN

EXECUTE'SELECT schedule.cron2jsontext($1::cstring)::jsonb'

INTO cron

USING params->>'cron';

ELSIF params?'rule' THEN

cron := params->'rule';

ELSIF NOT params?'date'AND NOT params?'dates' THEN

RAISE EXCEPTION'There is no information aboutjob''s schedule'

USING HINT='Use''cron'' - cron string,''rule'' - json to set schedule rules or''date'' and''dates'' to set exact date(s)';

END IF;

IF cronIS NOT NULLAND cron?'dates' THEN

EXECUTE'SELECT array_agg(value)::text[] from jsonb_array_elements_text($1) as X'

INTO tdates

USING cron->'dates';

ELSE

tdates :='{}'::text[];

END IF;

IF cronIS NOT NULL THEN

IF cron?'date' THEN

dates :=schedule._get_array_from_jsonb(dates, cron->'date');

END IF;

IF cron?'dates' THEN

dates :=schedule._get_array_from_jsonb(dates, cron->'dates');

END IF;

END IF;

IF params?'date' THEN

tdates := array_append(tdates, params->>'date');

END IF;

IF params?'date' THEN

dates :=schedule._get_array_from_jsonb(dates, params->'date');

END IF;

IF params?'dates' THEN

dates :=schedule._get_array_from_jsonb(dates, params->'dates');

END IF;

N := array_length(dates,1);

IF N>0 THEN

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'

INTO dates USING dates;

cron := COALESCE(cron,'{}'::jsonb)|| json_build_object('dates', array_to_json(dates))::jsonb;

END IF;

clean_cron :='{}'::jsonb;

FOR nameINSELECT*FROM unnest('{dates, crontab, onstart, days, hours, wdays, months, minutes}'::text[])

LOOP

IF cron?name THEN

clean_cron := jsonb_set(clean_cron, array_append('{}'::text[], name), cron->name);

END IF;

END LOOP;

RETURN clean_cron;

END

$BODY$

LANGUAGE plpgsql;

IF params?'dates' THEN

EXECUTE'SELECT array_agg(value)::text[] from jsonb_array_elements_text($1) as X'

INTO dates

USING params->'dates';

tdates := array_cat(tdates, dates);

END IF;

CREATEFUNCTIONschedule._get_array_from_jsonb(dsttext[], value jsonb) RETURNStext[]AS

$BODY$

DECLARE

vtypetext;

IF value ISNULL THEN

RETURN dst;

END IF;

IF tdatesIS NOT NULLAND array_length(tdates,1)>0 THEN

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'

INTO dates

USING tdates;

cron := COALESCE(cron,'{}'::jsonb)|| json_build_object('dates', array_to_json(dates))::jsonb;

END IF;

RETURN cron;

EXECUTE'SELECT jsonb_typeof($1)'

INTO vtype

USING value;

IF vtype='string' THEN

EXECUTE'SELECT array_append($1, $2->>0)'

INTO dst

USING dst, value;

ELSIF vtype='array' THEN

EXECUTE'SELECT $1 || array_agg(value)::text[] from jsonb_array_elements_text($2)'

INTO dst

USING dst, value;

ELSE

RAISE EXCEPTION'The value could be only''string'' or''array'' type';

END IF;

RETURN dst;

END

$BODY$

LANGUAGE plpgsql;

CREATEFUNCTIONschedule._get_commands_from_attrs(params jsonb) RETURNStext[]AS

$BODY$

DECLARE

commandstext[];

commandstext[];

Ninteger;

IF params?'command' THEN

EXECUTE'SELECT array_append(''{}''::text[], $1)'

INTO commands

USING params->>'command';

ELSIF params?'commands' THEN

EXECUTE'SELECT array_agg(value)::text[] from jsonb_array_elements_text($1) as X'

INTO commands

USING params->'commands';

ELSE

RAISE EXCEPTION'There is no information about what task to execute'

USING HINT='Use''command'' or''commands'' key to transmit information';

N :=0;

IF params?'command' THEN

commands :=schedule._get_array_from_jsonb(commands, params->'command');

END IF;

IF params?'commands' THEN

commands :=schedule._get_array_from_jsonb(commands, params->'commands');

END IF;

N := array_length(commands,1);

IF N isNULLor N=0 THEN

RAISE EXCEPTION'There is no information about what job to execute'

USING HINT='Use''command'' or''commands'' key to transmit information';

END IF;

RETURN commands;

LANGUAGE plpgsql

SECURITY DEFINER;

CREATEFUNCTIONschedule.get_user_owned_cron() RETURNS SETOFschedule.cron_recAS

CREATEFUNCTIONschedule.get_owned_cron() RETURNS SETOFschedule.cron_recAS

$BODY$

DECLARE

iischedule.cron;

LANGUAGE plpgsql

SECURITY DEFINER;

CREATEFUNCTIONschedule.get_user_owned_cron(usenametext) RETURNS SETOFschedule.cron_recAS

CREATEFUNCTIONschedule.get_owned_cron(usenametext) RETURNS SETOFschedule.cron_recAS

$BODY$

DECLARE

iischedule.cron;

LANGUAGE plpgsql

SECURITY DEFINER;

CREATEFUNCTIONschedule.get_user_owned_cron() RETURNS SETOFschedule.cron_recAS

$BODY$

RETURN QUERYSELECT*fromschedule.get_owned_cron();

END

$BODY$

LANGUAGE plpgsql

SECURITY DEFINER;

CREATEFUNCTIONschedule.get_user_owned_cron(usenametext) RETURNS SETOFschedule.cron_recAS

$BODY$

RETURN QUERYSELECT*fromschedule.get_owned_cron(usename);

END

$BODY$

LANGUAGE plpgsql

SECURITY DEFINER;

CREATEFUNCTIONschedule.get_user_cron() RETURNS SETOFschedule.cron_recAS

$BODY$

DECLARE

LANGUAGE plpgsql

SECURITY DEFINER;

CREATEFUNCTIONschedule.get_active_jobs(usenametext) RETURNS SETOFschedule.cron_jobAS

$BODY$

DECLARE

RETURN QUERYSELECT*FROMschedule.get_user_active_jobs(usename);

END

$BODY$

LANGUAGE plpgsql

SECURITY DEFINER;

CREATEFUNCTIONschedule.get_active_jobs() RETURNS SETOFschedule.cron_jobAS

$BODY$

DECLARE

LANGUAGE plpgsql

SECURITY DEFINER;

CREATEFUNCTIONschedule.get_log(usenametext) RETURNS SETOFschedule.cron_jobAS

$BODY$

RETURN QUERYSELECT*FROMschedule.get_user_log(usename);

END

$BODY$

LANGUAGE plpgsql

SECURITY DEFINER;

CREATEFUNCTIONschedule.get_log() RETURNS SETOFschedule.cron_jobAS

$BODY$

createuser__temp_robot;

createuser__temp_root WITH SUPERUSER;

SET SESSION AUTHORIZATION __temp_root;

selectschedule.create_job(

);

select id,node,name,rule,do_sql,same_transaction, postpone,retryfromschedule.cronorder by id;

select id, node, name, rule, do_sql, same_transaction, postpone, retryfromschedule.cronorder by id;

RESET SESSION AUTHORIZATION;

dropuser __temp_root;

dropuser __temp_robot;

volatilesig_atomic_tgot_sigterm= false;

staticchar*scheduler_databases=NULL;

staticchar*scheduler_nodename=NULL;

staticchar*scheduler_transaction_state=NULL;

staticintscheduler_max_workers=2;

staticboolscheduler_service_enabled= false;

staticchar*scheduler_schema=NULL;

char*scheduler_databases=NULL;

char*scheduler_nodename=NULL;

char*scheduler_transaction_state=NULL;

intscheduler_max_workers=2;

boolscheduler_service_enabled= false;

char*scheduler_schema=NULL;

schd_manager_share_t*shared;

boolrefresh= false;

CurrentResourceOwner=ResourceOwnerCreate(NULL,"pgpro_scheduler");

init_worker_mem_ctx("Parent scheduler context");

elog(LOG,"Start PostgresPro scheduler.");

SetConfigOption("application_name","pgp-s supervisor",PGC_USERSET,PGC_S_SESSION);

pgstat_report_activity(STATE_RUNNING,"Initialize");

pqsignal(SIGHUP,worker_spi_sighup);

BGWORKER_BACKEND_DATABASE_CONNECTION;

worker.bgw_start_time=BgWorkerStart_ConsistentState;

worker.bgw_restart_time=BGW_NEVER_RESTART;

worker.bgw_main=parent_scheduler_main;

worker.bgw_main=NULL;

worker.bgw_notify_pid=0;

worker.bgw_main_arg=0;

strcpy(worker.bgw_name,"pgpro scheduler");

worker.bgw_main_arg=Int32GetDatum(0);

worker.bgw_extra[0]=0;

memcpy(worker.bgw_library_name,"pgpro_scheduler",16);

RegisterBackgroundWorker(&worker);

}

char_array_t*readBasesToCheck(void);

externDatumcron_string_to_json_text(PG_FUNCTION_ARGS);

void_PG_init(void);

voidparent_scheduler_main(Datum)pg_attribute_noreturn();

externPGDLLEXPORTvoidparent_scheduler_main(Datum)pg_attribute_noreturn();

intcheckSchedulerNamespace(void);

voidmanager_worker_main(Datumarg);

pid_tregisterManagerWorker(schd_manager_t*man);

voidreload_db_role_config(char*dbname);

char**errors;

}executor_error_t;

voidexecutor_worker_main(Datumarg);

externPGDLLEXPORTvoidexecutor_worker_main(Datumarg);

job_t*initializeExecutorJob(schd_executor_share_t*data);

voidset_shared_message(schd_executor_share_t*shared,executor_error_t*ee);

TimestampTzget_next_excution_time(char*sql,executor_error_t*ee);