Changeset 60d5e7392f328a65f988f7c7402b6a1bf7d4d5a6

Show
Ignore:
Timestamp:
10/27/09 18:17:52 (9 years ago)
Author:
Theo Schlossnagle <jesus@omniti.com>
git-committer:
Theo Schlossnagle <jesus@omniti.com> 1256667472 +0000
git-parent:

[d2dd72fa3f0404e6651f95cdc95214380a5630a2]

git-author:
Theo Schlossnagle <jesus@omniti.com> 1256667472 +0000
Message:

make this more reusable, refs #150

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved
  • sql/crontab

    rd2dd72f r60d5e73  
    22* * * * * /opt/pgsql835/bin/psql -d reconnoiter -U stratcon -c "select stratcon.rollup_metric_numeric(rollup) from metric_numeric_rollup_config order by seconds asc;" >/tmp/rollup.log 2>&1 
    33#cleanup jobs 
    4 01 00 * * * /opt/pgsql835/bin/psql -d reconnoiter -U reconnoiter -c "select stratcon.archive_part_maint('noit.metric_numeric_archive', 'day', 7);" 1>/dev/null 
    5 01 00 * * * /opt/pgsql835/bin/psql -d reconnoiter -U reconnoiter -c "select stratcon.archive_part_maint('noit.metric_text_archive', 'day', 7);" 1>/dev/null 
    6 01 00 * * * /opt/pgsql835/bin/psql -d reconnoiter -U reconnoiter -c "select stratcon.archive_part_maint('noit.check_status_archive', 'day' 7);" 1>/dev/null 
    7 01 00 * * * /opt/pgsql835/bin/psql -d reconnoiter -U reconnoiter -c "select stratcon.archive_part_maint('noit.check_archive', 'day' 7);" 1>/dev/null 
     401 00 * * * /opt/pgsql835/bin/psql -d reconnoiter -U reconnoiter -c "select stratcon.archive_part_maint('noit.metric_numeric_archive', 'whence', 'day', 7);" 1>/dev/null 
     501 00 * * * /opt/pgsql835/bin/psql -d reconnoiter -U reconnoiter -c "select stratcon.archive_part_maint('noit.metric_text_archive', 'whence', 'day', 7);" 1>/dev/null 
     601 00 * * * /opt/pgsql835/bin/psql -d reconnoiter -U reconnoiter -c "select stratcon.archive_part_maint('noit.check_status_archive', 'whence', 'day', 7);" 1>/dev/null 
     701 00 * * * /opt/pgsql835/bin/psql -d reconnoiter -U reconnoiter -c "select stratcon.archive_part_maint('noit.check_archive', 'whence', 'day', 7);" 1>/dev/null 
    8801 00 * * * /opt/pgsql835/bin/psql -d reconnoiter -c "delete from prism.saved_graphs where saved = false and last_update<current_timestamp - '1 day' ::interval;" 1>/dev/null 
  • sql/sprocs/stratcon.archive_part_maint.sql

    rd2dd72f r60d5e73  
    1 CREATE OR REPLACE FUNCTION stratcon.archive_part_maint(in_parent_table text, units text, cnt integer) RETURNS void  
     1CREATE OR REPLACE FUNCTION stratcon.archive_part_maint(in_parent_table text, in_column text, in_units text, in_cnt integer) RETURNS void  
    22AS $$ 
    33DECLARE 
     
    3737 
    3838    IF v_recent_part IS NULL THEN 
    39         select (date_trunc(units, current_date) - ('1 '||units)::interval)::date 
     39        select (date_trunc(in_units, current_date) - ('1 '||in_units)::interval)::date 
    4040          into v_recent_part; 
    4141    END IF; 
    4242 
    43     select date_trunc(units, v_recent_part + ('1 '||units)::interval)::date 
     43    select date_trunc(in_units, v_recent_part + ('1 '||in_units)::interval)::date 
    4444      into v_next_part; 
    4545 
    4646    LOOP 
    47         IF v_next_part > current_date + (cnt * ('1 '||units)::interval) THEN 
     47        IF v_next_part > current_date + (in_cnt * ('1 '||in_units)::interval) THEN 
    4848            EXIT; 
    4949        END IF; 
     
    5555        execute 'CREATE TABLE ' || v_schema_name || '.' || v_table_name || '(' || 
    5656                'CONSTRAINT ' || v_constraint_name || 
    57                 E' CHECK (whence >= \'' || v_next_part::text || E' 00:00:00-00\'::timestamptz AND ' || 
    58                 E'        whence < \'' || (v_next_part + ('1 '||units)::interval)::date::text || E' 00:00:00-00\'::timestamptz)' || 
     57                ' CHECK (' || in_column || E' >= \'' || v_next_part::text || E' 00:00:00-00\'::timestamptz AND ' || 
     58                '        ' || in_column || E' < \'' || (v_next_part + ('1 '||in_units)::interval)::date::text || E' 00:00:00-00\'::timestamptz)' || 
    5959                ') INHERITS (' || in_parent_table || ')'; 
    6060 
     
    126126        END LOOP; 
    127127 
    128         v_next_part := (v_next_part + ('1 '||units)::interval)::date; 
     128        v_next_part := (v_next_part + ('1 '||in_units)::interval)::date; 
    129129    END LOOP; 
    130130END