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

Commit21cdd44

Browse files
committed
Merge branch 'PGPROEE9_6_scheduler' into PGPROEE9_6
2 parentsf1faa89 +fcc5f08 commit21cdd44

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
DROPVIEW job_status;
42
DROPVIEW all_job_status;
53

@@ -15,7 +13,7 @@ CREATE VIEW job_status AS
1513
max_run_timeas max_duration, submit_time, canceled,
1614
start_time, statusas is_success, reasonas error, done_time,
1715
'done'::job_at_status_t status
18-
FROMschedule.at_jobs_donewhere owner=session_user
16+
FROM@extschema@.at_jobs_donewhere owner=session_user
1917
UNION
2018
SELECT
2119
id, node, name, comments, atas run_after,
@@ -24,7 +22,7 @@ CREATE VIEW job_status AS
2422
max_run_timeas max_duration, submit_time, canceled, start_time,
2523
NULLas is_success,NULLas error,NULLas done_time,
2624
'processing'::job_at_status_t status
27-
FROM ONLYschedule.at_jobs_processwhere owner=session_user
25+
FROM ONLY@extschema@.at_jobs_processwhere owner=session_user
2826
UNION
2927
SELECT
3028
id, node, name, comments, atas run_after,
@@ -34,7 +32,7 @@ CREATE VIEW job_status AS
3432
NULLas start_time,NULLas is_success,NULLas error,
3533
NULLas done_time,
3634
'submitted'::job_at_status_t status
37-
FROM ONLYschedule.at_jobs_submittedwhere owner=session_user;
35+
FROM ONLY@extschema@.at_jobs_submittedwhere owner=session_user;
3836

