
 
 
 
 
 CREATE OR REPLACE FUNCTION stats_rollup2_proc()
 RETURNS VOID
 LANGUAGE plpgsql
 VOLATILE
 AS
 $BODY$
 DECLARE
   l_time_cur cursor (current_time_in timestamp without time zone) for
           select sample_time, partition_index
             from vpx_sample_time2
            where rollup_counter is null
              and sample_time < current_time_in - '1 hour'::interval
            order by 1 asc
            limit 6;
 
   l_month                     numeric(2,0);
   l_time                      vpx_sample_time2.sample_time%type;
   l_time_rowid                vpx_sample_time3.time_id%type;
   l_cnt                       int;
   l_rollup_counter            vpx_sample_time3.rollup_counter%type;
   l_start_time_id             vpx_sample_time2.time_id%type;
   l_rollup_start_time         vpx_sample_time2.sample_time%type;
   l_rollup_flag               numeric(1):=1;
   l_next_interval             numeric(10,0);
   l_current_interval          numeric(10,0);
   l_stat_rollup_level         numeric(10,0);
   l_null_device_id            int;
   l_last_sample_time          vpx_sample_time2.sample_time%type;
   l_source_interval_seq_num   numeric(1) := 2;
 
   l_hs1_partition_index        vpx_sample_time2.partition_index%type;
   l_hs1_frompartitionidxclause varchar(255);
   l_hs2_frompartitionidxclause varchar(255);
   l_previous_time              vpx_sample_time2.sample_time%type;
   l_next_time                  vpx_sample_time2.sample_time%type;
   l_last_part2                 vpx_sample_time2.partition_index%type;
   v_errmsg                     vpx_proc_log.description%type;
   v_err_log                    CHAR(3);
   v_sqlstate                   text;
   v_message                    text;
   v_context                    text;
 
 BEGIN
 
 l_rollup_start_time := timezone('UTC', clock_timestamp());
 
 
 select COALESCE(value,'OFF') into v_err_log
    from vpx_parameter
    where name = 'DBProc.Log.Debug.Info';
 
 
 
 
 
 
 select rollup_enabled_flg into l_rollup_flag
   from vpx_stat_interval_def
  where interval_seq_num = l_source_interval_seq_num + 1;
 
 select max(sample_time) into l_last_sample_time
   from vpx_sample_time2
  where rollup_counter is null;
 
 if (l_rollup_flag = 1) and (l_last_sample_time is not null) then
   select interval_val into l_current_interval
     from vpx_stat_interval_def
    where interval_seq_num = l_source_interval_seq_num;
 
   select stats_level, interval_val into l_stat_rollup_level, l_next_interval
     from vpx_stat_interval_def
    where interval_seq_num = l_source_interval_seq_num + 1;
 
   SELECT DEVICE_ID
      INTO l_null_device_id
      FROM VPX_DEVICE
      WHERE DEVICE_NAME = ''
      OR DEVICE_NAME IS NULL
      OR DEVICE_NAME = ' ';
 
   
    select count(*) into l_cnt
      from vpx_sample_time2
     where rollup_counter is not null and
       partition_index_hs3 is not null;
 
    
    
    IF l_cnt = 0 then
       
       UPDATE VPX_SAMPLE_TIME2
          SET PARTITION_INDEX_HS3 = 1
        WHERE PARTITION_INDEX =
                (SELECT PARTITION_INDEX
                   FROM VPX_SAMPLE_TIME2
                  WHERE SAMPLE_TIME =
                         (SELECT MIN(SAMPLE_TIME)
                            FROM VPX_SAMPLE_TIME2
                           WHERE ROLLUP_COUNTER IS NULL
                             AND SAMPLE_TIME < l_rollup_start_time - '1 hour'::interval));
          
          ANALYZE vpx_sample_time2;
          l_last_part2 := 1;
    END IF;
 
    l_cnt:=1;
    open l_time_cur(l_rollup_start_time);
 
    loop
     fetch l_time_cur into l_time, l_hs1_partition_index;
     if not found then
       exit;
     end if;
 
     
     
     
     
 
     
     select partition_index_hs3 into l_last_part2
       from vpx_sample_time2
      where sample_time = (select max(sample_time)
                             from vpx_sample_time2
                            where partition_index_hs3 is not null);
 
     
     select min(sample_time) into l_previous_time
       from vpx_sample_time2
      where partition_index_hs3 = l_last_part2;
 
     select min(sample_time) into l_next_time
       from vpx_sample_time2
      where rollup_counter is null
        and sample_time < l_rollup_start_time - '60 min'::interval;
 
     if l_next_time - '24 hour'::interval >= l_previous_time then
        l_last_part2 := l_last_part2 + 1;
     end if;
 
     
     
     
     if l_last_part2 > 32 then
        l_last_part2 := 1;
     end if;
 
      
      select rollup_counter, time_id
        into l_rollup_counter, l_start_time_id
        from vpx_sample_time2
       where sample_time = l_time;
 
     
      l_time := date_trunc('hour', l_time) - (mod(date_part('hour', l_time)::int, 2) || ' hour')::interval;
 
      
      
      if l_time - '24 hour'::interval >= l_next_time then
         l_last_part2 := l_last_part2 + 1;
         l_month := case when EXTRACT(month FROM l_next_time) in(1,3,5,7,8,10,12) then 31
                         when EXTRACT(month FROM l_next_time) in(4,6,9,11) then 30
                         else case when (mod(EXTRACT(year FROM l_next_time),4) = 0 and
                                         mod(EXTRACT(year FROM l_next_time),100) != 0) or
                                         (mod(EXTRACT(year FROM l_next_time),400) = 0)
                                   then 29
                              else 28
                              end
                         end;
 
         if l_last_part2 = l_month + 1 then
            l_last_part2 := 1;
            l_next_time := l_time;
         end if;
      end if;
 
      l_hs1_fromPartitionIdxClause := 'VPX_HIST_STAT2_' || TO_CHAR(l_hs1_partition_index, 'FM9999');
      l_hs2_fromPartitionIdxClause := 'VPX_HIST_STAT3_' || TO_CHAR(l_last_part2, 'FM9999');
 
     
     
     
     
     if  l_rollup_counter is null
         and (l_last_sample_time + (l_current_interval || ' sec')::interval) >= (l_time + (l_next_interval || ' sec')::interval) then
 
         
         update vpx_sample_time2
            set rollup_counter = (l_start_time_id::text || l_cnt)::numeric(38,0),
                partition_index_hs3 = l_last_part2
          where sample_time < l_time + (l_next_interval || ' sec')::interval
            and sample_time >= l_time;
          
          ANALYZE vpx_sample_time2;
 
         select time_id
           into l_time_rowid
           from vpx_sample_time3
          where sample_time = l_time
            and sample_interval=l_next_interval;
 
 
         if not found then
           select NEXTVAL('VPX_SAMPLE_TIME3_SEQ')
             into l_time_rowid;
           insert into vpx_sample_time3
                  (time_id, sample_time, sample_interval, partition_index)
           values
                  (l_time_rowid, l_time, l_next_interval, l_last_part2);
         end if;
 
 
         
         
         
         
         
         
         
         BEGIN
           EXECUTE '
           INSERT INTO '||l_hs2_fromPartitionIdxClause||'(counter_id, time_id, stat_val)
           SELECT st.counter_id, $1,
              vc_stat_agg(st.stat_val, sd.rollup_type, sm.sample_time)
             FROM vpx_sample_time2 sm
             JOIN '||l_hs1_fromPartitionIdxClause||' st
                ON st.time_id = sm.time_id
             JOIN vpx_stat_counter sc ON st.counter_id = SC.COUNTER_ID
             JOIN vpx_stat_def sd ON SC.STAT_ID = SD.ID
            WHERE sm.rollup_counter = ($2::text || $3)::numeric(38,0)
              AND ((SC.DEVICE_ID = $4 and SD.STAT_LEVEL <= $5)
                   or (SC.DEVICE_ID <> $6 and SD.PERDEVICE_STAT_LEVEL <= $7))
              AND SD.ROLLUP_TYPE BETWEEN 0 AND 4
            GROUP BY st.counter_id'
           USING l_time_rowid,
                 l_start_time_id, l_cnt,
                 l_null_device_id, l_stat_rollup_level,
                 l_null_device_id, l_stat_rollup_level;
         EXCEPTION
           WHEN OTHERS THEN
               v_errmsg := 'Error:' || cast(sqlstate as VARCHAR(30)) || ' :' || coalesce(SQLERRM, '');
               IF v_err_log = 'ON' THEN
                  INSERT INTO VPX_PROC_LOG (PROC_NAME, EVENT, EVENT_DT, ERR_FLAG, DESCRIPTION)
                       VALUES ('STATS_ROLLUP2_PROC','ERR', TIMEZONE('UTC',clock_timestamp()), -1, SUBSTR(v_errmsg, 1, 1000));
               END IF;
         END;
 
         l_cnt:=l_cnt+1;
 
       END IF;
 
       END LOOP;
     close l_time_cur;
 
 
 ELSE
      NULL;
 END IF;
 
 EXCEPTION WHEN OTHERS THEN
     GET STACKED DIAGNOSTICS
         v_sqlstate = returned_sqlstate,
         v_message = message_text,
         v_context = pg_exception_context;
     RAISE LOG 'sqlstate: %', v_sqlstate;
     RAISE LOG 'message: %', v_message;
     RAISE LOG 'context: %', v_context;
   WHEN OTHERS THEN
     PERFORM 1
     FROM    pg_cursors
     WHERE   name = 'l_time_cur';
 
     IF FOUND THEN
       CLOSE l_time_cur; 
     END IF;
     v_errmsg := 'Error:' || cast(sqlstate as VARCHAR(30)) || ' :' || coalesce(SQLERRM, '');
     if v_err_log = 'ON' then
         INSERT INTO VPX_PROC_LOG (PROC_NAME, EVENT, EVENT_DT, ERR_FLAG, DESCRIPTION)
            VALUES ('STATS_ROLLUP1_PROC','ERR', TIMEZONE('UTC',clock_timestamp()), -1, SUBSTR(v_errmsg, 1, 1000));
     end if;
 end;
 $BODY$
