-- Copyright (c) 2004, Jim C. Nasby (decibel@rrs.decibel.org)
-- All rights reserved.
--
-- $Id: rrs_functions.sql 53 2005-03-25 05:55:08Z decibel $

SET client_encoding = 'SQL_ASCII';
SET check_function_bodies = false;

-- SET SESSION AUTHORIZATION 'pgsql';

SET search_path = rrs, pg_catalog;

CREATE OR REPLACE FUNCTION "update"() RETURNS integer
    AS '
DECLARE
    v_max_end_time rrs.bucket.end_time%TYPE;
    v_last_end_time rrs.bucket.end_time%TYPE;
    v_rows int;
    v_total_rows int;
    v_rrs rrs.rrs%ROWTYPE;
    v_source rrs.source%ROWTYPE;
    v_sql text;

    v_start_time rrs.history.start_time%TYPE;
    v_run_time interval;
    v_my_oid oid;
BEGIN
    -- Figure out our OID and try to aquire a lock
    SELECT ''rrs.update()''::regprocedure::oid
            INTO v_my_oid
    ;
    
    IF rrs.update_lock(v_my_oid, 1) = 0 THEN
        RAISE NOTICE ''rrs.update: unable to aquire lock'';
        RETURN -1;
    END IF;

    -- remember when we started for later
    v_start_time := rrs.tod();

    -- make sure all the buckets are up to date
    v_total_rows := rrs.update_buckets( v_start_time );

    -- Run through each source, updating each RRD for each source
    FOR v_source IN SELECT * FROM rrs.source
    LOOP
    RAISE INFO ''rrs.update: source_name = %'', v_source.source_name;
        -- Run through all the RRDs
        FOR v_rrs IN SELECT * FROM rrs.rrs ORDER BY coalesce( parent, -1 ), rrs_id
        LOOP
            v_rows := 0;
            SELECT max(end_time)
                INTO v_max_end_time
                FROM rrs.bucket
                WHERE rrs_id = v_rrs.rrs_id
            ;

            IF v_max_end_time IS NOT NULL THEN
                SELECT INTO v_last_end_time
                        last_end_time
                    FROM rrs.source_status
                    WHERE rrs_id = v_rrs.rrs_id
                        AND source_id = v_source.source_id
                ;
                IF NOT FOUND THEN
                    v_last_end_time := ''1970-01-01''::timestamptz;
                END IF;

                IF v_last_end_time = v_max_end_time THEN
                    RAISE INFO ''Nothing to do for % rrs_id %, skipping...'', v_source.source_name, v_rrs.rrs_id;
                ELSE
                    RAISE INFO ''Inserting into % for rrs_id % from % to %'', v_source.source_name, v_rrs.rrs_id, v_last_end_time, v_max_end_time;
                    IF v_rrs.parent IS NULL THEN
                        v_sql :=
                        ''INSERT INTO '' || v_source.insert_table || ''
                                            ( bucket_id, '' || v_source.group_clause || '', ''
                                                        || v_source.insert_aggregate_fields || '' )
                            SELECT a.rrs_bucket_id, '' || v_source.group_clause || ''
                                        , '' || v_source.primary_aggregate || ''
                                FROM
                                    (SELECT b.bucket_id AS rrs_bucket_id, s.*
                                        FROM rrs.bucket b
                                            JOIN '' || v_source.source_table || '' s
                                                ON (
                                                    b.prev_end_time  < '' || quote_ident(v_source.source_timestamptz_field) || ''
                                                    AND b.end_time >= '' || quote_ident(v_source.source_timestamptz_field) || ''
                                                    AND '' || quote_ident(v_source.source_timestamptz_field) || '' <= '' || quote_literal(v_max_end_time) || ''
                                                    AND '' || quote_ident(v_source.source_timestamptz_field) || '' > '' || quote_literal(v_last_end_time) || ''
                                                    )
                                        WHERE b.rrs_id = '' || quote_literal(v_rrs.rrs_id) || ''
                                            AND b.end_time <= '' || quote_literal(v_max_end_time) || ''
                                            AND b.end_time > '' || quote_literal(v_last_end_time) || ''
                                    ) a
                                GROUP BY rrs_bucket_id, '' || v_source.group_clause || '';''
                        ;
                    ELSE
                        -- Thanks to dealing with rrs.bucket twice, this query is a bit tricky. We want to look at rrs.bucket
                        -- for the rrs we''re *updating*, so that we know what our ranges are. Then, we want to query the 
                        -- parent data, and group it by the different ranges
                        v_sql := 
                        ''INSERT INTO '' || v_source.insert_table || ''
                                            ( bucket_id, '' || v_source.group_clause || '', ''
                                                        || v_source.insert_aggregate_fields || '' )
                            SELECT a.rrs_bucket_id, '' || v_source.group_clause || ''
                                        , '' || v_source.rrs_aggregate || ''
                                FROM 
                                    -- Wrap this whole thing in a sub-select to avoid field name conflicts
                                    (
                                    SELECT b.bucket_id AS rrs_bucket_id, r.*
                                        FROM '' || v_source.insert_table || '' r
                                            JOIN rrs.bucket p ON (r.bucket_id = p.bucket_id)
                                            , rrs.bucket b

                                        -- Get just the appropriate buckets for the RRD we are *updating*
                                        WHERE b.rrs_id = '' || quote_literal(v_rrs.rrs_id) || ''
                                            AND b.end_time <= '' || quote_literal(v_max_end_time) || ''
                                            AND b.end_time > '' || quote_literal(v_last_end_time) || ''

                                        -- Select the parent data but only for the appropriate time slots
                                            AND p.rrs_id = '' || quote_literal(v_rrs.parent) || ''
                                            AND p.end_time <= b.end_time
                                            AND p.end_time > b.prev_end_time
                                    ) a
                                GROUP BY rrs_bucket_id, '' || v_source.group_clause || '';''
                        ;
                    END IF;
                    RAISE DEBUG ''Executing query: %'', v_sql;
                    EXECUTE v_sql;

                    GET DIAGNOSTICS v_rows = ROW_COUNT;
                    RAISE INFO ''% rows inserted'', v_rows;

                    UPDATE rrs.source_status
                        SET last_end_time = v_max_end_time
                        WHERE rrs_id = v_rrs.rrs_id
                            AND source_id = v_source.source_id
                    ;
                    IF NOT FOUND THEN
                        INSERT INTO rrs.source_status( rrs_id, source_id, last_end_time )
                            VALUES( v_rrs.rrs_id, v_source.source_id, v_max_end_time )
                        ;
                    END IF;
                END IF;
            END IF;

            v_total_rows := v_total_rows + v_rows;
            --debug.f(''alert_rrs %s rows added for rrs_id %s'', v_rows, v_rrs.rrs_id);
        END LOOP;
    END LOOP;

    v_run_time := rrs.log_time( v_start_time );
    RAISE INFO ''% rows updated in %'', v_total_rows, v_run_time;

    --debug.f(''alert_rrs exit'');
    PERFORM rrs.update_lock(v_my_oid, 0);
    RETURN v_total_rows;
