1CREATE FUNCTION partition_gap_fill(p_parent_table text) RETURNS integer
2    LANGUAGE plpgsql
3    AS $$
4DECLARE
5
6v_child_created                     boolean;
7v_children_created_count            int := 0;
8v_control                           text;
9v_control_type                      text;
10v_current_child_start_id            bigint;
11v_current_child_start_timestamp     timestamptz;
12v_expected_next_child_id            bigint;
13v_expected_next_child_timestamp     timestamptz;
14v_final_child_schemaname            text;
15v_final_child_start_id              bigint;
16v_final_child_start_timestamp       timestamptz;
17v_final_child_tablename             text;
18v_interval_id                       bigint;
19v_interval_time                     interval;
20v_previous_child_schemaname         text;
21v_previous_child_tablename          text;
22v_previous_child_start_id           bigint;
23v_previous_child_start_timestamp    timestamptz;
24v_parent_schema                     text;
25v_parent_table                      text;
26v_parent_tablename                  text;
27v_partition_interval                text;
28v_row                               record;
29
30BEGIN
31
32SELECT parent_table, partition_interval, control
33INTO v_parent_table, v_partition_interval, v_control
34FROM @extschema@.part_config
35WHERE parent_table = p_parent_table;
36IF v_parent_table IS NULL THEN
37    RAISE EXCEPTION 'Given parent table has no configuration in pg_partman: %', p_parent_table;
38END IF;
39
40SELECT n.nspname, c.relname INTO v_parent_schema, v_parent_tablename
41FROM pg_catalog.pg_class c
42JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
43WHERE n.nspname = split_part(p_parent_table, '.', 1)::name
44AND c.relname = split_part(p_parent_table, '.', 2)::name;
45    IF v_parent_tablename IS NULL THEN
46        RAISE EXCEPTION 'Unable to find given parent table in system catalogs. Ensure it is schema qualified: %', p_parent_table;
47    END IF;
48
49SELECT general_type INTO v_control_type FROM @extschema@.check_control_type(v_parent_schema, v_parent_tablename, v_control);
50
51SELECT partition_schemaname, partition_tablename
52INTO v_final_child_schemaname, v_final_child_tablename
53FROM @extschema@.show_partitions(v_parent_table, 'DESC')
54LIMIT 1;
55
56IF v_control_type = 'time' THEN
57
58    v_interval_time := v_partition_interval::interval;
59
60    SELECT child_start_time INTO v_final_child_start_timestamp
61        FROM @extschema@.show_partition_info(format('%s', v_final_child_schemaname||'.'||v_final_child_tablename), p_parent_table := v_parent_table);
62
63    FOR v_row IN
64        SELECT partition_schemaname, partition_tablename
65        FROM @extschema@.show_partitions(v_parent_table, 'ASC')
66    LOOP
67
68        RAISE DEBUG 'v_row.partition_tablename: %, v_final_child_start_timestamp: %', v_row.partition_tablename, v_final_child_start_timestamp;
69
70        IF v_previous_child_tablename IS NULL THEN
71            v_previous_child_schemaname := v_row.partition_schemaname;
72            v_previous_child_tablename := v_row.partition_tablename;
73            SELECT child_start_time INTO v_previous_child_start_timestamp
74                FROM @extschema@.show_partition_info(format('%s', v_previous_child_schemaname||'.'||v_previous_child_tablename), p_parent_table := v_parent_table);
75            CONTINUE;
76        END IF;
77
78        v_expected_next_child_timestamp := v_previous_child_start_timestamp + v_interval_time;
79
80        RAISE DEBUG 'v_expected_next_child_timestamp: %', v_expected_next_child_timestamp;
81
82        IF v_expected_next_child_timestamp = v_final_child_start_timestamp THEN
83            EXIT;
84        END IF;
85
86        SELECT child_start_time INTO v_current_child_start_timestamp
87            FROM @extschema@.show_partition_info(format('%s', v_row.partition_schemaname||'.'||v_row.partition_tablename), p_parent_table := v_parent_table);
88
89        RAISE DEBUG 'v_current_child_start_timestamp: %', v_current_child_start_timestamp;
90
91        IF v_expected_next_child_timestamp != v_current_child_start_timestamp THEN
92            v_child_created :=  @extschema@.create_partition_time(v_parent_table, ARRAY[v_expected_next_child_timestamp]);
93            IF v_child_created THEN
94                v_children_created_count := v_children_created_count + 1;
95                v_child_created := false;
96            END IF;
97            SELECT partition_schema, partition_table INTO v_previous_child_schemaname, v_previous_child_tablename
98                FROM @extschema@.show_partition_name(v_parent_table, v_expected_next_child_timestamp::text);
99            -- Need to stay in another inner loop until the next expected child timestamp matches the current one
100            -- Once it does, exit. This means gap is filled.
101            LOOP
102                v_previous_child_start_timestamp := v_expected_next_child_timestamp;
103                v_expected_next_child_timestamp := v_expected_next_child_timestamp + v_interval_time;
104                IF v_expected_next_child_timestamp = v_current_child_start_timestamp THEN
105                    EXIT;
106                ELSE
107
108        RAISE DEBUG 'inner loop: v_previous_child_start_timestamp: %, v_expected_next_child_timestamp: %, v_children_created_count: %'
109                , v_previous_child_start_timestamp, v_expected_next_child_timestamp, v_children_created_count;
110
111                    v_child_created := @extschema@.create_partition_time(v_parent_table, ARRAY[v_expected_next_child_timestamp]);
112                    IF v_child_created THEN
113                        v_children_created_count := v_children_created_count + 1;
114                        v_child_created := false;
115                    END IF;
116                END IF;
117            END LOOP; -- end expected child loop
118        END IF;
119
120        v_previous_child_schemaname := v_row.partition_schemaname;
121        v_previous_child_tablename := v_row.partition_tablename;
122        SELECT child_start_time INTO v_previous_child_start_timestamp
123            FROM @extschema@.show_partition_info(format('%s', v_previous_child_schemaname||'.'||v_previous_child_tablename), p_parent_table := v_parent_table);
124
125    END LOOP; -- end time loop
126
127ELSIF v_control_type = 'id' THEN
128
129    v_interval_id := v_partition_interval::bigint;
130
131    SELECT child_start_id INTO v_final_child_start_id
132        FROM @extschema@.show_partition_info(format('%s', v_final_child_schemaname||'.'||v_final_child_tablename), p_parent_table := v_parent_table);
133
134    FOR v_row IN
135        SELECT partition_schemaname, partition_tablename
136        FROM @extschema@.show_partitions(v_parent_table, 'ASC')
137    LOOP
138
139        RAISE DEBUG 'v_row.partition_tablename: %, v_final_child_start_id: %', v_row.partition_tablename, v_final_child_start_id;
140
141        IF v_previous_child_tablename IS NULL THEN
142            v_previous_child_schemaname := v_row.partition_schemaname;
143            v_previous_child_tablename := v_row.partition_tablename;
144            SELECT child_start_id INTO v_previous_child_start_id
145                FROM @extschema@.show_partition_info(format('%s', v_previous_child_schemaname||'.'||v_previous_child_tablename), p_parent_table := v_parent_table);
146            CONTINUE;
147        END IF;
148
149        v_expected_next_child_id := v_previous_child_start_id + v_interval_id;
150
151        RAISE DEBUG 'v_expected_next_child_id: %', v_expected_next_child_id;
152
153        IF v_expected_next_child_id = v_final_child_start_id THEN
154            EXIT;
155        END IF;
156
157        SELECT child_start_id INTO v_current_child_start_id
158            FROM @extschema@.show_partition_info(format('%s', v_row.partition_schemaname||'.'||v_row.partition_tablename), p_parent_table := v_parent_table);
159
160        RAISE DEBUG 'v_current_child_start_id: %', v_current_child_start_id;
161
162        IF v_expected_next_child_id != v_current_child_start_id THEN
163            v_child_created :=  @extschema@.create_partition_id(v_parent_table, ARRAY[v_expected_next_child_id]);
164            IF v_child_created THEN
165                v_children_created_count := v_children_created_count + 1;
166                v_child_created := false;
167            END IF;
168            SELECT partition_schema, partition_table INTO v_previous_child_schemaname, v_previous_child_tablename
169                FROM @extschema@.show_partition_name(v_parent_table, v_expected_next_child_id::text);
170            -- Need to stay in another inner loop until the next expected child id matches the current one
171            -- Once it does, exit. This means gap is filled.
172            LOOP
173                v_previous_child_start_id := v_expected_next_child_id;
174                v_expected_next_child_id := v_expected_next_child_id + v_interval_id;
175                IF v_expected_next_child_id = v_current_child_start_id THEN
176                    EXIT;
177                ELSE
178
179        RAISE DEBUG 'inner loop: v_previous_child_start_id: %, v_expected_next_child_id: %, v_children_created_count: %'
180                , v_previous_child_start_id, v_expected_next_child_id, v_children_created_count;
181
182                    v_child_created := @extschema@.create_partition_id(v_parent_table, ARRAY[v_expected_next_child_id]);
183                    IF v_child_created THEN
184                        v_children_created_count := v_children_created_count + 1;
185                        v_child_created := false;
186                    END IF;
187                END IF;
188            END LOOP; -- end expected child loop
189        END IF;
190
191        v_previous_child_schemaname := v_row.partition_schemaname;
192        v_previous_child_tablename := v_row.partition_tablename;
193        SELECT child_start_id INTO v_previous_child_start_id
194            FROM @extschema@.show_partition_info(format('%s', v_previous_child_schemaname||'.'||v_previous_child_tablename), p_parent_table := v_parent_table);
195
196    END LOOP; -- end id loop
197
198END IF; -- end time/id if
199
200RETURN v_children_created_count;
201
202END
203$$;
204
205