1CREATE FUNCTION @extschema@.drop_partition_time(p_parent_table text, p_retention interval DEFAULT NULL, p_keep_table boolean DEFAULT NULL, p_keep_index boolean DEFAULT NULL, p_retention_schema text DEFAULT NULL) RETURNS int 2 LANGUAGE plpgsql 3 AS $$ 4DECLARE 5 6ex_context text; 7ex_detail text; 8ex_hint text; 9ex_message text; 10v_adv_lock boolean; 11v_control text; 12v_control_type text; 13v_count int; 14v_datetime_string text; 15v_drop_count int := 0; 16v_epoch text; 17v_index record; 18v_job_id bigint; 19v_jobmon boolean; 20v_jobmon_schema text; 21v_new_search_path text := '@extschema@,pg_temp'; 22v_old_search_path text; 23v_parent_schema text; 24v_parent_tablename text; 25v_partition_interval interval; 26v_partition_timestamp timestamptz; 27v_partition_type text; 28v_retention interval; 29v_retention_keep_index boolean; 30v_retention_keep_table boolean; 31v_retention_schema text; 32v_row record; 33v_step_id bigint; 34 35BEGIN 36/* 37 * Function to drop child tables from a time-based partition set. 38 * Options to move table to different schema, drop only indexes or actually drop the table from the database. 39 */ 40 41v_adv_lock := pg_try_advisory_xact_lock(hashtext('pg_partman drop_partition_time')); 42IF v_adv_lock = 'false' THEN 43 RAISE NOTICE 'drop_partition_time already running.'; 44 RETURN 0; 45END IF; 46 47-- Allow override of configuration options 48IF p_retention IS NULL THEN 49 SELECT 50 partition_type 51 , control 52 , partition_interval::interval 53 , epoch 54 , retention::interval 55 , retention_keep_table 56 , retention_keep_index 57 , datetime_string 58 , retention_schema 59 , jobmon 60 INTO 61 v_partition_type 62 , v_control 63 , v_partition_interval 64 , v_epoch 65 , v_retention 66 , v_retention_keep_table 67 , v_retention_keep_index 68 , v_datetime_string 69 , v_retention_schema 70 , v_jobmon 71 FROM @extschema@.part_config 72 WHERE parent_table = p_parent_table 73 AND retention IS NOT NULL; 74 75 IF v_partition_interval IS NULL THEN 76 RAISE EXCEPTION 'Configuration for given parent table with a retention period not found: %', p_parent_table; 77 END IF; 78ELSE 79 SELECT 80 partition_type 81 , partition_interval::interval 82 , epoch 83 , retention_keep_table 84 , retention_keep_index 85 , datetime_string 86 , retention_schema 87 , jobmon 88 INTO 89 v_partition_type 90 , v_partition_interval 91 , v_epoch 92 , v_retention_keep_table 93 , v_retention_keep_index 94 , v_datetime_string 95 , v_retention_schema 96 , v_jobmon 97 FROM @extschema@.part_config 98 WHERE parent_table = p_parent_table; 99 v_retention := p_retention; 100 101 IF v_partition_interval IS NULL THEN 102 RAISE EXCEPTION 'Configuration for given parent table not found: %', p_parent_table; 103 END IF; 104END IF; 105 106SELECT general_type INTO v_control_type FROM @extschema@.check_control_type(v_parent_schema, v_parent_tablename, v_control); 107IF v_control_type <> 'time' THEN 108 IF (v_control_type = 'id' AND v_epoch = 'none') OR v_control_type <> 'id' THEN 109 RAISE EXCEPTION 'Cannot run on partition set without time based control column or epoch flag set with an id column. Found control: %, epoch: %', v_control_type, v_epoch; 110 END IF; 111END IF; 112 113SELECT current_setting('search_path') INTO v_old_search_path; 114IF v_jobmon THEN 115 SELECT nspname INTO v_jobmon_schema FROM pg_catalog.pg_namespace n, pg_catalog.pg_extension e WHERE e.extname = 'pg_jobmon'::name AND e.extnamespace = n.oid; 116 IF v_jobmon_schema IS NOT NULL THEN 117 v_new_search_path := '@extschema@,'||v_jobmon_schema||',pg_temp'; 118 END IF; 119END IF; 120EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_new_search_path, 'false'); 121 122IF p_keep_table IS NOT NULL THEN 123 v_retention_keep_table = p_keep_table; 124END IF; 125IF p_keep_index IS NOT NULL THEN 126 v_retention_keep_index = p_keep_index; 127END IF; 128IF p_retention_schema IS NOT NULL THEN 129 v_retention_schema = p_retention_schema; 130END IF; 131 132SELECT schemaname, tablename INTO v_parent_schema, v_parent_tablename 133FROM pg_catalog.pg_tables 134WHERE schemaname = split_part(p_parent_table, '.', 1)::name 135AND tablename = split_part(p_parent_table, '.', 2)::name; 136 137-- Loop through child tables of the given parent 138-- Must go in ascending order to avoid dropping what may be the "last" partition in the set after dropping tables that match retention period 139FOR v_row IN 140 SELECT partition_schemaname, partition_tablename FROM @extschema@.show_partitions(p_parent_table, 'ASC') 141LOOP 142 -- pull out datetime portion of partition's tablename to make the next one 143 SELECT child_start_time INTO v_partition_timestamp FROM @extschema@.show_partition_info(v_row.partition_schemaname||'.'||v_row.partition_tablename 144 , v_partition_interval::text 145 , p_parent_table); 146 -- Add one interval since partition names contain the start of the constraint period 147 IF v_retention < (CURRENT_TIMESTAMP - (v_partition_timestamp + v_partition_interval)) THEN 148 149 -- Do not allow final partition to be dropped 150 SELECT count(*) INTO v_count FROM @extschema@.show_partitions(p_parent_table); 151 IF v_count = 1 THEN 152 RAISE WARNING 'Attempt to drop final partition in partition set % as part of retention policy. If you see this message multiple times for the same table, advise reviewing retention policy and/or data entry into the partition set. Also consider setting "infinite_time_partitions = true" if there are large gaps in data insertion.).', p_parent_table; 153 CONTINUE; 154 END IF; 155 156 -- Only create a jobmon entry if there's actual retention work done 157 IF v_jobmon_schema IS NOT NULL AND v_job_id IS NULL THEN 158 v_job_id := add_job(format('PARTMAN DROP TIME PARTITION: %s', p_parent_table)); 159 END IF; 160 161 IF v_jobmon_schema IS NOT NULL THEN 162 v_step_id := add_step(v_job_id, format('Detach/Uninherit table %s.%s from %s' 163 , v_row.partition_schemaname 164 , v_row.partition_tablename 165 , p_parent_table)); 166 END IF; 167 IF v_retention_keep_table = true THEN 168 -- No need to detach partition before dropping since it's going away anyway 169 -- Avoids issue of FKs not allowing detachment (Github Issue #294). 170 IF v_partition_type = 'native' THEN 171 EXECUTE format('ALTER TABLE %I.%I DETACH PARTITION %I.%I' 172 , v_parent_schema 173 , v_parent_tablename 174 , v_row.partition_schemaname 175 , v_row.partition_tablename); 176 ELSE 177 EXECUTE format('ALTER TABLE %I.%I NO INHERIT %I.%I' 178 , v_row.partition_schemaname 179 , v_row.partition_tablename 180 , v_parent_schema 181 , v_parent_tablename); 182 END IF; 183 END IF; 184 IF v_partition_type = 'time-custom' THEN 185 DELETE FROM @extschema@.custom_time_partitions WHERE parent_table = p_parent_table AND child_table = v_row.partition_schemaname||'.'||v_row.partition_tablename; 186 END IF; 187 IF v_jobmon_schema IS NOT NULL THEN 188 PERFORM update_step(v_step_id, 'OK', 'Done'); 189 END IF; 190 191 IF v_retention_schema IS NULL THEN 192 IF v_retention_keep_table = false THEN 193 IF v_jobmon_schema IS NOT NULL THEN 194 v_step_id := add_step(v_job_id, format('Drop table %s.%s', v_row.partition_schemaname, v_row.partition_tablename)); 195 END IF; 196 EXECUTE format('DROP TABLE %I.%I CASCADE', v_row.partition_schemaname, v_row.partition_tablename); 197 IF v_jobmon_schema IS NOT NULL THEN 198 PERFORM update_step(v_step_id, 'OK', 'Done'); 199 END IF; 200 ELSIF v_retention_keep_index = false THEN 201 IF v_partition_type = 'partman' OR 202 ( v_partition_type = 'native' AND current_setting('server_version_num')::int < 110000) THEN 203 -- Cannot drop child indexes on native partition sets in PG11+ 204 FOR v_index IN 205 WITH child_info AS ( 206 SELECT c1.oid 207 FROM pg_catalog.pg_class c1 208 JOIN pg_catalog.pg_namespace n1 ON c1.relnamespace = n1.oid 209 WHERE c1.relname = v_row.partition_tablename::name 210 AND n1.nspname = v_row.partition_schemaname::name 211 ) 212 SELECT c.relname as name 213 , con.conname 214 FROM pg_catalog.pg_index i 215 JOIN pg_catalog.pg_class c ON i.indexrelid = c.oid 216 LEFT JOIN pg_catalog.pg_constraint con ON i.indexrelid = con.conindid 217 JOIN child_info ON i.indrelid = child_info.oid 218 LOOP 219 IF v_jobmon_schema IS NOT NULL THEN 220 v_step_id := add_step(v_job_id, format('Drop index %s from %s.%s' 221 , v_index.name 222 , v_row.partition_schemaname 223 , v_row.partition_tablename)); 224 END IF; 225 IF v_index.conname IS NOT NULL THEN 226 EXECUTE format('ALTER TABLE %I.%I DROP CONSTRAINT %I' 227 , v_row.partition_schemaname 228 , v_row.partition_tablename 229 , v_index.conname); 230 ELSE 231 EXECUTE format('DROP INDEX %I.%I', v_parent_schema, v_index.name); 232 END IF; 233 IF v_jobmon_schema IS NOT NULL THEN 234 PERFORM update_step(v_step_id, 'OK', 'Done'); 235 END IF; 236 END LOOP; 237 END IF; -- end native/11 check 238 END IF; -- end v_retention_keep_index IF 239 ELSE -- Move to new schema 240 IF v_jobmon_schema IS NOT NULL THEN 241 v_step_id := add_step(v_job_id, format('Moving table %s.%s to schema %s' 242 , v_row.partition_schemaname 243 , v_row.partition_tablename 244 , v_retention_schema)); 245 END IF; 246 247 EXECUTE format('ALTER TABLE %I.%I SET SCHEMA %I', v_row.partition_schemaname, v_row.partition_tablename, v_retention_schema); 248 249 250 IF v_jobmon_schema IS NOT NULL THEN 251 PERFORM update_step(v_step_id, 'OK', 'Done'); 252 END IF; 253 END IF; -- End retention schema if 254 255 -- If child table is a subpartition, remove it from part_config & part_config_sub (should cascade due to FK) 256 DELETE FROM @extschema@.part_config WHERE parent_table = v_row.partition_schemaname||'.'||v_row.partition_tablename; 257 258 v_drop_count := v_drop_count + 1; 259 END IF; -- End retention check IF 260 261END LOOP; -- End child table loop 262 263IF v_jobmon_schema IS NOT NULL THEN 264 IF v_job_id IS NOT NULL THEN 265 v_step_id := add_step(v_job_id, 'Finished partition drop maintenance'); 266 PERFORM update_step(v_step_id, 'OK', format('%s partitions dropped.', v_drop_count)); 267 PERFORM close_job(v_job_id); 268 END IF; 269END IF; 270 271EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false'); 272 273RETURN v_drop_count; 274 275EXCEPTION 276 WHEN OTHERS THEN 277 GET STACKED DIAGNOSTICS ex_message = MESSAGE_TEXT, 278 ex_context = PG_EXCEPTION_CONTEXT, 279 ex_detail = PG_EXCEPTION_DETAIL, 280 ex_hint = PG_EXCEPTION_HINT; 281 IF v_jobmon_schema IS NOT NULL THEN 282 IF v_job_id IS NULL THEN 283 EXECUTE format('SELECT %I.add_job(''PARTMAN DROP TIME PARTITION: %s'')', v_jobmon_schema, p_parent_table) INTO v_job_id; 284 EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before job logging started'')', v_jobmon_schema, v_job_id, p_parent_table) INTO v_step_id; 285 ELSIF v_step_id IS NULL THEN 286 EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before first step logged'')', v_jobmon_schema, v_job_id) INTO v_step_id; 287 END IF; 288 EXECUTE format('SELECT %I.update_step(%s, ''CRITICAL'', %L)', v_jobmon_schema, v_step_id, 'ERROR: '||coalesce(SQLERRM,'unknown')); 289 EXECUTE format('SELECT %I.fail_job(%s)', v_jobmon_schema, v_job_id); 290 END IF; 291 RAISE EXCEPTION '% 292CONTEXT: % 293DETAIL: % 294HINT: %', ex_message, ex_context, ex_detail, ex_hint; 295END 296$$; 297 298 299