root/sql/sprocs/stratcon.drop_old_metrics.sql

Revision 51b2a89650aa59a060f72e44e0936e37650c04d6, 2.2 kB (checked in by Theo Schlossnagle <jesus@omniti.com>, 2 years ago)

Cherry pick two fixed from whizz

[4d04680929d4d03879c7a9d30830f6eeea39a338]
Add function to drop old metrics data

[6a78a0b46f8ca626ab9978b5a6ea7ef031a4bfa3]
Cosmetics, change few NOTICEs to DEBUGs

  • Property mode set to 100644
Line 
1 -- Function: stratcon.drop_old_metrics(interval, boolean)
2
3 -- DROP FUNCTION stratcon.drop_old_metrics(interval, boolean);
4
5 CREATE OR REPLACE FUNCTION stratcon.drop_old_metrics(in_keep interval, in_doit boolean DEFAULT false)
6   RETURNS integer AS
7 $BODY$declare
8   v_last date;
9   v_rollups date;
10   v_tdate text;
11   v_rec record;
12   v_sql text;
13   v_count integer := 0;
14 begin
15
16   -- refuse to delete todays data
17   if in_keep < '1 minute'::interval then
18     raise exception 'Cannot drop today data, specify some positive interval';
19   end if;
20   v_last := current_date - in_keep;
21
22   -- get the last data, that has been rolled up
23   select max(rollup_time)::date into v_rollups from noit.metric_numeric_rollup_5m;
24
25   -- if the data to delete is still needed for rollups, refuse to delete it
26   if v_last >= v_rollups then
27     raise exception 'Cannot drop data, that is not rolled up. Are your rollups running? Last rollup is: %', v_rollups;
28   end if;
29   v_tdate = extract(year from v_last) ||
30             lpad(extract(month from v_last)::text, 2, '0') ||
31             lpad(extract(day from v_last)::text, 2, '0');
32
33   -- get table names to drop
34   for v_rec in
35     select nspname||'.'||relname as tablename
36       from pg_class c
37       join pg_namespace n on (c.relnamespace = n.oid)
38      where relkind = 'r'
39        and relname like 'metric_%_archive_%'
40        and ( relname <= ('metric_numeric_archive_' || v_tdate) or relname <= ('metric_text_archive_' || v_tdate))
41      order by relname
42   loop
43     v_sql := 'drop table ' || v_rec.tablename;
44     raise notice 'Dropping table %', v_rec.tablename;
45     if in_doit then
46       execute v_sql;
47     else
48       raise notice 'Dry run, would execute: %', v_sql;
49     end if;
50     v_count := v_count + 1;
51   end loop;
52   return v_count;
53 end;$BODY$
54   LANGUAGE 'plpgsql' VOLATILE
55   COST 100;
56 ALTER FUNCTION stratcon.drop_old_metrics(interval, boolean) OWNER TO postgres;
57 COMMENT ON FUNCTION stratcon.drop_old_metrics(interval, boolean) IS 'Drop old metric data, that has been rolled up. Will refuse to drop data, that is still needed.
58 Parameters:
59  in_keep - how long into the past to keep data
60  in_doit - must be set to true to actually drop the tables, if set to false, will perform dry run';
Note: See TracBrowser for help on using the browser.