root/sql/sprocs/stratcon.archive_part_maint.sql

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

add back in the partitioning maintenance functions

  • Property mode set to 100644
Line 
1 CREATE OR REPLACE FUNCTION stratcon.archive_part_maint(in_parent_table text, in_start date) 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   select (in_start - '1 month'::interval)::date into v_recent_part;
15   v_parent_table := substring(in_parent_table from E'\\.(.+)');
16   IF v_parent_table IS NULL THEN
17     v_parent_table := in_parent_table;
18   END IF;
19   v_schema_name := substring(in_parent_table from E'^([^.]+)');
20   IF v_schema_name IS NULL THEN
21     v_schema_name := 'stratcon';
22   END IF;
23
24     select date_trunc('month', v_recent_part + '1 month'::interval)::date into v_next_part;
25
26     LOOP
27         IF v_next_part > current_date + '1 month'::interval THEN
28             EXIT;
29         END IF;
30         v_table_name := v_parent_table || '_' || extract(YEAR from v_next_part) ||
31                         lpad(extract(month from v_next_part)::text, 2, '0');
32         v_constraint_name := 'check_' || v_table_name;
33
34         execute 'CREATE TABLE ' || v_schema_name || '.' || v_table_name || '(' ||
35                 'CONSTRAINT ' || v_constraint_name ||
36                 E' CHECK (whence >= \'' || v_next_part::text || E' 00:00:00-00\'::timestamptz AND ' ||
37                 E'        whence < \'' || (v_next_part + '1 month'::interval)::date::text || E' 00:00:00-00\'::timestamptz)' ||
38                 ') INHERITS (' || in_parent_table || ')';
39
40         RAISE INFO 'created partition %', v_table_name;
41
42         FOR v_rec in
43             select replace(indexdef, v_parent_table, v_table_name) as sql
44               from pg_indexes
45              where tablename = v_parent_table and schemaname = v_schema_name
46         LOOP
47           RAISE INFO 'recreated parent indexes on %', v_table_name;
48           execute v_rec.sql;
49         END LOOP;
50
51         -- no public access
52         select count(*) > 0 into v_has_perms
53           from information_schema.table_privileges
54          where table_schema=v_schema_name and table_name=v_parent_table;
55
56         IF v_has_perms THEN
57           execute 'REVOKE ALL ON ' || v_schema_name || '.' || v_table_name || ' FROM PUBLIC';
58         END IF;
59
60         FOR v_rec in
61             select 'GRANT ' || privilege_type || ' ON ' || v_schema_name || '.' || v_table_name ||
62                    ' TO ' || grantee as sql
63               from information_schema.table_privileges
64              where table_schema=v_schema_name and table_name=v_parent_table
65         LOOP
66           execute v_rec.sql;
67         END LOOP;
68
69         FOR v_rec in
70             select tgname, tgtype, nspname, proname
71               from pg_class as c join pg_trigger as t on(c.oid = t.tgrelid)
72               join pg_proc as p on(t.tgfoid = p.oid)
73               join pg_namespace as n on(p.pronamespace = n.oid)
74              where relname = v_parent_table
75                and proname <> 'parent_empty' LOOP
76           v_sql := 'CREATE TRIGGER ' || v_rec.tgname || '';
77           IF 0 != (v_rec.tgtype & 2) THEN
78             v_sql := v_sql || ' BEFORE ';
79           ELSE
80             v_sql := v_sql || ' AFTER ';
81           END IF;
82           IF 0 != (v_rec.tgtype & 4) THEN
83             v_sql := v_sql || ' INSERT ';
84           END IF;
85           IF 0 != (v_rec.tgtype & 8) THEN
86             IF 0 != (v_rec.tgtype & 4) THEN
87               v_sql := v_sql || ' OR ';
88             END IF;
89             v_sql := v_sql || ' DELETE ';
90           END IF;
91           IF 0 != (v_rec.tgtype & 16) THEN
92             IF 0 != (v_rec.tgtype & 12) THEN
93               v_sql := v_sql || ' OR ';
94             END IF;
95             v_sql := v_sql || ' UPDATE ';
96           END IF;
97           v_sql := v_sql || ' ON ' || v_schema_name || '.' || v_table_name;
98           IF 0 != (v_rec.tgtype & 1) THEN
99             v_sql := v_sql || ' FOR EACH ROW ';
100           ELSE
101             v_sql := v_sql || ' FOR EACH STATEMENT ';
102           END IF;
103           v_sql := v_sql || ' EXECUTE PROCEDURE ' || v_rec.nspname || '.' || v_rec.proname || '()';
104           execute v_sql;
105         END LOOP;
106
107         v_next_part := (v_next_part + '1 month'::interval)::date;
108     END LOOP;
109 END
110 $$ LANGUAGE plpgsql;
111
Note: See TracBrowser for help on using the browser.