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 
CREATE TABLE stratcon.loading_dock_metric_numeric_s ( 

37 
sid integer NOT NULL, 

38 
whence timestamp NOT NULL, 

39 
name text NOT NULL, 

40 
value numeric, 

41 
PRIMARY KEY(whence,sid,name) 

42 
); 

43 


44 
CREATE TABLE stratcon.loading_dock_metric_text_s ( 

45 
sid integer NOT NULL, 

46 
whence timestamp NOT NULL, 

47 
name text NOT NULL, 

48 
value text, 

49 
PRIMARY KEY(whence,sid,name) 

50 
); 

51 


52 
CREATE TABLE stratcon.loading_dock_metric_text_s_change_log ( 

53 
sid integer NOT NULL, 

54 
whence timestamp NOT NULL, 

55 
name text NOT NULL, 

56 
value text, 

57 
PRIMARY KEY(whence,sid,name) 

58 
); 

59 


60 


61 
CREATE TABLE stratcon.rollup_matrix_numeric_60m( 

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 
stddev_value numeric, 

68 
min_value numeric , 

69 
max_value numeric , 

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

71 


72 
CREATE TABLE stratcon.rollup_matrix_numeric_6hours( 

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 
stddev_value numeric, 

79 
min_value numeric , 

80 
max_value numeric , 

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

82 


83 
CREATE TABLE stratcon.rollup_matrix_numeric_12hours( 

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 
stddev_value numeric, 

90 
min_value numeric , 

91 
max_value numeric , 

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

93 


94 
CREATE TABLE stratcon.rollup_matrix_numeric_5m ( 

95 
sid integer NOT NULL, 

96 
name text NOT NULL, 

97 
rollup_time timestamp NOT NULL, 

98 
count_rows integer, 

99 
avg_value numeric, 

100 
stddev_value numeric, 

101 
min_value numeric, 

102 
max_value numeric, 

103 
PRIMARY KEY (rollup_time,sid,name) 

104 
); 

105 


106 
CREATE TABLE stratcon.rollup_matrix_numeric_20m ( 

107 
sid integer NOT NULL, 

108 
name text NOT NULL, 

109 
rollup_time timestamp NOT NULL, 

110 
count_rows integer, 

111 
avg_value numeric, 

112 
stddev_value numeric, 

113 
min_value numeric, 

114 
max_value numeric, 

115 
PRIMARY KEY (rollup_time,sid,name) 

116 
); 

117 


118 
CREATE TABLE stratcon.map_uuid_to_sid ( 

119 
id uuid NOT NULL, 

120 
sid integer NOT NULL, 

121 
PRIMARY KEY(id,sid) 

122 
); 

123 


124 
CREATE TABLE stratcon.log_whence_s ( 

125 
whence timestamp NOT NULL, 

126 
interval varchar2(20, 

127 
PRIMARY KEY(whence,interval) 

128 
); 

129 


130 
CREATE TABLE stratcon.rollup_runner ( 

131 
rollup_table character varying(100), 

132 
runner character varying(22) 

133 
); 

134 


135 
CREATE TABLE stratcon.metric_name_summary ( 

136 
sid integer NOT NULL, 

137 
metric_name text NOT NULL, 

138 
metric_type character varying(22), 

139 
active boolean default 'true', 

140 
PRIMARY KEY (sid,name) 

141 
); 

142 
 Schema Sequence 

143 


144 
CREATE SEQUENCE stratcon.seq_sid 

145 
START WITH 50 

146 
INCREMENT BY 1 

147 
NO MAXVALUE 

148 
NO MINVALUE 

149 
CACHE 1; 

150 


151 


152 


153 
 GRANTS 

154 


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

170 
ALTER TABLE stratcon.seq_sid OWNER TO stratcon; 

171 


172 


173 
 Function To generate SID from ID 

174 


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

176 
RETURNS integer 

177 
AS $$ 

178 
DECLARE 

179 
v_ex_sid integer; 

180 
v_new_sid integer; 

181 


182 
BEGIN 

183 


184 
SELECT sid FROM stratcon.map_uuid_to_sid WHERE id=v_in_id 

185 
INTO v_ex_sid; 

186 


187 
IF NOT FOUND THEN 

188 
SELECT nextval('stratcon.seq_sid') 

189 
INTO v_new_sid; 

190 


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

192 


193 
RETURN v_new_sid; 

194 
ELSE 

195 
RETURN v_ex_sid; 

196 
END IF; 

197 


198 
END 

199 
$$ LANGUAGE plpgsql; 

200 


201 
 Trigger Function to log dock status Changes 

202 


203 
CREATE TRIGGER loading_dock_status_s_change_log 

204 
AFTER INSERT ON stratcon.loading_dock_status_s 

205 
FOR EACH ROW 

206 
EXECUTE PROCEDURE stratcon.loading_dock_status_s_change_log(); 

207 


208 


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

210 
AS $$ 

211 
DECLARE 

212 
v_state CHAR(1); 

213 
v_avail CHAR(1); 

214 
BEGIN 

215 


216 
IF TG_OP = 'INSERT' THEN 

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

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

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

220 
INTO v_state,v_avail; 

221 


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

223 


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

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

226 


227 
END IF; 

228 


229 
ELSE 

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

231 
END IF; 

232 


233 
RETURN NULL; 

234 


235 
END 

236 
$$ 

237 
LANGUAGE plpgsql; 

238 


239 


240 
 Trigger Function to log Metrix Text Changes 

241 


242 
CREATE TRIGGER loading_dock_metric_text_s_change_log 

243 
AFTER INSERT ON stratcon.loading_dock_metric_text_s 

244 
FOR EACH ROW 

245 
EXECUTE PROCEDURE stratcon.loading_dock_metric_text_s_change_log(); 

246 


247 


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

249 
AS $$ 

250 
DECLARE 

251 
v_oldvalue TEXT; 

252 
v_sid integer; 

253 
v_name text; 

254 
BEGIN 

255 


256 
IF TG_OP = 'INSERT' THEN 

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

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

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

260 
INTO v_oldvalue; 

261 


262 
IF v_oldvalue IS DISTINCT FROM NEW.value THEN 

263 


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

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

266 
END IF; 

267 


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

269 
INTO v_sid,v_name; 

270 
IF NOT FOUND THEN 

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

272 
END IF; 

273 


274 
ELSE 

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

276 
END IF; 

277 


278 
RETURN NULL; 

279 


280 
END 

281 
$$ 

282 
LANGUAGE plpgsql; 

283 


284 
 Trigger on Metrix Numeric to log last inserted timestamp 

285 


286 
CREATE OR REPLACE FUNCTION stratcon.loading_dock_metric_numeric_s_whence_log() 

287 
RETURNS trigger 

288 
AS $$ 

289 
DECLARE 

290 
v_whence timestamptz; 

291 
v_sid integer; 

292 
v_name text; 

293 
BEGIN 

294 
IF TG_OP = 'INSERT' THEN 

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

296 
INTO v_whence; 

297 
IF NOT FOUND THEN 

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

299 
END IF; 

300 


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

302 
INTO v_sid,v_name; 

303 
IF NOT FOUND THEN 

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

305 
END IF; 

306 


307 
END IF; 

308 


309 
RETURN NULL; 

310 
END 

311 
$$ 

312 
LANGUAGE plpgsql; 

313 


314 
 5 minutes rollup 

315 


316 
CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_5m() 

317 
RETURNS void 

318 
AS $$ 

319 
DECLARE 

320 


321 
rec stratcon.rollup_matrix_numeric_5m%rowtype; 

322 
v_sql TEXT; 

323 
v_min_whence TIMESTAMP; 

324 
v_max_rollup_5 TIMESTAMP; 

325 
v_whence TIMESTAMP; 

326 
rows INT; 

327 
v_nrunning INT; 

328 
v_self VARCHAR(22); 

329 


330 
BEGIN 

331 


332 
SELECT COUNT(1) INTO v_nrunning 

333 
from stratcon.rollup_runner t, pg_stat_activity a 

334 
where rollup_table ='rollup_matrix_numeric_5m' 

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

336 


337 
IF v_nrunning > 0 THEN 

338 
RAISE NOTICE 'stratcon.rollup_matrix_numeric_5m already running'; 

339 
RETURN ; 

340 
END IF; 

341 


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

343 
FROM pg_stat_activity 

344 
WHERE procpid = pg_backend_pid(); 

345 


346 
IF v_self IS NULL THEN 

347 
RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_5m cannot selfidentify'; 

348 
END IF; 

349 


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

351 


352 
EXECUTE v_sql; 

353 


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

355 
INTO v_min_whence; 

356 


357 
SELECT MAX(rollup_time) FROM stratcon.rollup_matrix_numeric_5m 

358 
INTO v_max_rollup_5; 

359 


360 
 Insert Log for 20 minutes rollup 

361 


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

363 
INTO v_whence; 

364 
IF NOT FOUND THEN 

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

366 
END IF; 

367 


368 
IF v_min_whence <= v_max_rollup_5 THEN 

369 


370 
DELETE FROM stratcon.rollup_matrix_numeric_5m 

371 
WHERE rollup_time = v_min_whence; 

372 


373 
END IF; 

374 


375 
FOR rec IN 

376 
SELECT sid , name,v_min_whence as rollup_time, 

377 
COUNT(1) as count_rows ,AVG(value) as avg_value,STDDEV(value) as stddev_value ,MIN(value) as min_value ,MAX(value) as max_value 

378 
FROM stratcon.loading_dock_metric_numeric_s 

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

380 
GROUP BY rollup_time,sid,name 

381 


382 
LOOP 

383 


384 


385 
INSERT INTO stratcon.rollup_matrix_numeric_5m 

386 
(sid,name,rollup_time,count_rows,avg_value,stddev_value,min_value,max_value) VALUES 

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

388 


389 
END LOOP; 

390 


391 
 Delete from whence log table 

392 


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

394 


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

396 


397 
RETURN; 

398 


399 
EXCEPTION 

400 
WHEN RAISE_EXCEPTION THEN 

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

402 
RAISE EXCEPTION '%', SQLERRM; 

403 
WHEN OTHERS THEN 

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

405 
RAISE NOTICE '%', SQLERRM; 

406 
END 

407 
$$ LANGUAGE plpgsql; 

408 


409 


410 
 20 minutes rollup 

411 


412 
CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_20m() 

413 
RETURNS void 

414 
AS $$ 

415 
DECLARE 

416 


417 
rec stratcon.rollup_matrix_numeric_20m%rowtype; 

418 
v_sql TEXT; 

419 
v_min_whence TIMESTAMP; 

420 
v_max_rollup_20 TIMESTAMP; 

421 
v_whence TIMESTAMP; 

422 
rows INT; 

423 
v_nrunning INT; 

424 
v_self VARCHAR(22); 

425 


426 
BEGIN 

427 


428 
SELECT COUNT(1) INTO v_nrunning 

429 
from stratcon.rollup_runner t, pg_stat_activity a 

430 
where rollup_table ='rollup_matrix_numeric_20m' 

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

432 


433 
IF v_nrunning > 0 THEN 

434 
RAISE NOTICE 'stratcon.rollup_matrix_numeric_20m already running'; 

435 
RETURN ; 

436 
END IF; 

437 


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

439 
FROM pg_stat_activity 

440 
WHERE procpid = pg_backend_pid(); 

441 


442 
IF v_self IS NULL THEN 

443 
RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_20m cannot selfidentify'; 

444 
END IF; 

445 


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

447 


448 
EXECUTE v_sql; 

449 


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

451 
INTO v_min_whence; 

452 


453 
SELECT MAX(rollup_time) FROM stratcon.rollup_matrix_numeric_20m 

454 
INTO v_max_rollup_20; 

455 


456 
 Insert Log for Hourly rollup 

457 


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

459 
INTO v_whence; 

460 
IF NOT FOUND THEN 

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

462 
END IF; 

463 


464 
IF v_min_whence <= v_max_rollup_20 THEN 

465 


466 
DELETE FROM stratcon.rollup_matrix_numeric_20m 

467 
WHERE rollup_time = v_min_whence; 

468 


469 
END IF; 

470 


471 
FOR rec IN 

472 
SELECT sid , name,v_min_whence as rollup_time, 

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

474 
stddev(stddev_value) as stddev_value, 

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

476 
FROM stratcon.rollup_matrix_numeric_5m 

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

478 
GROUP BY sid,name 

479 


480 
LOOP 

481 


482 


483 
INSERT INTO stratcon.rollup_matrix_numeric_20m 

484 
(sid,name,rollup_time,count_rows,avg_value,stddev_value,min_value,max_value) VALUES 

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

486 


487 
END LOOP; 

488 


489 
 Delete from whence log table 

490 


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

492 


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

494 


495 
RETURN; 

496 


497 
EXCEPTION 

498 
WHEN RAISE_EXCEPTION THEN 

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

500 
RAISE EXCEPTION '%', SQLERRM; 

501 
WHEN OTHERS THEN 

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

503 
RAISE NOTICE '%', SQLERRM; 

504 
END 

505 
$$ LANGUAGE plpgsql; 

506 


507 


508 
 1 hour rollup 

509 


510 


511 
CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_60m() 

512 
RETURNS void 

513 
AS $$ 

514 
DECLARE 

515 
rec stratcon.rollup_matrix_numeric_60m%rowtype; 

516 
v_sql TEXT; 

517 
v_min_whence TIMESTAMP; 

518 
v_max_rollup_60 TIMESTAMP; 

519 
v_whence TIMESTAMP; 

520 
v_nrunning INT; 

521 
v_self VARCHAR(22); 

522 


523 
BEGIN 

524 


525 
SELECT COUNT(1) INTO v_nrunning 

526 
from stratcon.rollup_runner t, pg_stat_activity a 

527 
where rollup_table ='rollup_matrix_numeric_60m' 

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

529 


530 
IF v_nrunning > 0 THEN 

531 
RAISE NOTICE 'stratcon.rollup_matrix_numeric_60m already running'; 

532 
RETURN ; 

533 
END IF; 

534 


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

536 
FROM pg_stat_activity 

537 
WHERE procpid = pg_backend_pid(); 

538 


539 
IF v_self IS NULL THEN 

540 
RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_60m cannot selfidentify'; 

541 
END IF; 

542 


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

544 


545 
EXECUTE v_sql; 

546 


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

548 
INTO v_min_whence; 

549 


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

551 
INTO v_max_rollup_60; 

552 


553 
 Insert Log for 6 Hour rollup 

554 


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

556 
INTO v_whence; 

557 
IF NOT FOUND THEN 

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

559 
END IF; 

560 


561 


562 
IF v_min_whence <= v_max_rollup_60 THEN 

563 


564 
DELETE FROM stratcon.rollup_matrix_numeric_60m 

565 
WHERE rollup_time= v_min_whence; 

566 


567 
END IF; 

568 


569 
FOR rec IN 

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

571 
stddev(stddev_value) as stddev_value, 

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

573 
FROM stratcon.rollup_matrix_numeric_20m 

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

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

576 
LOOP 

577 


578 
INSERT INTO stratcon.rollup_matrix_numeric_60m 

579 
(sid,name,rollup_time,count_rows,avg_value,stddev_value,min_value,max_value) VALUES 

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

581 


582 
END LOOP; 

583 


584 


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

586 


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

588 


589 
RETURN; 

590 


591 
EXCEPTION 

592 
WHEN RAISE_EXCEPTION THEN 

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

594 
RAISE EXCEPTION '%', SQLERRM; 

595 
WHEN OTHERS THEN 

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

597 
RAISE NOTICE '%', SQLERRM; 

598 
END 

599 
$$ LANGUAGE plpgsql; 

600 


601 


602 
 6 hours 

603 


604 
CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_6hours() 

605 
RETURNS void 

606 
AS $$ 

607 
DECLARE 

608 
rec stratcon.rollup_matrix_numeric_6hours%rowtype; 

609 
v_sql TEXT; 

610 
v_min_whence TIMESTAMP; 

611 
v_max_rollup_6 TIMESTAMP; 

612 
v_whence TIMESTAMP; 

613 
v_nrunning INT; 

614 
v_self VARCHAR(22); 

615 


616 
BEGIN 

617 


618 
SELECT COUNT(1) INTO v_nrunning 

619 
from stratcon.rollup_runner t, pg_stat_activity a 

620 
where rollup_table ='rollup_matrix_numeric_6hours' 

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

622 


623 
IF v_nrunning > 0 THEN 

624 
RAISE NOTICE 'stratcon.rollup_matrix_numeric_6hours already running'; 

625 
RETURN ; 

626 
END IF; 

627 


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

629 
FROM pg_stat_activity 

630 
WHERE procpid = pg_backend_pid(); 

631 


632 
IF v_self IS NULL THEN 

633 
RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_6hours cannot selfidentify'; 

634 
END IF; 

635 


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

637 


638 
EXECUTE v_sql; 

639 


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

641 
INTO v_min_whence; 

642 


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

644 
INTO v_max_rollup_6; 

645 


646 
 Insert Log for 12 Hours rollup 

647 


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

649 
INTO v_whence; 

650 
IF NOT FOUND THEN 

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

652 
END IF; 

653 


654 


655 
IF v_min_whence <= v_max_rollup_6 THEN 

656 


657 
DELETE FROM stratcon.rollup_matrix_numeric_6hours 

658 
WHERE rollup_time= v_min_whence; 

659 


660 
END IF; 

661 


662 
FOR rec IN 

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

664 
STDDEV(stddev_value) as stddev_value, 

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

666 
FROM stratcon.rollup_matrix_numeric_60m 

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

668 
GROUP BY sid,name 

669 
LOOP 

670 


671 


672 
INSERT INTO stratcon.rollup_matrix_numeric_6hours 

673 
(sid,name,rollup_time,count_rows,avg_value,stddev_value,min_value,max_value) VALUES 

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

675 


676 
END LOOP; 

677 


678 


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

680 


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

682 


683 
RETURN; 

684 


685 
EXCEPTION 

686 
WHEN RAISE_EXCEPTION THEN 

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

688 
RAISE EXCEPTION '%', SQLERRM; 

689 
WHEN OTHERS THEN 

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

691 
RAISE NOTICE '%', SQLERRM; 

692 
END 

693 
$$ LANGUAGE plpgsql; 

694 


695 


696 
 12 hours 

697 


698 
CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_12hours() 

699 
RETURNS void 

700 
AS $$ 

701 
DECLARE 

702 
rec stratcon.rollup_matrix_numeric_12hours%rowtype; 

703 
v_sql TEXT; 

704 
v_min_whence TIMESTAMP; 

705 
v_max_rollup_12 TIMESTAMP; 

706 
v_whence TIMESTAMP; 

707 
v_nrunning INT; 

708 
v_self VARCHAR(22); 

709 


710 


711 
BEGIN 

712 


713 
SELECT COUNT(1) INTO v_nrunning 

714 
from stratcon.rollup_runner t, pg_stat_activity a 

715 
where rollup_table ='rollup_matrix_numeric_12hours' 

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

717 


718 
IF v_nrunning > 0 THEN 

719 
RAISE NOTICE 'stratcon.rollup_matrix_numeric_12hours already running'; 

720 
RETURN ; 

721 
END IF; 

722 


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

724 
FROM pg_stat_activity 

725 
WHERE procpid = pg_backend_pid(); 

726 


727 
IF v_self IS NULL THEN 

728 
RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_12hours cannot selfidentify'; 

729 
END IF; 

730 


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

732 


733 
EXECUTE v_sql; 

734 


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

736 
INTO v_min_whence; 

737 


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

739 
INTO v_max_rollup_12; 

740 


741 
/* Insert Log for 24 Hours rollup 

742 


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

744 
INTO v_whence; 

745 
IF NOT FOUND THEN 

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

747 
END IF; 

748 
*/ 

749 


750 
IF v_min_whence <= v_max_rollup_12 THEN 

751 


752 
DELETE FROM stratcon.rollup_matrix_numeric_12hours 

753 
WHERE rollup_time= v_min_whence; 

754 


755 
END IF; 

756 


757 
FOR rec IN 

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

759 
STDDEV(stddev_value) as stddev_value, 

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

761 
FROM stratcon.rollup_matrix_numeric_6hours 

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

763 
GROUP BY sid,name 

764 
LOOP 

765 


766 


767 
INSERT INTO stratcon.rollup_matrix_numeric_12hours 

768 
(sid,name,rollup_time,count_rows,avg_value,stddev_value,min_value,max_value) VALUES 

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

770 


771 
END LOOP; 

772 


773 


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

775 


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

777 


778 
RETURN; 

779 


780 
EXCEPTION 

781 
WHEN RAISE_EXCEPTION THEN 

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

783 
RAISE EXCEPTION '%', SQLERRM; 

784 
WHEN OTHERS THEN 

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

786 
RAISE NOTICE '%', SQLERRM; 

787 
END 

788 
$$ LANGUAGE plpgsql; 

789 


790 


791 
create or replace function 

792 
stratcon.fetch_varset(in_check uuid, 

793 
in_name text, 

794 
in_start_time timestamp, 

795 
in_end_time timestamp, 

796 
in_hopeful_nperiods int) 

797 
returns setof stratcon.loading_dock_metric_text_s_change_log as 

798 
$$ 

799 
declare 

800 
v_sid int; 

801 
v_target record; 

802 
v_start_adj timestamp; 

803 
v_start_text text; 

804 
v_next_text text; 

805 
v_end_adj timestamp; 

806 
v_change_row stratcon.loading_dock_metric_text_s_change_log%rowtype; 

807 
begin 

808 
 Map out uuid to an sid. 

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

810 
if not found then 

811 
return; 

812 
end if; 

813 


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

815 


816 
select 'epoch'::timestamp + 

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

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

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

820 
into v_start_adj; 

821 


822 
select 'epoch'::timestamp + 

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

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

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

826 
into v_end_adj; 

827 


828 
for v_change_row in 

829 
select sid, 'epoch'::timestamp + 

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

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

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

833 
name, value 

834 
from stratcon.loading_dock_metric_text_s_change_log 

835 
where sid = v_sid 

836 
and name = in_name 

837 
and whence <= v_start_adj 

838 
order by 'epoch'::timestamp + 

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

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

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

842 
limit 1 

843 
loop 

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

845 
end loop; 

846 


847 
for v_change_row in 

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

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

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

851 
 left join 

852 
(select 'epoch'::timestamp + 

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

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

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

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

857 
from stratcon.loading_dock_metric_text_s_change_log 

858 
where sid = v_sid 

859 
and name = in_name 

860 
and whence > v_start_adj 

861 
and whence <= v_end_adj) d 

862 
 using (whence) 

863 
order by whence asc 

864 
loop 

865 
v_next_text := v_change_row.value; 

866 
if v_change_row.value is not null and 

867 
v_start_text != v_change_row.value then 

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

869 
else 

870 
v_change_row.value := v_start_text; 

871 
end if; 

872 
if v_next_text is not null then 

873 
v_start_text := v_next_text; 

874 
end if; 

875 
return next v_change_row; 

876 
end loop; 

877 


878 
return; 

879 
end 

880 
$$ language 'plpgsql'; 

881 


882 


883 
create or replace function 

884 
stratcon.choose_window(in_start_time timestamp, 

885 
in_end_time timestamp, 

886 
in_hopeful_nperiods int, 

887 
out tablename text, 

888 
out period interval, 

889 
out nperiods int) 

890 
returns setof record as 

891 
$$ 

892 
declare 

893 
window record; 

894 
begin 

895 
 Figure out which table we should be looking in 

896 
for window in 

897 
select atablename, aperiod, anperiods 

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

899 
abs(case when iv/isec  in_hopeful_nperiods < 0 

900 
then 10 * (in_hopeful_nperiods  iv/isec) 

901 
else iv/isec  in_hopeful_nperiods 

902 
end) as badness 

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

904 
extract('epoch' from in_start_time) as iv 

905 
) i, 

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

907 
'rollup_matrix_numeric_5m' as atablename 

908 
union all 

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

910 
'rollup_matrix_numeric_20m' as atablename 

911 
union all 

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

913 
'rollup_matrix_numeric_60m' as atablename 

914 
union all 

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

916 
'rollup_matrix_numeric_6hours' as atablename 

917 
union all 

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

919 
'rollup_matrix_numeric_12hours' as atablename 

920 
) ivs 

921 
) b 

922 
order by badness asc 

923 
limit 1 

924 
loop 

925 
tablename := window.atablename; 

926 
period := window.aperiod; 

927 
nperiods := window.anperiods; 

928 
return next; 

929 
end loop; 

930 
return; 

931 
end 

932 
$$ language 'plpgsql'; 

933 


934 
create or replace function 

935 
stratcon.fetch_dataset(in_check uuid, 

936 
in_name text, 

937 
in_start_time timestamp, 

938 
in_end_time timestamp, 

939 
in_hopeful_nperiods int, 

940 
derive boolean) 

941 
returns setof stratcon.rollup_matrix_numeric_5m as 

942 
$$ 

943 
declare 

944 
v_sql text; 

945 
v_sid int; 

946 
v_target record; 

947 
v_interval numeric; 

948 
v_start_adj timestamp; 

949 
v_end_adj timestamp; 

950 
v_l_rollup_row stratcon.rollup_matrix_numeric_5m%rowtype; 

951 
v_rollup_row stratcon.rollup_matrix_numeric_5m%rowtype; 

952 
v_r_rollup_row stratcon.rollup_matrix_numeric_5m%rowtype; 

953 
begin 

954 


955 
 Map out uuid to an sid. 

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

957 
if not found then 

958 
return; 

959 
end if; 

960 


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

962 


963 
if not found then 

964 
raise exception 'no target table'; 

965 
return; 

966 
end if; 

967 


968 
select 'epoch'::timestamp + 

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

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

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

972 
into v_start_adj; 

973 


974 
select 'epoch'::timestamp + 

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

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

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

978 
into v_end_adj; 

979 


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

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

982 
'd.stddev_value, d.min_value, d.max_value '  

983 
' from '  

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

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

986 
' as rollup_time'  

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

988 
'left join '  

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

990 
' where sid = '  v_sid  

991 
' and name = '  quote_literal(in_name)  

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

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

994 
' using(rollup_time)'; 

995 


996 
for v_rollup_row in execute v_sql loop 

997 
if derive is true then 

998 
v_r_rollup_row := v_rollup_row; 

999 
if v_l_rollup_row.count_rows is not null and 

1000 
v_rollup_row.count_rows is not null then 

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

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

1003 
v_r_rollup_row.avg_value := 

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

1005 
v_r_rollup_row.stddev_value := 

1006 
(v_rollup_row.stddev_value  v_l_rollup_row.stddev_value) / v_interval; 

1007 
v_r_rollup_row.min_value := 

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

1009 
v_r_rollup_row.max_value := 

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

1011 
else 

1012 
v_r_rollup_row.count_rows = NULL; 

1013 
v_r_rollup_row.avg_value = NULL; 

1014 
v_r_rollup_row.stddev_value = NULL; 

1015 
v_r_rollup_row.min_value = NULL; 

1016 
v_r_rollup_row.max_value = NULL; 

1017 
end if; 

1018 
else 

1019 
v_r_rollup_row := v_rollup_row; 

1020 
end if; 

1021 
return next v_r_rollup_row; 

1022 
v_l_rollup_row := v_rollup_row; 

1023 
end loop; 

1024 
return; 

1025 
end 

1026 
$$ language 'plpgsql'; 

1027 


1028 
COMMIT; 
