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 
 Schema Sequence 

136 


137 
CREATE SEQUENCE stratcon.seq_sid 

138 
START WITH 50 

139 
INCREMENT BY 1 

140 
NO MAXVALUE 

141 
NO MINVALUE 

142 
CACHE 1; 

143 


144 


145 


146 
 GRANTS 

147 


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

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

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

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

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

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

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

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

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

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

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

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

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

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

162 
ALTER TABLE stratcon.seq_sid OWNER TO stratcon; 

163 


164 


165 
 Function To generate SID from ID 

166 


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

168 
RETURNS integer 

169 
AS $$ 

170 
DECLARE 

171 
v_ex_sid integer; 

172 
v_new_sid integer; 

173 


174 
BEGIN 

175 


176 
SELECT sid FROM stratcon.map_uuid_to_sid WHERE id=v_in_id 

177 
INTO v_ex_sid; 

178 


179 
IF NOT FOUND THEN 

180 
SELECT nextval('stratcon.seq_sid') 

181 
INTO v_new_sid; 

182 


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

184 


185 
RETURN v_new_sid; 

186 
ELSE 

187 
RETURN v_ex_sid; 

188 
END IF; 

189 


190 
END 

191 
$$ LANGUAGE plpgsql; 

192 


193 
 Trigger Function to log dock status Changes 

194 


195 
CREATE TRIGGER loading_dock_status_s_change_log 

196 
AFTER INSERT ON stratcon.loading_dock_status_s 

197 
FOR EACH ROW 

198 
EXECUTE PROCEDURE stratcon.loading_dock_status_s_change_log(); 

199 


200 


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

202 
AS $$ 

203 
DECLARE 

204 
v_state CHAR(1); 

205 
v_avail CHAR(1); 

206 
BEGIN 

207 


208 
IF TG_OP = 'INSERT' THEN 

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

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

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

212 
INTO v_state,v_avail; 

213 


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

215 


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

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

218 


219 
END IF; 

220 


221 
ELSE 

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

223 
END IF; 

224 


225 
RETURN NULL; 

226 


227 
END 

228 
$$ 

229 
LANGUAGE plpgsql; 

230 


231 


232 
 Trigger Function to log Metrix Text Changes 

233 


234 
CREATE TRIGGER loading_dock_metric_text_s_change_log 

235 
AFTER INSERT ON stratcon.loading_dock_metric_text_s 

236 
FOR EACH ROW 

237 
EXECUTE PROCEDURE stratcon.loading_dock_metric_text_s_change_log(); 

238 


239 


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

241 
AS $$ 

242 
DECLARE 

243 
v_oldvalue TEXT; 

244 
BEGIN 

245 


246 
IF TG_OP = 'INSERT' THEN 

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

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

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

250 
INTO v_oldvalue; 

251 


252 
IF v_oldvalue IS DISTINCT FROM NEW.value THEN 

253 


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

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

256 


257 
END IF; 

258 


259 
ELSE 

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

261 
END IF; 

262 


263 
RETURN NULL; 

264 


265 
END 

266 
$$ 

267 
LANGUAGE plpgsql; 

268 


269 
 Trigger on Metrix Numeric to log last inserted timestamp 

270 


271 
CREATE OR REPLACE FUNCTION stratcon.loading_dock_metric_numeric_s_whence_log() 

272 
RETURNS trigger 

273 
AS $$ 

274 
DECLARE 

275 
v_whence timestamptz; 

276 
BEGIN 

277 
IF TG_OP = 'INSERT' THEN 

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

279 
INTO v_whence; 

280 
IF NOT FOUND THEN 

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

282 
END IF; 

283 
END IF; 

284 


285 
RETURN NULL; 

286 
END 

287 
$$ 

288 
LANGUAGE plpgsql; 

289 


290 
 5 minutes rollup 

291 


292 
CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_5m() 

293 
RETURNS void 

294 
AS $$ 

295 
DECLARE 

296 


297 
rec stratcon.rollup_matrix_numeric_5m%rowtype; 

298 
v_sql TEXT; 

299 
v_min_whence TIMESTAMP; 