END;
'
    LANGUAGE plpgsql
    SECURITY DEFINER
;

--
-- TOC entry 17 (OID 591228975)
-- Name: max_end_time_to_delete(integer); Type: FUNCTION; Schema: rrs; Owner: pgsql
--

CREATE OR REPLACE FUNCTION max_end_time_to_delete(integer) RETURNS timestamp with time zone
    AS '
DECLARE
    p_rrs_id ALIAS FOR $1;
    v_min_end_time TIMESTAMP WITH TIME ZONE;
BEGIN
    -- For each rrs if no data has been captured yet we''ll get a null. If we do
    -- that means don''t delete anything.

    -- Update maximum end_time that can be removed based on rrs_source_status
    -- We don''t want to delete any buckets that have never been updated. We also
    -- need to consider that there may be sources with no records in source_status, so
    -- we do an outer join.
    -- We also need to take our parent RRDs into account.
    SELECT INTO v_min_end_time
            -- coalesce before min otherwise NULLs are ignored
            min(coalesce(last_end_time, ''1970-01-01 00:00:00-00''))
        FROM rrs.source s
            JOIN rrs.rrs r ON (1=1)
            LEFT JOIN rrs.source_status ss ON (ss.source_id = s.source_id AND ss.rrs_id = r.rrs_id)
        WHERE r.rrs_id = p_rrs_id
            OR r.parent = p_rrs_id
    ;

    /*
    SELECT debug.f(''update_rrs_buckets v_min_end_time for rrs_id %s after alert_rrs = %s''::text
                , p_rrs_id::text
                , coalesce(v_min_end_time::text, ''NULL'')
        );
        */
    RAISE DEBUG ''v_min_end_time for rrs % after rrs.source_status = %'', p_rrs_id, v_min_end_time;

    -- Check on keep_buckets
    -- Find the last bucket created, and subtract keep buckets from it
    SELECT INTO v_min_end_time
            min(    (SELECT max(end_time)
                                    FROM rrs.bucket
                                    WHERE rrs_id = p_rrs_id
                                ) - time_per_bucket * keep_buckets
                            , v_min_end_time
                        )
        FROM rrs.rrs
        WHERE rrs_id = p_rrs_id
    ;
    
    /*
    SELECT debug.f(''update_rrs_buckets v_min_end_time for rrs_id %s keep_buckets = %s''
                , p_rrs_id::text
                , CASE WHEN v_min_end_time::text IS NULL THEN ''NULL'' ELSE v_min_end_time END
            );
            */
    RAISE DEBUG ''v_min_end_time for rrs % keep buckets = %'', p_rrs_id, v_min_end_time;

    RETURN v_min_end_time;
