root/sql/sprocs/stratcon.archive_part_maint.sql

Revision d2dd72fa3f0404e6651f95cdc95214380a5630a2, 5.0 kB (checked in by Theo Schlossnagle <jesus@omniti.com>, 4 years ago)

partitioning by day now -- so says the Treat, refs #150

  • Property mode set to 100644
Line 
1 CREATE OR REPLACE FUNCTION stratcon.archive_part_maint(in_parent_table text, units text, cnt integer) RETURNS void
2 AS $$
3 DECLARE
4     v_recent_part date;
5     v_schema_name text;
6     v_table_name text;
7     v_constraint_name text;
8     v_next_part date;
9     v_parent_table text;
10     v_rec record;
11     v_sql text;
12     v_has_perms boolean;
13 BEGIN
14     v_parent_table := substring(in_parent_table from E'\\.(.+)');
15     IF v_parent_table IS NULL THEN
16       v_parent_table := in_parent_table;
17     END IF;
18     v_schema_name := substring(in_parent_table from E'^([^.]+)');
19     IF v_schema_name IS NULL THEN
20       v_schema_name := 'stratcon';
21     END IF;
22
23     select d into v_recent_part from (
24         select overlay(
25                    overlay(
26                        substring(relname from E'_([0-9]{8})$')
27                        placing '-' from 7 for 0)
28                    placing '-' from 5 for 0)::date as d from pg_class
29          where oid in (select inhrelid
30                          from pg_inherits as i
31                          join pg_class as c on(i.inhparent = c.oid)
32                          join pg_namespace as n on (c.relnamespace = n.oid)
33                         where nspname = v_schema_name
34                           and relname = v_parent_table)) as x
35     order by d desc
36     limit 1;
37
38     IF v_recent_part IS NULL THEN
39         select (date_trunc(units, current_date) - ('1 '||units)::interval)::date
40           into v_recent_part;
41     END IF;
42
43     select date_trunc(units, v_recent_part + ('1 '||units)::interval)::date
44       into v_next_part;
45
46     LOOP
47         IF v_next_part > current_date + (cnt * ('1 '||units)::interval) THEN
48             EXIT;
49         END IF;
50         v_table_name := v_parent_table || '_' || extract(YEAR from v_next_part) ||
51                         lpad(extract(month from v_next_part)::text, 2, '0') ||
52                         lpad(extract(day from v_next_part)::text, 2, '0');
53         v_constraint_name := 'check_' || v_table_name;
54
55         execute 'CREATE TABLE ' || v_schema_name || '.' || v_table_name || '(' ||
56                 '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)' ||
59                 ') INHERITS (' || in_parent_table || ')';
60
61         RAISE INFO 'created partition %', v_table_name;
62
63         FOR v_rec in
64             select replace(indexdef, v_parent_table, v_table_name) as sql
65               from pg_indexes
66              where tablename = v_parent_table and schemaname = v_schema_name
67         LOOP
68           RAISE INFO 'recreated parent indexes on %', v_table_name;
69           execute v_rec.sql;
70         END LOOP;
71
72         -- no public access
73         select count(*) > 0 into v_has_perms
74           from information_schema.table_privileges
75          where table_schema=v_schema_name and table_name=v_parent_table;
76
77         IF v_has_perms THEN
78           execute 'REVOKE ALL ON ' || v_schema_name || '.' || v_table_name || ' FROM PUBLIC';
79         END IF;
80
81         FOR v_rec in
82             select 'GRANT ' || privilege_type || ' ON ' || v_schema_name || '.' || v_table_name ||
83                    ' TO ' || grantee as sql
84               from information_schema.table_privileges
85              where table_schema=v_schema_name and table_name=v_parent_table
86         LOOP
87           execute v_rec.sql;
88         END LOOP;
89
90         FOR v_rec in
91             select tgname, tgtype, nspname, proname
92               from pg_class as c join pg_trigger as t on(c.oid = t.tgrelid)
93               join pg_proc as p on(t.tgfoid = p.oid)
94               join pg_namespace as n on(p.pronamespace = n.oid)
95              where relname = v_parent_table
96                and proname <> 'parent_empty' LOOP
97           v_sql := 'CREATE TRIGGER ' || v_rec.tgname || '';
98           IF 0 != (v_rec.tgtype & 2) THEN
99             v_sql := v_sql || ' BEFORE ';
100           ELSE
101             v_sql := v_sql || ' AFTER ';
102           END IF;
103           IF 0 != (v_rec.tgtype & 4) THEN
104             v_sql := v_sql || ' INSERT ';
105           END IF;
106           IF 0 != (v_rec.tgtype & 8) THEN
107             IF 0 != (v_rec.tgtype & 4) THEN
108               v_sql := v_sql || ' OR ';
109             END IF;
110             v_sql := v_sql || ' DELETE ';
111           END IF;
112           IF 0 != (v_rec.tgtype & 16) THEN
113             IF 0 != (v_rec.tgtype & 12) THEN
114               v_sql := v_sql || ' OR ';
115             END IF;
116             v_sql := v_sql || ' UPDATE ';
117           END IF;
118           v_sql := v_sql || ' ON ' || v_schema_name || '.' || v_table_name;
119           IF 0 != (v_rec.tgtype & 1) THEN
120             v_sql := v_sql || ' FOR EACH ROW ';
121           ELSE
122             v_sql := v_sql || ' FOR EACH STATEMENT ';
123           END IF;
124           v_sql := v_sql || ' EXECUTE PROCEDURE ' || v_rec.nspname || '.' || v_rec.proname || '()';
125           execute v_sql;
126         END LOOP;
127
128         v_next_part := (v_next_part + ('1 '||units)::interval)::date;
129     END LOOP;
130 END
131 $$ LANGUAGE plpgsql;
132
Note: See TracBrowser for help on using the browser.