1 
BEGIN; 

2 


3 
 Tables 

4 


5 
CREATE TABLE stratcon.loading_dock_check_s ( 

6 
sid integer NOT NULL, 

7 
remote_address inet, 

8 
whence timestamptz NOT NULL, 

9 
id uuid NOT NULL, 

10 
target text NOT NULL, 

11 
module text NOT NULL, 

12 
name text NOT NULL, 

13 
PRIMARY KEY(sid,id,whence) 

14 
); 

15 


16 
CREATE TABLE stratcon.loading_dock_status_s ( 

17 
sid integer NOT NULL, 

18 
whence timestamptz NOT NULL, 

19 
state character(1) NOT NULL, 

20 
availability character(1) NOT NULL, 

21 
duration integer NOT NULL, 

22 
status text, 

23 
PRIMARY KEY(sid,whence) 

24 
); 

25 


26 
CREATE TABLE stratcon.loading_dock_status_s_change_log ( 

27 
sid integer NOT NULL, 

28 
whence timestamptz NOT NULL, 

29 
state character(1) NOT NULL, 

30 
availability character(1) NOT NULL, 

31 
duration integer NOT NULL, 

32 
status text, 

33 
PRIMARY KEY(sid,whence) 

34 
); 

35 


36 


37 
CREATE TABLE stratcon.loading_dock_metric_text_s ( 

38 
sid integer NOT NULL, 

39 
whence timestamptz NOT NULL, 

40 
name text NOT NULL, 

41 
value text, 

42 
PRIMARY KEY(whence,sid,name) 

43 
); 

44 


45 
CREATE TABLE stratcon.loading_dock_metric_text_s_change_log ( 

46 
sid integer NOT NULL, 

47 
whence timestamptz NOT NULL, 

48 
name text NOT NULL, 

49 
value text, 

50 
PRIMARY KEY(whence,sid,name) 

51 
); 

52 


53 
CREATE TABLE stratcon.loading_dock_metric_numeric_s ( 

54 
sid integer NOT NULL, 

55 
whence timestamptz NOT NULL, 

56 
name text NOT NULL, 

57 
value numeric, 

58 
PRIMARY KEY(whence,sid,name) 

59 
); 

60 


61 
CREATE TABLE stratcon.rollup_matrix_numeric_5m ( 

62 
sid integer NOT NULL, 

63 
name text NOT NULL, 

64 
rollup_time timestamptz NOT NULL, 

65 
count_rows integer, 

66 
avg_value numeric, 

67 
min_value numeric, 

68 
max_value numeric, 

69 
PRIMARY KEY (rollup_time,sid,name) 

70 
); 

71 


72 
CREATE TABLE stratcon.rollup_matrix_numeric_20m ( 

73 
sid integer NOT NULL, 

74 
name text NOT NULL, 

75 
rollup_time timestamptz NOT NULL, 

76 
count_rows integer, 

77 
avg_value numeric, 

78 
min_value numeric, 

79 
max_value numeric, 

80 
PRIMARY KEY (rollup_time,sid,name) 

81 
); 

82 


83 
CREATE OR REPLACE FUNCTION stratcon.date_hour(timestamptz) 

84 
RETURNS timestamptz as $BODY$ 

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

86 
$BODY$ 

87 
language 'sql' 

88 
IMMUTABLE STRICT; 

89 


90 
CREATE INDEX idx_rollup_matrix_numeric_20m_rollup_time 

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

92 


