Skip to content

Commit 21cdd44

Browse files
vbwagnervbwagner
committed
Merge branch 'PGPROEE9_6_scheduler' into PGPROEE9_6
2 parents f1faa89 + fcc5f08 commit 21cdd44

File tree

4 files changed

+475
-260
lines changed

4 files changed

+475
-260
lines changed

‎contrib/pgpro_scheduler/pgpro_scheduler--2.0--2.1.sql

Lines changed: 191 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,3 @@
1-
SET search_path TO schedule;
2-
31
DROP VIEW job_status;
42
DROP VIEW all_job_status;
53

@@ -15,7 +13,7 @@ CREATE VIEW job_status AS
1513
max_run_time as max_duration, submit_time, canceled,
1614
start_time, status as is_success, reason as error, done_time,
1715
'done'::job_at_status_t status
18-
FROM schedule.at_jobs_done where owner = session_user
16+
FROM @extschema@.at_jobs_done where owner = session_user
1917
UNION
2018
SELECT
2119
id, node, name, comments, at as run_after,
@@ -24,7 +22,7 @@ CREATE VIEW job_status AS
2422
max_run_time as max_duration, submit_time, canceled, start_time,
2523
NULL as is_success, NULL as error, NULL as done_time,
2624
'processing'::job_at_status_t status
27-
FROM ONLY schedule.at_jobs_process where owner = session_user
25+
FROM ONLY @extschema@.at_jobs_process where owner = session_user
2826
UNION
2927
SELECT
3028
id, node, name, comments, at as run_after,
@@ -34,7 +32,7 @@ CREATE VIEW job_status AS
3432
NULL as start_time, NULL as is_success, NULL as error,
3533
NULL as done_time,
3634
'submitted'::job_at_status_t status
37-
FROM ONLY schedule.at_jobs_submitted where owner = session_user;
35+
FROM ONLY @extschema@.at_jobs_submitted where owner = session_user;
3836

