root/trunk/autonomous_logging_tool/autonomous_logging_tool.install.sql

Revision 3, 5.5 kB (checked in by robert, 7 years ago)

autonomous logging tool, uses dblink

Line 
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_details (
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_details_step_id_seq') INTO v_step_id;
79
80     INSERT INTO alt.job_details (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_details 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
Note: See TracBrowser for help on using the browser.