1 |
START TRANSACTION; |
---|
2 |
|
---|
3 |
CREATE SCHEMA alt; |
---|
4 |
|
---|
5 |
create table alt.job_log ( |
---|
6 |
job_id bigserial primary key, |
---|
7 |
owner text not null, |
---|
8 |
job_name text not null, |
---|
9 |
start_time timestamp not null, |
---|
10 |
end_time timestamp, |
---|
11 |
status text, |
---|
12 |
pid integer not null |
---|
13 |
); |
---|
14 |
|
---|
15 |
create index job_log_job_name_idx on alt.job_log(job_name); |
---|
16 |
create index job_log_start_time_idx on alt.job_log(start_time); |
---|
17 |
|
---|
18 |
create table alt.job_detail ( |
---|
19 |
job_id bigint not null references alt.job_log(job_id), |
---|
20 |
step_id bigserial not null, |
---|
21 |
action text not null, |
---|
22 |
start_time timestamp not null, |
---|
23 |
end_time timestamp, |
---|
24 |
elapsed_time integer, |
---|
25 |
status text, |
---|
26 |
message text, |
---|
27 |
PRIMARY KEY (job_id, step_id) |
---|
28 |
); |
---|
29 |
|
---|
30 |
-- procedures |
---|
31 |
|
---|
32 |
CREATE OR REPLACE FUNCTION alt._autonomous_add_job (in p_owner text, in p_job_name text, p_pid integer) |
---|
33 |
RETURNS integer |
---|
34 |
AS $$ |
---|
35 |
DECLARE |
---|
36 |
v_job_id INTEGER; |
---|
37 |
BEGIN |
---|
38 |
SELECT nextval('alt.job_log_job_id_seq') INTO v_job_id; |
---|
39 |
|
---|
40 |
INSERT INTO alt.job_log (job_id, owner, job_name, start_time, pid) |
---|
41 |
VALUES (v_job_id, p_owner, p_job_name, current_timestamp, p_pid); |
---|
42 |
|
---|
43 |
RETURN v_job_id; |
---|
44 |
END |
---|
45 |
$$ LANGUAGE plpgsql; |
---|
46 |
|
---|
47 |
|
---|
48 |
CREATE OR REPLACE FUNCTION alt.add_job (in p_job_name text) |
---|
49 |
RETURNS integer |
---|
50 |
AS $$ |
---|
51 |
DECLARE |
---|
52 |
v_job_id INTEGER; |
---|
53 |
v_remote_query TEXT; |
---|
54 |
BEGIN |
---|
55 |
v_remote_query := 'SELECT alt._autonomous_add_job (' || |
---|
56 |
quote_literal(current_user) || ',' || |
---|
57 |
quote_literal(p_job_name) || ',' || |
---|
58 |
pg_backend_pid() || ')'; |
---|
59 |
|
---|
60 |
EXECUTE 'SELECT job_id FROM dblink.dblink(''dbname='|| current_database() || |
---|
61 |
''','|| quote_literal(v_remote_query) || ',TRUE) t (job_id int)' INTO v_job_id; |
---|
62 |
|
---|
63 |
IF v_job_id IS NULL THEN |
---|
64 |
RAISE EXCEPTION 'Job creation failed'; |
---|
65 |
END IF; |
---|
66 |
|
---|
67 |
RETURN v_job_id; |
---|
68 |
END |
---|
69 |
$$ LANGUAGE plpgsql; |
---|
70 |
|
---|
71 |
|
---|
72 |
CREATE OR REPLACE FUNCTION alt._autonomous_add_step (in p_job_id integer, in p_action text) |
---|
73 |
RETURNS integer |
---|
74 |
AS $$ |
---|
75 |
DECLARE |
---|
76 |
v_step_id INTEGER; |
---|
77 |
BEGIN |
---|
78 |
SELECT nextval('alt.job_detail_step_id_seq') INTO v_step_id; |
---|
79 |
|
---|
80 |
INSERT INTO alt.job_detail (job_id, step_id, action, start_time) |
---|
81 |
VALUES (p_job_id, v_step_id, p_action, current_timestamp); |
---|
82 |
|
---|
83 |
RETURN v_step_id; |
---|
84 |
END |
---|
85 |
$$ LANGUAGE plpgsql; |
---|
86 |
|
---|
87 |
CREATE OR REPLACE FUNCTION alt.add_step (in p_job_id integer, in p_action text) |
---|
88 |
RETURNS integer |
---|
89 |
AS $$ |
---|
90 |
DECLARE |
---|
91 |
v_step_id INTEGER; |
---|
92 |
v_remote_query TEXT; |
---|
93 |
BEGIN |
---|
94 |
v_remote_query := 'SELECT alt._autonomous_add_step (' || |
---|
95 |
p_job_id || ',' || |
---|
96 |
quote_literal(p_action) || ')'; |
---|
97 |
|
---|
98 |
EXECUTE 'SELECT step_id FROM dblink.dblink(''dbname='|| current_database() || |
---|
99 |
''','|| quote_literal(v_remote_query) || ',TRUE) t (step_id int)' INTO v_step_id; |
---|
100 |
|
---|
101 |
IF v_step_id IS NULL THEN |
---|
102 |
RAISE EXCEPTION 'Job creation failed'; |
---|
103 |
END IF; |
---|
104 |
|
---|
105 |
RETURN v_step_id; |
---|
106 |
END |
---|
107 |
$$ LANGUAGE plpgsql; |
---|
108 |
|
---|
109 |
|
---|
110 |
CREATE OR REPLACE FUNCTION alt._autonomous_upd_step (in p_job_id integer, in p_step_id integer, in p_status text, in p_message text) |
---|
111 |
RETURNS integer |
---|
112 |
AS $$ |
---|
113 |
DECLARE |
---|
114 |
v_numrows integer; |
---|
115 |
BEGIN |
---|
116 |
UPDATE alt.job_detail SET |
---|
117 |
end_time = current_timestamp, |
---|
118 |
elapsed_time = date_part('epoch',now() - start_time)::integer, |
---|
119 |
status = p_status, |
---|
120 |
message = p_message |
---|
121 |
WHERE job_id = p_job_id AND step_id = p_step_id; |
---|
122 |
GET DIAGNOSTICS v_numrows = ROW_COUNT; |
---|
123 |
RETURN v_numrows; |
---|
124 |
END |
---|
125 |
$$ LANGUAGE plpgsql; |
---|
126 |
|
---|
127 |
CREATE OR REPLACE FUNCTION alt.upd_step (in p_job_id integer, in p_step_id integer, in p_status text, in p_message text) |
---|
128 |
RETURNS void |
---|
129 |
AS $$ |
---|
130 |
DECLARE |
---|
131 |
v_remote_query TEXT; |
---|
132 |
BEGIN |
---|
133 |
v_remote_query := 'SELECT alt._autonomous_upd_step ('|| |
---|
134 |
p_job_id || ',' || |
---|
135 |
p_step_id || ',' || |
---|
136 |
quote_literal(p_status) || ',' || |
---|
137 |
quote_literal(p_message) || ')'; |
---|
138 |
|
---|
139 |
EXECUTE 'SELECT devnull FROM dblink.dblink(''dbname=' || current_database() || |
---|
140 |
''','|| quote_literal(v_remote_query) || ',TRUE) t (devnull int)'; |
---|
141 |
END |
---|
142 |
$$ LANGUAGE plpgsql; |
---|
143 |
|
---|
144 |
|
---|
145 |
CREATE OR REPLACE FUNCTION alt._autonomous_close_job (in p_job_id integer) |
---|
146 |
RETURNS integer |
---|
147 |
AS $$ |
---|
148 |
DECLARE |
---|
149 |
v_numrows integer; |
---|
150 |
BEGIN |
---|
151 |
UPDATE alt.job_log SET |
---|
152 |
end_time = current_timestamp, |
---|
153 |
status = 'OK' |
---|
154 |
WHERE job_id = p_job_id; |
---|
155 |
GET DIAGNOSTICS v_numrows = ROW_COUNT; |
---|
156 |
RETURN v_numrows; |
---|
157 |
END |
---|
158 |
$$ LANGUAGE plpgsql; |
---|
159 |
|
---|
160 |
CREATE OR REPLACE FUNCTION alt.close_job (in p_job_id integer) |
---|
161 |
RETURNS void |
---|
162 |
AS $$ |
---|
163 |
DECLARE |
---|
164 |
v_remote_query TEXT; |
---|
165 |
BEGIN |
---|
166 |
v_remote_query := 'SELECT alt._autonomous_close_job('||p_job_id||')'; |
---|
167 |
|
---|
168 |
EXECUTE 'SELECT devnull FROM dblink.dblink(''dbname=' || current_database() || |
---|
169 |
''',' || quote_literal(v_remote_query) || ',TRUE) t (devnull int)'; |
---|
170 |
END |
---|
171 |
$$ LANGUAGE plpgsql; |
---|
172 |
|
---|
173 |
|
---|
174 |
CREATE OR REPLACE FUNCTION alt._autonomous_fail_job (in p_job_id integer) |
---|
175 |
RETURNS integer |
---|
176 |
AS $$ |
---|
177 |
DECLARE |
---|
178 |
v_numrows integer; |
---|
179 |
BEGIN |
---|
180 |
UPDATE alt.job_log SET |
---|
181 |
end_time = current_timestamp, |
---|
182 |
status = 'BAD' |
---|
183 |
WHERE job_id = p_job_id; |
---|
184 |
GET DIAGNOSTICS v_numrows = ROW_COUNT; |
---|
185 |
RETURN v_numrows; |
---|
186 |
END |
---|
187 |
$$ LANGUAGE plpgsql; |
---|
188 |
|
---|
189 |
CREATE OR REPLACE FUNCTION alt.fail_job (in p_job_id integer) |
---|
190 |
RETURNS void |
---|
191 |
AS $$ |
---|
192 |
DECLARE |
---|
193 |
v_remote_query TEXT; |
---|
194 |
BEGIN |
---|
195 |
v_remote_query := 'SELECT alt._autonomous_fail_job('||p_job_id||')'; |
---|
196 |
|
---|
197 |
EXECUTE 'SELECT devnull FROM dblink.dblink(''dbname=' || current_database() || |
---|
198 |
''',' || quote_literal(v_remote_query) || ',TRUE) t (devnull int)'; |
---|
199 |
|
---|
200 |
END |
---|
201 |
$$ LANGUAGE plpgsql; |
---|
202 |
|
---|
203 |
|
---|
204 |
CREATE OR REPLACE FUNCTION alt._autonomous_cancel_job (in p_job_id integer) |
---|
205 |
RETURNS integer |
---|
206 |
AS $$ |
---|
207 |
DECLARE |
---|
208 |
p_pid INTEGER; |
---|
209 |
BEGIN |
---|
210 |
SELECT pid FROM alt.job_logs WHERE job_id = p_job_id INTO p_pid; |
---|
211 |
SELECT pg_cancel_backend(p_pid); |
---|
212 |
SELECT alt._autonomous_fail_job(p_job_id); |
---|
213 |
END |
---|
214 |
$$ LANGUAGE plpgsql; |
---|
215 |
|
---|
216 |
END; |
---|
217 |
|
---|