1 
BEGIN; 

2 


3 
 Tables 

4 


5 
CREATE TABLE stratcon.loading_dock_check_s ( 

6 
sid integer NOT NULL, 

7 
remote_address inet, 

8 
whence timestamptz NOT NULL, 

9 
id uuid NOT NULL, 

10 
target text NOT NULL, 

11 
module text NOT NULL, 

12 
name text NOT NULL, 

13 
PRIMARY KEY(sid,id,whence) 

14 
); 

15 


16 
CREATE TABLE stratcon.loading_dock_status_s ( 

17 
sid integer NOT NULL, 

18 
whence timestamptz NOT NULL, 

19 
state character(1) NOT NULL, 

20 
availability character(1) NOT NULL, 

21 
duration integer NOT NULL, 

22 
status text, 

23 
PRIMARY KEY(sid,whence) 

24 
); 

25 


26 
CREATE TABLE stratcon.loading_dock_status_s_change_log ( 

27 
sid integer NOT NULL, 

28 
whence timestamptz NOT NULL, 

29 
state character(1) NOT NULL, 

30 
availability character(1) NOT NULL, 

31 
duration integer NOT NULL, 

32 
status text, 

33 
PRIMARY KEY(sid,whence) 

34 
); 

35 


36 


37 
CREATE TABLE stratcon.loading_dock_metric_text_s ( 

38 
sid integer NOT NULL, 

39 
whence timestamptz NOT NULL, 

40 
name text NOT NULL, 

41 
value text, 

42 
PRIMARY KEY(whence,sid,name) 

43 
); 

44 


45 
CREATE TABLE stratcon.loading_dock_metric_text_s_change_log ( 

46 
sid integer NOT NULL, 

47 
whence timestamptz NOT NULL, 

48 
name text NOT NULL, 

49 
value text, 

50 
PRIMARY KEY(whence,sid,name) 

51 
); 

52 


53 
CREATE TABLE stratcon.loading_dock_metric_numeric_s ( 

54 
sid integer NOT NULL, 

55 
whence timestamptz NOT NULL, 

56 
name text NOT NULL, 

57 
value numeric, 

58 
PRIMARY KEY(whence,sid,name) 

59 
); 

60 


61 
CREATE TABLE stratcon.rollup_matrix_numeric_5m ( 

62 
sid integer NOT NULL, 

63 
name text NOT NULL, 

64 
rollup_time timestamptz NOT NULL, 

65 
count_rows integer, 

66 
avg_value numeric, 

67 
min_value numeric, 

68 
max_value numeric, 

69 
PRIMARY KEY (rollup_time,sid,name) 

70 
); 

71 


72 
CREATE TABLE stratcon.rollup_matrix_numeric_20m ( 

73 
sid integer NOT NULL, 

74 
name text NOT NULL, 

75 
rollup_time timestamptz NOT NULL, 

76 
count_rows integer, 

77 
avg_value numeric, 

78 
min_value numeric, 

79 
max_value numeric, 

80 
PRIMARY KEY (rollup_time,sid,name) 

81 
); 

82 


83 
CREATE OR REPLACE FUNCTION stratcon.date_hour(timestamptz) 

84 
RETURNS timestamptz as $BODY$ 

85 
SELECT date_trunc('hour',$1); 

86 
$BODY$ 

87 
language 'sql' 

88 
IMMUTABLE STRICT; 

89 


90 
CREATE INDEX idx_rollup_matrix_numeric_20m_rollup_time 

91 
ON stratcon.rollup_matrix_numeric_20m(date_hour(rollup_time)); 

92 


93 
CREATE TABLE stratcon.rollup_matrix_numeric_60m( 

94 
sid integer not null, 

95 
name text not null, 

96 
rollup_time timestamptz not null, 

97 
count_rows integer, 

98 
avg_value numeric , 

99 
min_value numeric , 

100 
max_value numeric , 

101 
PRIMARY KEY(rollup_time,sid,name)); 

102 


103 
CREATE TABLE stratcon.rollup_matrix_numeric_6hours( 

104 
sid integer not null, 

105 
name text not null, 

106 
rollup_time timestamptz not null, 

107 
count_rows integer, 

108 
avg_value numeric , 

109 
min_value numeric , 

110 
max_value numeric , 

111 
PRIMARY KEY(rollup_time6,sid,name)); 

112 


113 
CREATE TABLE stratcon.rollup_matrix_numeric_12hours( 

114 
sid integer not null, 

115 
name text not null, 

116 
rollup_time timestamptz not null, 

117 
count_rows integer, 

118 
avg_value numeric , 

119 
min_value numeric , 

120 
max_value numeric , 

121 
PRIMARY KEY(rollup_time12,sid,name)); 

122 


123 
CREATE TABLE stratcon.map_uuid_to_sid ( 

124 
id uuid NOT NULL, 

125 
sid integer NOT NULL, 

126 
PRIMARY KEY(id,sid) 

127 
); 

128 


