/
create_prev_time_partition.sql
96 lines (83 loc) · 3.84 KB
/
create_prev_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
/*
* Populate the child table(s) of a time-based partition set with old data from the original parent
*/
CREATE FUNCTION create_prev_time_partition(p_parent_table text, p_batch int DEFAULT 1) RETURNS bigint
LANGUAGE plpgsql SECURITY DEFINER
AS $$
DECLARE
v_control text;
v_datetime_string text;
v_last_partition_name text;
v_max_partition_timestamp timestamp;
v_min_control timestamp;
v_min_partition_timestamp timestamp;
v_part_interval interval;
v_partition_timestamp timestamp[];
v_rowcount bigint;
v_sql text;
v_total_rows bigint := 0;
v_type text;
BEGIN
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;
EXECUTE 'SELECT min('||v_control||') FROM ONLY '||p_parent_table INTO v_min_control;
IF v_min_control IS NULL THEN
RETURN 0;
END IF;
CASE
WHEN v_part_interval = '15 mins' THEN
v_min_partition_timestamp := date_trunc('hour', v_min_control) +
'15min'::interval * floor(date_part('minute', v_min_control) / 15.0);
WHEN v_part_interval = '30 mins' THEN
v_min_partition_timestamp := date_trunc('hour', v_min_control) +
'30min'::interval * floor(date_part('minute', v_min_control) / 30.0);
WHEN v_part_interval = '1 hour' THEN
v_min_partition_timestamp := date_trunc('hour', v_min_control);
WHEN v_part_interval = '1 day' THEN
v_min_partition_timestamp := date_trunc('day', v_min_control);
WHEN v_part_interval = '1 week' THEN
v_min_partition_timestamp := date_trunc('week', v_min_control);
WHEN v_part_interval = '1 month' THEN
v_min_partition_timestamp := date_trunc('month', v_min_control);
WHEN v_part_interval = '3 months' THEN
v_min_partition_timestamp := date_trunc('quarter', v_min_control);
WHEN v_part_interval = '1 year' THEN
v_min_partition_timestamp := date_trunc('year', v_min_control);
END CASE;
-- Subtract 1 so that batch count number actually makes sense
FOR i IN 0..p_batch-1 LOOP
v_partition_timestamp := ARRAY[(v_min_partition_timestamp + (v_part_interval*i))::timestamp];
RAISE NOTICE 'v_partition_timestamp: %',v_partition_timestamp;
v_max_partition_timestamp := v_min_partition_timestamp + (v_part_interval*(i+1));
RAISE NOTICE 'v_max_partition_timestamp: %',v_max_partition_timestamp;
v_sql := 'SELECT @extschema@.create_time_partition('||quote_literal(p_parent_table)||','||quote_literal(v_control)||','
||quote_literal(v_part_interval)||','||quote_literal(v_datetime_string)||','||quote_literal(v_partition_timestamp)||')';
RAISE NOTICE 'v_sql: %', v_sql;
EXECUTE v_sql INTO v_last_partition_name;
-- create_time_partition() already checks to see if the partition exists and skips creation if it does.
-- So this function will still work with already existing partitions to get all data moved out of parent table up to and including when
-- pg_partman was used to set up partitioning.
v_sql := 'WITH partition_data AS (
DELETE FROM ONLY '||p_parent_table||' WHERE '||v_control||' >= '||quote_literal(v_min_partition_timestamp)||
' AND '||v_control||' < '||quote_literal(v_max_partition_timestamp)||' RETURNING *)
INSERT INTO '||v_last_partition_name||' SELECT * FROM partition_data';
RAISE NOTICE 'v_sql: %', v_sql;
EXECUTE v_sql;
GET DIAGNOSTICS v_rowcount = ROW_COUNT;
v_total_rows := v_total_rows + v_rowcount;
IF v_rowcount = 0 THEN
EXIT;
END IF;
END LOOP;
RETURN v_total_rows;
END
$$;