3937
CREATEVIEWall_job_statusAS
4038
SELECT
@@ -44,7 +42,7 @@ CREATE VIEW all_job_status AS
4442
max_run_timeas max_duration, submit_time, canceled,
4543
start_time, statusas is_success, reasonas error, done_time,
4644
'done'::job_at_status_t status
47-
FROMschedule.at_jobs_done
45+
FROM@extschema@.at_jobs_done
4846
UNION
4947
SELECT
5048
id, node, name, comments, atas run_after,
@@ -53,7 +51,7 @@ CREATE VIEW all_job_status AS
5351
max_run_timeas max_duration, submit_time, canceled, start_time,
5452
NULLas is_success,NULLas error,NULLas done_time,
5553
'processing'::job_at_status_t status
56-
FROM ONLYschedule.at_jobs_process
54+
FROM ONLY@extschema@.at_jobs_process
5755
UNION
5856
SELECT
5957
id, node, name, comments, atas run_after,
@@ -63,8 +61,191 @@ CREATE VIEW all_job_status AS
6361
NULLas start_time,NULLas is_success,NULLas error,
6462
NULLas done_time,
6563
'submitted'::job_at_status_t status
66-
FROM ONLYschedule.at_jobs_submitted;
64+
FROM ONLY @extschema@.at_jobs_submitted;
65+
66+
GRANTSELECTON @extschema@.job_status TO public;
67+
68+
DROPFUNCTION get_log();
69+
DROPFUNCTION get_log(text);
70+
DROPFUNCTION get_user_log();
71+
72+
73+
CREATEINDEXON cron (owner);
74+
CREATEINDEXON cron (executor);
75+
76+
--
77+
-- show all scheduled jobs
78+
--
79+
CREATEVIEWall_jobs_logAS
80+
SELECT
81+
coalesce(c.id,l.cron)as cron,
82+
c.nodeas node,
83+
l.start_atas scheduled_at,
84+
coalesce(c.name,'--DELETED--')as name,
85+
c.commentsas comments,
86+
c.do_sqlas commands,
87+
c.executoras run_as,
88+
c.owneras owner,
89+
c.same_transactionas use_same_transaction,
90+
l.startedas started,
91+
l.last_start_availableas last_start_available,
92+
l.finishedas finished,
93+
c.max_run_timeas max_run_time,
94+
c.onrollback_statementas onrollback,
95+
c.next_time_statementas next_time_statement,
96+
c.max_instancesas max_instances,
97+
CASE WHENl.status THEN
98+
'done'::@extschema@.job_status_t
99+
ELSE
100+
'error'::@extschema@.job_status_t
101+
ENDas status,
102+
l.messageas message
103+
104+
FROM @extschema@.logas lLEFT OUTER JOIN @extschema@.cronas cONc.id=l.cron;
105+
106+
--
107+
-- show scheduled jobs of session user
108+
--
109+
110+
CREATEVIEWjobs_logAS
111+
SELECT
112+
coalesce(c.id,l.cron)as cron,
113+
c.nodeas node,
114+
l.start_atas scheduled_at,
115+
coalesce(c.name,'--DELETED--')as name,
116+
c.commentsas comments,
117+
c.do_sqlas commands,
118+
c.executoras run_as,
119+
c.owneras owner,
120+
c.same_transactionas use_same_transaction,
121+
l.startedas started,
122+
l.last_start_availableas last_start_available,
123+
l.finishedas finished,
124+
c.max_run_timeas max_run_time,
125+
c.onrollback_statementas onrollback,
126+
c.next_time_statementas next_time_statement,
127+
c.max_instancesas max_instances,
128+
CASE WHENl.status THEN
129+
'done'::@extschema@.job_status_t
130+
ELSE
131+
'error'::@extschema@.job_status_t
132+
ENDas status,
133+
l.messageas message
134+
FROM logas l, cronas cWHEREc.executor=session_userANDc.id=l.cron;
135+
136+
137+
CREATEFUNCTIONget_log(usenametext) RETURNS
138+
table(
139+
cronint,
140+
nodetext,
141+
scheduled_attimestamp with time zone,
142+
nametext,
143+
commentstext,
144+
commandstext[],
145+
run_astext,
146+
ownertext,
147+
use_same_transactionboolean,
148+
startedtimestamp with time zone,
149+
last_start_availabletimestamp with time zone,
150+
finishedtimestamp with time zone,
151+
max_run_time interval,
152+
onrollbacktext,
153+
next_time_statementtext,
154+
max_instancesinteger,
155+
status @extschema@.job_status_t,
156+
messagetext
157+
)
158+
AS
159+
$BODY$
160+
SELECT*FROM @extschema@.all_jobs_logwhere owner= usename;
161+
$BODY$
162+
LANGUAGE sql STABLE;
163+
164+
165+
166+
CREATEFUNCTIONget_log() RETURNS
167+
table(
168+
cronint,
169+
nodetext,
170+
scheduled_attimestamp with time zone,
171+
nametext,
172+
commentstext,
173+
commandstext[],
174+
run_astext,
175+
ownertext,
176+
use_same_transactionboolean,
177+
startedtimestamp with time zone,
178+
last_start_availabletimestamp with time zone,
179+
finishedtimestamp with time zone,
180+
max_run_time interval,
181+
onrollbacktext,
182+
next_time_statementtext,
183+
max_instancesinteger,
184+
status @extschema@.job_status_t,
185+
messagetext
186+
)
187+
AS
188+
$BODY$
189+
SELECT*FROM @extschema@.all_jobs_log;
190+
$BODY$
191+
LANGUAGE sql STABLE;
192+
193+
194+
CREATEFUNCTIONget_user_log() RETURNS
195+
table(
196+
cronint,
197+
nodetext,
198+
scheduled_attimestamp with time zone,
199+
nametext,
200+
commentstext,
201+
commandstext[],
202+
run_astext,
203+
ownertext,
204+
use_same_transactionboolean,
205+
startedtimestamp with time zone,
206+
last_start_availabletimestamp with time zone,
207+
finishedtimestamp with time zone,
208+
max_run_time interval,
209+
onrollbacktext,
210+
next_time_statementtext,
211+
max_instancesinteger,
212+
status @extschema@.job_status_t,
213+
messagetext
214+
)
215+
AS
216+
$BODY$
217+
SELECT*FROM @extschema@.jobs_log;
218+
$BODY$
219+
LANGUAGE sql STABLE;
220+
221+
GRANTSELECTON @extschema@.jobs_log TO public;
222+
223+
ALTERTABLE @extschema@.at ADDPRIMARY KEY (start_at, cron);
224+
ALTERTABLE @extschema@.log ADDPRIMARY KEY (start_at, cron);
225+
226+
227+
DROPFUNCTION _get_array_from_jsonb(text[], jsonb);
228+
229+
CREATEFUNCTION_get_array_from_jsonb(dsttext[], src jsonb) RETURNStext[]AS
230+
$BODY$
231+
DECLARE
232+
vtypetext;
233+
BEGIN
234+
IF src ISNULL THEN
235+
RETURN dst;
236+
END IF;
237+
238+
SELECT INTO vtype jsonb_typeof(src);
67239

68-
GRANTSELECTONschedule.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 plpgsqlset search_path TO @extschema@;

0 commit comments

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