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 timestamp 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 timestamp 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 timestamp 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 timestamp 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 timestamp 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 timestamp 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 timestamp 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 timestamp 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 TABLE stratcon.rollup_matrix_numeric_60m( 

84 
sid integer not null, 

85 
name text not null, 

86 
rollup_time timestamp not null, 

87 
count_rows integer, 

88 
avg_value numeric , 

89 
min_value numeric , 

90 
max_value numeric , 

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

92 


93 
CREATE TABLE stratcon.rollup_matrix_numeric_6hours( 

94 
sid integer not null, 

95 
name text not null, 

96 
rollup_time timestamp not null, 

97 
count_rows integer, 

98 
avg_value numeric , 

99 
min_value numeric , 

100 
max_value numeric , 

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

102 


103 
CREATE TABLE stratcon.rollup_matrix_numeric_12hours( 

104 
sid integer not null, 

105 
name text not null, 

106 
rollup_time timestamp not null, 

107 
count_rows integer, 

108 
avg_value numeric , 

109 
min_value numeric , 

110 
max_value numeric , 

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

112 


113 
CREATE TABLE stratcon.map_uuid_to_sid ( 

114 
id uuid NOT NULL, 

115 
sid integer NOT NULL, 

116 
PRIMARY KEY(id,sid) 

117 
); 

118 


119 
CREATE TABLE stratcon.log_whence_s ( 

120 
whence timestamp NOT NULL, 

121 
interval varchar2(20, 

122 
PRIMARY KEY(whence,interval) 

123 
); 

124 


125 
CREATE TABLE stratcon.rollup_runner ( 

126 
rollup_table character varying(100), 

127 
runner character varying(22) 

128 
); 

129 


130 
CREATE TABLE stratcon.metric_name_summary ( 

131 
sid integer NOT NULL, 

132 
metric_name text NOT NULL, 

133 
metric_type character varying(22), 

134 
active boolean default 'true', 

135 
PRIMARY KEY (sid,name) 

136 
); 

137 
 Schema Sequence 

138 


139 
CREATE SEQUENCE stratcon.seq_sid 

140 
START WITH 50 

141 
INCREMENT BY 1 

142 
NO MAXVALUE 

143 
NO MINVALUE 

144 
CACHE 1; 

145 


146 


147 


148 
 GRANTS 

149 


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

151 
GRANT SELECT,INSERT ON stratcon.loading_dock_status_s_change_log TO stratcon; 

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

153 
GRANT SELECT,INSERT ON stratcon.loading_dock_metric_numeric_s TO stratcon; 

154 
GRANT SELECT,INSERT ON stratcon.loading_dock_metric_text_s_change_log TO stratcon; 

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

156 
GRANT SELECT,INSERT ON stratcon.loading_dock_metric_text_s TO stratcon; 

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

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

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

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

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

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

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

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

165 
ALTER TABLE stratcon.seq_sid OWNER TO stratcon; 

166 


167 


168 
 Function To generate SID from ID 

169 


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

171 
RETURNS integer 

172 
AS $$ 

173 
DECLARE 

174 
v_ex_sid integer; 

175 
v_new_sid integer; 

176 


177 
BEGIN 

178 


179 
SELECT sid FROM stratcon.map_uuid_to_sid WHERE id=v_in_id 

180 
INTO v_ex_sid; 

181 


182 
IF NOT FOUND THEN 

183 
SELECT nextval('stratcon.seq_sid') 

184 
INTO v_new_sid; 

185 


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

187 


188 
RETURN v_new_sid; 

189 
ELSE 

190 
RETURN v_ex_sid; 

191 
END IF; 

192 


193 
END 

194 
$$ LANGUAGE plpgsql; 

195 


196 
 Trigger Function to log dock status Changes 

197 


198 
CREATE TRIGGER loading_dock_status_s_change_log 

199 
AFTER INSERT ON stratcon.loading_dock_status_s 

200 
FOR EACH ROW 

201 
EXECUTE PROCEDURE stratcon.loading_dock_status_s_change_log(); 

202 


203 


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

205 
AS $$ 

206 
DECLARE 

207 
v_state CHAR(1); 

208 
v_avail CHAR(1); 

209 
BEGIN 

210 


211 
IF TG_OP = 'INSERT' THEN 

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

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

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

215 
INTO v_state,v_avail; 

216 


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

218 


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

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

221 


222 
END IF; 

223 


224 
ELSE 

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

226 
END IF; 

227 


228 
RETURN NULL; 

229 


230 
END 

231 
$$ 

232 
LANGUAGE plpgsql; 

233 


234 


235 
 Trigger Function to log Metrix Text Changes 

236 


237 
CREATE TRIGGER loading_dock_metric_text_s_change_log 

238 
AFTER INSERT ON stratcon.loading_dock_metric_text_s 

239 
FOR EACH ROW 

240 
EXECUTE PROCEDURE stratcon.loading_dock_metric_text_s_change_log(); 

241 


242 


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

244 
AS $$ 

245 
DECLARE 

246 
v_oldvalue TEXT; 

247 
v_sid integer; 

248 
v_name text; 

249 
BEGIN 

250 


251 
IF TG_OP = 'INSERT' THEN 

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

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

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

255 
INTO v_oldvalue; 

256 


257 
IF v_oldvalue IS DISTINCT FROM NEW.value THEN 

258 


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

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

261 
END IF; 

262 


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

264 
INTO v_sid,v_name; 

265 
IF NOT FOUND THEN 

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

267 
END IF; 

268 


269 
ELSE 

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

271 
END IF; 

272 


273 
RETURN NULL; 

274 


275 
END 

276 
$$ 

277 
LANGUAGE plpgsql; 

278 


279 
 Trigger on Metrix Numeric to log last inserted timestamp 

280 


281 
CREATE OR REPLACE FUNCTION stratcon.loading_dock_metric_numeric_s_whence_log() 

282 
RETURNS trigger 

283 
AS $$ 

284 
DECLARE 

285 
v_whence timestamptz; 

286 
v_sid integer; 

287 
v_name text; 

288 
BEGIN 

289 
IF TG_OP = 'INSERT' THEN 

290 
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' 

291 
INTO v_whence; 

292 
IF NOT FOUND THEN 

293 
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'); 

294 
END IF; 

295 


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

297 
INTO v_sid,v_name; 

298 
IF NOT FOUND THEN 

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

300 
END IF; 

301 


302 
END IF; 

303 


304 
RETURN NULL; 

305 
END 

306 
$$ 

307 
LANGUAGE plpgsql; 

308 


309 
 5 minutes rollup 

310 


311 
CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_5m() 

312 
RETURNS void 

313 
AS $$ 

314 
DECLARE 

315 


316 
rec stratcon.rollup_matrix_numeric_5m%rowtype; 

317 
v_sql TEXT; 

318 
v_min_whence TIMESTAMP; 

319 
v_max_rollup_5 TIMESTAMP; 

320 
v_whence TIMESTAMP; 

321 
rows INT; 

322 
v_nrunning INT; 

323 
v_self VARCHAR(22); 

324 


325 
BEGIN 

326 


327 
SELECT COUNT(1) INTO v_nrunning 

328 
from stratcon.rollup_runner t, pg_stat_activity a 

329 
where rollup_table ='rollup_matrix_numeric_5m' 

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

331 


332 
IF v_nrunning > 0 THEN 

333 
RAISE NOTICE 'stratcon.rollup_matrix_numeric_5m already running'; 

334 
RETURN ; 

335 
END IF; 

336 


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

338 
FROM pg_stat_activity 

339 
WHERE procpid = pg_backend_pid(); 

340 


341 
IF v_self IS NULL THEN 

342 
RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_5m cannot selfidentify'; 

343 
END IF; 

344 


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

346 


347 
EXECUTE v_sql; 

348 


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

350 
INTO v_min_whence; 

351 


352 
SELECT MAX(rollup_time) FROM stratcon.rollup_matrix_numeric_5m 

353 
INTO v_max_rollup_5; 

354 


355 
 Insert Log for 20 minutes rollup 

356 


357 
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' 

358 
INTO v_whence; 

359 
IF NOT FOUND THEN 

360 
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'); 

361 
END IF; 

362 


363 
IF v_min_whence <= v_max_rollup_5 THEN 

364 


365 
DELETE FROM stratcon.rollup_matrix_numeric_5m 

366 
WHERE rollup_time = v_min_whence; 

367 


368 
END IF; 

369 


370 
FOR rec IN 

371 
SELECT sid , name,v_min_whence as rollup_time, 

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

373 
FROM stratcon.loading_dock_metric_numeric_s 

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

375 
GROUP BY rollup_time,sid,name 

376 


377 
LOOP 

378 


379 


380 
INSERT INTO stratcon.rollup_matrix_numeric_5m 

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

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

383 


384 
END LOOP; 

385 


386 
 Delete from whence log table 

387 


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

389 


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

391 


392 
RETURN; 

393 


394 
EXCEPTION 

395 
WHEN RAISE_EXCEPTION THEN 

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

397 
RAISE EXCEPTION '%', SQLERRM; 

398 
WHEN OTHERS THEN 

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

400 
RAISE NOTICE '%', SQLERRM; 

401 
END 

402 
$$ LANGUAGE plpgsql; 

403 


404 


405 
 20 minutes rollup 

406 


407 
CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_20m() 

408 
RETURNS void 

409 
AS $$ 

410 
DECLARE 

411 


412 
rec stratcon.rollup_matrix_numeric_20m%rowtype; 

413 
v_sql TEXT; 

414 
v_min_whence TIMESTAMP; 

415 
v_max_rollup_20 TIMESTAMP; 

416 
v_whence TIMESTAMP; 

417 
rows INT; 

418 
v_nrunning INT; 

419 
v_self VARCHAR(22); 

420 


421 
BEGIN 

422 


423 
SELECT COUNT(1) INTO v_nrunning 

424 
from stratcon.rollup_runner t, pg_stat_activity a 

425 
where rollup_table ='rollup_matrix_numeric_20m' 

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

427 


428 
IF v_nrunning > 0 THEN 

429 
RAISE NOTICE 'stratcon.rollup_matrix_numeric_20m already running'; 

430 
RETURN ; 

431 
END IF; 

432 


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

434 
FROM pg_stat_activity 

435 
WHERE procpid = pg_backend_pid(); 

436 


437 
IF v_self IS NULL THEN 

438 
RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_20m cannot selfidentify'; 

439 
END IF; 

440 


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

442 


443 
EXECUTE v_sql; 

444 


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

446 
INTO v_min_whence; 

447 


448 
SELECT MAX(rollup_time) FROM stratcon.rollup_matrix_numeric_20m 

449 
INTO v_max_rollup_20; 

450 


451 
 Insert Log for Hourly rollup 

452 


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

454 
INTO v_whence; 

455 
IF NOT FOUND THEN 

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

457 
END IF; 

458 


459 
IF v_min_whence <= v_max_rollup_20 THEN 

460 


461 
DELETE FROM stratcon.rollup_matrix_numeric_20m 

462 
WHERE rollup_time = v_min_whence; 

463 


464 
END IF; 

465 


466 
FOR rec IN 

467 
SELECT sid , name,v_min_whence as rollup_time, 

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

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

470 
FROM stratcon.rollup_matrix_numeric_5m 

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

472 
GROUP BY sid,name 

473 


474 
LOOP 

475 


476 


477 
INSERT INTO stratcon.rollup_matrix_numeric_20m 

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

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

480 


481 
END LOOP; 

482 


483 
 Delete from whence log table 

484 


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

486 


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

488 


489 
RETURN; 

490 


491 
EXCEPTION 

492 
WHEN RAISE_EXCEPTION THEN 

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

494 
RAISE EXCEPTION '%', SQLERRM; 

495 
WHEN OTHERS THEN 

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

497 
RAISE NOTICE '%', SQLERRM; 

498 
END 

499 
$$ LANGUAGE plpgsql; 

500 


501 


502 
 1 hour rollup 

503 


504 


505 
CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_60m() 

506 
RETURNS void 

507 
AS $$ 

508 
DECLARE 

509 
rec stratcon.rollup_matrix_numeric_60m%rowtype; 

510 
v_sql TEXT; 

511 
v_min_whence TIMESTAMP; 

512 
v_max_rollup_60 TIMESTAMP; 

513 
v_whence TIMESTAMP; 

514 
v_nrunning INT; 

515 
v_self VARCHAR(22); 

516 


517 
BEGIN 

518 


519 
SELECT COUNT(1) INTO v_nrunning 

520 
from stratcon.rollup_runner t, pg_stat_activity a 

521 
where rollup_table ='rollup_matrix_numeric_60m' 

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

523 


524 
IF v_nrunning > 0 THEN 

525 
RAISE NOTICE 'stratcon.rollup_matrix_numeric_60m already running'; 

526 
RETURN ; 

527 
END IF; 

528 


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

530 
FROM pg_stat_activity 

531 
WHERE procpid = pg_backend_pid(); 

532 


533 
IF v_self IS NULL THEN 

534 
RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_60m cannot selfidentify'; 

535 
END IF; 

536 


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

538 


539 
EXECUTE v_sql; 

540 


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

542 
INTO v_min_whence; 

543 


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

545 
INTO v_max_rollup_60; 

546 


547 
 Insert Log for 6 Hour rollup 

548 


549 
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' 

550 
INTO v_whence; 

551 
IF NOT FOUND THEN 

552 
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'); 

553 
END IF; 

554 


555 


556 
IF v_min_whence <= v_max_rollup_60 THEN 

557 


558 
DELETE FROM stratcon.rollup_matrix_numeric_60m 

559 
WHERE rollup_time= v_min_whence; 

560 


561 
END IF; 

562 


563 
FOR rec IN 

564 
SELECT sid,name,date_trunc('hour',rollup_time) as rollup_time,SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value, 

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

566 
FROM stratcon.rollup_matrix_numeric_20m 

567 
WHERE date_trunc('hour',rollup_time)= date_trunc('hour',v_min_whence) 

568 
GROUP BY date_trunc('hour',rollup_time),sid,name 

569 
LOOP 

570 


571 
INSERT INTO stratcon.rollup_matrix_numeric_60m 

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

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

574 


575 
END LOOP; 

576 


577 


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

579 


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

581 


582 
RETURN; 

583 


584 
EXCEPTION 

585 
WHEN RAISE_EXCEPTION THEN 

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

587 
RAISE EXCEPTION '%', SQLERRM; 

588 
WHEN OTHERS THEN 

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

590 
RAISE NOTICE '%', SQLERRM; 

591 
END 

592 
$$ LANGUAGE plpgsql; 

593 


594 


595 
 6 hours 

596 


597 
CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_6hours() 

598 
RETURNS void 

599 
AS $$ 

600 
DECLARE 

601 
rec stratcon.rollup_matrix_numeric_6hours%rowtype; 

602 
v_sql TEXT; 

603 
v_min_whence TIMESTAMP; 

604 
v_max_rollup_6 TIMESTAMP; 

605 
v_whence TIMESTAMP; 

606 
v_nrunning INT; 

607 
v_self VARCHAR(22); 

608 


609 
BEGIN 

610 


611 
SELECT COUNT(1) INTO v_nrunning 

612 
from stratcon.rollup_runner t, pg_stat_activity a 

613 
where rollup_table ='rollup_matrix_numeric_6hours' 

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

615 


616 
IF v_nrunning > 0 THEN 

617 
RAISE NOTICE 'stratcon.rollup_matrix_numeric_6hours already running'; 

618 
RETURN ; 

619 
END IF; 

620 


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

622 
FROM pg_stat_activity 

623 
WHERE procpid = pg_backend_pid(); 

624 


625 
IF v_self IS NULL THEN 

626 
RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_6hours cannot selfidentify'; 

627 
END IF; 

628 


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

630 


631 
EXECUTE v_sql; 

632 


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

634 
INTO v_min_whence; 

635 


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

637 
INTO v_max_rollup_6; 

638 


639 
 Insert Log for 12 Hours rollup 

640 


641 
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' 

642 
INTO v_whence; 

643 
IF NOT FOUND THEN 

644 
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'); 

645 
END IF; 

646 


647 


648 
IF v_min_whence <= v_max_rollup_6 THEN 

649 


650 
DELETE FROM stratcon.rollup_matrix_numeric_6hours 

651 
WHERE rollup_time= v_min_whence; 

652 


653 
END IF; 

654 


655 
FOR rec IN 

656 
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, 

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

658 
FROM stratcon.rollup_matrix_numeric_60m 

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

660 
GROUP BY sid,name 

661 
LOOP 

662 


663 


664 
INSERT INTO stratcon.rollup_matrix_numeric_6hours 

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

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

667 


668 
END LOOP; 

669 


670 


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

672 


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

674 


675 
RETURN; 

676 


677 
EXCEPTION 

678 
WHEN RAISE_EXCEPTION THEN 

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

680 
RAISE EXCEPTION '%', SQLERRM; 

681 
WHEN OTHERS THEN 

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

683 
RAISE NOTICE '%', SQLERRM; 

684 
END 

685 
$$ LANGUAGE plpgsql; 

686 


687 


688 
 12 hours 

689 


690 
CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_12hours() 

691 
RETURNS void 

692 
AS $$ 

693 
DECLARE 

694 
rec stratcon.rollup_matrix_numeric_12hours%rowtype; 

695 
v_sql TEXT; 

696 
v_min_whence TIMESTAMP; 

697 
v_max_rollup_12 TIMESTAMP; 

698 
v_whence TIMESTAMP; 

699 
v_nrunning INT; 

700 
v_self VARCHAR(22); 

701 


702 


703 
BEGIN 

704 


705 
SELECT COUNT(1) INTO v_nrunning 

706 
from stratcon.rollup_runner t, pg_stat_activity a 

707 
where rollup_table ='rollup_matrix_numeric_12hours' 

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

709 


710 
IF v_nrunning > 0 THEN 

711 
RAISE NOTICE 'stratcon.rollup_matrix_numeric_12hours already running'; 

712 
RETURN ; 

713 
END IF; 

714 


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

716 
FROM pg_stat_activity 

717 
WHERE procpid = pg_backend_pid(); 

718 


719 
IF v_self IS NULL THEN 

720 
RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_12hours cannot selfidentify'; 

721 
END IF; 

722 


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

724 


725 
EXECUTE v_sql; 

726 


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

728 
INTO v_min_whence; 

729 


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

731 
INTO v_max_rollup_12; 

732 


733 
/* Insert Log for 24 Hours rollup 

734 


735 
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' 

736 
INTO v_whence; 

737 
IF NOT FOUND THEN 

738 
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'); 

739 
END IF; 

740 
*/ 

741 


742 
IF v_min_whence <= v_max_rollup_12 THEN 

743 


744 
DELETE FROM stratcon.rollup_matrix_numeric_12hours 

745 
WHERE rollup_time= v_min_whence; 

746 


747 
END IF; 

748 


749 
FOR rec IN 

750 
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, 

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

752 
FROM stratcon.rollup_matrix_numeric_6hours 

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

754 
GROUP BY sid,name 

755 
LOOP 

756 


757 


758 
INSERT INTO stratcon.rollup_matrix_numeric_12hours 

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

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

761 


762 
END LOOP; 

763 


764 


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

766 


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

768 


769 
RETURN; 

770 


771 
EXCEPTION 

772 
WHEN RAISE_EXCEPTION THEN 

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

774 
RAISE EXCEPTION '%', SQLERRM; 

775 
WHEN OTHERS THEN 

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

777 
RAISE NOTICE '%', SQLERRM; 

778 
END 

779 
$$ LANGUAGE plpgsql; 

780 


781 


782 
create or replace function 

783 
stratcon.fetch_varset(in_check uuid, 

784 
in_name text, 

785 
in_start_time timestamp, 

786 
in_end_time timestamp, 

787 
in_hopeful_nperiods int) 

788 
returns setof stratcon.loading_dock_metric_text_s_change_log as 

789 
$$ 

790 
declare 

791 
v_sid int; 

792 
v_target record; 

793 
v_start_adj timestamp; 

794 
v_start_text text; 

795 
v_next_text text; 

796 
v_end_adj timestamp; 

797 
v_change_row stratcon.loading_dock_metric_text_s_change_log%rowtype; 

798 
begin 

799 
 Map out uuid to an sid. 

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

801 
if not found then 

802 
return; 

803 
end if; 

804 


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

806 


807 
select 'epoch'::timestamp + 

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

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

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

811 
into v_start_adj; 

812 


813 
select 'epoch'::timestamp + 

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

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

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

817 
into v_end_adj; 

818 


819 
for v_change_row in 

820 
select sid, 'epoch'::timestamp + 

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

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

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

824 
name, value 

825 
from stratcon.loading_dock_metric_text_s_change_log 

826 
where sid = v_sid 

827 
and name = in_name 

828 
and whence <= v_start_adj 

829 
order by 'epoch'::timestamp + 

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

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

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

833 
limit 1 

834 
loop 

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

836 
end loop; 

837 


838 
for v_change_row in 

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

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

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

842 
 left join 

843 
(select 'epoch'::timestamp + 

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

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

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

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

848 
from stratcon.loading_dock_metric_text_s_change_log 

849 
where sid = v_sid 

850 
and name = in_name 

851 
and whence > v_start_adj 

852 
and whence <= v_end_adj) d 

853 
 using (whence) 

854 
order by whence asc 

855 
loop 

856 
v_next_text := v_change_row.value; 

857 
if v_change_row.value is not null and 

858 
v_start_text != v_change_row.value then 

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

860 
else 

861 
v_change_row.value := v_start_text; 

862 
end if; 

863 
if v_next_text is not null then 

864 
v_start_text := v_next_text; 

865 
end if; 

866 
return next v_change_row; 

867 
end loop; 

868 


869 
return; 

870 
end 

871 
$$ language 'plpgsql'; 

872 


873 


874 
create or replace function 

875 
stratcon.choose_window(in_start_time timestamp, 

876 
in_end_time timestamp, 

877 
in_hopeful_nperiods int, 

878 
out tablename text, 

879 
out period interval, 

880 
out nperiods int) 

881 
returns setof record as 

882 
$$ 

883 
declare 

884 
window record; 

885 
begin 

886 
 Figure out which table we should be looking in 

887 
for window in 

888 
select atablename, aperiod, anperiods 

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

890 
abs(case when iv/isec  in_hopeful_nperiods < 0 

891 
then 10 * (in_hopeful_nperiods  iv/isec) 

892 
else iv/isec  in_hopeful_nperiods 

893 
end) as badness 

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

895 
extract('epoch' from in_start_time) as iv 

896 
) i, 

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

898 
'rollup_matrix_numeric_5m' as atablename 

899 
union all 

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

901 
'rollup_matrix_numeric_20m' as atablename 

902 
union all 

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

904 
'rollup_matrix_numeric_60m' as atablename 

905 
union all 

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

907 
'rollup_matrix_numeric_6hours' as atablename 

908 
union all 

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

910 
'rollup_matrix_numeric_12hours' as atablename 

911 
) ivs 

912 
) b 

913 
order by badness asc 

914 
limit 1 

915 
loop 

916 
tablename := window.atablename; 

917 
period := window.aperiod; 

918 
nperiods := window.anperiods; 

919 
return next; 

920 
end loop; 

921 
return; 

922 
end 

923 
$$ language 'plpgsql'; 

924 


925 
create or replace function 

926 
stratcon.fetch_dataset(in_check uuid, 

927 
in_name text, 

928 
in_start_time timestamp, 

929 
in_end_time timestamp, 

930 
in_hopeful_nperiods int, 

931 
derive boolean) 

932 
returns setof stratcon.rollup_matrix_numeric_5m as 

933 
$$ 

934 
declare 

935 
v_sql text; 

936 
v_sid int; 

937 
v_target record; 

938 
v_interval numeric; 

939 
v_start_adj timestamp; 

940 
v_end_adj timestamp; 

941 
v_l_rollup_row stratcon.rollup_matrix_numeric_5m%rowtype; 

942 
v_rollup_row stratcon.rollup_matrix_numeric_5m%rowtype; 

943 
v_r_rollup_row stratcon.rollup_matrix_numeric_5m%rowtype; 

944 
begin 

945 


946 
 Map out uuid to an sid. 

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

948 
if not found then 

949 
return; 

950 
end if; 

951 


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

953 


954 
if not found then 

955 
raise exception 'no target table'; 

956 
return; 

957 
end if; 

958 


959 
select 'epoch'::timestamp + 

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

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

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

963 
into v_start_adj; 

964 


965 
select 'epoch'::timestamp + 

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

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

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

969 
into v_end_adj; 

970 


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

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

973 
'd.min_value, d.max_value '  

974 
' from '  

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

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

977 
' as rollup_time'  

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

979 
'left join '  

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

981 
' where sid = '  v_sid  

982 
' and name = '  quote_literal(in_name)  

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

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

985 
' using(rollup_time)'; 

986 


987 
for v_rollup_row in execute v_sql loop 

988 
if derive is true then 

989 
v_r_rollup_row := v_rollup_row; 

990 
if v_l_rollup_row.count_rows is not null and 

991 
v_rollup_row.count_rows is not null then 

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

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

994 
v_r_rollup_row.avg_value := 

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

996 
v_r_rollup_row.min_value := 

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

998 
v_r_rollup_row.max_value := 

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

1000 
else 

1001 
v_r_rollup_row.count_rows = NULL; 

1002 
v_r_rollup_row.avg_value = NULL; 

1003 
v_r_rollup_row.min_value = NULL; 

1004 
v_r_rollup_row.max_value = NULL; 

1005 
end if; 

1006 
else 

1007 
v_r_rollup_row := v_rollup_row; 

1008 
end if; 

1009 
return next v_r_rollup_row; 

1010 
v_l_rollup_row := v_rollup_row; 

1011 
end loop; 

1012 
return; 

1013 
end 

1014 
$$ language 'plpgsql'; 

1015 


1016 
COMMIT; 
