Show
Ignore:
Timestamp:
07/02/08 18:29:43 (6 years ago)
Author:
Denish Patel <denish@omniti.com>
git-committer:
Denish Patel <denish@omniti.com> 1215023383 +0000
git-parent:

[deeaea617276c3961438854251d49f293af8dd6d]

git-author:
Denish Patel <denish@omniti.com> 1215023383 +0000
Message:

Exported Latest DDL dump

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved
  • sql/reconnoiter_ddl_dump.sql

    r6390014 r5366a70  
    2323 
    2424SET default_with_oids = false; 
     25 
     26-- 
     27-- Name: current_node_config; Type: TABLE; Schema: stratcon; Owner: stratcon; Tablespace:  
     28-- 
     29 
     30CREATE TABLE current_node_config ( 
     31    remote_address inet NOT NULL, 
     32    node_type text NOT NULL, 
     33    whence timestamp with time zone NOT NULL, 
     34    config xml NOT NULL 
     35); 
     36 
     37 
     38ALTER TABLE stratcon.current_node_config OWNER TO stratcon; 
     39 
     40-- 
     41-- Name: current_node_config_changelog; Type: TABLE; Schema: stratcon; Owner: stratcon; Tablespace:  
     42-- 
     43 
     44CREATE TABLE current_node_config_changelog ( 
     45    remote_address inet NOT NULL, 
     46    node_type text NOT NULL, 
     47    whence timestamp with time zone NOT NULL, 
     48    config xml NOT NULL 
     49); 
     50 
     51 
     52ALTER TABLE stratcon.current_node_config_changelog OWNER TO stratcon; 
    2553 
    2654-- 
     
    162190    rollup_time timestamp with time zone NOT NULL, 
    163191    count_rows integer, 
    164     avg_value numeric, 
    165     min_value numeric, 
    166     max_value numeric 
     192    avg_value numeric 
    167193); 
    168194 
     
    179205    rollup_time timestamp with time zone NOT NULL, 
    180206    count_rows integer, 
    181     avg_value numeric, 
    182     min_value numeric, 
    183     max_value numeric 
     207    avg_value numeric 
    184208); 
    185209 
     
    196220    rollup_time timestamp with time zone NOT NULL, 
    197221    count_rows integer, 
    198     avg_value numeric, 
    199     min_value numeric, 
    200     max_value numeric 
     222    avg_value numeric 
    201223); 
    202224 
     
    213235    rollup_time timestamp with time zone NOT NULL, 
    214236    count_rows integer, 
    215     avg_value numeric, 
    216     min_value numeric, 
    217     max_value numeric 
     237    avg_value numeric 
    218238); 
    219239 
     
    230250    rollup_time timestamp with time zone NOT NULL, 
    231251    count_rows integer, 
    232     avg_value numeric, 
    233     min_value numeric, 
    234     max_value numeric 
     252    avg_value numeric 
    235253); 
    236254 
     
    411429 
    412430  v_sql := 'select ' || v_sid || ' as sid, ' || quote_literal(in_name) || ' as name, ' || 
    413            's.rollup_time, d.count_rows, d.avg_value, ' || 
    414            'd.min_value, d.max_value ' || 
     431           's.rollup_time, d.count_rows, d.avg_value ' || 
    415432           ' from ' || 
    416433           '(select ' || quote_literal(v_start_adj) || '::timestamp' || 
     
    435452        v_r_rollup_row.avg_value := 
    436453          (v_rollup_row.avg_value - v_l_rollup_row.avg_value) / v_interval; 
    437         v_r_rollup_row.min_value := 
    438           (v_rollup_row.min_value - v_l_rollup_row.min_value) / v_interval; 
    439         v_r_rollup_row.max_value := 
    440           (v_rollup_row.max_value - v_l_rollup_row.max_value) / v_interval; 
    441454      else 
    442455        v_r_rollup_row.count_rows = NULL; 
    443456        v_r_rollup_row.avg_value = NULL; 
    444         v_r_rollup_row.min_value = NULL; 
    445         v_r_rollup_row.max_value = NULL; 
     457         
    446458      end if; 
    447459    else 
     
    594606DECLARE 
    595607v_whence timestamptz; 
     608v_whence_5 timestamptz; 
    596609v_sid integer; 
    597610v_name text; 
    598611BEGIN 
    599612IF TG_OP = 'INSERT' THEN 
    600    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' 
     613  
     614 v_whence_5:=date_trunc('H',NEW.WHENCE) + (round(extract('minute' from NEW.WHENCE)/5)*5) * '1 minute'::interval; 
     615  
     616   SELECT whence FROM stratcon.log_whence_s WHERE whence=v_whence_5 and interval='5 minutes' 
    601617     INTO v_whence; 
     618      
    602619   IF NOT FOUND THEN 
    603        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'); 
     620      BEGIN 
     621       INSERT INTO  stratcon.log_whence_s VALUES(v_whence_5,'5 minutes'); 
     622       EXCEPTION 
     623        WHEN UNIQUE_VIOLATION THEN 
     624        -- do nothing  
     625      END; 
    604626    END IF; 
    605627 
     
    611633 
    612634END IF; 
    613  
    614635    RETURN NULL; 
    615636END 
     
    916937   
    917938    FOR rec IN  
    918                 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, 
    919          MIN(min_value) as min_value ,MAX(max_value) as max_value 
     939                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 
    920940         FROM stratcon.rollup_matrix_numeric_6hours 
    921941           WHERE rollup_time<= v_min_whence and rollup_time> v_min_whence-'12 hour'::interval 
     
    925945        
    926946          INSERT INTO stratcon.rollup_matrix_numeric_12hours 
    927           (sid,name,rollup_time,count_rows,avg_value,min_value,max_value) VALUES 
    928           (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.min_value,rec.max_value); 
     947          (sid,name,rollup_time,count_rows,avg_value) VALUES 
     948          (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value); 
    929949           
    930950     END LOOP; 
     
    941961 
    942962RETURN; 
    943  
    944963EXCEPTION 
    945964    WHEN RAISE_EXCEPTION THEN 
     
    10211040 FOR rec IN  
    10221041                SELECT sid , name,v_min_whence as rollup_time, 
    1023                        SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value, 
    1024        MIN(min_value) as min_value ,MAX(max_value) as max_value 
     1042                       SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value 
    10251043       FROM stratcon.rollup_matrix_numeric_5m 
    10261044                      WHERE rollup_time<= v_min_whence AND rollup_time > v_min_whence -'20 minutes'::interval 
     
    10311049         
    10321050        INSERT INTO stratcon.rollup_matrix_numeric_20m 
    1033          (sid,name,rollup_time,count_rows,avg_value,min_value,max_value) VALUES  
    1034          (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.min_value,rec.max_value); 
     1051         (sid,name,rollup_time,count_rows,avg_value) VALUES  
     1052         (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value); 
    10351053         
    10361054   END LOOP; 
     
    11191137       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'); 
    11201138   END IF; 
    1121     
    11221139 IF v_min_whence <= v_max_rollup_5 THEN 
    11231140 
     
    11291146 FOR rec IN  
    11301147                SELECT sid , name,v_min_whence as rollup_time, 
    1131                       COUNT(1) as count_rows ,AVG(value) as avg_value,MIN(value) as min_value ,MAX(value) as max_value 
     1148                      COUNT(1) as count_rows ,AVG(value) as avg_value 
    11321149                      FROM stratcon.loading_dock_metric_numeric_s 
    11331150                      WHERE WHENCE <= v_min_whence AND WHENCE > v_min_whence -'5 minutes'::interval 
     
    11381155         
    11391156        INSERT INTO stratcon.rollup_matrix_numeric_5m 
    1140          (sid,name,rollup_time,count_rows,avg_value,min_value,max_value) VALUES  
    1141          (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.min_value,rec.max_value); 
     1157         (sid,name,rollup_time,count_rows,avg_value) VALUES  
     1158         (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value); 
    11421159         
    11431160   END LOOP; 
     
    12331250   
    12341251    FOR rec IN  
    1235                 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, 
    1236          MIN(min_value) as min_value ,MAX(max_value) as max_value 
     1252                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 
    12371253         FROM stratcon.rollup_matrix_numeric_20m 
    12381254           WHERE date_hour(rollup_time)= v_min_whence 
     
    12411257       
    12421258          INSERT INTO stratcon.rollup_matrix_numeric_60m 
    1243           (sid,name,rollup_time,count_rows,avg_value,min_value,max_value) VALUES 
    1244           (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.min_value,rec.max_value); 
     1259          (sid,name,rollup_time,count_rows,avg_value) VALUES 
     1260          (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value); 
    12451261           
    12461262     END LOOP; 
     
    12601276EXCEPTION 
    12611277    WHEN RAISE_EXCEPTION THEN 
    1262        UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_60m'; 
    12631278       RAISE EXCEPTION '%', SQLERRM; 
    12641279    WHEN OTHERS THEN 
     
    13351350   
    13361351    FOR rec IN  
    1337                 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, 
    1338          MIN(min_value) as min_value ,MAX(max_value) as max_value 
     1352                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 
    13391353         FROM stratcon.rollup_matrix_numeric_60m 
    13401354           WHERE rollup_time<= v_min_whence and rollup_time> v_min_whence-'6 hour'::interval 
     
    13441358        
    13451359          INSERT INTO stratcon.rollup_matrix_numeric_6hours 
    1346           (sid,name,rollup_time,count_rows,avg_value,min_value,max_value) VALUES 
    1347           (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.min_value,rec.max_value); 
     1360          (sid,name,rollup_time,count_rows,avg_value) VALUES 
     1361          (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value); 
     1362           
    13481363     END LOOP; 
    13491364 
     
    13611376EXCEPTION 
    13621377    WHEN RAISE_EXCEPTION THEN 
    1363        UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_6hours';  
    13641378       RAISE EXCEPTION '%', SQLERRM; 
    13651379    WHEN OTHERS THEN 
     
    13731387 
    13741388-- 
     1389-- Name: update_config(inet, text, timestamp with time zone, xml); Type: FUNCTION; Schema: stratcon; Owner: stratcon 
     1390-- 
     1391 
     1392CREATE FUNCTION update_config(v_remote_address_in inet, v_node_type_in text, v_whence_in timestamp with time zone, v_config_in xml) RETURNS void 
     1393    AS $$ 
     1394DECLARE 
     1395    v_config xml; 
     1396BEGIN 
     1397    select config into v_config from stratcon.current_node_config 
     1398     where remote_address = v_remote_address_in 
     1399       and node_type = v_node_type_in; 
     1400    IF FOUND THEN 
     1401        IF v_config::text = v_config_in::text THEN 
     1402            RETURN; 
     1403        END IF; 
     1404        delete from stratcon.current_node_config 
     1405              where _address = v_remote_address_in 
     1406                and node_type = v_node_type_in; 
     1407    END IF; 
     1408    insert into stratcon.current_node_config 
     1409                (remote_address, node_type, whence, config) 
     1410         values (v_remote_address_in, v_node_type_in, v_whence_in, v_config_in); 
     1411    insert into stratcon.current_node_config_changelog 
     1412                (remote_address, node_type, whence, config) 
     1413         values (v_remote_address_in, v_node_type_in, v_whence_in, v_config_in); 
     1414END 
     1415$$ 
     1416    LANGUAGE plpgsql; 
     1417 
     1418 
     1419ALTER FUNCTION stratcon.update_config(v_remote_address_in inet, v_node_type_in text, v_whence_in timestamp with time zone, v_config_in xml) OWNER TO stratcon; 
     1420 
     1421-- 
    13751422-- Name: seq_sid; Type: SEQUENCE; Schema: stratcon; Owner: stratcon 
    13761423-- 
     
    13861433 
    13871434-- 
     1435-- Name: current_node_config_changelog_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: stratcon; Tablespace:  
     1436-- 
     1437 
     1438ALTER TABLE ONLY current_node_config_changelog 
     1439    ADD CONSTRAINT current_node_config_changelog_pkey PRIMARY KEY (remote_address, node_type, whence); 
     1440 
     1441 
     1442-- 
     1443-- Name: current_node_config_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: stratcon; Tablespace:  
     1444-- 
     1445 
     1446ALTER TABLE ONLY current_node_config 
     1447    ADD CONSTRAINT current_node_config_pkey PRIMARY KEY (remote_address, node_type); 
     1448 
     1449 
     1450-- 
    13881451-- Name: loading_dock_check_s_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: omniti; Tablespace:  
    13891452-- 
     
    14641527    ADD CONSTRAINT rollup_matrix_numeric_12hours_pkey PRIMARY KEY (rollup_time, sid, name); 
    14651528 
     1529ALTER TABLE rollup_matrix_numeric_12hours CLUSTER ON rollup_matrix_numeric_12hours_pkey; 
     1530 
    14661531 
    14671532-- 
     
    14721537    ADD CONSTRAINT rollup_matrix_numeric_20m_new_pkey PRIMARY KEY (rollup_time, sid, name); 
    14731538 
     1539ALTER TABLE rollup_matrix_numeric_20m CLUSTER ON rollup_matrix_numeric_20m_new_pkey; 
     1540 
    14741541 
    14751542-- 
     
    14801547    ADD CONSTRAINT rollup_matrix_numeric_5m_pkey PRIMARY KEY (rollup_time, sid, name); 
    14811548 
     1549ALTER TABLE rollup_matrix_numeric_5m CLUSTER ON rollup_matrix_numeric_5m_pkey; 
     1550 
    14821551 
    14831552-- 
     
    14881557    ADD CONSTRAINT rollup_matrix_numeric_60m_pkey PRIMARY KEY (rollup_time, sid, name); 
    14891558 
     1559ALTER TABLE rollup_matrix_numeric_60m CLUSTER ON rollup_matrix_numeric_60m_pkey; 
     1560 
    14901561 
    14911562-- 
     
    14951566ALTER TABLE ONLY rollup_matrix_numeric_6hours 
    14961567    ADD CONSTRAINT rollup_matrix_numeric_6hours_pkey PRIMARY KEY (rollup_time, sid, name); 
     1568 
     1569ALTER TABLE rollup_matrix_numeric_6hours CLUSTER ON rollup_matrix_numeric_6hours_pkey; 
    14971570 
    14981571