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