END;
'
    LANGUAGE plpgsql;


--
-- TOC entry 18 (OID 591228976)
-- Name: update_buckets(); Type: FUNCTION; Schema: rrs; Owner: pgsql
--

CREATE OR REPLACE FUNCTION update_buckets( history_data_interval.start_time%TYPE ) RETURNS integer
    AS '
DECLARE
    p_start_time ALIAS FOR $1;

    v_delete_end_time TIMESTAMP WITH TIME ZONE;
    v_first_end_time TIMESTAMP WITH TIME ZONE;
    v_last_end_time TIMESTAMP WITH TIME ZONE;
    v_rrs rrs.rrs%ROWTYPE;
    v_source rrs.source%ROWTYPE;
    v_rows int;
    v_sql text;
    v_rec record;
    v_buckets_added int := 0;
BEGIN
    --debug.f(''update_buckets enter'');

    -- Run through each RRD
    FOR v_rrs IN SELECT * FROM rrs.rrs ORDER BY coalesce( parent, -1 ), rrs_id
    LOOP
        --debug.f(''update_buckets deleting old buckets for rrs_id %'', v_rrs.rrs_id);
        RAISE INFO ''update_buckets deleting old buckets for rrs_id %'', v_rrs.rrs_id;
        -- Find out the most recent bucket we can delete
        v_delete_end_time := rrs.max_end_time_to_delete(v_rrs.rrs_id);
        -- Do the delete (won''t find any records if v_delete_end_time ended up NULL)
        /*
        debug.f(''update_buckets DELETE FROM rrs.bucket WHERE rrs_id = % AND end_time <= %''
                    , v_rrs.rrs_id
                    , CASE WHEN v_delete_end_time IS NULL THEN ''NULL'' ELSE to_char(v_delete_end_time) END
            );
        */
        DELETE FROM rrs.bucket
            WHERE rrs_id = v_rrs.rrs_id
                AND end_time <= v_delete_end_time
        ;
        GET DIAGNOSTICS v_rows = ROW_COUNT;

        --debug.f(''update_buckets % buckets deleted for rrs_id %'', SQL%ROWCOUNT, v_rrs.rrs_id);
        -- Add new records
        --debug.f(''update_buckets adding new buckets for rrs_id %'', v_rrs.rrs_id);
        RAISE INFO ''update_buckets: % buckets deleted, adding new buckets for rrs_id %'', v_rows, v_rrs.rrs_id;
        -- Is parent NULL? If so do things differently
        IF v_rrs.parent IS NULL THEN
            -- First, see if buckets already exist.
            SELECT max(end_time)
                INTO v_first_end_time
                FROM rrs.bucket
                WHERE rrs_id = v_rrs.rrs_id
            ;
            -- No records exist? Figure out the oldest time to use.
            IF v_first_end_time IS NULL THEN
                /*
                debug.f(''update_buckets no data found in rrs.bucket for top level rrs_id %''
                            || '', checking page_log.log''
                            , v_rrs.rrs_id
                    );
                */
                RAISE LOG ''update_buckets no data found in rrs.bucket for top level rrs_id %, checking sources'' , v_rrs.rrs_id ;
                v_sql := NULL;
                FOR v_source IN SELECT * FROM rrs.source
                LOOP
                    IF v_sql IS NOT NULL THEN
                        v_sql := v_sql || ''
                                UNION ALL'';
                    ELSE
                        v_sql := '''';
                    END IF;
                    v_sql := v_sql || ''
                        SELECT min('' || quote_ident(v_source.source_timestamptz_field) || '') AS ts
                            FROM '' || v_source.source_table 
                    ;
                END LOOP;
                v_sql := ''
                    SELECT min(ts) AS ts
                        FROM ('' || v_sql || ''
                            ) a
                    ''
                ;
                RAISE DEBUG ''executing SQL: %'', v_sql;
                FOR v_rec IN EXECUTE v_sql
                LOOP
                    v_first_end_time := v_rec.ts;
                END LOOP;
            END IF;

            -- Now, figure out what bucket we should start adding with
            IF v_first_end_time IS NULL THEN
                --debug.f(''update_buckets no data found in page_log.log, skipping to next RRD'');
                RAISE LOG ''update_buckets no data found in sources, skipping to next RRD'';
            ELSE
                v_first_end_time := rrs.interval_time( v_first_end_time, v_rrs.time_per_bucket) + v_rrs.time_per_bucket;

                -- Figure out our end time. This is an expensive routine, so don''t do it if we''re close to caught up
                IF v_first_end_time > rrs.tod() - rrs.setting_get( ''desired run length'' )::interval * 5 THEN
                    v_last_end_time := rrs.tod();
                ELSE
                    v_last_end_time := rrs.calculate_end_time( v_first_end_time, p_start_time );
                END IF;

                --debug.f(''update_buckets new first_end_time is %'', v_first_end_time);
                RAISE LOG ''update_buckets new first_end_time is %'', v_first_end_time;
                v_rows :=  rrs.add_buckets(v_rrs.rrs_id, v_rrs.time_per_bucket, v_first_end_time, v_last_end_time);
                v_buckets_added = v_buckets_added + v_rows;
                RAISE INFO ''update_buckets: % buckets added'', v_rows;
            END IF;
        ELSE
            -- Parent is NOT NULL.
            -- First, find out what our first end time is. If there are already buckets for this RRS, then it will be the
            -- last bucket for this RRS.
            RAISE DEBUG ''rrs.parent IS NOT NULL'';
            SELECT max(end_time)
                INTO v_first_end_time
                FROM rrs.bucket
                WHERE rrs_id = v_rrs.rrs_id
            ;
            
            -- If there''s no buckets for this RRS, use the *first* bucket in our parent. We use the first because we want
            -- to populate this RRS with as much data as possible.
            IF v_first_end_time IS NULL THEN
                /*
                debug.f(''update_buckets no buckets found for rrs_id %, checking parent (%) for data''
                            , v_rrs.rrs_id, v_rrs.parent
                    );
                    */
                RAISE LOG ''update_buckets no buckets found for rrs_id %, checking parent (%) for data'' , v_rrs.rrs_id, v_rrs.parent ;
                SELECT min(end_time)
                    INTO v_first_end_time
                    FROM rrs.bucket
                    WHERE rrs_id = v_rrs.parent
                ;
            END IF;

            IF v_first_end_time IS NULL THEN
                -- If there''s no data for our parent, we don''t want to create any buckets, because we''d end up with missing
                -- data.
                --debug.f(''update_buckets no data available for rrs_id %, skipping to next RRD'', v_rrs.rrs_id);
                RAISE LOG ''update_buckets no data available for rrs_id %, skipping to next RRD'', v_rrs.rrs_id;
            ELSE
                -- If we have our first end time, figure out what the last end time will be. This is just the last end time
                -- of our parent (and no more, because we don''t want to miss any data).
                SELECT max(end_time)
                    INTO v_last_end_time
                    FROM rrs.bucket
                    WHERE rrs_id = v_rrs.parent
                ;
                v_rows := rrs.add_buckets(v_rrs.rrs_id, v_rrs.time_per_bucket, v_first_end_time, v_last_end_time);
                v_buckets_added = v_buckets_added + v_rows;
                RAISE INFO ''update_buckets: % buckets added'', v_rows;
            END IF;
        END IF;
    END LOOP;
    --debug.f(''update_buckets exit'');
    RETURN v_buckets_added;
END;
'
    LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION calculate_end_time ( rrs.bucket.end_time%TYPE, rrs.history.start_time%TYPE ) RETURNS timestamp with time zone AS '
DECLARE
    p_first_end_time ALIAS FOR $1;
    p_start_time ALIAS FOR $2;

    r record;
    v_desired_run_time interval;
    v_last_start_time rrs.history.start_time%TYPE;
    v_min_interval interval;
    v_max_interval interval;
    v_next_data_interval rrs.history_data_interval.data_interval%TYPE;
    v_last_data_interval_secs double precision;
    v_last_end_time rrs.bucket.end_time%TYPE;
BEGIN
    v_desired_run_time := rrs.setting_get(''desired run time'');
    IF v_desired_run_time IS NULL THEN
        v_desired_run_time := rrs.setting_set( ''desired run time'', ''50 seconds'' );
        v_desired_run_time := rrs.setting_get(''desired run time'');
        RAISE WARNING ''calculate_end_time: WARNING desired run time not set, setting to %'', v_desired_run_time;
    END IF;

    v_min_interval = rrs.setting_get(''minimum data interval'');
    IF v_min_interval IS NULL THEN
        v_min_interval = rrs.setting_set(
                                            ''minimum data interval''
                                            , ( v_desired_run_time * 10 )::text
                                        )
        ;
        v_min_interval = rrs.setting_get(''minimum data interval'');
        RAISE WARNING ''calculate_end_time: WARNING minimum data interval not set, setting to %'', v_min_interval;
    END IF;

    v_max_interval = rrs.setting_get(''maximum data interval'');
    IF v_max_interval IS NULL THEN
        v_max_interval = rrs.setting_set(
                                            ''maximum data interval''
                                            , ( v_min_interval * 100 )::text
                                        )
        ;
        v_max_interval = rrs.setting_get(''maximum data interval'');
        RAISE WARNING ''calculate_end_time: WARNING maximum data interval not set, setting to %'', v_max_interval;
    END IF;

    IF v_max_interval < v_min_interval THEN
        RAISE EXCEPTION ''calculate_end_time: EXCEPTION! maximum data interval(%) < minimum data interval(%)'', v_max_interval, v_min_interval;
    END IF;

    RAISE DEBUG ''calculate_end_time BEGIN: p_first_end_time %, p_start_time %, desired run time %, minimum data interval %, maximum data interval %'', p_first_end_time, p_start_time, v_desired_run_time, v_min_interval;

    /*
        For each row in history, calculate the ratio of desired runtime to actual, and apply that to the
        data_interval that was used in that run. Take the average of those and use it for our value.
    */
    
    SELECT INTO v_next_data_interval, v_last_start_time
            avg(next_data_interval), max(start_time)
        FROM rrs.history_data_interval_run_time_v
        WHERE next_data_interval IS NOT NULL
    ;
    SELECT INTO v_last_data_interval_secs
            rrs.interval_to_seconds(data_interval)
        FROM rrs.history_data_interval
        WHERE start_time = v_last_start_time
    ;

    IF v_next_data_interval IS NULL THEN
        RAISE INFO ''calculate_end_time: no data available, using minimum interval (%)'', v_min_interval;
        v_next_data_interval := v_min_interval;
    ELSE
        IF rrs.interval_to_seconds(v_next_data_interval) > v_last_data_interval_secs * 3 THEN
            RAISE INFO ''calculate_end_time: maximum data interval slew exceeded, capping to 3x last setting'';
            v_next_data_interval := rrs.seconds_to_interval( v_last_data_interval_secs * 3 );
        END IF;
    END IF;
    IF v_next_data_interval < v_min_interval THEN
        RAISE INFO ''v_next_data_interval % too low, forcing to minimum interval (%)'', v_next_data_interval, v_min_interval;
        v_next_data_interval := v_min_interval;
    END IF;
    IF v_next_data_interval > v_max_interval THEN
        RAISE INFO ''v_next_data_interval % too high, forcing to maximum interval (%)'', v_next_data_interval, v_max_interval;
        v_next_data_interval := v_max_interval;
    END IF;

    -- Now we have an upper limit on how many buckets to process, but we don''t want to go beyond rrs.tod()
    v_last_end_time := min( rrs.tod(), p_first_end_time + v_next_data_interval );
    v_next_data_interval := v_last_end_time - p_first_end_time;

    -- Log how much time we''ll actually be processing
    INSERT INTO rrs.history_data_interval( start_time, end_time, data_interval )
        VALUES( p_start_time, p_start_time, v_next_data_interval )
    ;

    RAISE INFO ''limiting bucket creation to %'', v_next_data_interval; 
    RAISE DEBUG ''calculate_end_time RETURN: v_last_end_time %'', v_last_end_time;
    RETURN v_last_end_time;
END;
' LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION interval_to_seconds(interval) RETURNS double precision AS '
    SELECT extract( EPOCH FROM $1 );
' LANGUAGE sql IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION seconds_to_interval(double precision) RETURNS interval AS '
    SELECT ( $1::text || '' seconds'' )::interval(6);
' LANGUAGE sql IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION interval_time(timestamp with time zone, interval) RETURNS timestamp with time zone
    AS '
SELECT ''1970-01-01 GMT''::timestamp with time zone + rrs.seconds_to_interval(
            floor( extract( EPOCH FROM $1 ) / rrs.interval_to_seconds($2) ) * rrs.interval_to_seconds($2)::int
        ) ;
'
    LANGUAGE sql IMMUTABLE STRICT;


--
-- TOC entry 20 (OID 591228978)
-- Name: add_buckets(integer, interval, timestamp with time zone, timestamp with time zone); Type: FUNCTION; Schema: rrs; Owner: pgsql
--

CREATE OR REPLACE FUNCTION add_buckets(integer, interval, timestamp with time zone, timestamp with time zone) RETURNS integer
    AS '
DECLARE
    p_rrs_id ALIAS FOR $1;
    p_time_per_bucket ALIAS FOR $2;
    p_first_end_time ALIAS FOR $3;
    p_last_end_time ALIAS FOR $4;

    v_current_end_time    TIMESTAMP WITH TIME ZONE;
    v_max_end_time        TIMESTAMP WITH TIME ZONE;
    v_buckets_added       int := 0;
BEGIN
        --debug.f(''update_rrs_buckets: add_buckets called with NULL first_end_time'');
    IF p_first_end_time IS NOT NULL THEN
        RAISE DEBUG ''update_rrs_buckets: add_buckets enter (rrs_id=%, time_per_bucket=%, first_end_time=>%, last_end_time=>%)'', p_rrs_id, p_time_per_bucket, p_first_end_time, p_last_end_time;
        -- Set v_current_end_time to a "cleaned up" version of first_end_time that we know falls
        -- on the proper boundaries
        v_current_end_time := rrs.interval_time( p_first_end_time, p_time_per_bucket );

        -- Figure out what the most recent bucket we can create is
        v_max_end_time := rrs.interval_time( p_last_end_time, p_time_per_bucket ); 
        /*
        debug.f(''add_buckets: adding buckets for rrs_id % between % and %''
                    , p_rrs_id
                    , v_current_end_time
                    , v_max_end_time
            );
        */
        RAISE DEBUG ''add_buckets: adding buckets for rrs_id % between % and %'', p_rrs_id, v_current_end_time, v_max_end_time;
        WHILE v_current_end_time <= v_max_end_time
        LOOP
            IF NOT EXISTS( SELECT * FROM rrs.bucket WHERE rrs_id = p_rrs_id AND end_time = v_current_end_time ) THEN
                INSERT INTO rrs.bucket(rrs_id, end_time, prev_end_time)
                    VALUES(p_rrs_id, v_current_end_time, v_current_end_time - p_time_per_bucket)
                ;
                v_buckets_added := v_buckets_added + 1;
            END IF;
            v_current_end_time := v_current_end_time + p_time_per_bucket;
        END LOOP;
        --debug.f(''update_rrs_buckets % buckets added to rrs_id %'', v_buckets_added, p_rrs_id);
        RAISE DEBUG ''add_buckets: % buckets added to rrs_id %'', v_buckets_added, p_rrs_id;
    END IF;

    RETURN v_buckets_added;
END;
'
    LANGUAGE plpgsql;



CREATE OR REPLACE FUNCTION log_time( timestamptz ) RETURNS interval AS '
DECLARE
    p_start_time ALIAS FOR $1;

    v_history_length bigint;
    v_history_data_interval_length bigint;
    v_end_time rrs.history.end_time%TYPE;
    v_min_keep_time rrs.history.start_time%TYPE;
BEGIN
    v_end_time := timeofday();
    RAISE DEBUG ''log_time: p_start_time %, v_end_time %'', p_start_time, v_end_time;

    -- Trim the history table
    v_history_length := rrs.setting_get( ''history length'' );
    IF v_history_length IS NULL THEN
        v_history_length := rrs.setting_set( ''history length'', 100 );
        v_history_length := rrs.setting_get( ''history length'' );
        RAISE WARNING ''log_time: WARNING history length not set, setting to %'', v_history_length;
    END IF;
    SELECT INTO v_min_keep_time
            min( start_time )
        FROM (
                SELECT start_time
                    FROM (
                            SELECT start_time
                                FROM rrs.history
                                ORDER BY start_time DESC
                        ) ordered
                    LIMIT v_history_length
            ) limited
    ;
    IF FOUND THEN
        DELETE FROM rrs.history WHERE start_time < v_min_keep_time;
    END IF;

    -- If a record already exists that means it was added by calculate_end_time, so we need
    -- to update end_time instead of inserting into the table
    IF EXISTS (SELECT * FROM rrs.history_data_interval WHERE start_time = p_start_time) THEN
        -- Trim the history table
        v_history_data_interval_length := rrs.setting_get( ''history_data_interval length'' );
        IF v_history_data_interval_length IS NULL THEN
            v_history_data_interval_length := rrs.setting_set( ''history_data_interval length'', 20 );
            v_history_data_interval_length := rrs.setting_get( ''history_data_interval length'' );
            RAISE WARNING ''log_time: WARNING history_data_interval length not set, setting to %'', v_history_data_interval_length;
        END IF;

        IF v_history_data_interval_length >= v_history_length THEN
            IF v_history_data_interval_length > v_history_length THEN
                RAISE INFO ''log_time: history_data_interval length (%) > history_length(%)'', v_history_data_interval_length, v_history_length;
            END IF;
        ELSE
            SELECT INTO v_min_keep_time
                    min( start_time )
                FROM (
                        SELECT start_time
                            FROM (
                                    SELECT start_time
                                        FROM rrs.history_data_interval
                                        ORDER BY start_time DESC
                                ) ordered
                            LIMIT v_history_data_interval_length
                    ) limited
            ;
            IF FOUND THEN
                DELETE FROM rrs.history_data_interval WHERE start_time < v_min_keep_time;
            END IF;
        END IF;
        
        UPDATE rrs.history
            SET end_time = v_end_time
            WHERE start_time = p_start_time
        ;
    ELSE
        INSERT INTO rrs.history( start_time, end_time )
            VALUES( p_start_time, v_end_time )
        ;
    END IF;

    RETURN v_end_time - p_start_time;
END;
'
    LANGUAGE plpgsql
    SECURITY DEFINER
;


CREATE OR REPLACE FUNCTION setting_get(text) RETURNS text AS '
    SELECT setting FROM rrs.setting WHERE setting_name = $1;
'
    LANGUAGE sql
    SECURITY DEFINER
;

CREATE OR REPLACE FUNCTION setting_set(text, text) RETURNS text AS '
DECLARE
    p_setting_name ALIAS FOR $1;
    p_setting ALIAS FOR $2;

    v_old_setting rrs.setting.setting%TYPE;
BEGIN
    SELECT INTO v_old_setting
            setting
        FROM rrs.setting
        WHERE setting_name = p_setting_name
        FOR UPDATE
    ;

    IF p_setting IS NULL THEN
        IF FOUND THEN
            DELETE FROM rrs.setting
                WHERE setting_name = p_setting_name
            ;
        END IF;
    ELSE
        IF NOT FOUND THEN
            INSERT INTO rrs.setting( setting_name, setting )
                VALUES( p_setting_name, p_setting )
            ;
            v_old_setting := NULL;
        ELSE
            UPDATE rrs.setting
                SET setting = p_setting
                WHERE setting_name = p_setting_name
            ;
        END IF;
    END IF;

    RETURN v_old_setting;
END;
'
    LANGUAGE plpgsql
    SECURITY DEFINER
;

CREATE OR REPLACE FUNCTION tod() RETURNS timestamptz AS '
    SELECT timeofday()::timestamptz
' LANGUAGE sql
;


GRANT EXECUTE ON FUNCTION update() TO public;

-- vi: expandtab sw=4 ts=4