300 
v_max_rollup_5 TIMESTAMP; 

301 
v_whence TIMESTAMP; 

302 
rows INT; 

303 
v_nrunning INT; 

304 
v_self VARCHAR(22); 

305 


306 
BEGIN 

307 


308 
SELECT COUNT(1) INTO v_nrunning 

309 
from stratcon.rollup_runner t, pg_stat_activity a 

310 
where rollup_table ='rollup_matrix_numeric_5m' 

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

312 


313 
IF v_nrunning > 0 THEN 

314 
RAISE NOTICE 'stratcon.rollup_matrix_numeric_5m already running'; 

315 
RETURN ; 

316 
END IF; 

317 


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

319 
FROM pg_stat_activity 

320 
WHERE procpid = pg_backend_pid(); 

321 


322 
IF v_self IS NULL THEN 

323 
RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_5m cannot selfidentify'; 

324 
END IF; 

325 


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

327 


328 
EXECUTE v_sql; 

329 


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

331 
INTO v_min_whence; 

332 


333 
SELECT MAX(rollup_time) FROM stratcon.rollup_matrix_numeric_5m 

334 
INTO v_max_rollup_5; 

335 


336 
 Insert Log for 20 minutes rollup 

337 


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

339 
INTO v_whence; 

340 
IF NOT FOUND THEN 

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

342 
END IF; 

343 


344 
IF v_min_whence <= v_max_rollup_5 THEN 

345 


346 
DELETE FROM stratcon.rollup_matrix_numeric_5m 

347 
WHERE rollup_time = v_min_whence; 

348 


349 
END IF; 

350 


351 
FOR rec IN 

352 
SELECT sid , name,v_min_whence as rollup_time, 

353 
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 

354 
FROM stratcon.loading_dock_metric_numeric_s 

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

356 
GROUP BY rollup_time,sid,name 

357 


358 
LOOP 

359 


360 


361 
INSERT INTO stratcon.rollup_matrix_numeric_5m 

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

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

364 


365 
END LOOP; 

366 


367 
 Delete from whence log table 

368 


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

370 


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

372 


373 
RETURN; 

374 


375 
EXCEPTION 

376 
WHEN RAISE_EXCEPTION THEN 

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

378 
RAISE EXCEPTION '%', SQLERRM; 

379 
WHEN OTHERS THEN 

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

381 
RAISE NOTICE '%', SQLERRM; 

382 
END 

383 
$$ LANGUAGE plpgsql; 

384 


385 


386 
 20 minutes rollup 

387 


388 
CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_20m() 

389 
RETURNS void 

390 
AS $$ 

391 
DECLARE 

392 


393 
rec stratcon.rollup_matrix_numeric_20m%rowtype; 

394 
v_sql TEXT; 

395 
v_min_whence TIMESTAMP; 

396 
v_max_rollup_20 TIMESTAMP; 

397 
v_whence TIMESTAMP; 

398 
rows INT; 

399 
v_nrunning INT; 

400 
v_self VARCHAR(22); 

401 


402 
BEGIN 

403 


404 
SELECT COUNT(1) INTO v_nrunning 

405 
from stratcon.rollup_runner t, pg_stat_activity a 

406 
where rollup_table ='rollup_matrix_numeric_20m' 

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

408 


409 
IF v_nrunning > 0 THEN 

410 
RAISE NOTICE 'stratcon.rollup_matrix_numeric_20m already running'; 

411 
RETURN ; 

412 
END IF; 

413 


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

415 
FROM pg_stat_activity 

416 
WHERE procpid = pg_backend_pid(); 

417 


418 
IF v_self IS NULL THEN 

419 
RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_20m cannot selfidentify'; 

420 
END IF; 

421 


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

423 


424 
EXECUTE v_sql; 

425 


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

427 
INTO v_min_whence; 

428 


429 
SELECT MAX(rollup_time) FROM stratcon.rollup_matrix_numeric_20m 

430 
INTO v_max_rollup_20; 

431 


432 
 Insert Log for Hourly rollup 

433 


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

435 
INTO v_whence; 

436 
IF NOT FOUND THEN 

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