129 
CREATE TABLE stratcon.log_whence_s ( 

130 
whence timestamptz NOT NULL, 

131 
interval varchar2(20, 

132 
PRIMARY KEY(whence,interval) 

133 
); 

134 


135 
CREATE TABLE stratcon.rollup_runner ( 

136 
rollup_table character varying(100), 

137 
runner character varying(22) 

138 
); 

139 


140 
CREATE TABLE stratcon.metric_name_summary ( 

141 
sid integer NOT NULL, 

142 
metric_name text NOT NULL, 

143 
metric_type character varying(22), 

144 
active boolean default 'true', 

145 
PRIMARY KEY (sid,name) 

146 
); 

147 
 Schema Sequence 

148 


149 
CREATE SEQUENCE stratcon.seq_sid 

150 
START WITH 1 

151 
INCREMENT BY 1 

152 
NO MAXVALUE 

153 
NO MINVALUE 

154 
CACHE 1; 

155 


156 


157 


158 
 GRANTS 

159 


160 
GRANT SELECT,INSERT ON stratcon.loading_dock_status_s TO stratcon; 

161 
GRANT SELECT,INSERT,DELETE ON stratcon.loading_dock_status_s_change_log TO stratcon; 

162 
GRANT SELECT,INSERT ON stratcon.loading_dock_check_s TO stratcon; 

163 
GRANT SELECT,INSERT,DELETE ON stratcon.loading_dock_metric_numeric_s TO stratcon; 

164 
GRANT SELECT,INSERT,DELETE ON stratcon.loading_dock_metric_text_s_change_log TO stratcon; 

165 
GRANT SELECT,INSERT,DELETE ON stratcon.log_whence_s TO stratcon; 

166 
GRANT SELECT,INSERT,DELETE ON stratcon.loading_dock_metric_text_s TO stratcon; 

167 
GRANT SELECT,INSERT,DELETE ON stratcon.rollup_matrix_numeric_60m TO stratcon; 

168 
GRANT SELECT,INSERT,DELETE ON stratcon.rollup_matrix_numeric_5m TO stratcon; 

169 
GRANT SELECT,INSERT,DELETE ON stratcon.rollup_matrix_numeric_20m TO stratcon; 

170 
GRANT SELECT,INSERT,DELETE ON stratcon.rollup_matrix_numeric_6hours TO stratcon; 

171 
GRANT SELECT,INSERT,DELETE ON stratcon.rollup_matrix_numeric_12hours TO stratcon; 

172 
GRANT SELECT,INSERT ON stratcon.map_uuid_to_sid TO stratcon; 

173 
GRANT SELECT,INSERT,UPDATE,DELETE ON stratcon.rollup_runner TO stratcon; 

174 
GRANT SELECT,INSERT,UPDATE,DELETE ON stratcon.metric_name_summary TO stratcon; 

175 
ALTER TABLE stratcon.seq_sid OWNER TO stratcon; 

176 


177 


178 
 Function To generate SID from ID 

179 


180 
CREATE OR REPLACE FUNCTION stratcon.generate_sid_from_id(v_in_id uuid) 

181 
RETURNS integer 

182 
AS $$ 

183 
DECLARE 

184 
v_ex_sid integer; 

185 
v_new_sid integer; 

186 


187 
BEGIN 

188 


189 
SELECT sid FROM stratcon.map_uuid_to_sid WHERE id=v_in_id 

190 
INTO v_ex_sid; 

191 


192 
IF NOT FOUND THEN 

193 
SELECT nextval('stratcon.seq_sid') 

194 
INTO v_new_sid; 

195 


196 
INSERT INTO stratcon.map_uuid_to_sid(id,sid) VALUES (v_in_id,v_new_sid); 

197 


198 
RETURN v_new_sid; 

199 
ELSE 

200 
RETURN v_ex_sid; 

201 
END IF; 

202 


203 
END 

204 
$$ LANGUAGE plpgsql; 

205 


206 
 Trigger Function to log dock status Changes 

207 


208 
CREATE TRIGGER loading_dock_status_s_change_log 

209 
AFTER INSERT ON stratcon.loading_dock_status_s 

210 
FOR EACH ROW 

211 
EXECUTE PROCEDURE stratcon.loading_dock_status_s_change_log(); 

212 


213 


214 
CREATE OR REPLACE FUNCTION stratcon.loading_dock_status_s_change_log() RETURNS trigger 

215 
AS $$ 

216 
DECLARE 

217 
v_state CHAR(1); 

218 
v_avail CHAR(1); 

219 
BEGIN 

220 


221 
IF TG_OP = 'INSERT' THEN 

222 
SELECT state,availability FROM stratcon.loading_dock_status_s WHERE sid = NEW.sid 

223 
AND WHENCE = (SELECT max(whence) FROM stratcon.loading_dock_metric_text_s_change_log 

224 
WHERE SID=NEW.sid and WHENCE <> NEW.whence ) 

225 
INTO v_state,v_avail; 

226 


227 
IF v_state IS DISTINCT FROM NEW.state OR v_avail IS DISTINCT FROM NEW.availability THEN 

228 


229 
INSERT INTO stratcon.loading_dock_status_s_change_log (sid,whence,state,availability,duration,status) 

230 
VALUES (NEW.sid,NEW.whence,NEW.state,NEW.availability,NEW.duration,NEW.status); 

231 


232 
END IF; 

233 


234 
ELSE 

235 
RAISE EXCEPTION 'Something wrong with stratcon.loading_dock_status_s_change_log'; 

236 
END IF; 

237 


238 
RETURN NULL; 

239 


240 
END 

241 
$$ 

242 
LANGUAGE plpgsql; 

243 


244 


245 
 Trigger Function to log Metrix Text Changes 

246 


247 
CREATE TRIGGER loading_dock_metric_text_s_change_log 

248 
AFTER INSERT ON stratcon.loading_dock_metric_text_s 

249 
FOR EACH ROW 

250 
EXECUTE PROCEDURE stratcon.loading_dock_metric_text_s_change_log(); 

251 


252 


253 
CREATE OR REPLACE FUNCTION stratcon.loading_dock_metric_text_s_change_log() RETURNS trigger 

254 
AS $$ 

255 
DECLARE 

256 
v_oldvalue TEXT; 

257 
v_sid integer; 

258 
v_name text; 

259 
BEGIN 

260 


261 
IF TG_OP = 'INSERT' THEN 

262 
SELECT value FROM stratcon.loading_dock_metric_text_s WHERE sid = NEW.sid AND name = NEW.name 

263 
AND WHENCE = (SELECT max(whence) FROM stratcon.loading_dock_metric_text_s_change_log 

264 
WHERE WHENCE <> NEW.WHENCE and sid=NEW.sid and name=NEW.name ) 

265 
INTO v_oldvalue; 

266 


267 
IF v_oldvalue IS DISTINCT FROM NEW.value THEN 

268 


269 
INSERT INTO stratcon.loading_dock_metric_text_s_change_log (sid,whence,name,value) 

270 
VALUES (NEW.sid, NEW.whence, NEW.name, NEW.value); 

271 
END IF; 

272 


273 
SELECT sid,metric_name FROM stratcon.metric_name_summary WHERE sid=NEW.sid and metric_name=NEW.name 

274 
INTO v_sid,v_name; 

275 
IF NOT FOUND THEN 

276 
INSERT INTO stratcon.metric_name_summary(sid,metric_name,metric_type) VALUES(NEW.sid,NEW.name,'text'); 

277 
END IF; 

278 


279 
ELSE 

280 
RAISE EXCEPTION 'something wrong with stratcon.loading_dock_metric_text_s_change_log '; 

281 
END IF; 

282 


283 
RETURN NULL; 

284 


285 
END 

286 
$$ 

287 
LANGUAGE plpgsql; 

288 


289 
 Trigger on Metrix Numeric to log last inserted timestamp 

290 


291 
CREATE OR REPLACE FUNCTION stratcon.loading_dock_metric_numeric_s_whence_log() 

292 
RETURNS trigger 

293 
AS $$ 

294 
DECLARE 

295 
v_whence timestamptz; 

296 
v_sid integer; 

297 
v_name text; 

298 
BEGIN 

299 
IF TG_OP = 'INSERT' THEN 

300 
SELECT whence FROM stratcon.log_whence_s WHERE whence=date_trunc('H',NEW.WHENCE) + (round(extract('minute' from NEW.WHENCE)/5)*5) * '1 minute'::interval and interval='5 minutes' 

301 
INTO v_whence; 

302 
IF NOT FOUND THEN 

303 
INSERT INTO stratcon.log_whence_s VALUES(date_trunc('H',NEW.WHENCE) + (round(extract('minute' from NEW.WHENCE)/5)*5) * '1 minute'::interval,'5 minutes'); 

304 
END IF; 

305 


306 
SELECT sid,metric_name FROM stratcon.metric_name_summary WHERE sid=NEW.sid and metric_name=NEW.name 

307 
INTO v_sid,v_name; 

308 
IF NOT FOUND THEN 

309 
INSERT INTO stratcon.metric_name_summary VALUES(NEW.sid,NEW.name,'numeric'); 

310 
END IF; 

311 


312 
END IF; 

313 


314 
RETURN NULL; 

315 
END 

316 
$$ 

317 
LANGUAGE plpgsql; 

318 


319 
 5 minutes rollup 

320 


321 
CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_5m() 

322 
RETURNS void 

323 
AS $$ 

324 
DECLARE 

325 


326 
rec stratcon.rollup_matrix_numeric_5m%rowtype; 

327 
v_sql TEXT; 

328 
v_min_whence TIMESTAMPTZ; 

329 
v_max_rollup_5 TIMESTAMPTZ; 

330 
v_whence TIMESTAMPTZ; 

331 
rows INT; 

332 
v_nrunning INT; 

333 
v_self VARCHAR(22); 

334 
whenceint RECORD; 

335 
BEGIN 

336 


337 
SELECT COUNT(1) INTO v_nrunning 

338 
from stratcon.rollup_runner t, pg_stat_activity a 

339 
where rollup_table ='rollup_matrix_numeric_5m' 

340 
and runner = procpid  '.'  date_part('epoch',backend_start); 

341 


342 
IF v_nrunning > 0 THEN 

343 
RAISE NOTICE 'stratcon.rollup_matrix_numeric_5m already running'; 

344 
RETURN ; 

345 
END IF; 

346 


347 
SELECT INTO v_self procpid  '.'  date_part('epoch',backend_start) 

348 
FROM pg_stat_activity 

349 
WHERE procpid = pg_backend_pid(); 

350 


351 
IF v_self IS NULL THEN 

352 
RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_5m cannot selfidentify'; 

353 
END IF; 

354 


355 
v_sql = 'update stratcon.rollup_runner set runner = '''  v_self  ''' where rollup_table = ''rollup_matrix_numeric_5m'''; 

356 


357 
EXECUTE v_sql; 

358 


359 
FOR whenceint IN SELECT * FROM stratcon.log_whence_s WHERE interval='5 minutes' LOOP 

360 


361 


362 
SELECT MIN(whence) FROM stratcon.log_whence_s WHERE interval='5 minutes' 

363 
INTO v_min_whence; 

364 


365 
SELECT MAX(rollup_time) FROM stratcon.rollup_matrix_numeric_5m 

366 
INTO v_max_rollup_5; 

367 


368 
 Insert Log for 20 minutes rollup 

369 


370 
SELECT whence FROM stratcon.log_whence_s WHERE whence=date_trunc('H',v_min_whence) + (round(extract('minute' from v_min_whence)/20)*20) * '1 minute'::interval and interval='20 minutes' 

371 
INTO v_whence; 

372 
IF NOT FOUND THEN 

373 
INSERT INTO stratcon.log_whence_s VALUES(date_trunc('H',v_min_whence) + (round(extract('minute' from v_min_whence)/20)*20) * '1 minute'::interval,'20 minutes'); 

374 
END IF; 

375 


376 
IF v_min_whence <= v_max_rollup_5 THEN 

377 


378 
DELETE FROM stratcon.rollup_matrix_numeric_5m 

379 
WHERE rollup_time = v_min_whence; 

380 


381 
END IF; 

382 


383 
FOR rec IN 

384 
SELECT sid , name,v_min_whence as rollup_time, 

385 
COUNT(1) as count_rows ,AVG(value) as avg_value,MIN(value) as min_value ,MAX(value) as max_value 

386 
FROM stratcon.loading_dock_metric_numeric_s 

387 
WHERE WHENCE <= v_min_whence AND WHENCE > v_min_whence '5 minutes'::interval 

388 
GROUP BY rollup_time,sid,name 

389 


390 
LOOP 

391 


392 


393 
INSERT INTO stratcon.rollup_matrix_numeric_5m 

394 
(sid,name,rollup_time,count_rows,avg_value,min_value,max_value) VALUES 

395 
(rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.min_value,rec.max_value); 

396 


397 
END LOOP; 

398 


399 
 Delete from whence log table 

400 


401 
DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='5 minutes'; 

402 


403 
v_min_whence:= NULL; 

404 
v_max_rollup_5:= NULL; 

405 


406 
END LOOP; 

407 


408 
UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_5m'; 

409 


410 
RETURN; 

411 


412 
EXCEPTION 

413 
WHEN RAISE_EXCEPTION THEN 

414 
UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_5m'; 

415 
RAISE EXCEPTION '%', SQLERRM; 

416 
WHEN OTHERS THEN 

417 
RAISE NOTICE '%', SQLERRM; 

418 
END 

419 
$$ LANGUAGE plpgsql; 

420 


421 


422 
 20 minutes rollup 

423 


424 
CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_20m() 

425 
RETURNS void 

426 
AS $$ 

427 
DECLARE 

428 


429 
rec stratcon.rollup_matrix_numeric_20m%rowtype; 

430 
v_sql TEXT; 

431 
v_min_whence TIMESTAMPTZ; 

432 
v_max_rollup_20 TIMESTAMPTZ; 

433 
v_whence TIMESTAMPTZ; 

434 
rows INT; 

435 
v_nrunning INT; 

436 
v_self VARCHAR(22); 

437 
whenceint RECORD; 

438 
BEGIN 

439 


440 
SELECT COUNT(1) INTO v_nrunning 

441 
from stratcon.rollup_runner t, pg_stat_activity a 

442 
where rollup_table ='rollup_matrix_numeric_20m' 

443 
and runner = procpid  '.'  date_part('epoch',backend_start); 

444 


445 
IF v_nrunning > 0 THEN 

446 
RAISE NOTICE 'stratcon.rollup_matrix_numeric_20m already running'; 

447 
RETURN ; 

448 
END IF; 

449 


450 
SELECT INTO v_self procpid  '.'  date_part('epoch',backend_start) 

451 
FROM pg_stat_activity 

452 
WHERE procpid = pg_backend_pid(); 

453 


454 
IF v_self IS NULL THEN 

455 
RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_20m cannot selfidentify'; 

456 
END IF; 

457 


458 
v_sql = 'update stratcon.rollup_runner set runner = '''  v_self  ''' where rollup_table = ''rollup_matrix_numeric_20m'''; 

459 


460 
EXECUTE v_sql; 

461 


462 
FOR whenceint IN SELECT * FROM stratcon.log_whence_s WHERE interval='20 minutes' LOOP 

463 


464 
SELECT MIN(whence) FROM stratcon.log_whence_s WHERE interval='20 minutes' 

465 
INTO v_min_whence; 

466 


467 
SELECT MAX(rollup_time) FROM stratcon.rollup_matrix_numeric_20m 

468 
INTO v_max_rollup_20; 

469 


470 
 Insert Log for Hourly rollup 

471 


472 
SELECT whence FROM stratcon.log_whence_s WHERE whence=date_trunc('H',v_min_whence) and interval='1 hour' 

473 
INTO v_whence; 

474 
IF NOT FOUND THEN 

475 
INSERT INTO stratcon.log_whence_s VALUES(date_trunc('H',v_min_whence),'1 hour'); 

476 
END IF; 

477 


478 
IF v_min_whence <= v_max_rollup_20 THEN 

479 


480 
DELETE FROM stratcon.rollup_matrix_numeric_20m 

481 
WHERE rollup_time = v_min_whence; 

482 


483 
END IF; 

484 


485 
FOR rec IN 

486 
SELECT sid , name,v_min_whence as rollup_time, 

487 
SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value, 

488 
MIN(min_value) as min_value ,MAX(max_value) as max_value 

489 
FROM stratcon.rollup_matrix_numeric_5m 

490 
WHERE rollup_time<= v_min_whence AND rollup_time > v_min_whence '20 minutes'::interval 

491 
GROUP BY sid,name 

492 


493 
LOOP 

494 


495 


496 
INSERT INTO stratcon.rollup_matrix_numeric_20m 

497 
(sid,name,rollup_time,count_rows,avg_value,min_value,max_value) VALUES 

498 
(rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.min_value,rec.max_value); 

499 


500 
END LOOP; 

501 


502 
 Delete from whence log table 

503 


504 
DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='20 minutes'; 

505 


506 
v_min_whence:= NULL; 

507 
v_max_rollup_20:= NULL; 

508 


509 
END LOOP; 

510 


511 
UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_20m'; 

512 


513 
RETURN; 

514 


515 
EXCEPTION 

516 
WHEN RAISE_EXCEPTION THEN 

517 
UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_20m'; 

518 
RAISE EXCEPTION '%', SQLERRM; 

519 
WHEN OTHERS THEN 

520 
RAISE NOTICE '%', SQLERRM; 

521 
END 

522 
$$ LANGUAGE plpgsql; 

523 


524 


525 
 1 hour rollup 

526 


527 


528 
CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_60m() 

529 
RETURNS void 

530 
AS $$ 

531 
DECLARE 

532 
rec stratcon.rollup_matrix_numeric_60m%rowtype; 

533 
v_sql TEXT; 

534 
v_min_whence TIMESTAMPTZ; 

535 
v_max_rollup_60 TIMESTAMPTZ; 

536 
v_whence TIMESTAMPTZ; 

537 
v_nrunning INT; 

538 
v_self VARCHAR(22); 

539 
whenceint RECORD; 

540 
BEGIN 

541 


542 
SELECT COUNT(1) INTO v_nrunning 

543 
from stratcon.rollup_runner t, pg_stat_activity a 

544 
where rollup_table ='rollup_matrix_numeric_60m' 

545 
and runner = procpid  '.'  date_part('epoch',backend_start); 

546 


547 
IF v_nrunning > 0 THEN 

548 
RAISE NOTICE 'stratcon.rollup_matrix_numeric_60m already running'; 

549 
RETURN ; 

550 
END IF; 

551 


552 
SELECT INTO v_self procpid  '.'  date_part('epoch',backend_start) 

553 
FROM pg_stat_activity 

554 
WHERE procpid = pg_backend_pid(); 

555 


556 
IF v_self IS NULL THEN 

557 
RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_60m cannot selfidentify'; 

558 
END IF; 

559 


560 
v_sql = 'update stratcon.rollup_runner set runner = '''  v_self  ''' where rollup_table = ''rollup_matrix_numeric_60m'''; 

561 


562 
EXECUTE v_sql; 

563 


564 
FOR whenceint IN SELECT * FROM stratcon.log_whence_s WHERE interval='1 hour' LOOP 

565 


566 
SELECT min(whence) FROM stratcon.log_whence_s WHERE interval='1 hour' 

567 
INTO v_min_whence; 

568 


569 
SELECT max(date_trunc('H',rollup_time)) FROM stratcon.rollup_matrix_numeric_60m 

570 
INTO v_max_rollup_60; 

571 


572 
 Insert Log for 6 Hour rollup 

573 


574 
SELECT whence FROM stratcon.log_whence_s WHERE whence=date_trunc('day', v_min_whence) + (floor(extract('hour' from v_min_whence)/6)*6) * '1 hour'::interval and interval='6 hours' 

575 
INTO v_whence; 

576 
IF NOT FOUND THEN 

577 
INSERT INTO stratcon.log_whence_s VALUES(date_trunc('day', v_min_whence) + (floor(extract('hour' from v_min_whence)/6)*6) * '1 hour'::interval,'6 hours'); 

578 
END IF; 

579 


580 


581 
IF v_min_whence <= v_max_rollup_60 THEN 

582 


583 
DELETE FROM stratcon.rollup_matrix_numeric_60m 

584 
WHERE rollup_time= v_min_whence; 

585 


586 
END IF; 

587 


588 
FOR rec IN 

589 
SELECT sid,name,date_hour(rollup_time) as rollup_time,SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value, 

590 
MIN(min_value) as min_value ,MAX(max_value) as max_value 

591 
FROM stratcon.rollup_matrix_numeric_20m 

592 
WHERE date_hour(rollup_time)= v_min_whence 

593 
GROUP BY date_hour(rollup_time),sid,name 

594 
LOOP 

595 


596 
INSERT INTO stratcon.rollup_matrix_numeric_60m 

597 
(sid,name,rollup_time,count_rows,avg_value,min_value,max_value) VALUES 

598 
(rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.min_value,rec.max_value); 

599 


600 
END LOOP; 

601 


602 


603 
DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='1 hour'; 

604 


605 
v_min_whence := NULL; 

606 
v_max_rollup_60 := NULL; 

607 


608 
END LOOP; 

609 


610 
UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_60m'; 

611 


612 
RETURN; 

613 


614 
EXCEPTION 

615 
WHEN RAISE_EXCEPTION THEN 

616 
UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_60m'; 

617 
RAISE EXCEPTION '%', SQLERRM; 

618 
WHEN OTHERS THEN 

619 
RAISE NOTICE '%', SQLERRM; 

620 
END 

621 
$$ LANGUAGE plpgsql; 

622 


623 


624 
 6 hours 

625 


626 
CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_6hours() 

627 
RETURNS void 

628 
AS $$ 

629 
DECLARE 

630 
rec stratcon.rollup_matrix_numeric_6hours%rowtype; 

631 
v_sql TEXT; 

632 
v_min_whence TIMESTAMPTZ; 

633 
v_max_rollup_6 TIMESTAMPTZ; 

634 
v_whence TIMESTAMPTZ; 

635 
v_nrunning INT; 

636 
v_self VARCHAR(22); 

637 
whenceint RECORD; 

638 
BEGIN 

639 


640 
SELECT COUNT(1) INTO v_nrunning 

641 
from stratcon.rollup_runner t, pg_stat_activity a 

642 
where rollup_table ='rollup_matrix_numeric_6hours' 

643 
and runner = procpid  '.'  date_part('epoch',backend_start); 

644 


645 
IF v_nrunning > 0 THEN 

646 
RAISE NOTICE 'stratcon.rollup_matrix_numeric_6hours already running'; 

647 
RETURN ; 

648 
END IF; 

649 


650 
SELECT INTO v_self procpid  '.'  date_part('epoch',backend_start) 

651 
FROM pg_stat_activity 

652 
WHERE procpid = pg_backend_pid(); 

653 


654 
IF v_self IS NULL THEN 

655 
RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_6hours cannot selfidentify'; 

656 
END IF; 

657 


658 
v_sql = 'update stratcon.rollup_runner set runner = '''  v_self  ''' where rollup_table = ''rollup_matrix_numeric_6hours'''; 

659 


660 
EXECUTE v_sql; 

661 


662 
FOR whenceint IN SELECT * FROM stratcon.log_whence_s WHERE interval='6 hours' LOOP 

663 


664 
SELECT min(whence) FROM stratcon.log_whence_s WHERE interval='6 hours' 

665 
INTO v_min_whence; 

666 


667 
SELECT max(date_trunc('H',rollup_time)) FROM stratcon.rollup_matrix_numeric_6hours 

668 
INTO v_max_rollup_6; 

669 


670 
 Insert Log for 12 Hours rollup 

671 


672 
SELECT whence FROM stratcon.log_whence_s WHERE whence=date_trunc('day', v_min_whence) + (floor(extract('hour' from v_min_whence)/12)*12) * '1 hour'::interval and interval='12 hours' 

673 
INTO v_whence; 

674 
IF NOT FOUND THEN 

675 
INSERT INTO stratcon.log_whence_s VALUES(date_trunc('day', v_min_whence) + (floor(extract('hour' from v_min_whence)/12)*12) * '1 hour'::interval,'12 hours'); 

676 
END IF; 

677 


678 


679 
IF v_min_whence <= v_max_rollup_6 THEN 

680 


681 
DELETE FROM stratcon.rollup_matrix_numeric_6hours 

682 
WHERE rollup_time= v_min_whence; 

683 


684 
END IF; 

685 


686 
FOR rec IN 

687 
SELECT sid,name,v_min_whence as rollup_time,SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value, 

688 
MIN(min_value) as min_value ,MAX(max_value) as max_value 

689 
FROM stratcon.rollup_matrix_numeric_60m 

690 
WHERE rollup_time<= v_min_whence and rollup_time> v_min_whence'6 hour'::interval 

691 
GROUP BY sid,name 

692 
LOOP 

693 


694 


695 
INSERT INTO stratcon.rollup_matrix_numeric_6hours 

696 
(sid,name,rollup_time,count_rows,avg_value,min_value,max_value) VALUES 

697 
(rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.min_value,rec.max_value); 

698 


699 
END LOOP; 

700 


701 


702 
DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='6 hours'; 

703 
v_min_whence := NULL; 

704 
v_max_rollup_6 := NULL; 

705 


706 
END LOOP; 

707 


708 
UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_6hours'; 

709 


710 
RETURN; 

711 


712 
EXCEPTION 

713 
WHEN RAISE_EXCEPTION THEN 

714 
UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_6hours'; 

715 
RAISE EXCEPTION '%', SQLERRM; 

716 
WHEN OTHERS THEN 

717 
RAISE NOTICE '%', SQLERRM; 

718 
END 

719 
$$ LANGUAGE plpgsql; 

720 


721 


722 
 12 hours 

723 


724 
CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_12hours() 

725 
RETURNS void 

726 
AS $$ 

727 
DECLARE 

728 
rec stratcon.rollup_matrix_numeric_12hours%rowtype; 

729 
v_sql TEXT; 

730 
v_min_whence TIMESTAMPTZ; 

731 
v_max_rollup_12 TIMESTAMPTZ; 

732 
v_whence TIMESTAMPTZ; 

733 
v_nrunning INT; 

734 
v_self VARCHAR(22); 

735 
whenceint RECORD; 

736 


737 
BEGIN 

738 


739 
SELECT COUNT(1) INTO v_nrunning 

740 
from stratcon.rollup_runner t, pg_stat_activity a 

741 
where rollup_table ='rollup_matrix_numeric_12hours' 

742 
and runner = procpid  '.'  date_part('epoch',backend_start); 

743 


744 
IF v_nrunning > 0 THEN 

745 
RAISE NOTICE 'stratcon.rollup_matrix_numeric_12hours already running'; 

746 
RETURN ; 

747 
END IF; 

748 


749 
SELECT INTO v_self procpid  '.'  date_part('epoch',backend_start) 

750 
FROM pg_stat_activity 

751 
WHERE procpid = pg_backend_pid(); 

752 


753 
IF v_self IS NULL THEN 

754 
RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_12hours cannot selfidentify'; 

755 
END IF; 

756 


757 
v_sql = 'update stratcon.rollup_runner set runner = '''  v_self  ''' where rollup_table = ''rollup_matrix_numeric_12hours'''; 

758 


759 
EXECUTE v_sql; 

760 


761 
FOR whenceint IN SELECT * FROM stratcon.log_whence_s WHERE interval='12 hours' LOOP 

762 


763 
SELECT min(whence) FROM stratcon.log_whence_s WHERE interval='12 hours' 

764 
INTO v_min_whence; 

765 


766 
SELECT max(date_trunc('H',rollup_time)) FROM stratcon.rollup_matrix_numeric_12hours 

767 
INTO v_max_rollup_12; 

768 


769 
/* Insert Log for 24 Hours rollup 

770 


771 
SELECT whence FROM stratcon.log_whence_s WHERE whence=date_trunc('day', v_min_whence) + (floor(extract('hour' from v_min_whence)/24)*24) * '1 hour'::interval and interval='24 hours' 

772 
INTO v_whence; 

773 
IF NOT FOUND THEN 

774 
INSERT INTO stratcon.log_whence_s VALUES(date_trunc('day', v_min_whence) + (floor(extract('hour' from v_min_whence)/24)*24) * '1 hour'::interval,'24 hours'); 

775 
END IF; 

776 
*/ 

777 


778 
IF v_min_whence <= v_max_rollup_12 THEN 

779 


780 
DELETE FROM stratcon.rollup_matrix_numeric_12hours 

781 
WHERE rollup_time= v_min_whence; 

782 


783 
END IF; 

784 


785 
FOR rec IN 

786 
SELECT sid,name,v_min_whence as rollup_time,SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value, 

787 
MIN(min_value) as min_value ,MAX(max_value) as max_value 

788 
FROM stratcon.rollup_matrix_numeric_6hours 

789 
WHERE rollup_time<= v_min_whence and rollup_time> v_min_whence'12 hour'::interval 

790 
GROUP BY sid,name 

791 
LOOP 

792 


793 


794 
INSERT INTO stratcon.rollup_matrix_numeric_12hours 

795 
(sid,name,rollup_time,count_rows,avg_value,min_value,max_value) VALUES 

796 
(rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.min_value,rec.max_value); 

797 


798 
END LOOP; 

799 


800 


801 
DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='12 hours'; 

802 


803 
v_min_whence := NULL; 

804 
v_max_rollup_12 := NULL; 

805 


806 
END LOOP; 

807 


808 
UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_12hours'; 

809 


810 
RETURN; 

811 


812 
EXCEPTION 

813 
WHEN RAISE_EXCEPTION THEN 

814 
UPDATE stratcon.rollup_runner set runner = '' where rollup_table = 'rollup_matrix_numeric_12hours'; 

815 
RAISE EXCEPTION '%', SQLERRM; 

816 
WHEN OTHERS THEN 

817 
RAISE NOTICE '%', SQLERRM; 

818 
END 

819 
$$ LANGUAGE plpgsql; 

820 


821 


822 
create or replace function 

823 
stratcon.fetch_varset(in_check uuid, 

824 
in_name text, 

825 
in_start_time timestamptz, 

826 
in_end_time timestamptz, 

827 
in_hopeful_nperiods int) 

828 
returns setof stratcon.loading_dock_metric_text_s_change_log as 

829 
$$ 

830 
declare 

831 
v_sid int; 

832 
v_target record; 

833 
v_start_adj timestamptz; 

834 
v_start_text text; 

835 
v_next_text text; 

836 
v_end_adj timestamptz; 

837 
v_change_row stratcon.loading_dock_metric_text_s_change_log%rowtype; 

838 
begin 

839 
 Map out uuid to an sid. 

840 
select sid into v_sid from stratcon.map_uuid_to_sid where id = in_check; 

841 
if not found then 

842 
return; 

843 
end if; 

844 


845 
select * into v_target from stratcon.choose_window(in_start_time, in_end_time, in_hopeful_nperiods); 

846 


847 
select 'epoch'::timestamp + 

848 
((floor(extract('epoch' from in_start_time) / 

849 
extract('epoch' from v_target.period)) * 

850 
extract('epoch' from v_target.period))  ' seconds') ::interval 

851 
into v_start_adj; 

852 


853 
select 'epoch'::timestamp + 

854 
((floor(extract('epoch' from in_end_time) / 

855 
extract('epoch' from v_target.period)) * 

856 
extract('epoch' from v_target.period))  ' seconds') ::interval 

857 
into v_end_adj; 

858 


859 
for v_change_row in 

860 
select sid, 'epoch'::timestamp + 

861 
((floor(extract('epoch' from whence) / 

862 
extract('epoch' from v_target.period)) * 

863 
extract('epoch' from v_target.period))  ' seconds') ::interval as whence, 

864 
name, value 

865 
from stratcon.loading_dock_metric_text_s_change_log 

866 
where sid = v_sid 

867 
and name = in_name 

868 
and whence <= v_start_adj 

869 
order by 'epoch'::timestamp + 

870 
((floor(extract('epoch' from whence) / 

871 
extract('epoch' from v_target.period)) * 

872 
extract('epoch' from v_target.period))  ' seconds') ::interval desc 

873 
limit 1 

874 
loop 

875 
v_start_text := coalesce(v_change_row.value, '[unset]'); 

876 
end loop; 

877 


878 
for v_change_row in 

879 
select v_sid as sid, whence, in_name as name, value from 

880 
 (select v_start_adj::timestamp + t * v_target.period::interval as whence 

881 
 from generate_series(1, v_target.nperiods) t) s 

882 
 left join 

883 
(select 'epoch'::timestamp + 

884 
((floor(extract('epoch' from whence) / 

885 
extract('epoch' from v_target.period)) * 

886 
extract('epoch' from v_target.period))  ' seconds') ::interval as whence, 

887 
coalesce(value, '[unset]') as value 

888 
from stratcon.loading_dock_metric_text_s_change_log 

889 
where sid = v_sid 

890 
and name = in_name 

891 
and whence > v_start_adj 

892 
and whence <= v_end_adj) d 

893 
 using (whence) 

894 
order by whence asc 

895 
loop 

896 
v_next_text := v_change_row.value; 

897 
if v_change_row.value is not null and 

898 
v_start_text != v_change_row.value then 

899 
v_change_row.value := coalesce(v_start_text, '[unset]')  ' > '  coalesce(v_change_row.value, '[unset]'); 

900 
else 

901 
v_change_row.value := v_start_text; 

902 
end if; 

903 
if v_next_text is not null then 

904 
v_start_text := v_next_text; 

905 
end if; 

906 
return next v_change_row; 

907 
end loop; 

908 


909 
return; 

910 
end 

911 
$$ language 'plpgsql'; 

912 


913 


914 
create or replace function 

915 
stratcon.choose_window(in_start_time timestamptz, 

916 
in_end_time timestamptz, 

917 
in_hopeful_nperiods int, 

918 
out tablename text, 

919 
out period interval, 

920 
out nperiods int) 

921 
returns setof record as 

922 
$$ 

923 
declare 

924 
window record; 

925 
begin 

926 
 Figure out which table we should be looking in 

927 
for window in 

928 
select atablename, aperiod, anperiods 

929 
from (select aperiod, iv/isec as anperiods, atablename, 

930 
abs(case when iv/isec  in_hopeful_nperiods < 0 

931 
then 10 * (in_hopeful_nperiods  iv/isec) 

932 
else iv/isec  in_hopeful_nperiods 

933 
end) as badness 

934 
from (select extract('epoch' from in_end_time)  

935 
extract('epoch' from in_start_time) as iv 

936 
) i, 

937 
( select 5*60 as isec, '5 minutes'::interval as aperiod, 

938 
'rollup_matrix_numeric_5m' as atablename 

939 
union all 

940 
select 20*60 as isec, '20 minutes'::interval as aperiod, 

941 
'rollup_matrix_numeric_20m' as atablename 

942 
union all 

943 
select 60*60 as isec, '1 hour'::interval as aperiod, 

944 
'rollup_matrix_numeric_60m' as atablename 

945 
union all 

946 
select 6*60*60 as isec, '6 hours'::interval as aaperiod, 

947 
'rollup_matrix_numeric_6hours' as atablename 

948 
union all 

949 
select 12*60*60 as isec, '12 hours'::interval as aperiod, 

950 
'rollup_matrix_numeric_12hours' as atablename 

951 
) ivs 

952 
) b 

953 
order by badness asc 

954 
limit 1 

955 
loop 

956 
tablename := window.atablename; 

957 
period := window.aperiod; 

958 
nperiods := window.anperiods; 

959 
return next; 

960 
end loop; 

961 
return; 

962 
end 

963 
$$ language 'plpgsql'; 

964 


965 
create or replace function 

966 
stratcon.fetch_dataset(in_check uuid, 

967 
in_name text, 

968 
in_start_time timestamptz, 

969 
in_end_time timestamptz, 

970 
in_hopeful_nperiods int, 

971 
derive boolean) 

972 
returns setof stratcon.rollup_matrix_numeric_5m as 

973 
$$ 

974 
declare 

975 
v_sql text; 

976 
v_sid int; 

977 
v_target record; 

978 
v_interval numeric; 

979 
v_start_adj timestamptz; 

980 
v_end_adj timestamptz; 

981 
v_l_rollup_row stratcon.rollup_matrix_numeric_5m%rowtype; 

982 
v_rollup_row stratcon.rollup_matrix_numeric_5m%rowtype; 

983 
v_r_rollup_row stratcon.rollup_matrix_numeric_5m%rowtype; 

984 
begin 

985 


986 
 Map out uuid to an sid. 

987 
select sid into v_sid from stratcon.map_uuid_to_sid where id = in_check; 

988 
if not found then 

989 
return; 

990 
end if; 

991 


992 
select * into v_target from stratcon.choose_window(in_start_time, in_end_time, in_hopeful_nperiods); 

993 


994 
if not found then 

995 
raise exception 'no target table'; 

996 
return; 

997 
end if; 

998 


999 
select 'epoch'::timestamp + 

1000 
((floor(extract('epoch' from in_start_time) / 

1001 
extract('epoch' from v_target.period)) * 

1002 
extract('epoch' from v_target.period))  ' seconds') ::interval 

1003 
into v_start_adj; 

1004 


1005 
select 'epoch'::timestamp + 

1006 
((floor(extract('epoch' from in_end_time) / 

1007 
extract('epoch' from v_target.period)) * 

1008 
extract('epoch' from v_target.period))  ' seconds') ::interval 

1009 
into v_end_adj; 

1010 


1011 
v_sql := 'select '  v_sid  ' as sid, '  quote_literal(in_name)  ' as name, '  

1012 
's.rollup_time, d.count_rows, d.avg_value, '  

1013 
'd.min_value, d.max_value '  

1014 
' from '  

1015 
'(select '  quote_literal(v_start_adj)  '::timestamp'  

1016 
' + t * '  quote_literal(v_target.period)  '::interval'  

1017 
' as rollup_time'  

1018 
' from generate_series(1,'  v_target.nperiods  ') t) s '  

1019 
'left join '  

1020 
'(select * from stratcon.'  v_target.tablename  

1021 
' where sid = '  v_sid  

1022 
' and name = '  quote_literal(in_name)  

1023 
' and rollup_time between '  quote_literal(v_start_adj)  '::timestamp'  

1024 
' and '  quote_literal(v_end_adj)  '::timestamp) d'  

1025 
' using(rollup_time)'; 

1026 


1027 
for v_rollup_row in execute v_sql loop 

1028 
if derive is true then 

1029 
v_r_rollup_row := v_rollup_row; 

1030 
if v_l_rollup_row.count_rows is not null and 

1031 
v_rollup_row.count_rows is not null then 

1032 
v_interval := extract('epoch' from v_rollup_row.rollup_time)  extract('epoch' from v_l_rollup_row.rollup_time); 

1033 
v_r_rollup_row.count_rows := (v_l_rollup_row.count_rows + v_rollup_row.count_rows) / 2; 

1034 
v_r_rollup_row.avg_value := 

1035 
(v_rollup_row.avg_value  v_l_rollup_row.avg_value) / v_interval; 

1036 
v_r_rollup_row.min_value := 

1037 
(v_rollup_row.min_value  v_l_rollup_row.min_value) / v_interval; 

1038 
v_r_rollup_row.max_value := 

1039 
(v_rollup_row.max_value  v_l_rollup_row.max_value) / v_interval; 

1040 
else 

1041 
v_r_rollup_row.count_rows = NULL; 

1042 
v_r_rollup_row.avg_value = NULL; 

1043 
v_r_rollup_row.min_value = NULL; 

1044 
v_r_rollup_row.max_value = NULL; 

1045 
end if; 

1046 
else 

1047 
v_r_rollup_row := v_rollup_row; 

1048 
end if; 

1049 
return next v_r_rollup_row; 

1050 
v_l_rollup_row := v_rollup_row; 

1051 
end loop; 

1052 
return; 

1053 
end 

1054 
$$ language 'plpgsql'; 

1055 


1056 


1057 
 Remove Metric based on UUID and Metric_Name 

1058 


1059 
CREATE OR REPLACE FUNCTION stratcon.remove_metric (in_uuid uuid, 

1060 
in_metric_name text, 

1061 
v_debug text, 

1062 
OUT v_out text) 

1063 
RETURNS text 

1064 
AS $$ 

1065 
DECLARE 

1066 
v_del_sid INT; 

1067 
v_del_metric_name TEXT; 

1068 
v_del_metric_type TEXT; 

1069 
deleted_t INT; 

1070 
deleted_tc INT; 

1071 
deleted_n INT; 

1072 
deleted_5 INT; 

1073 
deleted_20 INT; 

1074 
deleted_60 INT; 

1075 
deleted_6h INT; 

1076 
deleted_12h INT; 

1077 
deleted_sum INT; 

1078 


1079 
BEGIN 

1080 
SELECT s.sid,m.metric_name,m.metric_type 

1081 
FROM 

1082 
stratcon.map_uuid_to_sid s, 

1083 
stratcon.metric_name_summary m 

1084 
WHERE s.id=in_uuid 

1085 
and s.sid=m.sid 

1086 
and m.metric_name=in_metric_name 

1087 
INTO v_del_sid,v_del_metric_name,v_del_metric_type; 

1088 
IF NOT FOUND THEN 

1089 
IF v_debug = 'DEBUG' THEN 

1090 
RAISE NOTICE 'Given UUID can not map to SID,Metric Name: %,%',in_uuid,in_metric_name; 

1091 
END IF; 

1092 
v_out:='Please Supply Valid UUID,Metric Name Combination :'in_uuid','in_metric_name; 

1093 
RETURN; 

1094 
END IF; 

1095 
IF v_debug = 'DEBUG' THEN 

1096 
RAISE NOTICE 'Delete In Progress For: %,%,%',v_del_sid,v_del_metric_name,v_del_metric_type; 

1097 
END IF; 

1098 


1099 
 Check of Text or Numeric Type 

1100 
IF v_del_metric_type ='text' THEN 

1101 
 Delete from Metrix Tex table 

1102 
DELETE FROM stratcon.loading_dock_metric_text_s WHERE sid=v_del_sid AND name=v_del_metric_name; 

1103 
GET DIAGNOSTICS deleted_t = ROW_COUNT; 

1104 
IF v_debug = 'DEBUG' THEN 

1105 
RAISE NOTICE 'DELELTED ROWS FROM loading_dock_metric_text_s : %',deleted; 

1106 
END IF; 

1107 
 Delete from Metrix Change Log table 

1108 
DELETE FROM stratcon.loading_dock_metric_text_s_change_log WHERE sid=v_del_sid AND name=v_del_metric_name; 

1109 
GET DIAGNOSTICS deleted_tc = ROW_COUNT; 

1110 
IF v_debug = 'DEBUG' THEN 

1111 
RAISE NOTICE 'DELELTED ROWS FROM loading_dock_metric_text_s_change_log : %',deleted; 

1112 
END IF; 

1113 
ELSE 

1114 
 Delete from Metrix Numeric table 

1115 
DELETE FROM stratcon.loading_dock_metric_numeric_s WHERE sid=v_del_sid AND name=v_del_metric_name; 

1116 
GET DIAGNOSTICS deleted_n = ROW_COUNT; 

1117 
IF v_debug = 'DEBUG' THEN 

1118 
RAISE NOTICE 'DELELTED ROWS FROM loading_dock_metric_numeric_s : %',deleted; 

1119 
END IF; 

1120 
 Delete from Rollup tables 

1121 
DELETE FROM stratcon.rollup_matrix_numeric_5m WHERE sid=v_del_sid AND name=v_del_metric_name; 

1122 
GET DIAGNOSTICS deleted_5 = ROW_COUNT; 

1123 
IF v_debug = 'DEBUG' THEN 

1124 
RAISE NOTICE 'DELELTED ROWS FROM rollup_matrix_numeric_5m : %',deleted; 

1125 
END IF; 

1126 
DELETE FROM stratcon.rollup_matrix_numeric_20m WHERE sid=v_del_sid AND name=v_del_metric_name; 

1127 
GET DIAGNOSTICS deleted_20= ROW_COUNT; 

1128 
IF v_debug = 'DEBUG' THEN 

1129 
RAISE NOTICE 'DELELTED ROWS FROM rollup_matrix_numeric_20m : %',deleted; 

1130 
END IF; 

1131 
DELETE FROM stratcon.rollup_matrix_numeric_60m WHERE sid=v_del_sid AND name=v_del_metric_name; 

1132 
GET DIAGNOSTICS deleted_60 = ROW_COUNT; 

1133 
IF v_debug = 'DEBUG' THEN 

1134 
RAISE NOTICE 'DELELTED ROWS FROM rollup_matrix_numeric_60m : %',deleted; 

1135 
END IF; 

1136 
DELETE FROM stratcon.rollup_matrix_numeric_6hours WHERE sid=v_del_sid AND name=v_del_metric_name; 

1137 
GET DIAGNOSTICS deleted_6h = ROW_COUNT; 

1138 
IF v_debug = 'DEBUG' THEN 

1139 
RAISE NOTICE 'DELELTED ROWS FROM rollup_matrix_numeric_6hours : %',deleted; 

1140 
END IF; 

1141 
DELETE FROM stratcon.rollup_matrix_numeric_12hours WHERE sid=v_del_sid AND name=v_del_metric_name; 

1142 
GET DIAGNOSTICS deleted_12h = ROW_COUNT; 

1143 
IF v_debug = 'DEBUG' THEN 

1144 
RAISE NOTICE 'DELELTED ROWS FROM rollup_matrix_numeric_12hours : %',deleted; 

1145 
END IF; 

1146 
END IF; 

1147 
 Delete from metrix summary table 

1148 
DELETE FROM stratcon.metrix_name_summary WHERE sid=v_del_sid AND metric_name=v_del_metric_name; 

1149 
GET DIAGNOSTICS deleted_sum= ROW_COUNT; 

1150 
IF v_debug = 'DEBUG' THEN 

1151 
RAISE NOTICE 'DELELTED ROWS FROM metric_name_summary : %',deleted; 

1152 
END IF; 

1153 
v_out:='Deleted Rows for Metric_Text, Metrix_Text_change_log,Metric_Numeric,Rollup_5m,Rollup_20m,Rollup_1hour,Rollup_6hours,Rollup_12hours,Metric_Summary:'deleted_t','deleted_tc','deleted_n','deleted_5','deleted_20','deleted_60','deleted_6h','deleted_12h','deleted_sum; 

1154 
RETURN; 

1155 
EXCEPTION 

1156 
WHEN RAISE_EXCEPTION THEN 

1157 
RAISE EXCEPTION '%', SQLERRM; 

1158 
WHEN OTHERS THEN 

1159 
RAISE NOTICE '%', SQLERRM; 

1160 
END 

1161 
$$ LANGUAGE plpgsql; 

1162 


1163 
COMMIT; 
