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

Commit1b8cff0

Browse files
author
Vladimir Ershov
committed
more accurate with schema && get_log from view
1 parentc24a589 commit1b8cff0

File tree

3 files changed

+434
-246
lines changed

3 files changed

+434
-246
lines changed

‎pgpro_scheduler--2.0--2.1.sql

Lines changed: 165 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,165 @@ 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;
67222

68-
GRANTSELECTONschedule.job_status TO public;
223+
ALTERTABLE @extschema@.at ADDPRIMARY KEY (start_at, cron);
224+
ALTERTABLE @extschema@.log ADDPRIMARY KEY (start_at, cron);
69225

70-
RESET search_path;

0 commit comments

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