438 
END IF; 

439 


440 
IF v_min_whence <= v_max_rollup_20 THEN 

441 


442 
DELETE FROM stratcon.rollup_matrix_numeric_20m 

443 
WHERE rollup_time = v_min_whence; 

444 


445 
END IF; 

446 


447 
FOR rec IN 

448 
SELECT sid , name,v_min_whence as rollup_time, 

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

450 
stddev(stddev_value) as stddev_value, 

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

452 
FROM stratcon.rollup_matrix_numeric_5m 

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

454 
GROUP BY sid,name 

455 


456 
LOOP 

457 


458 


459 
INSERT INTO stratcon.rollup_matrix_numeric_20m 

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

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

462 


463 
END LOOP; 

464 


465 
 Delete from whence log table 

466 


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

468 


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

470 


471 
RETURN; 

472 


473 
EXCEPTION 

474 
WHEN RAISE_EXCEPTION THEN 

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

476 
RAISE EXCEPTION '%', SQLERRM; 

477 
WHEN OTHERS THEN 

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

479 
RAISE NOTICE '%', SQLERRM; 

480 
END 

481 
$$ LANGUAGE plpgsql; 

482 


483 


484 
 1 hour rollup 

485 


486 


487 
CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_60m() 

488 
RETURNS void 

489 
AS $$ 

490 
DECLARE 

491 
rec stratcon.rollup_matrix_numeric_60m%rowtype; 

492 
v_sql TEXT; 

493 
v_min_whence TIMESTAMP; 

494 
v_max_rollup_60 TIMESTAMP; 

495 
v_whence TIMESTAMP; 

496 
v_nrunning INT; 

497 
v_self VARCHAR(22); 

498 


499 
BEGIN 

500 


501 
SELECT COUNT(1) INTO v_nrunning 

502 
from stratcon.rollup_runner t, pg_stat_activity a 

503 
where rollup_table ='rollup_matrix_numeric_60m' 

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

505 


506 
IF v_nrunning > 0 THEN 

507 
RAISE NOTICE 'stratcon.rollup_matrix_numeric_60m already running'; 

508 
RETURN ; 

509 
END IF; 

510 


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

512 
FROM pg_stat_activity 

513 
WHERE procpid = pg_backend_pid(); 

514 


515 
IF v_self IS NULL THEN 

516 
RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_60m cannot selfidentify'; 

517 
END IF; 

518 


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

520 


521 
EXECUTE v_sql; 

522 


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

524 
INTO v_min_whence; 

525 


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

527 
INTO v_max_rollup_60; 

528 


529 
 Insert Log for 6 Hour rollup 

530 


531 
SELECT whence FROM stratcon.log_whence_s WHERE whence=date_trunc('H',v_min_whence) and interval='6 hours' 

532 
INTO v_whence; 

533 
IF NOT FOUND THEN 

534 
INSERT INTO stratcon.log_whence_s VALUES(date_trunc('H',v_min_whence),'6 hours'); 

535 
END IF; 

536 


537 


538 
IF v_min_whence <= v_max_rollup_60 THEN 

539 


540 
DELETE FROM stratcon.rollup_matrix_numeric_60m 

541 
WHERE rollup_time= v_min_whence; 

542 


543 
END IF; 

544 


545 
FOR rec IN 

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

547 
stddev(stddev_value) as stddev_value, 

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

549 
FROM stratcon.rollup_matrix_numeric_20m 

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

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

552 
LOOP 

553 


554 
INSERT INTO stratcon.rollup_matrix_numeric_60m 

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

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

557 


558 
END LOOP; 

559 


560 


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

562 


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

564 


565 
RETURN; 

566 


567 
EXCEPTION 

568 
WHEN RAISE_EXCEPTION THEN 

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

570 
RAISE EXCEPTION '%', SQLERRM; 

571 
WHEN OTHERS THEN 

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

573 
RAISE NOTICE '%', SQLERRM; 

574 
END 

575 
$$ LANGUAGE plpgsql; 

576 


577 


578 
 6 hours 

579 


580 
CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_6hours() 

581 
RETURNS void 

582 
AS $$ 