3937
CREATE VIEW all_job_status AS
4038
SELECT
@@ -44,7 +42,7 @@ CREATE VIEW all_job_status AS
4442
max_run_time as max_duration, submit_time, canceled,
4543
start_time, status as is_success, reason as error, done_time,
4644
'done'::job_at_status_t status
47-
FROM schedule.at_jobs_done
45+
FROM @extschema@.at_jobs_done
4846
UNION
4947
SELECT
5048
id, node, name, comments, at as run_after,
@@ -53,7 +51,7 @@ CREATE VIEW all_job_status AS
5351
max_run_time as max_duration, submit_time, canceled, start_time,
5452
NULL as is_success, NULL as error, NULL as done_time,
5553
'processing'::job_at_status_t status
56-
FROM ONLY schedule.at_jobs_process
54+
FROM ONLY @extschema@.at_jobs_process
5755
UNION
5856
SELECT
5957
id, node, name, comments, at as run_after,
@@ -63,8 +61,191 @@ CREATE VIEW all_job_status AS
6361
NULL as start_time, NULL as is_success, NULL as error,
6462
NULL as done_time,
6563
'submitted'::job_at_status_t status
66-
FROM ONLY schedule.at_jobs_submitted;
64+
FROM ONLY @extschema@.at_jobs_submitted;
65+
66+
GRANT SELECT ON @extschema@.job_status TO public;
67+
68+
DROP FUNCTION get_log();
69+
DROP FUNCTION get_log(text);
70+
DROP FUNCTION get_user_log();
71+
72+
73+
CREATE INDEX ON cron (owner);
74+
CREATE INDEX ON cron (executor);
75+
76+
--
77+
-- show all scheduled jobs
78+
--
79+
CREATE VIEW all_jobs_log AS
80+
SELECT
81+
coalesce(c.id, l.cron) as cron,
82+
c.node as node,
83+
l.start_at as scheduled_at,
84+
coalesce(c.name, '--DELETED--') as name,
85+
c.comments as comments,
86+
c.do_sql as commands,
87+
c.executor as run_as,
88+
c.owner as owner,
89+
c.same_transaction as use_same_transaction,
90+
l.started as started,
91+
l.last_start_available as last_start_available,
92+
l.finished as finished,
93+
c.max_run_time as max_run_time,
94+
c.onrollback_statement as onrollback,
95+
c.next_time_statement as next_time_statement,
96+
c.max_instances as max_instances,
97+
CASE WHEN l.status THEN
98+
'done'::@extschema@.job_status_t
99+
ELSE
100+
'error'::@extschema@.job_status_t
101+
END as status,
102+
l.message as message
103+
104+
FROM @extschema@.log as l LEFT OUTER JOIN @extschema@.cron as c ON c.id = l.cron;
105+
106+
--
107+
-- show scheduled jobs of session user
108+
--
109+
110+
CREATE VIEW jobs_log AS
111+
SELECT
112+
coalesce(c.id, l.cron) as cron,
113+
c.node as node,
114+
l.start_at as scheduled_at,
115+
coalesce(c.name, '--DELETED--') as name,
116+
c.comments as comments,
117+
c.do_sql as commands,
118+
c.executor as run_as,
119+
c.owner as owner,
120+
c.same_transaction as use_same_transaction,
121+
l.started as started,
122+
l.last_start_available as last_start_available,
123+
l.finished as finished,
124+
c.max_run_time as max_run_time,
125+
c.onrollback_statement as onrollback,
126+
c.next_time_statement as next_time_statement,
127+
c.max_instances as max_instances,
128+
CASE WHEN l.status THEN
129+
'done'::@extschema@.job_status_t
130+
ELSE
131+
'error'::@extschema@.job_status_t
132+
END as status,
133+
l.message as message
134+
FROM log as l, cron as c WHERE c.executor = session_user AND c.id = l.cron;
135+
136+
137+
CREATE FUNCTION get_log(usename text) RETURNS
138+
table(
139+
cron int,
140+
node text,
141+
scheduled_at timestamp with time zone,
142+
name text,
143+
comments text,
144+
commands text[],
145+
run_as text,
146+
owner text,
147+
use_same_transaction boolean,
148+
started timestamp with time zone,
149+
last_start_available timestamp with time zone,
150+
finished timestamp with time zone,
151+
max_run_time interval,
152+
onrollback text,
153+
next_time_statement text,
154+
max_instances integer,
155+
status @extschema@.job_status_t,
156+
message text
157+
)
158+
AS
159+
$BODY$
160+
SELECT * FROM @extschema@.all_jobs_log where owner = usename;
161+
$BODY$
162+
LANGUAGE sql STABLE;
163+
164+
165+
166+
CREATE FUNCTION get_log() RETURNS
167+
table(
168+
cron int,
169+
node text,
170+
scheduled_at timestamp with time zone,
171+
name text,
172+
comments text,
173+
commands text[],
174+
run_as text,
175+
owner text,
176+
use_same_transaction boolean,
177+
started timestamp with time zone,
178+
last_start_available timestamp with time zone,
179+
finished timestamp with time zone,
180+
max_run_time interval,
181+
onrollback text,
182+
next_time_statement text,
183+
max_instances integer,
184+
status @extschema@.job_status_t,
185+
message text
186+
)
187+
AS
188+
$BODY$
189+
SELECT * FROM @extschema@.all_jobs_log;
190+
$BODY$
191+
LANGUAGE sql STABLE;
192+
193+
194+
CREATE FUNCTION get_user_log() RETURNS
195+
table(
196+
cron int,
197+
node text,
198+
scheduled_at timestamp with time zone,
199+
name text,
200+
comments text,
201+
commands text[],
202+
run_as text,
203+
owner text,
204+
use_same_transaction boolean,
205+
started timestamp with time zone,
206+
last_start_available timestamp with time zone,
207+
finished timestamp with time zone,
208+
max_run_time interval,
209+
onrollback text,
210+
next_time_statement text,
211+
max_instances integer,
212+
status @extschema@.job_status_t,
213+
message text
214+
)
215+
AS
216+
$BODY$
217+
SELECT * FROM @extschema@.jobs_log;
218+
$BODY$
219+
LANGUAGE sql STABLE;
220+
221+
GRANT SELECT ON @extschema@.jobs_log TO public;
222+
223+
ALTER TABLE @extschema@.at ADD PRIMARY KEY (start_at, cron);
224+
ALTER TABLE @extschema@.log ADD PRIMARY KEY (start_at, cron);
225+
226+
227+
DROP FUNCTION _get_array_from_jsonb(text[], jsonb);
228+
229+
CREATE FUNCTION _get_array_from_jsonb(dst text[], src jsonb) RETURNS text[] AS
230+
$BODY$
231+
DECLARE
232+
vtype text;
233+
BEGIN
234+
IF src IS NULL THEN
235+
RETURN dst;
236+
END IF;
237+
238+
SELECT INTO vtype jsonb_typeof(src);
67239

68-
GRANT SELECT ON schedule.job_status TO public;
240+
IF vtype = 'string' THEN
241+
SELECT INTO dst array_append(dst, src->>0);
242+
ELSIF vtype = 'array' THEN
243+
SELECT INTO dst dst || array_agg(value)::text[] from jsonb_array_elements_text(src);
244+
ELSE
245+
RAISE EXCEPTION 'The value could be only ''string'' or ''array'' type';
246+
END IF;
69247

70-
RESET search_path;
248+
RETURN dst;
249+
END
250+
$BODY$
251+
LANGUAGE plpgsql set search_path TO @extschema@;

0 commit comments

Comments
 (0)