Changeset 3b5d1e9688429ed8e55ebcc0a750dda9a2fe54b4
 Timestamp:
 05/09/08 20:39:46 (10 years ago)
 gitparent:
 Files:

 sql/schema.sql (modified) (22 diffs)
Legend:
 Unmodified
 Added
 Removed
 Modified
 Copied
 Moved
sql/schema.sql
r7e71217 r3b5d1e9 6 6 sid integer NOT NULL, 7 7 remote_address inet, 8 whence timestamp with time zoneNOT NULL,8 whence timestamp NOT NULL, 9 9 id uuid NOT NULL, 10 10 target text NOT NULL, … … 16 16 CREATE TABLE stratcon.loading_dock_status_s ( 17 17 sid integer NOT NULL, 18 whence timestamp with time zoneNOT NULL,18 whence timestamp NOT NULL, 19 19 state character(1) NOT NULL, 20 20 availability character(1) NOT NULL, … … 26 26 CREATE TABLE stratcon.loading_dock_metric_numeric_s ( 27 27 sid integer NOT NULL, 28 whence timestamp with time zoneNOT NULL,28 whence timestamp NOT NULL, 29 29 name text NOT NULL, 30 30 value numeric, … … 34 34 CREATE TABLE stratcon.loading_dock_metric_text_s ( 35 35 sid integer NOT NULL, 36 whence timestamp with time zoneNOT NULL,36 whence timestamp NOT NULL, 37 37 name text NOT NULL, 38 38 value text, … … 42 42 CREATE TABLE stratcon.loading_dock_metric_text_s_change_log ( 43 43 sid integer NOT NULL, 44 whence timestamp with time zoneNOT NULL,44 whence timestamp NOT NULL, 45 45 name text NOT NULL, 46 46 value text, … … 85 85 sid integer NOT NULL, 86 86 name text NOT NULL, 87 rollup_time timestamp with time zoneNOT NULL,87 rollup_time timestamp NOT NULL, 88 88 count_rows integer, 89 89 avg_value numeric, … … 94 94 ); 95 95 96 CREATE TABLE stratcon.rollup_matrix_numeric_20m ( 97 sid integer NOT NULL, 98 name text NOT NULL, 99 rollup_time timestamp NOT NULL, 100 count_rows integer, 101 avg_value numeric, 102 stddev_value numeric, 103 min_value numeric, 104 max_value numeric, 105 PRIMARY KEY (rollup_time,sid,name) 106 ); 107 96 108 CREATE TABLE stratcon.map_uuid_to_sid ( 97 109 id uuid NOT NULL, … … 101 113 102 114 CREATE TABLE stratcon.log_whence_s ( 103 whence timestamp with time zoneNOT NULL,115 whence timestamp NOT NULL, 104 116 interval varchar2(20, 105 117 PRIMARY KEY(whence,interval) 118 ); 119 120 CREATE TABLE stratcon.rollup_runner ( 121 rollup_table character varying(100), 122 runner character varying(22) 106 123 ); 107 124 … … 131 148 GRANT SELECT,INSERT,DELETE ON stratcon.rollup_matrix_numeric_60m TO stratcon; 132 149 GRANT SELECT,INSERT,DELETE ON stratcon.rollup_matrix_numeric_5m TO stratcon; 150 GRANT SELECT,INSERT,DELETE ON stratcon.rollup_matrix_numeric_20m TO stratcon; 133 151 GRANT SELECT,INSERT,DELETE ON stratcon.rollup_matrix_numeric_6hours TO stratcon; 134 152 GRANT SELECT,INSERT,DELETE ON stratcon.rollup_matrix_numeric_12hours TO stratcon; 135 153 GRANT SELECT,INSERT ON stratcon.map_uuid_to_sid TO stratcon; 154 GRANT SELECT,INSERT,UPDATE,DELETE ON stratcon.rollup_runner TO stratcon; 136 155 ALTER TABLE stratcon.seq_sid OWNER TO stratcon; 137 156 … … 231 250 rec stratcon.rollup_matrix_numeric_5m%rowtype; 232 251 v_sql TEXT; 233 v_min_whence TIMESTAMP TZ;234 v_max_rollup_5 TIMESTAMP TZ;235 v_whence TIMESTAMP TZ;252 v_min_whence TIMESTAMP; 253 v_max_rollup_5 TIMESTAMP; 254 v_whence TIMESTAMP; 236 255 rows INT; 256 v_nrunning INT; 257 v_self VARCHAR(22); 237 258 238 259 BEGIN 260 261 SELECT COUNT(1) INTO v_nrunning 262 from stratcon.rollup_runner t, pg_stat_activity a 263 where rollup_table ='rollup_matrix_numeric_5m' 264 and runner = procpid  '.'  date_part('epoch',backend_start); 265 266 IF v_nrunning > 0 THEN 267 RAISE NOTICE 'stratcon.rollup_matrix_numeric_5m already running'; 268 RETURN ; 269 END IF; 270 271 SELECT INTO v_self procpid  '.'  date_part('epoch',backend_start) 272 FROM pg_stat_activity 273 WHERE procpid = pg_backend_pid(); 274 275 IF v_self IS NULL THEN 276 RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_5m cannot selfidentify'; 277 END IF; 278 279 v_sql = 'update stratcon.rollup_runner set runner = '''  v_self  ''' where rollup_table = ''rollup_matrix_numeric_5m'''; 280 281 EXECUTE v_sql; 239 282 240 283 SELECT MIN(whence) FROM stratcon.log_whence_s WHERE interval='5 minutes' … … 244 287 INTO v_max_rollup_5; 245 288 246  Insert Log for Hourlyrollup247 248 SELECT whence FROM stratcon.log_whence_s WHERE whence=date_trunc('H',v_min_whence) and interval='1 hour'289  Insert Log for 20 minutes rollup 290 291 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' 249 292 INTO v_whence; 250 293 IF NOT FOUND THEN 251 INSERT INTO stratcon.log_whence_s VALUES(date_trunc('H',v_min_whence) ,'1 hour');294 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'); 252 295 END IF; 253 296 … … 279 322 DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='5 minutes'; 280 323 324 UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_5m'; 325 281 326 RETURN; 282 327 283 328 EXCEPTION 284 329 WHEN RAISE_EXCEPTION THEN 330 UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_5m'; 285 331 RAISE EXCEPTION '%', SQLERRM; 286 332 WHEN OTHERS THEN 333 UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_5m'; 287 334 RAISE NOTICE '%', SQLERRM; 288 335 END 289 336 $$ LANGUAGE plpgsql; 290 337 291  1 hourl rollup 338 339  20 minutes rollup 340 341 CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_20m() 342 RETURNS void 343 AS $$ 344 DECLARE 345 346 rec stratcon.rollup_matrix_numeric_20m%rowtype; 347 v_sql TEXT; 348 v_min_whence TIMESTAMP; 349 v_max_rollup_20 TIMESTAMP; 350 v_whence TIMESTAMP; 351 rows INT; 352 v_nrunning INT; 353 v_self VARCHAR(22); 354 355 BEGIN 356 357 SELECT COUNT(1) INTO v_nrunning 358 from stratcon.rollup_runner t, pg_stat_activity a 359 where rollup_table ='rollup_matrix_numeric_20m' 360 and runner = procpid  '.'  date_part('epoch',backend_start); 361 362 IF v_nrunning > 0 THEN 363 RAISE NOTICE 'stratcon.rollup_matrix_numeric_20m already running'; 364 RETURN ; 365 END IF; 366 367 SELECT INTO v_self procpid  '.'  date_part('epoch',backend_start) 368 FROM pg_stat_activity 369 WHERE procpid = pg_backend_pid(); 370 371 IF v_self IS NULL THEN 372 RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_20m cannot selfidentify'; 373 END IF; 374 375 v_sql = 'update stratcon.rollup_runner set runner = '''  v_self  ''' where rollup_table = ''rollup_matrix_numeric_20m'''; 376 377 EXECUTE v_sql; 378 379 SELECT MIN(whence) FROM stratcon.log_whence_s WHERE interval='20 minutes' 380 INTO v_min_whence; 381 382 SELECT MAX(rollup_time) FROM stratcon.rollup_matrix_numeric_20m 383 INTO v_max_rollup_20; 384 385  Insert Log for Hourly rollup 386 387 SELECT whence FROM stratcon.log_whence_s WHERE whence=date_trunc('H',v_min_whence) and interval='1 hour' 388 INTO v_whence; 389 IF NOT FOUND THEN 390 INSERT INTO stratcon.log_whence_s VALUES(date_trunc('H',v_min_whence),'1 hour'); 391 END IF; 392 393 IF v_min_whence <= v_max_rollup_20 THEN 394 395 DELETE FROM stratcon.rollup_matrix_numeric_20m 396 WHERE rollup_time = v_min_whence; 397 398 END IF; 399 400 FOR rec IN 401 SELECT sid , name,v_min_whence as rollup_time, 402 SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value, 403 stddev(stddev_value) as stddev_value, 404 MIN(min_value) as min_value ,MAX(max_value) as max_value 405 FROM stratcon.rollup_matrix_numeric_5m 406 WHERE ROLLUP_TIME<= v_min_whence AND WHENCE > v_min_whence '20 minutes'::interval 407 GROUP BY rollup_time,sid,name 408 409 LOOP 410 411 412 INSERT INTO stratcon.rollup_matrix_numeric_20m 413 (sid,name,rollup_time,count_rows,avg_value,stddev_value,min_value,max_value) VALUES 414 (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.stddev_value,rec.min_value,rec.max_value); 415 416 END LOOP; 417 418  Delete from whence log table 419 420 DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='20 minutes'; 421 422 UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_20m'; 423 424 RETURN; 425 426 EXCEPTION 427 WHEN RAISE_EXCEPTION THEN 428 UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_20m'; 429 RAISE EXCEPTION '%', SQLERRM; 430 WHEN OTHERS THEN 431 UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_20m'; 432 RAISE NOTICE '%', SQLERRM; 433 END 434 $$ LANGUAGE plpgsql; 435 436 437  1 hour rollup 292 438 293 439 … … 298 444 rec stratcon.rollup_matrix_numeric_60m%rowtype; 299 445 v_sql TEXT; 300 v_min_whence TIMESTAMPTZ; 301 v_max_rollup_5 TIMESTAMPTZ; 302 v_whence TIMESTAMPTZ; 446 v_min_whence TIMESTAMP; 447 v_max_rollup_60 TIMESTAMP; 448 v_whence TIMESTAMP; 449 v_nrunning INT; 450 v_self VARCHAR(22); 451 303 452 BEGIN 453 454 SELECT COUNT(1) INTO v_nrunning 455 from stratcon.rollup_runner t, pg_stat_activity a 456 where rollup_table ='rollup_matrix_numeric_60m' 457 and runner = procpid  '.'  date_part('epoch',backend_start); 458 459 IF v_nrunning > 0 THEN 460 RAISE NOTICE 'stratcon.rollup_matrix_numeric_60m already running'; 461 RETURN ; 462 END IF; 463 464 SELECT INTO v_self procpid  '.'  date_part('epoch',backend_start) 465 FROM pg_stat_activity 466 WHERE procpid = pg_backend_pid(); 467 468 IF v_self IS NULL THEN 469 RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_60m cannot selfidentify'; 470 END IF; 471 472 v_sql = 'update stratcon.rollup_runner set runner = '''  v_self  ''' where rollup_table = ''rollup_matrix_numeric_60m'''; 473 474 EXECUTE v_sql; 304 475 305 476 SELECT min(whence) FROM stratcon.log_whence_s WHERE interval='1 hour' … … 307 478 308 479 SELECT max(date_trunc('H',rollup_time)) FROM stratcon.rollup_matrix_numeric_60m 309 INTO v_max_rollup_ 5;480 INTO v_max_rollup_60; 310 481 311 482  Insert Log for 6 Hour rollup … … 318 489 319 490 320 IF v_min_whence <= v_max_rollup_ 5THEN491 IF v_min_whence <= v_max_rollup_60 THEN 321 492 322 493 DELETE FROM stratcon.rollup_matrix_numeric_60m … … 329 500 stddev(stddev_value) as stddev_value, 330 501 MIN(min_value) as min_value ,MAX(max_value) as max_value 331 FROM stratcon.rollup_matrix_numeric_ 5m502 FROM stratcon.rollup_matrix_numeric_20m 332 503 WHERE date_trunc('hour',rollup_time)= date_trunc('hour',v_min_whence) 333 504 GROUP BY date_trunc('hour',rollup_time),sid,name … … 343 514 DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='1 hour'; 344 515 516 UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_60m'; 517 345 518 RETURN; 346 519 347 520 EXCEPTION 348 521 WHEN RAISE_EXCEPTION THEN 522 UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_60m'; 349 523 RAISE EXCEPTION '%', SQLERRM; 350 524 WHEN OTHERS THEN 525 UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_60m'; 351 526 RAISE NOTICE '%', SQLERRM; 352 527 END … … 362 537 rec stratcon.rollup_matrix_numeric_6hours%rowtype; 363 538 v_sql TEXT; 364 v_min_whence TIMESTAMPTZ; 365 v_max_rollup_6 TIMESTAMPTZ; 366 v_whence TIMESTAMPTZ; 367 539 v_min_whence TIMESTAMP; 540 v_max_rollup_6 TIMESTAMP; 541 v_whence TIMESTAMP; 542 v_nrunning INT; 543 v_self VARCHAR(22); 544 368 545 BEGIN 546 547 SELECT COUNT(1) INTO v_nrunning 548 from stratcon.rollup_runner t, pg_stat_activity a 549 where rollup_table ='rollup_matrix_numeric_6hours' 550 and runner = procpid  '.'  date_part('epoch',backend_start); 551 552 IF v_nrunning > 0 THEN 553 RAISE NOTICE 'stratcon.rollup_matrix_numeric_6hours already running'; 554 RETURN ; 555 END IF; 556 557 SELECT INTO v_self procpid  '.'  date_part('epoch',backend_start) 558 FROM pg_stat_activity 559 WHERE procpid = pg_backend_pid(); 560 561 IF v_self IS NULL THEN 562 RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_6hours cannot selfidentify'; 563 END IF; 564 565 v_sql = 'update stratcon.rollup_runner set runner = '''  v_self  ''' where rollup_table = ''rollup_matrix_numeric_6hours'''; 566 567 EXECUTE v_sql; 369 568 370 569 SELECT min(whence) FROM stratcon.log_whence_s WHERE interval='6 hours' … … 409 608 DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='6 hours'; 410 609 610 UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_6hours'; 611 411 612 RETURN; 412 613 413 614 EXCEPTION 414 615 WHEN RAISE_EXCEPTION THEN 616 UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_6hours'; 415 617 RAISE EXCEPTION '%', SQLERRM; 416 618 WHEN OTHERS THEN 417 RAISE NOTICE '%', SQLERRM; 619 UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_6hours'; 620 RAISE NOTICE '%', SQLERRM; 418 621 END 419 622 $$ LANGUAGE plpgsql; … … 428 631 rec stratcon.rollup_matrix_numeric_12hours%rowtype; 429 632 v_sql TEXT; 430 v_min_whence TIMESTAMPTZ; 431 v_max_rollup_12 TIMESTAMPTZ; 432 v_whence TIMESTAMPTZ; 633 v_min_whence TIMESTAMP; 634 v_max_rollup_12 TIMESTAMP; 635 v_whence TIMESTAMP; 636 v_nrunning INT; 637 v_self VARCHAR(22); 638 433 639 434 640 BEGIN 641 642 SELECT COUNT(1) INTO v_nrunning 643 from stratcon.rollup_runner t, pg_stat_activity a 644 where rollup_table ='rollup_matrix_numeric_12hours' 645 and runner = procpid  '.'  date_part('epoch',backend_start); 646 647 IF v_nrunning > 0 THEN 648 RAISE NOTICE 'stratcon.rollup_matrix_numeric_12hours already running'; 649 RETURN ; 650 END IF; 651 652 SELECT INTO v_self procpid  '.'  date_part('epoch',backend_start) 653 FROM pg_stat_activity 654 WHERE procpid = pg_backend_pid(); 655 656 IF v_self IS NULL THEN 657 RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_12hours cannot selfidentify'; 658 END IF; 659 660 v_sql = 'update stratcon.rollup_runner set runner = '''  v_self  ''' where rollup_table = ''rollup_matrix_numeric_12hours'''; 661 662 EXECUTE v_sql; 435 663 436 664 SELECT min(whence) FROM stratcon.log_whence_s WHERE interval='12 hours' … … 475 703 DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='12 hours'; 476 704 705 UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_12hours'; 706 477 707 RETURN; 478 708 479 709 EXCEPTION 480 710 WHEN RAISE_EXCEPTION THEN 481 RAISE EXCEPTION '%', SQLERRM; 711 UPDATE stratcon.rollup_runner set runner = '' where rollup_table = 'rollup_matrix_numeric_12hours'; 712 RAISE EXCEPTION '%', SQLERRM; 482 713 WHEN OTHERS THEN 714 UPDATE stratcon.rollup_runner set runner = '' where rollup_table = 'rollup_matrix_numeric_12hours'; 483 715 RAISE NOTICE '%', SQLERRM; 484 716 END … … 603 835 ( select 5*60 as isec, '5 minutes'::interval as aperiod, 604 836 'rollup_matrix_numeric_5m' as atablename 837 union all 838 select 20*60 as isec, '20 minutes'::interval as aperiod, 839 'rollup_matrix_numeric_20m' as atablename 605 840 union all 606 841 select 60*60 as isec, '1 hour'::interval as aperiod,