583 
DECLARE 

584 
rec stratcon.rollup_matrix_numeric_6hours%rowtype; 

585 
v_sql TEXT; 

586 
v_min_whence TIMESTAMP; 

587 
v_max_rollup_6 TIMESTAMP; 

588 
v_whence TIMESTAMP; 

589 
v_nrunning INT; 

590 
v_self VARCHAR(22); 

591 


592 
BEGIN 

593 


594 
SELECT COUNT(1) INTO v_nrunning 

595 
from stratcon.rollup_runner t, pg_stat_activity a 

596 
where rollup_table ='rollup_matrix_numeric_6hours' 

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

598 


599 
IF v_nrunning > 0 THEN 

600 
RAISE NOTICE 'stratcon.rollup_matrix_numeric_6hours already running'; 

601 
RETURN ; 

602 
END IF; 

603 


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

605 
FROM pg_stat_activity 

606 
WHERE procpid = pg_backend_pid(); 

607 


608 
IF v_self IS NULL THEN 

609 
RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_6hours cannot selfidentify'; 

610 
END IF; 

611 


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

613 


614 
EXECUTE v_sql; 

615 


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

617 
INTO v_min_whence; 

618 


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

620 
INTO v_max_rollup_6; 

621 


622 
 Insert Log for 12 Hours rollup 

623 


624 
SELECT whence FROM stratcon.log_whence_s WHERE whence=date_trunc('H',v_min_whence) and interval='12 hours' 

625 
INTO v_whence; 

626 
IF NOT FOUND THEN 

627 
INSERT INTO stratcon.log_whence_s VALUES(date_trunc('H',v_min_whence),'12 hours'); 

628 
END IF; 

629 


630 


631 
IF v_min_whence <= v_max_rollup_6 THEN 

632 


633 
DELETE FROM stratcon.rollup_matrix_numeric_6hours 

634 
WHERE rollup_time= v_min_whence; 

635 


636 
END IF; 

637 


638 
FOR rec IN 

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

640 
STDDEV(stddev_value) as stddev_value, 

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

642 
FROM stratcon.rollup_matrix_numeric_60m 

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

644 
GROUP BY sid,name 

645 
LOOP 

646 


647 


648 
INSERT INTO stratcon.rollup_matrix_numeric_6hours 

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

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

651 


652 
END LOOP; 

653 


654 


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

656 


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

658 


659 
RETURN; 

660 


661 
EXCEPTION 

662 
WHEN RAISE_EXCEPTION THEN 

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

664 
RAISE EXCEPTION '%', SQLERRM; 

665 
WHEN OTHERS THEN 

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

667 
RAISE NOTICE '%', SQLERRM; 

668 
END 

669 
$$ LANGUAGE plpgsql; 

670 


671 


672 
 12 hours 

673 


674 
CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_12hours() 

675 
RETURNS void 

676 
AS $$ 

677 
DECLARE 

678 
rec stratcon.rollup_matrix_numeric_12hours%rowtype; 

679 
v_sql TEXT; 

680 
v_min_whence TIMESTAMP; 

681 
v_max_rollup_12 TIMESTAMP; 

682 
v_whence TIMESTAMP; 

683 
v_nrunning INT; 

684 
v_self VARCHAR(22); 

685 


686 


687 
BEGIN 

688 


689 
SELECT COUNT(1) INTO v_nrunning 

690 
from stratcon.rollup_runner t, pg_stat_activity a 

691 
where rollup_table ='rollup_matrix_numeric_12hours' 

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

693 


694 
IF v_nrunning > 0 THEN 

695 
RAISE NOTICE 'stratcon.rollup_matrix_numeric_12hours already running'; 

696 
RETURN ; 

697 
END IF; 

698 


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

700 
FROM pg_stat_activity 

701 
WHERE procpid = pg_backend_pid(); 

702 


703 
IF v_self IS NULL THEN 

704 
RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_12hours cannot selfidentify'; 

705 
END IF; 

706 


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

708 


709 
EXECUTE v_sql; 

710 


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

712 
INTO v_min_whence; 

713 


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

715 
INTO v_max_rollup_12; 

