-
Notifications
You must be signed in to change notification settings - Fork 279
/
create_time_function.sql
176 lines (153 loc) · 8.49 KB
/
create_time_function.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
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
CREATE FUNCTION create_time_function(p_parent_table text) RETURNS void
LANGUAGE plpgsql SECURITY DEFINER
AS $$
DECLARE
v_1st_partition_name text;
v_1st_partition_timestamp timestamp;
v_2nd_partition_name text;
v_2nd_partition_timestamp timestamp;
v_control text;
v_current_partition_name text;
v_current_partition_timestamp timestamp;
v_datetime_string text;
v_final_partition_timestamp timestamp;
v_job_id bigint;
v_jobmon_schema text;
v_old_search_path text;
v_part_interval interval;
v_prev_partition_name text;
v_prev_partition_timestamp timestamp;
v_step_id bigint;
v_trig_func text;
v_type text;
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;
IF v_jobmon_schema IS NOT NULL THEN
v_job_id := add_job('PARTMAN CREATE FUNCTION: '||p_parent_table);
v_step_id := add_step(v_job_id, 'Creating partition function for table '||p_parent_table);
END IF;
SELECT type
, part_interval::interval
, control
, datetime_string
FROM @extschema@.part_config
WHERE parent_table = p_parent_table
AND (type = 'time-static' OR type = 'time-dynamic')
INTO v_type, v_part_interval, v_control, v_datetime_string;
IF NOT FOUND THEN
RAISE EXCEPTION 'ERROR: no config found for %', p_parent_table;
END IF;
IF v_type = 'time-static' THEN
CASE
WHEN v_part_interval = '15 mins' THEN
v_current_partition_timestamp := date_trunc('hour', CURRENT_TIMESTAMP) +
'15min'::interval * floor(date_part('minute', CURRENT_TIMESTAMP) / 15.0);
WHEN v_part_interval = '30 mins' THEN
v_current_partition_timestamp := date_trunc('hour', CURRENT_TIMESTAMP) +
'30min'::interval * floor(date_part('minute', CURRENT_TIMESTAMP) / 30.0);
WHEN v_part_interval = '1 hour' THEN
v_current_partition_timestamp := date_trunc('hour', CURRENT_TIMESTAMP);
WHEN v_part_interval = '1 day' THEN
v_current_partition_timestamp := date_trunc('day', CURRENT_TIMESTAMP);
WHEN v_part_interval = '1 week' THEN
v_current_partition_timestamp := date_trunc('week', CURRENT_TIMESTAMP);
WHEN v_part_interval = '1 month' THEN
v_current_partition_timestamp := date_trunc('month', CURRENT_TIMESTAMP);
WHEN v_part_interval = '1 year' THEN
v_current_partition_timestamp := date_trunc('year', CURRENT_TIMESTAMP);
END CASE;
v_prev_partition_timestamp := v_current_partition_timestamp - v_part_interval::interval;
v_1st_partition_timestamp := v_current_partition_timestamp + v_part_interval::interval;
v_2nd_partition_timestamp := v_1st_partition_timestamp + v_part_interval::interval;
v_final_partition_timestamp := v_2nd_partition_timestamp + v_part_interval::interval;
v_prev_partition_name := p_parent_table || '_p' || to_char(v_prev_partition_timestamp, v_datetime_string);
v_current_partition_name := p_parent_table || '_p' || to_char(v_current_partition_timestamp, v_datetime_string);
v_1st_partition_name := p_parent_table || '_p' || to_char(v_1st_partition_timestamp, v_datetime_string);
v_2nd_partition_name := p_parent_table || '_p' || to_char(v_2nd_partition_timestamp, v_datetime_string);
v_trig_func := 'CREATE OR REPLACE FUNCTION '||p_parent_table||'_part_trig_func() RETURNS trigger LANGUAGE plpgsql AS $t$
BEGIN
IF TG_OP = ''INSERT'' THEN
IF NEW.'||v_control||' >= '||quote_literal(v_current_partition_timestamp)||' AND NEW.'||v_control||' < '||quote_literal(v_1st_partition_timestamp)|| ' THEN
INSERT INTO '||v_current_partition_name||' VALUES (NEW.*);
ELSIF NEW.'||v_control||' >= '||quote_literal(v_1st_partition_timestamp)||' AND NEW.'||v_control||' < '||quote_literal(v_2nd_partition_timestamp)|| ' THEN
INSERT INTO '||v_1st_partition_name||' VALUES (NEW.*);
ELSIF NEW.'||v_control||' >= '||quote_literal(v_2nd_partition_timestamp)||' AND NEW.'||v_control||' < '||quote_literal(v_final_partition_timestamp)|| ' THEN
INSERT INTO '||v_2nd_partition_name||' VALUES (NEW.*);
ELSIF NEW.'||v_control||' >= '||quote_literal(v_prev_partition_timestamp)||' AND NEW.'||v_control||' < '||quote_literal(v_current_partition_timestamp)|| ' THEN
INSERT INTO '||v_prev_partition_name||' VALUES (NEW.*);
ELSE
RETURN NEW;
END IF;
END IF;
RETURN NULL;
END $t$;';
-- RAISE NOTICE 'v_trig_func: %',v_trig_func;
EXECUTE v_trig_func;
IF v_jobmon_schema IS NOT NULL THEN
PERFORM update_step(v_step_id, 'OK', 'Added function for current time interval: '||v_current_partition_timestamp||' to '||(v_1st_partition_timestamp-'1sec'::interval));
END IF;
ELSIF v_type = 'time-dynamic' THEN
v_trig_func := 'CREATE OR REPLACE FUNCTION '||p_parent_table||'_part_trig_func() RETURNS trigger LANGUAGE plpgsql AS $t$
DECLARE
v_partition_name text;
v_partition_timestamp timestamp;
BEGIN
IF TG_OP = ''INSERT'' THEN
';
CASE
WHEN v_part_interval = '15 mins' THEN
v_trig_func := v_trig_func||'v_partition_timestamp := date_trunc(''hour'', NEW.'||v_control||') +
''15min''::interval * floor(date_part(''minute'', NEW.'||v_control||') / 15.0);';
WHEN v_part_interval = '30 mins' THEN
v_trig_func := v_trig_func||'v_partition_timestamp := date_trunc(''hour'', NEW.'||v_control||') +
''30min''::interval * floor(date_part(''minute'', NEW.'||v_control||') / 30.0);';
WHEN v_part_interval = '1 hour' THEN
v_trig_func := v_trig_func||'v_partition_timestamp := date_trunc(''hour'', NEW.'||v_control||');';
WHEN v_part_interval = '1 day' THEN
v_trig_func := v_trig_func||'v_partition_timestamp := date_trunc(''day'', NEW.'||v_control||');';
WHEN v_part_interval = '1 week' THEN
v_trig_func := v_trig_func||'v_partition_timestamp := date_trunc(''week'', NEW.'||v_control||');';
WHEN v_part_interval = '1 month' THEN
v_trig_func := v_trig_func||'v_partition_timestamp := date_trunc(''month'', NEW.'||v_control||');';
WHEN v_part_interval = '1 year' THEN
v_trig_func := v_trig_func||'v_partition_timestamp := date_trunc(''year'', NEW.'||v_control||');';
END CASE;
v_trig_func := v_trig_func||'
v_partition_name := '''||p_parent_table||'_p''|| to_char(v_partition_timestamp, '||quote_literal(v_datetime_string)||');
EXECUTE ''INSERT INTO ''||v_partition_name||'' VALUES($1.*)'' USING NEW;
END IF;
RETURN NULL;
END $t$;';
--RAISE NOTICE 'v_trig_func: %',v_trig_func;
EXECUTE v_trig_func;
IF v_jobmon_schema IS NOT NULL THEN
PERFORM update_step(v_step_id, 'OK', 'Added function for dynamic time table: '||p_parent_table);
END IF;
ELSE
RAISE EXCEPTION 'ERROR: Invalid time partitioning type given: %', v_type;
END IF;
IF v_jobmon_schema IS NOT NULL THEN
PERFORM close_job(v_job_id);
EXECUTE 'SELECT set_config(''search_path'','''||v_old_search_path||''',''false'')';
END IF;
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 FUNCTION: '||p_parent_table);
v_step_id := add_step(v_job_id, 'Partition function 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
$$;