Changeset 848c999201996fa821ee897f8bc9e3c0979a575f for sql
 Timestamp:
 10/07/08 14:29:57 (8 years ago)
 gitparent:
 Files:

 sql/reconnoiter_ddl_dump.sql (modified) (21 diffs)
Legend:
 Unmodified
 Added
 Removed
 Modified
 Copied
 Moved
sql/reconnoiter_ddl_dump.sql
rfb7be7b r848c999 429 429 rollup_time timestamp with time zone NOT NULL, 430 430 count_rows integer, 431 avg_value numeric 431 avg_value numeric, 432 counter_dev numeric 432 433 ); 433 434 … … 444 445 rollup_time timestamp with time zone NOT NULL, 445 446 count_rows integer, 446 avg_value numeric 447 avg_value numeric, 448 counter_dev numeric 447 449 ); 448 450 … … 459 461 rollup_time timestamp with time zone NOT NULL, 460 462 count_rows integer, 461 avg_value numeric 463 avg_value numeric, 464 counter_dev numeric 462 465 ); 463 466 … … 474 477 rollup_time timestamp with time zone NOT NULL, 475 478 count_rows integer, 476 avg_value numeric 479 avg_value numeric, 480 counter_dev numeric 477 481 ); 478 482 … … 489 493 rollup_time timestamp with time zone NOT NULL, 490 494 count_rows integer, 491 avg_value numeric 495 avg_value numeric, 496 counter_dev numeric 492 497 ); 493 498 … … 705 710 706 711 CREATE FUNCTION trig_update_tsvector_saved_graphs() RETURNS trigger 707 AS $$ 708 DECLARE 712 AS $$DECLARE 709 713 BEGIN 710 714 IF TG_OP != 'INSERT' THEN 711 IF (NEW.graph_tags <> OLD.graph_tags ) THEN715 IF (NEW.graph_tags <> OLD.graph_tags OR NEW.title <> OLD.title) THEN 712 716 UPDATE prism.saved_graphs SET ts_search_all=prism.saved_graphs_tsvector(NEW.graphid) where graphid=NEW.graphid; 713 717 END IF; … … 1474 1478 1475 1479 CREATE FUNCTION rollup_matrix_numeric_12hours() RETURNS void 1476 AS $$ 1477 DECLARE 1480 AS $$DECLARE 1478 1481 rec stratcon.rollup_matrix_numeric_12hours%rowtype; 1479 1482 v_sql TEXT; … … 1534 1537 1535 1538 FOR rec IN 1536 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 1539 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, 1540 (SUM(counter_dev*count_rows)/SUM(count_rows)) as counter_dev 1537 1541 FROM stratcon.rollup_matrix_numeric_6hours 1538 1542 WHERE rollup_time<= v_min_whence and rollup_time> v_min_whence'12 hour'::interval … … 1542 1546 1543 1547 INSERT INTO stratcon.rollup_matrix_numeric_12hours 1544 (sid,name,rollup_time,count_rows,avg_value ) VALUES1545 (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value );1548 (sid,name,rollup_time,count_rows,avg_value,counter_dev) VALUES 1549 (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.counter_dev); 1546 1550 1547 1551 END LOOP; … … 1576 1580 1577 1581 CREATE FUNCTION rollup_matrix_numeric_20m() RETURNS void 1578 AS $$ 1579 DECLARE 1582 AS $$DECLARE 1580 1583 1581 1584 rec stratcon.rollup_matrix_numeric_20m%rowtype; … … 1637 1640 FOR rec IN 1638 1641 SELECT sid , name,v_min_whence as rollup_time, 1639 SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value 1642 SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value, 1643 (SUM(counter_dev*count_rows)/SUM(count_rows)) as counter_dev 1640 1644 FROM stratcon.rollup_matrix_numeric_5m 1641 1645 WHERE rollup_time<= v_min_whence AND rollup_time > v_min_whence '20 minutes'::interval … … 1646 1650 1647 1651 INSERT INTO stratcon.rollup_matrix_numeric_20m 1648 (sid,name,rollup_time,count_rows,avg_value ) VALUES1649 (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value );1652 (sid,name,rollup_time,count_rows,avg_value,counter_dev) VALUES 1653 (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.counter_dev); 1650 1654 1651 1655 END LOOP; … … 1682 1686 1683 1687 CREATE FUNCTION rollup_matrix_numeric_5m() RETURNS void 1684 AS $$ 1685 DECLARE 1688 AS $$DECLARE 1686 1689 1687 1690 rec stratcon.rollup_matrix_numeric_5m%rowtype; … … 1741 1744 END IF; 1742 1745 1743 FOR rec IN 1744 SELECT sid , name,v_min_whence as rollup_time, 1745 COUNT(1) as count_rows ,AVG(value) as avg_value 1746 FROM stratcon.loading_dock_metric_numeric_s 1747 WHERE WHENCE <= v_min_whence AND WHENCE > v_min_whence '5 minutes'::interval 1748 GROUP BY rollup_time,sid,name 1746 FOR rec IN 1747 1748 select n.sid, n.name, n.rollup_time, n.count_rows, n.avg_value, 1749 case when n.avg_value  l.avg_value >= 0 1750 then (n.avg_value  l.avg_value)/300.0 1751 else null end as counter_dev 1752 from (SELECT sid, name, v_min_whence as rollup_time, 1753 COUNT(1) as count_rows, avg(value) as avg_value 1754 FROM stratcon.loading_dock_metric_numeric_s 1755 WHERE whence <= v_min_whence AND whence > v_min_whence '5 minutes'::interval 1756 GROUP BY rollup_time,sid,name) as n 1757 left join stratcon.rollup_matrix_numeric_5m as l 1758 on (n.sid=l.sid and n.name=l.name and 1759 n.rollup_time  '5 minute'::interval = l.rollup_time) 1749 1760 1750 1761 LOOP … … 1752 1763 1753 1764 INSERT INTO stratcon.rollup_matrix_numeric_5m 1754 (sid,name,rollup_time,count_rows,avg_value ) VALUES1755 (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value );1765 (sid,name,rollup_time,count_rows,avg_value,counter_dev) VALUES 1766 (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.counter_dev); 1756 1767 1757 1768 END LOOP; … … 1788 1799 1789 1800 CREATE FUNCTION rollup_matrix_numeric_60m() RETURNS void 1790 AS $$ 1791 DECLARE 1801 AS $$DECLARE 1792 1802 rec stratcon.rollup_matrix_numeric_60m%rowtype; 1793 1803 v_sql TEXT; … … 1847 1857 1848 1858 FOR rec IN 1849 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 1859 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, 1860 (SUM(counter_dev*count_rows)/SUM(count_rows)) as counter_dev 1850 1861 FROM stratcon.rollup_matrix_numeric_20m 1851 1862 WHERE date_hour(rollup_time)= v_min_whence … … 1854 1865 1855 1866 INSERT INTO stratcon.rollup_matrix_numeric_60m 1856 (sid,name,rollup_time,count_rows,avg_value ) VALUES1857 (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value );1867 (sid,name,rollup_time,count_rows,avg_value,counter_dev) VALUES 1868 (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.counter_dev); 1858 1869 1859 1870 END LOOP; … … 1888 1899 1889 1900 CREATE FUNCTION rollup_matrix_numeric_6hours() RETURNS void 1890 AS $$ 1891 DECLARE 1901 AS $$DECLARE 1892 1902 rec stratcon.rollup_matrix_numeric_6hours%rowtype; 1893 1903 v_sql TEXT; … … 1947 1957 1948 1958 FOR rec IN 1949 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 1959 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, 1960 (SUM(counter_dev*count_rows)/SUM(count_rows)) as counter_dev 1950 1961 FROM stratcon.rollup_matrix_numeric_60m 1951 1962 WHERE rollup_time<= v_min_whence and rollup_time> v_min_whence'6 hour'::interval … … 1955 1966 1956 1967 INSERT INTO stratcon.rollup_matrix_numeric_6hours 1957 (sid,name,rollup_time,count_rows,avg_value ) VALUES1958 (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value );1968 (sid,name,rollup_time,count_rows,avg_value,counter_dev) VALUES 1969 (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.counter_dev); 1959 1970 1960 1971 END LOOP;