716 


717 
/* Insert Log for 24 Hours rollup 

718 


719 
SELECT whence FROM stratcon.log_whence_s WHERE whence=date_trunc('H',v_min_whence) and interval='24 hours' 

720 
INTO v_whence; 

721 
IF NOT FOUND THEN 

722 
INSERT INTO stratcon.log_whence_s VALUES(date_trunc('H',v_min_whence),'24 hours'); 

723 
END IF; 

724 
*/ 

725 


726 
IF v_min_whence <= v_max_rollup_12 THEN 

727 


728 
DELETE FROM stratcon.rollup_matrix_numeric_12hours 

729 
WHERE rollup_time= v_min_whence; 

730 


731 
END IF; 

732 


733 
FOR rec IN 

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

735 
STDDEV(stddev_value) as stddev_value, 

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

737 
FROM stratcon.rollup_matrix_numeric_6hours 

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

739 
GROUP BY sid,name 

740 
LOOP 

741 


742 


743 
INSERT INTO stratcon.rollup_matrix_numeric_12hours 

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

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

746 


747 
END LOOP; 

748 


749 


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

751 


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

753 


754 
RETURN; 

755 


756 
EXCEPTION 

757 
WHEN RAISE_EXCEPTION THEN 

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

759 
RAISE EXCEPTION '%', SQLERRM; 

760 
WHEN OTHERS THEN 

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

762 
RAISE NOTICE '%', SQLERRM; 

763 
END 

764 
$$ LANGUAGE plpgsql; 

765 


766 


767 
create or replace function 

768 
stratcon.fetch_varset(in_check uuid, 

769 
in_name text, 

770 
in_start_time timestamp, 

771 
in_end_time timestamp, 

772 
in_hopeful_nperiods int) 

773 
returns setof stratcon.loading_dock_metric_text_s_change_log as 

774 
$$ 

775 
declare 

776 
v_sid int; 

777 
v_target record; 

778 
v_start_adj timestamp; 

779 
v_start_text text; 

780 
v_next_text text; 

781 
v_end_adj timestamp; 

782 
v_change_row stratcon.loading_dock_metric_text_s_change_log%rowtype; 

783 
begin 

784 
 Map out uuid to an sid. 

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

786 
if not found then 

787 
return; 

788 
end if; 

789 


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

791 


792 
select 'epoch'::timestamp + 

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

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

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

796 
into v_start_adj; 

797 


798 
select 'epoch'::timestamp + 

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

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

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

802 
into v_end_adj; 

803 


804 
for v_change_row in 

805 
select sid, 'epoch'::timestamp + 

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

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

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

809 
name, value 

810 
from stratcon.loading_dock_metric_text_s_change_log 

811 
where sid = v_sid 

812 
and name = in_name 

813 
and whence <= v_start_adj 

814 
order by 'epoch'::timestamp + 

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

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

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

818 
limit 1 

819 
loop 

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

821 
end loop; 

822 


823 
for v_change_row in 

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

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

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

827 
 left join 

828 
(select 'epoch'::timestamp + 

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

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

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

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

833 
from stratcon.loading_dock_metric_text_s_change_log 

834 
where sid = v_sid 

835 
and name = in_name 

836 
and whence > v_start_adj 

837 
and whence <= v_end_adj) d 

838 
 using (whence) 

839 
order by whence asc 

840 
loop 

841 
v_next_text := v_change_row.value; 

842 
if v_change_row.value is not null and 

843 
v_start_text != v_change_row.value then 

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

845 
else 

846 
v_change_row.value := v_start_text; 

847 
end if; 

848 
if v_next_text is not null then 

849 
v_start_text := v_next_text; 

850 
end if; 

851 
return next v_change_row; 

852 
end loop; 

853 


854 
return; 

855 
end 

856 
$$ language 'plpgsql'; 

857 


858 


859 
create or replace function 

860 
stratcon.choose_window(in_start_time timestamp, 

861 
in_end_time timestamp, 

862 
in_hopeful_nperiods int, 

863 
out tablename text, 

864 
out period interval, 

865 
out nperiods int) 

