-
Notifications
You must be signed in to change notification settings - Fork 279
/
create_time_partition.sql
118 lines (97 loc) · 5.82 KB
/
create_time_partition.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
CREATE OR REPLACE FUNCTION part.create_time_partition (p_parent_table text, p_control text, p_interval interval, p_datetime_string text, p_partition_times timestamp[]) RETURNS text
LANGUAGE plpgsql SECURITY DEFINER
AS $$
DECLARE
v_job_id bigint;
v_jobmon_schema text;
v_old_search_path text;
v_partition_name text;
v_partition_timestamp_end timestamp;
v_partition_timestamp_start timestamp;
v_step_id bigint;
v_tablename text;
v_time timestamp;
BEGIN
SELECT nspname INTO v_jobmon_schema FROM pg_namespace n, pg_extension e WHERE e.extname = 'pg_jobmon' AND e.extnamespace = n.oid;
IF v_jobmon_schema IS NOT NULL THEN
SELECT current_setting('search_path') INTO v_old_search_path;
EXECUTE 'SELECT set_config(''search_path'',''part,'||v_jobmon_schema||''',''false'')';
END IF;
FOREACH v_time IN ARRAY p_partition_times LOOP
v_partition_name := p_parent_table || '_p';
IF p_interval = '1 year' OR p_interval = '1 month' OR p_interval = '1 day' OR p_interval = '1 hour' OR p_interval = '30 mins' OR p_interval = '15 mins' THEN
v_partition_name := v_partition_name || to_char(v_time, 'YYYY');
IF p_interval = '1 month' OR p_interval = '1 day' OR p_interval = '1 hour' OR p_interval = '30 mins' OR p_interval = '15 mins' THEN
v_partition_name := v_partition_name || '_' || to_char(v_time, 'MM');
IF p_interval = '1 day' OR p_interval = '1 hour' OR p_interval = '30 mins' OR p_interval = '15 mins' THEN
v_partition_name := v_partition_name || '_' || to_char(v_time, 'DD');
IF p_interval = '1 hour' OR p_interval = '30 mins' OR p_interval = '15 mins' THEN
v_partition_name := v_partition_name || '_' || to_char(v_time, 'HH24');
IF p_interval <> '30 mins' AND p_interval <> '15 mins' THEN
v_partition_name := v_partition_name || '00';
ELSIF p_interval = '15 mins' THEN
IF date_part('minute', v_time) < 15 THEN
v_partition_name := v_partition_name || '00';
ELSIF date_part('minute', v_time) >= 15 AND date_part('minute', v_time) < 30 THEN
v_partition_name := v_partition_name || '15';
ELSIF date_part('minute', v_time) >= 30 AND date_part('minute', v_time) < 45 THEN
v_partition_name := v_partition_name || '30';
ELSE
v_partition_name := v_partition_name || '45';
END IF;
ELSIF p_interval = '30 mins' THEN
IF date_part('minute', v_time) < 30 THEN
v_partition_name := v_partition_name || '00';
ELSE
v_partition_name := v_partition_name || '30';
END IF;
END IF;
END IF; -- end hour IF
END IF; -- end day IF
END IF; -- end month IF
ELSIF p_interval = '1 week' THEN
v_partition_name := v_partition_name || to_char(v_time, 'IYYY') || 'w' || to_char(v_time, 'IW');
END IF; -- end year/week IF
-- pull out datetime portion of last partition's tablename
v_partition_timestamp_start := to_timestamp(substring(v_partition_name from char_length(p_parent_table||'_p')+1), p_datetime_string);
v_partition_timestamp_end := to_timestamp(substring(v_partition_name from char_length(p_parent_table||'_p')+1), p_datetime_string) + p_interval;
SELECT schemaname ||'.'|| tablename INTO v_tablename FROM pg_catalog.pg_tables WHERE schemaname ||'.'|| tablename = v_partition_name;
IF v_tablename IS NOT NULL THEN
CONTINUE;
END IF;
IF v_jobmon_schema IS NOT NULL THEN
v_job_id := add_job('PARTMAN CREATE TABLE: '||p_parent_table);
v_step_id := add_step(v_job_id, 'Creating new partition '||v_partition_name||' with interval from '||v_partition_timestamp_start||' to '||(v_partition_timestamp_end-'1sec'::interval));
END IF;
IF position('.' in p_parent_table) > 0 THEN
v_tablename := substring(v_partition_name from position('.' in v_partition_name)+1);
END IF;
EXECUTE 'CREATE TABLE '||v_partition_name||' (LIKE '||p_parent_table||' INCLUDING DEFAULTS INCLUDING INDEXES) INHERITS ('||p_parent_table||')';
EXECUTE 'ALTER TABLE '||v_partition_name||' ADD CONSTRAINT '||v_tablename||'_partition_check
CHECK ('||p_control||'>='||quote_literal(v_partition_timestamp_start)||' AND '||p_control||'<'||quote_literal(v_partition_timestamp_end)||')';
IF v_jobmon_schema IS NOT NULL THEN
PERFORM update_step(v_step_id, 'OK', 'Done');
PERFORM close_job(v_job_id);
END IF;
END LOOP;
IF v_jobmon_schema IS NOT NULL THEN
EXECUTE 'SELECT set_config(''search_path'','''||v_old_search_path||''',''false'')';
END IF;
RETURN v_partition_name;
EXCEPTION
WHEN OTHERS THEN
IF v_jobmon_schema IS NOT NULL THEN
EXECUTE 'SELECT set_config(''search_path'',''part,'||v_jobmon_schema||''',''false'')';
IF v_job_id IS NULL THEN
v_job_id := add_job('PARTMAN CREATE TABLE: '||p_parent_table);
v_step_id := add_step(v_job_id, 'Partition maintenance for table '||p_parent_table||' failed');
ELSIF v_step_id IS NULL THEN
v_step_id := add_step(v_job_id, 'EXCEPTION before first step logged');
END IF;
PERFORM update_step(v_step_id, 'BAD', 'ERROR: '||coalesce(SQLERRM,'unknown'));
PERFORM fail_job(v_job_id);
EXECUTE 'SELECT set_config(''search_path'','''||v_old_search_path||''',''false'')';
END IF;
RAISE EXCEPTION '%', SQLERRM;
END
$$;