93 
CREATE TABLE stratcon.rollup_matrix_numeric_60m( 

94 
sid integer not null, 

95 
name text not null, 

96 
rollup_time timestamptz not null, 

97 
count_rows integer, 

98 
avg_value numeric , 

99 
min_value numeric , 

100 
max_value numeric , 

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

102 


103 
CREATE TABLE stratcon.rollup_matrix_numeric_6hours( 

104 
sid integer not null, 

105 
name text not null, 

106 
rollup_time timestamptz not null, 

107 
count_rows integer, 

108 
avg_value numeric , 

109 
min_value numeric , 

110 
max_value numeric , 

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

112 


113 
CREATE TABLE stratcon.rollup_matrix_numeric_12hours( 

114 
sid integer not null, 

115 
name text not null, 

116 
rollup_time timestamptz not null, 

117 
count_rows integer, 

118 
avg_value numeric , 

119 
min_value numeric , 

120 
max_value numeric , 

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

122 


123 
CREATE TABLE stratcon.map_uuid_to_sid ( 

124 
id uuid NOT NULL, 

125 
sid integer NOT NULL, 

126 
PRIMARY KEY(id,sid) 

127 
); 

128 


129 
CREATE TABLE stratcon.log_whence_s ( 

130 
whence timestamptz NOT NULL, 

131 
interval varchar2(20, 

132 
PRIMARY KEY(whence,interval) 

133 
); 

134 


135 
CREATE TABLE stratcon.rollup_runner ( 

136 
rollup_table character varying(100), 

137 
runner character varying(22) 

138 
); 

139 


140 
CREATE TABLE stratcon.metric_name_summary ( 

141 
sid integer NOT NULL, 

142 
metric_name text NOT NULL, 

143 
metric_type character varying(22), 

144 
active boolean default 'true', 

145 
PRIMARY KEY (sid,name) 

146 
); 

147 
 Schema Sequence 

148 


149 
CREATE SEQUENCE stratcon.seq_sid 

150 
START WITH 1 

151 
INCREMENT BY 1 

152 
NO MAXVALUE 

153 
NO MINVALUE 

154 
CACHE 1; 

155 


156 


157 


158 
 GRANTS 

159 


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

175 
ALTER TABLE stratcon.seq_sid OWNER TO stratcon; 

176 


177 


178 
 Function To generate SID from ID 

179 


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

181 
RETURNS integer 

182 
AS $$ 

183 
DECLARE 

184 
v_ex_sid integer; 

185 
v_new_sid integer; 

186 


187 
BEGIN 

188 


189 
SELECT sid FROM stratcon.map_uuid_to_sid WHERE id=v_in_id 

190 
INTO v_ex_sid; 

191 


192 
IF NOT FOUND THEN 

193 
SELECT nextval('stratcon.seq_sid') 

194 
INTO v_new_sid; 

195 


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

197 


198 
RETURN v_new_sid; 

199 
ELSE 

200 
RETURN v_ex_sid; 

201 
END IF; 

202 


203 
END 

204 
$$ LANGUAGE plpgsql; 

205 


206 
 Trigger Function to log dock status Changes 

207 


208 
CREATE TRIGGER loading_dock_status_s_change_log 

209 
AFTER INSERT ON stratcon.loading_dock_status_s 

210 
FOR EACH ROW 

211 
EXECUTE PROCEDURE stratcon.loading_dock_status_s_change_log(); 

212 


213 


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

215 
AS $$ 

216 
DECLARE 

217 
v_state CHAR(1); 

218 
v_avail CHAR(1); 

219 
BEGIN 

220 


221 
IF TG_OP = 'INSERT' THEN 

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

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

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

225 
INTO v_state,v_avail; 

226 


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

228 


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

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

231 


232 
END IF; 

233 


234 
ELSE 

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

236 
END IF; 

237 


238 
RETURN NULL; 

239 


240 
END 

241 
$$ 

242 
LANGUAGE plpgsql; 

243 


244 


245 
 Trigger Function to log Metrix Text Changes 

246 


247 
CREATE TRIGGER loading_dock_metric_text_s_change_log 

248 
AFTER INSERT ON stratcon.loading_dock_metric_text_s 

249 
FOR EACH ROW 

250 
EXECUTE PROCEDURE stratcon.loading_dock_metric_text_s_change_log(); 

251 


252 


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

254 
AS $$ 

255 
DECLARE 

256 
v_oldvalue TEXT; 

257 
v_sid integer; 

258 
v_name text; 

259 
BEGIN 

260 


261 
IF TG_OP = 'INSERT' THEN 

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

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

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

265 
INTO v_oldvalue; 

266 


267 
IF v_oldvalue IS DISTINCT FROM NEW.value THEN 

268 


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

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

271 
END IF; 

272 


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

274 
INTO v_sid,v_name; 

275 
IF NOT FOUND THEN 

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

277 
END IF; 

278 


279 
ELSE 

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

281 
END IF; 

282 


283 
RETURN NULL; 

284 


285 
END 

286 
$$ 

287 
LANGUAGE plpgsql; 

288 


289 
 Trigger on Metrix Numeric to log last inserted timestamp 

290 


291 
CREATE OR REPLACE FUNCTION stratcon.loading_dock_metric_numeric_s_whence_log() 

292 
RETURNS trigger 

293 
AS $$ 

294 
DECLARE 

295 
v_whence timestamptz; 

296 
v_sid integer; 

297 
v_name text; 

298 
BEGIN 

299 
IF TG_OP = 'INSERT' THEN 

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

301 
INTO v_whence; 

302 
IF NOT FOUND THEN 

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

304 
END IF; 

305 


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

307 
INTO v_sid,v_name; 

308 
IF NOT FOUND THEN 

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

310 
END IF; 

311 


312 
END IF; 

313 


314 
RETURN NULL; 

315 
END 

316 
$$ 

317 
LANGUAGE plpgsql; 

318 


319 
 5 minutes rollup 

320 


321 
CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_5m() 

322 
RETURNS void 

323 
AS $$ 

324 
DECLARE 

325 


326 
rec stratcon.rollup_matrix_numeric_5m%rowtype; 

327 
v_sql TEXT; 

328 
v_min_whence TIMESTAMPTZ; 

329 
v_max_rollup_5 TIMESTAMPTZ; 

330 
v_whence TIMESTAMPTZ; 

331 
rows INT; 

332 
v_nrunning INT; 

333 
v_self VARCHAR(22); 

334 


335 
BEGIN 

336 


337 
SELECT COUNT(1) INTO v_nrunning 

338 
from stratcon.rollup_runner t, pg_stat_activity a 

339 
where rollup_table ='rollup_matrix_numeric_5m' 

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

341 


342 
IF v_nrunning > 0 THEN 

343 
RAISE NOTICE 'stratcon.rollup_matrix_numeric_5m already running'; 

344 
RETURN ; 

345 
END IF; 

346 


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

348 
FROM pg_stat_activity 

349 
WHERE procpid = pg_backend_pid(); 

350 


351 
IF v_self IS NULL THEN 

352 
RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_5m cannot selfidentify'; 

353 
END IF; 

354 


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

356 


357 
EXECUTE v_sql; 

358 


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

360 
INTO v_min_whence; 

361 


362 
SELECT MAX(rollup_time) FROM stratcon.rollup_matrix_numeric_5m 

363 
INTO v_max_rollup_5; 

364 


365 
 Insert Log for 20 minutes rollup 

366 


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

368 
INTO v_whence; 

369 
IF NOT FOUND THEN 

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

371 
END IF; 

372 


373 
IF v_min_whence <= v_max_rollup_5 THEN 

374 


375 
DELETE FROM stratcon.rollup_matrix_numeric_5m 

376 
WHERE rollup_time = v_min_whence; 

377 


378 
END IF; 

379 


380 
FOR rec IN 

381 
SELECT sid , name,v_min_whence as rollup_time, 

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

383 
FROM stratcon.loading_dock_metric_numeric_s 

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

385 
GROUP BY rollup_time,sid,name 

386 


387 
LOOP 

388 


389 


390 
INSERT INTO stratcon.rollup_matrix_numeric_5m 

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

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

393 


394 
END LOOP; 

395 


396 
 Delete from whence log table 

397 


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

399 


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

401 


402 
RETURN; 

403 


404 
EXCEPTION 

405 
WHEN RAISE_EXCEPTION THEN 

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

407 
RAISE EXCEPTION '%', SQLERRM; 

408 
WHEN OTHERS THEN 

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

410 
RAISE NOTICE '%', SQLERRM; 

411 
END 

412 
$$ LANGUAGE plpgsql; 

413 


414 


415 
 20 minutes rollup 

416 


417 
CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_20m() 

418 
RETURNS void 

419 
AS $$ 

420 
DECLARE 

421 


422 
rec stratcon.rollup_matrix_numeric_20m%rowtype; 

423 
v_sql TEXT; 

424 
v_min_whence TIMESTAMPTZ; 

425 
v_max_rollup_20 TIMESTAMPTZ; 

426 
v_whence TIMESTAMPTZ; 

427 
rows INT; 

428 
v_nrunning INT; 

429 
v_self VARCHAR(22); 

430 


431 
BEGIN 

432 


433 
SELECT COUNT(1) INTO v_nrunning 

434 
from stratcon.rollup_runner t, pg_stat_activity a 

435 
where rollup_table ='rollup_matrix_numeric_20m' 

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

437 


438 
IF v_nrunning > 0 THEN 

439 
RAISE NOTICE 'stratcon.rollup_matrix_numeric_20m already running'; 

440 
RETURN ; 

441 
END IF; 

442 


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

444 
FROM pg_stat_activity 

445 
WHERE procpid = pg_backend_pid(); 

446 


447 
IF v_self IS NULL THEN 

448 
RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_20m cannot selfidentify'; 

449 
END IF; 

450 


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

452 


453 
EXECUTE v_sql; 

454 


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

456 
INTO v_min_whence; 

457 


458 
SELECT MAX(rollup_time) FROM stratcon.rollup_matrix_numeric_20m 

459 
INTO v_max_rollup_20; 

460 


461 
 Insert Log for Hourly rollup 

462 


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

464 
INTO v_whence; 

465 
IF NOT FOUND THEN 

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

467 
END IF; 

468 


469 
IF v_min_whence <= v_max_rollup_20 THEN 

470 


471 
DELETE FROM stratcon.rollup_matrix_numeric_20m 

472 
WHERE rollup_time = v_min_whence; 

473 


474 
END IF; 

475 


476 
FOR rec IN 

477 
SELECT sid , name,v_min_whence as rollup_time, 

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

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

480 
FROM stratcon.rollup_matrix_numeric_5m 

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

482 
GROUP BY sid,name 

483 


484 
LOOP 

485 


486 


487 
INSERT INTO stratcon.rollup_matrix_numeric_20m 

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

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

490 


491 
END LOOP; 

492 


493 
 Delete from whence log table 

494 


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

496 


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

498 


499 
RETURN; 

500 


501 
EXCEPTION 

502 
WHEN RAISE_EXCEPTION THEN 

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

504 
RAISE EXCEPTION '%', SQLERRM; 

505 
WHEN OTHERS THEN 

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

507 
RAISE NOTICE '%', SQLERRM; 

508 
END 

509 
$$ LANGUAGE plpgsql; 

510 


511 


512 
 1 hour rollup 

513 


514 


515 
CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_60m() 

516 
RETURNS void 

517 
AS $$ 

518 
DECLARE 

519 
rec stratcon.rollup_matrix_numeric_60m%rowtype; 

520 
v_sql TEXT; 

521 
v_min_whence TIMESTAMPTZ; 

522 
v_max_rollup_60 TIMESTAMPTZ; 

523 
v_whence TIMESTAMPTZ; 

524 
v_nrunning INT; 

525 
v_self VARCHAR(22); 

526 


527 
BEGIN 

528 


529 
SELECT COUNT(1) INTO v_nrunning 

530 
from stratcon.rollup_runner t, pg_stat_activity a 

531 
where rollup_table ='rollup_matrix_numeric_60m' 

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

533 


534 
IF v_nrunning > 0 THEN 

535 
RAISE NOTICE 'stratcon.rollup_matrix_numeric_60m already running'; 

536 
RETURN ; 

537 
END IF; 

538 


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

540 
FROM pg_stat_activity 

541 
WHERE procpid = pg_backend_pid(); 

542 


543 
IF v_self IS NULL THEN 

544 
RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_60m cannot selfidentify'; 

545 
END IF; 

546 


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

548 


549 
EXECUTE v_sql; 

550 


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

552 
INTO v_min_whence; 

553 


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

555 
INTO v_max_rollup_60; 

556 


557 
 Insert Log for 6 Hour rollup 

558 


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

560 
INTO v_whence; 

561 
IF NOT FOUND THEN 

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

563 
END IF; 

564 


565 


566 
IF v_min_whence <= v_max_rollup_60 THEN 

567 


568 
DELETE FROM stratcon.rollup_matrix_numeric_60m 

569 
WHERE rollup_time= v_min_whence; 

570 


571 
END IF; 

572 


573 
FOR rec IN 

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

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

576 
FROM stratcon.rollup_matrix_numeric_20m 

577 
WHERE date_hour(rollup_time)= v_min_whence 

578 
GROUP BY date_hour(rollup_time),sid,name 

579 
LOOP 

580 


581 
INSERT INTO stratcon.rollup_matrix_numeric_60m 

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

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

584 


585 
END LOOP; 

586 


587 


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

589 


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

591 


592 
RETURN; 

593 


594 
EXCEPTION 

595 
WHEN RAISE_EXCEPTION THEN 

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

597 
RAISE EXCEPTION '%', SQLERRM; 

598 
WHEN OTHERS THEN 

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

600 
RAISE NOTICE '%', SQLERRM; 

601 
END 

602 
$$ LANGUAGE plpgsql; 

603 


604 


605 
 6 hours 

606 


607 
CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_6hours() 

608 
RETURNS void 

609 
AS $$ 

610 
DECLARE 

611 
rec stratcon.rollup_matrix_numeric_6hours%rowtype; 

612 
v_sql TEXT; 

613 
v_min_whence TIMESTAMPTZ; 

614 
v_max_rollup_6 TIMESTAMPTZ; 

615 
v_whence TIMESTAMPTZ; 

616 
v_nrunning INT; 

617 
v_self VARCHAR(22); 

618 


619 
BEGIN 

620 


621 
SELECT COUNT(1) INTO v_nrunning 

622 
from stratcon.rollup_runner t, pg_stat_activity a 

623 
where rollup_table ='rollup_matrix_numeric_6hours' 

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

625 


626 
IF v_nrunning > 0 THEN 

627 
RAISE NOTICE 'stratcon.rollup_matrix_numeric_6hours already running'; 

628 
RETURN ; 

629 
END IF; 

630 


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

632 
FROM pg_stat_activity 

633 
WHERE procpid = pg_backend_pid(); 

634 


635 
IF v_self IS NULL THEN 

636 
RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_6hours cannot selfidentify'; 

637 
END IF; 

638 


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

640 


641 
EXECUTE v_sql; 

642 


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

644 
INTO v_min_whence; 

645 


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

647 
INTO v_max_rollup_6; 

648 


649 
 Insert Log for 12 Hours rollup 

650 


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

652 
INTO v_whence; 

653 
IF NOT FOUND THEN 

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

655 
END IF; 

656 


657 


658 
IF v_min_whence <= v_max_rollup_6 THEN 

659 


660 
DELETE FROM stratcon.rollup_matrix_numeric_6hours 

661 
WHERE rollup_time= v_min_whence; 

662 


663 
END IF; 

664 


665 
FOR rec IN 

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

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

668 
FROM stratcon.rollup_matrix_numeric_60m 

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

670 
GROUP BY sid,name 

671 
LOOP 

672 


673 


674 
INSERT INTO stratcon.rollup_matrix_numeric_6hours 

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

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

677 


678 
END LOOP; 

679 


680 


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

682 


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

684 


685 
RETURN; 

686 


687 
EXCEPTION 

688 
WHEN RAISE_EXCEPTION THEN 

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

690 
RAISE EXCEPTION '%', SQLERRM; 

691 
WHEN OTHERS THEN 

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

693 
RAISE NOTICE '%', SQLERRM; 

694 
END 

695 
$$ LANGUAGE plpgsql; 

696 


697 


698 
 12 hours 

699 


700 
CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_12hours() 

701 
RETURNS void 

702 
AS $$ 

703 
DECLARE 

704 
rec stratcon.rollup_matrix_numeric_12hours%rowtype; 

705 
v_sql TEXT; 

706 
v_min_whence TIMESTAMPTZ; 

707 
v_max_rollup_12 TIMESTAMPTZ; 

708 
v_whence TIMESTAMPTZ; 

709 
v_nrunning INT; 

710 
v_self VARCHAR(22); 

711 


712 


713 
BEGIN 

714 


715 
SELECT COUNT(1) INTO v_nrunning 

716 
from stratcon.rollup_runner t, pg_stat_activity a 

717 
where rollup_table ='rollup_matrix_numeric_12hours' 

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

719 


720 
IF v_nrunning > 0 THEN 

721 
RAISE NOTICE 'stratcon.rollup_matrix_numeric_12hours already running'; 

722 
RETURN ; 

723 
END IF; 

724 


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

726 
FROM pg_stat_activity 

727 
WHERE procpid = pg_backend_pid(); 

728 


729 
IF v_self IS NULL THEN 

730 
RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_12hours cannot selfidentify'; 

731 
END IF; 

732 


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

734 


735 
EXECUTE v_sql; 

736 


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

738 
INTO v_min_whence; 

739 


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

741 
INTO v_max_rollup_12; 

742 


743 
/* Insert Log for 24 Hours rollup 

744 


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

746 
INTO v_whence; 

747 
IF NOT FOUND THEN 

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

749 
END IF; 

750 
*/ 

751 


752 
IF v_min_whence <= v_max_rollup_12 THEN 

753 


754 
DELETE FROM stratcon.rollup_matrix_numeric_12hours 

755 
WHERE rollup_time= v_min_whence; 

756 


757 
END IF; 

758 


759 
FOR rec IN 

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

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

762 
FROM stratcon.rollup_matrix_numeric_6hours 

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

764 
GROUP BY sid,name 

765 
LOOP 

766 


767 


768 
INSERT INTO stratcon.rollup_matrix_numeric_12hours 

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

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

771 


772 
END LOOP; 

773 


774 


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

776 


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

778 


779 
RETURN; 

780 


781 
EXCEPTION 

782 
WHEN RAISE_EXCEPTION THEN 

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

784 
RAISE EXCEPTION '%', SQLERRM; 

785 
WHEN OTHERS THEN 

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

787 
RAISE NOTICE '%', SQLERRM; 

788 
END 

789 
$$ LANGUAGE plpgsql; 

790 


791 


792 
create or replace function 

793 
stratcon.fetch_varset(in_check uuid, 

794 
in_name text, 

795 
in_start_time timestamptz, 

796 
in_end_time timestamptz, 

797 
in_hopeful_nperiods int) 

798 
returns setof stratcon.loading_dock_metric_text_s_change_log as 

799 
$$ 

800 
declare 

801 
v_sid int; 

802 
v_target record; 

803 
v_start_adj timestamptz; 

804 
v_start_text text; 

805 
v_next_text text; 

806 
v_end_adj timestamptz; 

807 
v_change_row stratcon.loading_dock_metric_text_s_change_log%rowtype; 

808 
begin 

809 
 Map out uuid to an sid. 

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

811 
if not found then 

812 
return; 

813 
end if; 

814 


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

816 


817 
select 'epoch'::timestamp + 

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

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

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

821 
into v_start_adj; 

822 


823 
select 'epoch'::timestamp + 

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

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

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

827 
into v_end_adj; 

828 


829 
for v_change_row in 

830 
select sid, 'epoch'::timestamp + 

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

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

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

834 
name, value 

835 
from stratcon.loading_dock_metric_text_s_change_log 

836 
where sid = v_sid 

837 
and name = in_name 

838 
and whence <= v_start_adj 

839 
order by 'epoch'::timestamp + 

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

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

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

843 
limit 1 

844 
loop 

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

846 
end loop; 

847 


848 
for v_change_row in 

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

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

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

852 
 left join 

853 
(select 'epoch'::timestamp + 

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

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

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

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

858 
from stratcon.loading_dock_metric_text_s_change_log 

859 
where sid = v_sid 

860 
and name = in_name 

861 
and whence > v_start_adj 

862 
and whence <= v_end_adj) d 

863 
 using (whence) 

864 
order by whence asc 

865 
loop 

866 
v_next_text := v_change_row.value; 

867 
if v_change_row.value is not null and 

868 
v_start_text != v_change_row.value then 

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

870 
else 

871 
v_change_row.value := v_start_text; 

872 
end if; 

873 
if v_next_text is not null then 

874 
v_start_text := v_next_text; 

875 
end if; 

876 
return next v_change_row; 

877 
end loop; 

878 


879 
return; 

880 
end 

881 
$$ language 'plpgsql'; 

882 


883 


884 
create or replace function 

885 
stratcon.choose_window(in_start_time timestamptz, 

886 
in_end_time timestamptz, 

887 
in_hopeful_nperiods int, 

888 
out tablename text, 

889 
out period interval, 

890 
out nperiods int) 

891 
returns setof record as 

892 
$$ 

893 
declare 

894 
window record; 

895 
begin 

896 
 Figure out which table we should be looking in 

897 
for window in 

898 
select atablename, aperiod, anperiods 

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

900 
abs(case when iv/isec  in_hopeful_nperiods < 0 

901 
then 10 * (in_hopeful_nperiods  iv/isec) 

902 
else iv/isec  in_hopeful_nperiods 

903 
end) as badness 

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

905 
extract('epoch' from in_start_time) as iv 

906 
) i, 

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

908 
'rollup_matrix_numeric_5m' as atablename 

909 
union all 

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

911 
'rollup_matrix_numeric_20m' as atablename 

912 
union all 

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

914 
'rollup_matrix_numeric_60m' as atablename 

915 
union all 

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

917 
'rollup_matrix_numeric_6hours' as atablename 

918 
union all 

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

920 
'rollup_matrix_numeric_12hours' as atablename 

921 
) ivs 

922 
) b 

923 
order by badness asc 

924 
limit 1 

925 
loop 

926 
tablename := window.atablename; 

927 
period := window.aperiod; 

928 
nperiods := window.anperiods; 

929 
return next; 

930 
end loop; 

931 
return; 

932 
end 

933 
$$ language 'plpgsql'; 

934 


935 
create or replace function 

936 
stratcon.fetch_dataset(in_check uuid, 

937 
in_name text, 

938 
in_start_time timestamptz, 

939 
in_end_time timestamptz, 

940 
in_hopeful_nperiods int, 

941 
derive boolean) 

942 
returns setof stratcon.rollup_matrix_numeric_5m as 

943 
$$ 

944 
declare 

945 
v_sql text; 

946 
v_sid int; 

947 
v_target record; 

948 
v_interval numeric; 

949 
v_start_adj timestamptz; 

950 
v_end_adj timestamptz; 

951 
v_l_rollup_row stratcon.rollup_matrix_numeric_5m%rowtype; 

952 
v_rollup_row stratcon.rollup_matrix_numeric_5m%rowtype; 

953 
v_r_rollup_row stratcon.rollup_matrix_numeric_5m%rowtype; 

954 
begin 

955 


956 
 Map out uuid to an sid. 

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

958 
if not found then 

959 
return; 

960 
end if; 

961 


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

963 


964 
if not found then 

965 
raise exception 'no target table'; 

966 
return; 

967 
end if; 

968 


969 
select 'epoch'::timestamp + 

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

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

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

973 
into v_start_adj; 

974 


975 
select 'epoch'::timestamp + 

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

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

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

979 
into v_end_adj; 

980 


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

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

983 
'd.min_value, d.max_value '  

984 
' from '  

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

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

987 
' as rollup_time'  

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

989 
'left join '  

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

991 
' where sid = '  v_sid  

992 
' and name = '  quote_literal(in_name)  

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

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

995 
' using(rollup_time)'; 

996 


997 
for v_rollup_row in execute v_sql loop 

998 
if derive is true then 

999 
v_r_rollup_row := v_rollup_row; 

1000 
if v_l_rollup_row.count_rows is not null and 

1001 
v_rollup_row.count_rows is not null then 

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

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

1004 
v_r_rollup_row.avg_value := 

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

1006 
v_r_rollup_row.min_value := 

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

1008 
v_r_rollup_row.max_value := 

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

1010 
else 

1011 
v_r_rollup_row.count_rows = NULL; 

1012 
v_r_rollup_row.avg_value = NULL; 

1013 
v_r_rollup_row.min_value = NULL; 

1014 
v_r_rollup_row.max_value = NULL; 

1015 
end if; 

1016 
else 

1017 
v_r_rollup_row := v_rollup_row; 

1018 
end if; 

1019 
return next v_r_rollup_row; 

1020 
v_l_rollup_row := v_rollup_row; 

1021 
end loop; 

1022 
return; 

1023 
end 

1024 
$$ language 'plpgsql'; 

1025 


1026 
COMMIT; 