866 
returns setof record as 

867 
$$ 

868 
declare 

869 
window record; 

870 
begin 

871 
 Figure out which table we should be looking in 

872 
for window in 

873 
select atablename, aperiod, anperiods 

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

875 
abs(case when iv/isec  in_hopeful_nperiods < 0 

876 
then 10 * (in_hopeful_nperiods  iv/isec) 

877 
else iv/isec  in_hopeful_nperiods 

878 
end) as badness 

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

880 
extract('epoch' from in_start_time) as iv 

881 
) i, 

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

883 
'rollup_matrix_numeric_5m' as atablename 

884 
union all 

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

886 
'rollup_matrix_numeric_20m' as atablename 

887 
union all 

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

889 
'rollup_matrix_numeric_60m' as atablename 

890 
union all 

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

892 
'rollup_matrix_numeric_6hours' as atablename 

893 
union all 

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

895 
'rollup_matrix_numeric_12hours' as atablename 

896 
) ivs 

897 
) b 

898 
order by badness asc 

899 
limit 1 

900 
loop 

901 
tablename := window.atablename; 

902 
period := window.aperiod; 

903 
nperiods := window.anperiods; 

904 
return next; 

905 
end loop; 

906 
return; 

907 
end 

908 
$$ language 'plpgsql'; 

909 


910 
create or replace function 

911 
stratcon.fetch_dataset(in_check uuid, 

912 
in_name text, 

913 
in_start_time timestamp, 

914 
in_end_time timestamp, 

915 
in_hopeful_nperiods int, 

916 
derive boolean) 

917 
returns setof stratcon.rollup_matrix_numeric_5m as 

918 
$$ 

919 
declare 

920 
v_sql text; 

921 
v_sid int; 

922 
v_target record; 

923 
v_interval numeric; 

924 
v_start_adj timestamp; 

925 
v_end_adj timestamp; 

926 
v_l_rollup_row stratcon.rollup_matrix_numeric_5m%rowtype; 

927 
v_rollup_row stratcon.rollup_matrix_numeric_5m%rowtype; 

928 
v_r_rollup_row stratcon.rollup_matrix_numeric_5m%rowtype; 

929 
begin 

930 


931 
 Map out uuid to an sid. 

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

933 
if not found then 

934 
return; 

935 
end if; 

936 


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

938 


939 
select 'epoch'::timestamp + 

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

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

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

943 
into v_start_adj; 

944 


945 
select 'epoch'::timestamp + 

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

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

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

949 
into v_end_adj; 

950 


951 
if not found then 

952 
raise exception 'no target table'; 

953 
return; 

954 
end if; 

955 


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

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

958 
'd.stddev_value, d.min_value, d.max_value '  

959 
' from '  

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

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

962 
' as rollup_time'  

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

964 
'left join '  

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

966 
' where sid = '  v_sid  

967 
' and name = '  quote_literal(in_name)  

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

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

970 
' using(rollup_time)'; 

971 


972 
for v_rollup_row in execute v_sql loop 

973 
if derive is true then 

974 
v_r_rollup_row := v_rollup_row; 

975 
if v_l_rollup_row.count_rows is not null and 

976 
v_rollup_row.count_rows is not null then 

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

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

979 
v_r_rollup_row.avg_value := 

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

981 
v_r_rollup_row.stddev_value := 

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

983 
v_r_rollup_row.min_value := 

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

985 
v_r_rollup_row.max_value := 

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

987 
else 

988 
v_r_rollup_row.count_rows = NULL; 

989 
v_r_rollup_row.avg_value = NULL; 

990 
v_r_rollup_row.stddev_value = NULL; 

991 
v_r_rollup_row.min_value = NULL; 

992 
v_r_rollup_row.max_value = NULL; 

993 
end if; 

994 
else 

995 
v_r_rollup_row := v_rollup_row; 

996 
end if; 

997 
return next v_r_rollup_row; 

998 
v_l_rollup_row := v_rollup_row; 

999 
end loop; 

1000 
return; 

1001 
end 

1002 
$$ language 'plpgsql'; 

1003 


1004 
COMMIT; 
