1-- Bug fix: Make child table lookups more intelligent to be able to deal with schemas being in the current search_path. Functions this affects are: drop_time_partition(), drop_id_partition(), reapply_privileges(), undo_partition(), undo_partition_id(), undo_partition_time(). Before table names may not have matched properly when looping through all tables to drop or reset privileges. Thanks to https://github.com/terrorobe for reporting this issue. 2-- Bug fix: reapply_privileges() had unconditional calls to pg_jobmon functions and would fail if it wasn't installed. 3-- Added new parameter to drop partition functions to manually set an interval you'd like to drop. Makes it easier to cleanup a bunch of old partitions you don't need anymore without having to go through the whole retention policy setup if that's not needed. 4-- Renamed drop_time_partition() to drop_partition_time() and drop_id_partition() to drop_partition_id() to be more consistent with the other function names. Please check function ownership & privileges before and after update to ensure they are reset properly. 5 6DROP FUNCTION @extschema@.drop_time_partition(text, boolean, boolean); 7DROP FUNCTION @extschema@.drop_id_partition(text, boolean, boolean); 8 9/* 10 * Function to drop child tables from a time-based partition set. Options to drop indexes or actually drop the table from the database. 11 */ 12CREATE FUNCTION drop_partition_time(p_parent_table text, p_retention interval DEFAULT NULL, p_keep_table boolean DEFAULT NULL, p_keep_index boolean DEFAULT NULL) RETURNS int 13 LANGUAGE plpgsql SECURITY DEFINER 14 AS $$ 15DECLARE 16 17v_adv_lock boolean; 18v_child_table text; 19v_datetime_string text; 20v_drop_count int := 0; 21v_index record; 22v_job_id bigint; 23v_jobmon_schema text; 24v_old_search_path text; 25v_part_interval interval; 26v_partition_timestamp timestamp; 27v_quarter text; 28v_retention interval; 29v_retention_keep_index boolean; 30v_retention_keep_table boolean; 31v_step_id bigint; 32v_year text; 33 34BEGIN 35 36v_adv_lock := pg_try_advisory_lock(hashtext('pg_partman drop_partition_time')); 37IF v_adv_lock = 'false' THEN 38 RAISE NOTICE 'drop_partition_time already running.'; 39 RETURN 0; 40END IF; 41 42SELECT nspname INTO v_jobmon_schema FROM pg_namespace n, pg_extension e WHERE e.extname = 'pg_jobmon' AND e.extnamespace = n.oid; 43IF v_jobmon_schema IS NOT NULL THEN 44 SELECT current_setting('search_path') INTO v_old_search_path; 45 EXECUTE 'SELECT set_config(''search_path'',''@extschema@,'||v_jobmon_schema||''',''false'')'; 46END IF; 47 48-- Allow override of configuration options 49IF p_retention IS NULL THEN 50 SELECT 51 part_interval::interval 52 , retention::interval 53 , retention_keep_table 54 , retention_keep_index 55 , datetime_string 56 INTO 57 v_part_interval 58 , v_retention 59 , v_retention_keep_table 60 , v_retention_keep_index 61 , v_datetime_string 62 FROM @extschema@.part_config 63 WHERE parent_table = p_parent_table 64 AND (type = 'time-static' OR type = 'time-dynamic') 65 AND retention IS NOT NULL; 66 67 IF v_part_interval IS NULL THEN 68 RAISE EXCEPTION 'Configuration for given parent table with a retention period not found: %', p_parent_table; 69 END IF; 70ELSE 71 SELECT 72 part_interval::interval 73 , retention_keep_table 74 , retention_keep_index 75 , datetime_string 76 INTO 77 v_part_interval 78 , v_retention_keep_table 79 , v_retention_keep_index 80 , v_datetime_string 81 FROM @extschema@.part_config 82 WHERE parent_table = p_parent_table 83 AND (type = 'time-static' OR type = 'time-dynamic'); 84 v_retention := p_retention; 85 86 IF v_part_interval IS NULL THEN 87 RAISE EXCEPTION 'Configuration for given parent table not found: %', p_parent_table; 88 END IF; 89END IF; 90 91IF p_keep_table IS NOT NULL THEN 92 v_retention_keep_table = p_keep_table; 93END IF; 94IF p_keep_index IS NOT NULL THEN 95 v_retention_keep_index = p_keep_index; 96END IF; 97 98IF v_jobmon_schema IS NOT NULL THEN 99 v_job_id := add_job('PARTMAN DROP TIME PARTITION: '|| p_parent_table); 100END IF; 101 102-- Loop through child tables of the given parent 103FOR v_child_table IN 104 SELECT n.nspname||'.'||c.relname FROM pg_inherits i join pg_class c ON i.inhrelid = c.oid join pg_namespace n ON c.relnamespace = n.oid WHERE i.inhparent::regclass = p_parent_table::regclass ORDER BY i.inhrelid ASC 105LOOP 106 -- pull out datetime portion of last partition's tablename to make the next one 107 IF v_part_interval != '3 months' THEN 108 v_partition_timestamp := to_timestamp(substring(v_child_table from char_length(p_parent_table||'_p')+1), v_datetime_string); 109 ELSE 110 -- to_timestamp doesn't recognize 'Q' date string formater. Handle it 111 v_year := split_part(substring(v_child_table from char_length(p_parent_table||'_p')+1), 'q', 1); 112 v_quarter := split_part(substring(v_child_table from char_length(p_parent_table||'_p')+1), 'q', 2); 113 CASE 114 WHEN v_quarter = '1' THEN 115 v_partition_timestamp := to_timestamp(v_year || '-01-01', 'YYYY-MM-DD'); 116 WHEN v_quarter = '2' THEN 117 v_partition_timestamp := to_timestamp(v_year || '-04-01', 'YYYY-MM-DD'); 118 WHEN v_quarter = '3' THEN 119 v_partition_timestamp := to_timestamp(v_year || '-07-01', 'YYYY-MM-DD'); 120 WHEN v_quarter = '4' THEN 121 v_partition_timestamp := to_timestamp(v_year || '-10-01', 'YYYY-MM-DD'); 122 END CASE; 123 END IF; 124 125 -- Add one interval since partition names contain the start of the constraint period 126 IF v_retention < (CURRENT_TIMESTAMP - (v_partition_timestamp + v_part_interval)) THEN 127 IF v_jobmon_schema IS NOT NULL THEN 128 v_step_id := add_step(v_job_id, 'Uninherit table '||v_child_table||' from '||p_parent_table); 129 END IF; 130 EXECUTE 'ALTER TABLE '||v_child_table||' NO INHERIT ' || p_parent_table; 131 IF v_jobmon_schema IS NOT NULL THEN 132 PERFORM update_step(v_step_id, 'OK', 'Done'); 133 END IF; 134 IF v_retention_keep_table = false THEN 135 IF v_jobmon_schema IS NOT NULL THEN 136 v_step_id := add_step(v_job_id, 'Drop table '||v_child_table); 137 END IF; 138 EXECUTE 'DROP TABLE '||v_child_table; 139 IF v_jobmon_schema IS NOT NULL THEN 140 PERFORM update_step(v_step_id, 'OK', 'Done'); 141 END IF; 142 ELSIF v_retention_keep_index = false THEN 143 FOR v_index IN 144 SELECT i.indexrelid::regclass AS name 145 , c.conname 146 FROM pg_catalog.pg_index i 147 LEFT JOIN pg_catalog.pg_constraint c ON i.indexrelid = c.conindid 148 WHERE i.indrelid = v_child_table::regclass 149 LOOP 150 IF v_jobmon_schema IS NOT NULL THEN 151 v_step_id := add_step(v_job_id, 'Drop index '||v_index.name||' from '||v_child_table); 152 END IF; 153 IF v_index.conname IS NOT NULL THEN 154 EXECUTE 'ALTER TABLE '||v_child_table||' DROP CONSTRAINT '||v_index.conname; 155 ELSE 156 EXECUTE 'DROP INDEX '||v_index.name; 157 END IF; 158 IF v_jobmon_schema IS NOT NULL THEN 159 PERFORM update_step(v_step_id, 'OK', 'Done'); 160 END IF; 161 END LOOP; 162 END IF; 163 v_drop_count := v_drop_count + 1; 164 END IF; -- End retention check IF 165 166END LOOP; -- End child table loop 167 168IF v_jobmon_schema IS NOT NULL THEN 169 v_step_id := add_step(v_job_id, 'Finished partition drop maintenance'); 170 PERFORM update_step(v_step_id, 'OK', v_drop_count||' partitions dropped.'); 171 PERFORM close_job(v_job_id); 172 EXECUTE 'SELECT set_config(''search_path'','''||v_old_search_path||''',''false'')'; 173END IF; 174 175PERFORM pg_advisory_unlock(hashtext('pg_partman drop_partition_time')); 176 177RETURN v_drop_count; 178 179EXCEPTION 180 WHEN QUERY_CANCELED THEN 181 PERFORM pg_advisory_unlock(hashtext('pg_partman drop_partition_time')); 182 RAISE EXCEPTION '%', SQLERRM; 183 WHEN OTHERS THEN 184 IF v_jobmon_schema IS NOT NULL THEN 185 EXECUTE 'SELECT set_config(''search_path'',''@extschema@,'||v_jobmon_schema||''',''false'')'; 186 IF v_job_id IS NULL THEN 187 v_job_id := add_job('PARTMAN DROP TIME PARTITION'); 188 v_step_id := add_step(v_job_id, 'EXCEPTION before job logging started'); 189 END IF; 190 IF v_step_id IS NULL THEN 191 v_step_id := add_step(v_job_id, 'EXCEPTION before first step logged'); 192 END IF; 193 PERFORM update_step(v_step_id, 'CRITICAL', 'ERROR: '||coalesce(SQLERRM,'unknown')); 194 PERFORM fail_job(v_job_id); 195 EXECUTE 'SELECT set_config(''search_path'','''||v_old_search_path||''',''false'')'; 196 END IF; 197 PERFORM pg_advisory_unlock(hashtext('pg_partman drop_partition_time')); 198 RAISE EXCEPTION '%', SQLERRM; 199END 200$$; 201 202 203/* 204 * Function to drop child tables from a time-based partition set. Options to drop indexes or actually drop the table from the database. 205 */ 206CREATE FUNCTION drop_partition_id(p_parent_table text, p_retention bigint DEFAULT NULL, p_keep_table boolean DEFAULT NULL, p_keep_index boolean DEFAULT NULL) RETURNS int 207 LANGUAGE plpgsql SECURITY DEFINER 208 AS $$ 209DECLARE 210 211v_adv_lock boolean; 212v_child_table text; 213v_control text; 214v_drop_count int := 0; 215v_index record; 216v_job_id bigint; 217v_jobmon_schema text; 218v_max bigint; 219v_old_search_path text; 220v_part_interval bigint; 221v_partition_id bigint; 222v_retention bigint; 223v_retention_keep_index boolean; 224v_retention_keep_table boolean; 225v_step_id bigint; 226 227BEGIN 228 229v_adv_lock := pg_try_advisory_lock(hashtext('pg_partman drop_partition_id')); 230IF v_adv_lock = 'false' THEN 231 RAISE NOTICE 'drop_partition_id already running.'; 232 RETURN 0; 233END IF; 234 235SELECT nspname INTO v_jobmon_schema FROM pg_namespace n, pg_extension e WHERE e.extname = 'pg_jobmon' AND e.extnamespace = n.oid; 236IF v_jobmon_schema IS NOT NULL THEN 237 SELECT current_setting('search_path') INTO v_old_search_path; 238 EXECUTE 'SELECT set_config(''search_path'',''@extschema@,'||v_jobmon_schema||''',''false'')'; 239END IF; 240 241 242-- Allow override of configuration options 243IF p_retention IS NULL THEN 244 SELECT 245 part_interval::bigint 246 , control 247 , retention::bigint 248 , retention_keep_table 249 , retention_keep_index 250 INTO 251 v_part_interval 252 , v_control 253 , v_retention 254 , v_retention_keep_table 255 , v_retention_keep_index 256 FROM @extschema@.part_config 257 WHERE parent_table = p_parent_table 258 AND (type = 'id-static' OR type = 'id-dynamic') 259 AND retention IS NOT NULL; 260 261 IF v_part_interval IS NULL THEN 262 RAISE EXCEPTION 'Configuration for given parent table with a retention period not found: %', p_parent_table; 263 END IF; 264ELSE 265 SELECT 266 part_interval::bigint 267 , control 268 , retention_keep_table 269 , retention_keep_index 270 INTO 271 v_part_interval 272 , v_control 273 , v_retention_keep_table 274 , v_retention_keep_index 275 FROM @extschema@.part_config 276 WHERE parent_table = p_parent_table 277 AND (type = 'id-static' OR type = 'id-dynamic'); 278 v_retention := p_retention; 279 280 IF v_part_interval IS NULL THEN 281 RAISE EXCEPTION 'Configuration for given parent table not found: %', p_parent_table; 282 END IF; 283END IF; 284 285IF p_keep_table IS NOT NULL THEN 286 v_retention_keep_table = p_keep_table; 287END IF; 288IF p_keep_index IS NOT NULL THEN 289 v_retention_keep_index = p_keep_index; 290END IF; 291 292IF v_jobmon_schema IS NOT NULL THEN 293 v_job_id := add_job('PARTMAN DROP ID PARTITION: '|| p_parent_table); 294END IF; 295 296EXECUTE 'SELECT max('||v_control||') FROM '||p_parent_table INTO v_max; 297 298-- Loop through child tables of the given parent 299FOR v_child_table IN 300 SELECT n.nspname||'.'||c.relname FROM pg_inherits i join pg_class c ON i.inhrelid = c.oid join pg_namespace n ON c.relnamespace = n.oid WHERE i.inhparent::regclass = p_parent_table::regclass ORDER BY i.inhrelid ASC 301LOOP 302 v_partition_id := substring(v_child_table from char_length(p_parent_table||'_p')+1)::bigint; 303 304 -- Add one interval since partition names contain the start of the constraint period 305 IF v_retention <= (v_max - (v_partition_id + v_part_interval)) THEN 306 IF v_jobmon_schema IS NOT NULL THEN 307 v_step_id := add_step(v_job_id, 'Uninherit table '||v_child_table||' from '||p_parent_table); 308 END IF; 309 EXECUTE 'ALTER TABLE '||v_child_table||' NO INHERIT ' || p_parent_table; 310 IF v_jobmon_schema IS NOT NULL THEN 311 PERFORM update_step(v_step_id, 'OK', 'Done'); 312 END IF; 313 IF v_retention_keep_table = false THEN 314 IF v_jobmon_schema IS NOT NULL THEN 315 v_step_id := add_step(v_job_id, 'Drop table '||v_child_table); 316 END IF; 317 EXECUTE 'DROP TABLE '||v_child_table; 318 IF v_jobmon_schema IS NOT NULL THEN 319 PERFORM update_step(v_step_id, 'OK', 'Done'); 320 END IF; 321 ELSIF v_retention_keep_index = false THEN 322 FOR v_index IN 323 SELECT i.indexrelid::regclass AS name 324 , c.conname 325 FROM pg_catalog.pg_index i 326 LEFT JOIN pg_catalog.pg_constraint c ON i.indexrelid = c.conindid 327 WHERE i.indrelid = v_child_table::regclass 328 LOOP 329 IF v_jobmon_schema IS NOT NULL THEN 330 v_step_id := add_step(v_job_id, 'Drop index '||v_index.name||' from '||v_child_table); 331 END IF; 332 IF v_index.conname IS NOT NULL THEN 333 EXECUTE 'ALTER TABLE '||v_child_table||' DROP CONSTRAINT '||v_index.conname; 334 ELSE 335 EXECUTE 'DROP INDEX '||v_index.name; 336 END IF; 337 IF v_jobmon_schema IS NOT NULL THEN 338 PERFORM update_step(v_step_id, 'OK', 'Done'); 339 END IF; 340 END LOOP; 341 END IF; 342 v_drop_count := v_drop_count + 1; 343 END IF; -- End retention check IF 344 345END LOOP; -- End child table loop 346 347IF v_jobmon_schema IS NOT NULL THEN 348 v_step_id := add_step(v_job_id, 'Finished partition drop maintenance'); 349 PERFORM update_step(v_step_id, 'OK', v_drop_count||' partitions dropped.'); 350 PERFORM close_job(v_job_id); 351 EXECUTE 'SELECT set_config(''search_path'','''||v_old_search_path||''',''false'')'; 352END IF; 353 354PERFORM pg_advisory_unlock(hashtext('pg_partman drop_partition_id')); 355 356RETURN v_drop_count; 357 358EXCEPTION 359 WHEN QUERY_CANCELED THEN 360 PERFORM pg_advisory_unlock(hashtext('pg_partman drop_partition_id')); 361 RAISE EXCEPTION '%', SQLERRM; 362 WHEN OTHERS THEN 363 IF v_jobmon_schema IS NOT NULL THEN 364 EXECUTE 'SELECT set_config(''search_path'',''@extschema@,'||v_jobmon_schema||''',''false'')'; 365 IF v_job_id IS NULL THEN 366 v_job_id := add_job('PARTMAN DROP ID PARTITION'); 367 v_step_id := add_step(v_job_id, 'EXCEPTION before job logging started'); 368 END IF; 369 IF v_step_id IS NULL THEN 370 v_step_id := add_step(v_job_id, 'EXCEPTION before first step logged'); 371 END IF; 372 PERFORM update_step(v_step_id, 'CRITICAL', 'ERROR: '||coalesce(SQLERRM,'unknown')); 373 PERFORM fail_job(v_job_id); 374 EXECUTE 'SELECT set_config(''search_path'','''||v_old_search_path||''',''false'')'; 375 END IF; 376 PERFORM pg_advisory_unlock(hashtext('pg_partman drop_partition_id')); 377 RAISE EXCEPTION '%', SQLERRM; 378END 379$$; 380 381 382/* 383 * Function to re-apply ownership & privileges on all child tables in a partition set using parent table as reference 384 */ 385CREATE OR REPLACE FUNCTION reapply_privileges(p_parent_table text) RETURNS void 386 LANGUAGE plpgsql SECURITY DEFINER 387 AS $$ 388DECLARE 389 390v_all text[] := ARRAY['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER']; 391v_child_owner text; 392v_child_table text; 393v_child_grant record; 394v_count int; 395v_grant text; 396v_grantees text[]; 397v_job_id bigint; 398v_jobmon_schema text; 399v_match boolean; 400v_old_search_path text; 401v_parent_owner text; 402v_owner_sql text; 403v_revoke text[]; 404v_parent_grant record; 405v_sql text; 406v_step_id bigint; 407 408BEGIN 409 410SELECT count(*) INTO v_count FROM @extschema@.part_config WHERE parent_table = p_parent_table; 411IF v_count = 0 THEN 412 RAISE EXCEPTION 'Given table is not managed by this extention: %', p_parent_table; 413END IF; 414 415SELECT nspname INTO v_jobmon_schema FROM pg_namespace n, pg_extension e WHERE e.extname = 'pg_jobmon' AND e.extnamespace = n.oid; 416IF v_jobmon_schema IS NOT NULL THEN 417 SELECT current_setting('search_path') INTO v_old_search_path; 418 EXECUTE 'SELECT set_config(''search_path'',''@extschema@,'||v_jobmon_schema||''',''false'')'; 419END IF; 420 421IF v_jobmon_schema IS NOT NULL THEN 422 v_job_id := add_job('PARTMAN RE-APPLYING PRIVILEGES TO ALL CHILD TABLES OF: '||p_parent_table); 423 v_step_id := add_step(v_job_id, 'Setting new child table privileges'); 424END IF; 425 426SELECT tableowner INTO v_parent_owner FROM pg_tables WHERE schemaname ||'.'|| tablename = p_parent_table; 427 428FOR v_child_table IN 429 SELECT n.nspname||'.'||c.relname FROM pg_inherits i join pg_class c ON i.inhrelid = c.oid join pg_namespace n ON c.relnamespace = n.oid WHERE i.inhparent::regclass = p_parent_table::regclass ORDER BY i.inhrelid ASC 430LOOP 431 IF v_jobmon_schema IS NOT NULL THEN 432 PERFORM update_step(v_step_id, 'PENDING', 'Currently on child partition in ascending order: '||v_child_table); 433 END IF; 434 v_grantees := NULL; 435 FOR v_parent_grant IN 436 SELECT array_agg(DISTINCT privilege_type::text ORDER BY privilege_type::text) AS types, grantee 437 FROM information_schema.table_privileges 438 WHERE table_schema ||'.'|| table_name = p_parent_table 439 GROUP BY grantee 440 LOOP 441 -- Compare parent & child grants. Don't re-apply if it already exists 442 v_match := false; 443 FOR v_child_grant IN 444 SELECT array_agg(DISTINCT privilege_type::text ORDER BY privilege_type::text) AS types, grantee 445 FROM information_schema.table_privileges 446 WHERE table_schema ||'.'|| table_name = v_child_table 447 GROUP BY grantee 448 LOOP 449 IF v_parent_grant.types = v_child_grant.types AND v_parent_grant.grantee = v_child_grant.grantee THEN 450 v_match := true; 451 END IF; 452 END LOOP; 453 454 IF v_match = false THEN 455 EXECUTE 'GRANT '||array_to_string(v_parent_grant.types, ',')||' ON '||v_child_table||' TO '||v_parent_grant.grantee; 456 SELECT array_agg(r) INTO v_revoke FROM (SELECT unnest(v_all) AS r EXCEPT SELECT unnest(v_parent_grant.types)) x; 457 IF v_revoke IS NOT NULL THEN 458 EXECUTE 'REVOKE '||array_to_string(v_revoke, ',')||' ON '||v_child_table||' FROM '||v_parent_grant.grantee||' CASCADE'; 459 END IF; 460 END IF; 461 462 v_grantees := array_append(v_grantees, v_parent_grant.grantee::text); 463 464 END LOOP; 465 466 -- Revoke all privileges from roles that have none on the parent 467 IF v_grantees IS NOT NULL THEN 468 SELECT array_agg(r) INTO v_revoke FROM ( 469 SELECT DISTINCT grantee::text AS r FROM information_schema.table_privileges WHERE table_schema ||'.'|| table_name = v_child_table 470 EXCEPT 471 SELECT unnest(v_grantees)) x; 472 IF v_revoke IS NOT NULL THEN 473 EXECUTE 'REVOKE ALL ON '||v_child_table||' FROM '||array_to_string(v_revoke, ','); 474 END IF; 475 END IF; 476 477 SELECT tableowner INTO v_child_owner FROM pg_tables WHERE schemaname ||'.'|| tablename = v_child_table; 478 IF v_parent_owner <> v_child_owner THEN 479 EXECUTE 'ALTER TABLE '||v_child_table||' OWNER TO '||v_parent_owner; 480 END IF; 481 482END LOOP; 483 484IF v_jobmon_schema IS NOT NULL THEN 485 PERFORM update_step(v_step_id, 'OK', 'Done'); 486 PERFORM close_job(v_job_id); 487END IF; 488 489IF v_jobmon_schema IS NOT NULL THEN 490 EXECUTE 'SELECT set_config(''search_path'','''||v_old_search_path||''',''false'')'; 491END IF; 492 493EXCEPTION 494 WHEN OTHERS THEN 495 IF v_jobmon_schema IS NOT NULL THEN 496 EXECUTE 'SELECT set_config(''search_path'',''@extschema@,'||v_jobmon_schema||''',''false'')'; 497 IF v_job_id IS NULL THEN 498 v_job_id := add_job('PARTMAN CREATE TABLE: '||p_parent_table); 499 v_step_id := add_step(v_job_id, 'Partition maintenance for table '||p_parent_table||' failed'); 500 ELSIF v_step_id IS NULL THEN 501 v_step_id := add_step(v_job_id, 'EXCEPTION before first step logged'); 502 END IF; 503 PERFORM update_step(v_step_id, 'BAD', 'ERROR: '||coalesce(SQLERRM,'unknown')); 504 PERFORM fail_job(v_job_id); 505 EXECUTE 'SELECT set_config(''search_path'','''||v_old_search_path||''',''false'')'; 506 END IF; 507 RAISE EXCEPTION '%', SQLERRM; 508END 509$$; 510 511 512/* 513 * Function to undo partitioning. 514 * Will actually work on any parent/child table set, not just ones created by pg_partman. 515 */ 516CREATE OR REPLACE FUNCTION undo_partition(p_parent_table text, p_batch_count int DEFAULT 1, p_keep_table boolean DEFAULT true) RETURNS bigint 517 LANGUAGE plpgsql SECURITY DEFINER 518 AS $$ 519DECLARE 520 521v_adv_lock boolean; 522v_batch_loop_count bigint := 0; 523v_child_count bigint; 524v_child_table text; 525v_copy_sql text; 526v_job_id bigint; 527v_jobmon_schema text; 528v_old_search_path text; 529v_part_interval interval; 530v_rowcount bigint; 531v_step_id bigint; 532v_tablename text; 533v_total bigint := 0; 534v_undo_count int := 0; 535 536BEGIN 537 538v_adv_lock := pg_try_advisory_lock(hashtext('pg_partman undo_partition')); 539IF v_adv_lock = 'false' THEN 540 RAISE NOTICE 'undo_partition already running.'; 541 RETURN 0; 542END IF; 543 544SELECT nspname INTO v_jobmon_schema FROM pg_namespace n, pg_extension e WHERE e.extname = 'pg_jobmon' AND e.extnamespace = n.oid; 545IF v_jobmon_schema IS NOT NULL THEN 546 SELECT current_setting('search_path') INTO v_old_search_path; 547 EXECUTE 'SELECT set_config(''search_path'',''@extschema@,'||v_jobmon_schema||''',''false'')'; 548END IF; 549 550IF v_jobmon_schema IS NOT NULL THEN 551 v_job_id := add_job('PARTMAN UNDO PARTITIONING: '||p_parent_table); 552 v_step_id := add_step(v_job_id, 'Undoing partitioning for table '||p_parent_table); 553END IF; 554 555-- Stops new time partitions from being made as well as stopping child tables from being dropped if they were configured with a retention period. 556UPDATE @extschema@.part_config SET undo_in_progress = true WHERE parent_table = p_parent_table; 557-- Stop data going into child tables and stop new id partitions from being made. 558v_tablename := substring(p_parent_table from position('.' in p_parent_table)+1); 559EXECUTE 'DROP TRIGGER IF EXISTS '||v_tablename||'_part_trig ON '||p_parent_table; 560EXECUTE 'DROP FUNCTION IF EXISTS '||p_parent_table||'_part_trig_func()'; 561 562IF v_jobmon_schema IS NOT NULL THEN 563 PERFORM update_step(v_step_id, 'OK', 'Stopped partition creation process. Removed trigger & trigger function'); 564END IF; 565 566WHILE v_batch_loop_count < p_batch_count LOOP 567 SELECT n.nspname||'.'||c.relname INTO v_child_table 568 FROM pg_inherits i 569 JOIN pg_class c ON i.inhrelid = c.oid 570 JOIN pg_namespace n ON c.relnamespace = n.oid 571 WHERE i.inhparent::regclass = p_parent_table::regclass 572 ORDER BY i.inhrelid ASC; 573 574 EXIT WHEN v_child_table IS NULL; 575 576 EXECUTE 'SELECT count(*) FROM '||v_child_table INTO v_child_count; 577 IF v_child_count = 0 THEN 578 -- No rows left in this child table. Remove from partition set. 579 EXECUTE 'ALTER TABLE '||v_child_table||' NO INHERIT ' || p_parent_table; 580 IF p_keep_table = false THEN 581 EXECUTE 'DROP TABLE '||v_child_table; 582 IF v_jobmon_schema IS NOT NULL THEN 583 PERFORM update_step(v_step_id, 'OK', 'Child table DROPPED. Moved '||coalesce(v_rowcount, 0)||' rows to parent'); 584 END IF; 585 ELSE 586 IF v_jobmon_schema IS NOT NULL THEN 587 PERFORM update_step(v_step_id, 'OK', 'Child table UNINHERITED, not DROPPED. Copied '||coalesce(v_rowcount, 0)||' rows to parent'); 588 END IF; 589 END IF; 590 v_undo_count := v_undo_count + 1; 591 CONTINUE; 592 END IF; 593 594 IF v_jobmon_schema IS NOT NULL THEN 595 v_step_id := add_step(v_job_id, 'Removing child partition: '||v_child_table); 596 END IF; 597 598 v_copy_sql := 'INSERT INTO '||p_parent_table||' SELECT * FROM '||v_child_table; 599 EXECUTE v_copy_sql; 600 GET DIAGNOSTICS v_rowcount = ROW_COUNT; 601 v_total := v_total + v_rowcount; 602 603 EXECUTE 'ALTER TABLE '||v_child_table||' NO INHERIT ' || p_parent_table; 604 IF p_keep_table = false THEN 605 EXECUTE 'DROP TABLE '||v_child_table; 606 IF v_jobmon_schema IS NOT NULL THEN 607 PERFORM update_step(v_step_id, 'OK', 'Child table DROPPED. Moved '||v_rowcount||' rows to parent'); 608 END IF; 609 ELSE 610 IF v_jobmon_schema IS NOT NULL THEN 611 PERFORM update_step(v_step_id, 'OK', 'Child table UNINHERITED, not DROPPED. Copied '||v_rowcount||' rows to parent'); 612 END IF; 613 END IF; 614 v_batch_loop_count := v_batch_loop_count + 1; 615 v_undo_count := v_undo_count + 1; 616END LOOP; 617 618IF v_undo_count = 0 THEN 619 -- FOR loop never ran, so there's no child tables left. 620 DELETE FROM @extschema@.part_config WHERE parent_table = p_parent_table; 621 IF v_jobmon_schema IS NOT NULL THEN 622 v_step_id := add_step(v_job_id, 'Removing config from pg_partman (if it existed)'); 623 PERFORM update_step(v_step_id, 'OK', 'Done'); 624 END IF; 625END IF; 626 627RAISE NOTICE 'Copied % row(s) from % child table(s) to the parent: %', v_total, v_undo_count, p_parent_table; 628IF v_jobmon_schema IS NOT NULL THEN 629 v_step_id := add_step(v_job_id, 'Final stats'); 630 PERFORM update_step(v_step_id, 'OK', 'Copied '||v_total||' row(s) from '||v_undo_count||' child table(s) to the parent'); 631END IF; 632 633IF v_jobmon_schema IS NOT NULL THEN 634 PERFORM close_job(v_job_id); 635 EXECUTE 'SELECT set_config(''search_path'','''||v_old_search_path||''',''false'')'; 636END IF; 637 638PERFORM pg_advisory_unlock(hashtext('pg_partman undo_partition')); 639 640RETURN v_total; 641 642EXCEPTION 643 WHEN OTHERS THEN 644 IF v_jobmon_schema IS NOT NULL THEN 645 EXECUTE 'SELECT set_config(''search_path'',''@extschema@,'||v_jobmon_schema||''',''false'')'; 646 IF v_job_id IS NULL THEN 647 v_job_id := add_job('PARTMAN UNDO PARTITIONING: '||p_parent_table); 648 v_step_id := add_step(v_job_id, 'Partition function maintenance for table '||p_parent_table||' failed'); 649 ELSIF v_step_id IS NULL THEN 650 v_step_id := add_step(v_job_id, 'EXCEPTION before first step logged'); 651 END IF; 652 PERFORM update_step(v_step_id, 'CRITICAL', 'ERROR: '||coalesce(SQLERRM,'unknown')); 653 PERFORM fail_job(v_job_id); 654 EXECUTE 'SELECT set_config(''search_path'','''||v_old_search_path||''',''false'')'; 655 END IF; 656 RAISE EXCEPTION '%', SQLERRM; 657END 658$$; 659 660 661/* 662 * Function to undo id-based partitioning created by this extension 663 */ 664CREATE OR REPLACE FUNCTION undo_partition_id(p_parent_table text, p_batch_count int DEFAULT 1, p_batch_interval bigint DEFAULT NULL, p_keep_table boolean DEFAULT true) RETURNS bigint 665 LANGUAGE plpgsql SECURITY DEFINER 666 AS $$ 667DECLARE 668 669v_adv_lock boolean; 670v_batch_loop_count int := 0; 671v_child_loop_total bigint := 0; 672v_child_min bigint; 673v_child_table text; 674v_control text; 675v_inner_loop_count int; 676v_job_id bigint; 677v_jobmon_schema text; 678v_move_sql text; 679v_old_search_path text; 680v_part_interval bigint; 681v_row record; 682v_rowcount bigint; 683v_step_id bigint; 684v_tablename text; 685v_total bigint := 0; 686v_undo_count int := 0; 687 688BEGIN 689 690v_adv_lock := pg_try_advisory_lock(hashtext('pg_partman undo_id_partition')); 691IF v_adv_lock = 'false' THEN 692 RAISE NOTICE 'undo_id_partition already running.'; 693 RETURN 0; 694END IF; 695 696SELECT part_interval::bigint 697 , control 698INTO v_part_interval 699 , v_control 700FROM @extschema@.part_config 701WHERE parent_table = p_parent_table 702AND (type = 'id-static' OR type = 'id-dynamic'); 703 704IF v_part_interval IS NULL THEN 705 RAISE EXCEPTION 'Configuration for given parent table not found: %', p_parent_table; 706END IF; 707 708SELECT nspname INTO v_jobmon_schema FROM pg_namespace n, pg_extension e WHERE e.extname = 'pg_jobmon' AND e.extnamespace = n.oid; 709IF v_jobmon_schema IS NOT NULL THEN 710 SELECT current_setting('search_path') INTO v_old_search_path; 711 EXECUTE 'SELECT set_config(''search_path'',''@extschema@,'||v_jobmon_schema||''',''false'')'; 712END IF; 713 714IF v_jobmon_schema IS NOT NULL THEN 715 v_job_id := add_job('PARTMAN UNDO PARTITIONING: '||p_parent_table); 716 v_step_id := add_step(v_job_id, 'Undoing partitioning for table '||p_parent_table); 717END IF; 718 719IF p_batch_interval IS NULL THEN 720 p_batch_interval := v_part_interval; 721END IF; 722 723-- Stops new time partitions from being made as well as stopping child tables from being dropped if they were configured with a retention period. 724UPDATE @extschema@.part_config SET undo_in_progress = true WHERE parent_table = p_parent_table; 725-- Stop data going into child tables and stop new id partitions from being made. 726v_tablename := substring(p_parent_table from position('.' in p_parent_table)+1); 727EXECUTE 'DROP TRIGGER IF EXISTS '||v_tablename||'_part_trig ON '||p_parent_table; 728EXECUTE 'DROP FUNCTION IF EXISTS '||p_parent_table||'_part_trig_func()'; 729 730IF v_jobmon_schema IS NOT NULL THEN 731 PERFORM update_step(v_step_id, 'OK', 'Stopped partition creation process. Removed trigger & trigger function'); 732END IF; 733 734<<outer_child_loop>> 735WHILE v_batch_loop_count < p_batch_count LOOP 736 SELECT n.nspname||'.'||c.relname INTO v_child_table 737 FROM pg_inherits i 738 JOIN pg_class c ON i.inhrelid = c.oid 739 JOIN pg_namespace n ON c.relnamespace = n.oid 740 WHERE i.inhparent::regclass = p_parent_table::regclass 741 ORDER BY i.inhrelid ASC; 742 743 EXIT WHEN v_child_table IS NULL; 744 745 IF v_jobmon_schema IS NOT NULL THEN 746 v_step_id := add_step(v_job_id, 'Removing child partition: '||v_child_table); 747 END IF; 748 749 EXECUTE 'SELECT min('||v_control||') FROM '||v_child_table INTO v_child_min; 750 IF v_child_min IS NULL THEN 751 -- No rows left in this child table. Remove from partition set. 752 EXECUTE 'ALTER TABLE '||v_child_table||' NO INHERIT ' || p_parent_table; 753 IF p_keep_table = false THEN 754 EXECUTE 'DROP TABLE '||v_child_table; 755 IF v_jobmon_schema IS NOT NULL THEN 756 PERFORM update_step(v_step_id, 'OK', 'Child table DROPPED. Moved '||v_child_loop_total||' rows to parent'); 757 END IF; 758 ELSE 759 IF v_jobmon_schema IS NOT NULL THEN 760 PERFORM update_step(v_step_id, 'OK', 'Child table UNINHERITED, not DROPPED. Moved '||v_child_loop_total||' rows to parent'); 761 END IF; 762 END IF; 763 v_undo_count := v_undo_count + 1; 764 CONTINUE outer_child_loop; 765 END IF; 766 v_inner_loop_count := 1; 767 v_child_loop_total := 0; 768 <<inner_child_loop>> 769 LOOP 770 -- Get everything from the current child minimum up to the multiples of the given interval 771 v_move_sql := 'WITH move_data AS (DELETE FROM '||v_child_table|| 772 ' WHERE '||v_control||' <= '||quote_literal(v_child_min + (p_batch_interval * v_inner_loop_count))||' RETURNING *) 773 INSERT INTO '||p_parent_table||' SELECT * FROM move_data'; 774 EXECUTE v_move_sql; 775 GET DIAGNOSTICS v_rowcount = ROW_COUNT; 776 v_total := v_total + v_rowcount; 777 v_child_loop_total := v_child_loop_total + v_rowcount; 778 IF v_jobmon_schema IS NOT NULL THEN 779 PERFORM update_step(v_step_id, 'OK', 'Moved '||v_child_loop_total||' rows to parent.'); 780 END IF; 781 EXIT inner_child_loop WHEN v_rowcount = 0; -- exit before loop incr if table is empty 782 v_inner_loop_count := v_inner_loop_count + 1; 783 v_batch_loop_count := v_batch_loop_count + 1; 784 EXIT outer_child_loop WHEN v_batch_loop_count >= p_batch_count; -- Exit outer FOR loop if p_batch_count is reached 785 END LOOP inner_child_loop; 786END LOOP outer_child_loop; 787 788IF v_batch_loop_count < p_batch_count THEN 789 -- FOR loop never ran, so there's no child tables left. 790 DELETE FROM @extschema@.part_config WHERE parent_table = p_parent_table; 791 IF v_jobmon_schema IS NOT NULL THEN 792 v_step_id := add_step(v_job_id, 'Removing config from pg_partman'); 793 PERFORM update_step(v_step_id, 'OK', 'Done'); 794 END IF; 795END IF; 796 797RAISE NOTICE 'Copied % row(s) to the parent. Removed % partitions.', v_total, v_undo_count; 798IF v_jobmon_schema IS NOT NULL THEN 799 v_step_id := add_step(v_job_id, 'Final stats'); 800 PERFORM update_step(v_step_id, 'OK', 'Copied '||v_total||' row(s) to the parent. Removed '||v_undo_count||' partitions.'); 801END IF; 802 803IF v_jobmon_schema IS NOT NULL THEN 804 PERFORM close_job(v_job_id); 805 EXECUTE 'SELECT set_config(''search_path'','''||v_old_search_path||''',''false'')'; 806END IF; 807 808PERFORM pg_advisory_unlock(hashtext('pg_partman undo_id_partition')); 809 810RETURN v_total; 811 812EXCEPTION 813 WHEN OTHERS THEN 814 IF v_jobmon_schema IS NOT NULL THEN 815 EXECUTE 'SELECT set_config(''search_path'',''@extschema@,'||v_jobmon_schema||''',''false'')'; 816 IF v_job_id IS NULL THEN 817 v_job_id := add_job('PARTMAN UNDO PARTITIONING: '||p_parent_table); 818 v_step_id := add_step(v_job_id, 'Partition function maintenance for table '||p_parent_table||' failed'); 819 ELSIF v_step_id IS NULL THEN 820 v_step_id := add_step(v_job_id, 'EXCEPTION before first step logged'); 821 END IF; 822 PERFORM update_step(v_step_id, 'CRITICAL', 'ERROR: '||coalesce(SQLERRM,'unknown')); 823 PERFORM fail_job(v_job_id); 824 EXECUTE 'SELECT set_config(''search_path'','''||v_old_search_path||''',''false'')'; 825 END IF; 826 RAISE EXCEPTION '%', SQLERRM; 827END 828$$; 829 830 831/* 832 * Function to undo time-based partitioning created by this extension 833 */ 834CREATE OR REPLACE FUNCTION undo_partition_time(p_parent_table text, p_batch_count int DEFAULT 1, p_batch_interval interval DEFAULT NULL, p_keep_table boolean DEFAULT true) RETURNS bigint 835 LANGUAGE plpgsql SECURITY DEFINER 836 AS $$ 837DECLARE 838 839v_adv_lock boolean; 840v_batch_loop_count int := 0; 841v_child_min timestamptz; 842v_child_loop_total bigint := 0; 843v_child_table text; 844v_control text; 845v_inner_loop_count int; 846v_job_id bigint; 847v_jobmon_schema text; 848v_move_sql text; 849v_old_search_path text; 850v_part_interval interval; 851v_row record; 852v_rowcount bigint; 853v_step_id bigint; 854v_tablename text; 855v_total bigint := 0; 856v_undo_count int := 0; 857 858BEGIN 859 860v_adv_lock := pg_try_advisory_lock(hashtext('pg_partman undo_time_partition')); 861IF v_adv_lock = 'false' THEN 862 RAISE NOTICE 'undo_time_partition already running.'; 863 RETURN 0; 864END IF; 865 866SELECT part_interval::interval 867 , control 868INTO v_part_interval 869 , v_control 870FROM @extschema@.part_config 871WHERE parent_table = p_parent_table 872AND (type = 'time-static' OR type = 'time-dynamic'); 873 874IF v_part_interval IS NULL THEN 875 RAISE EXCEPTION 'Configuration for given parent table not found: %', p_parent_table; 876END IF; 877 878SELECT nspname INTO v_jobmon_schema FROM pg_namespace n, pg_extension e WHERE e.extname = 'pg_jobmon' AND e.extnamespace = n.oid; 879IF v_jobmon_schema IS NOT NULL THEN 880 SELECT current_setting('search_path') INTO v_old_search_path; 881 EXECUTE 'SELECT set_config(''search_path'',''@extschema@,'||v_jobmon_schema||''',''false'')'; 882END IF; 883 884IF v_jobmon_schema IS NOT NULL THEN 885 v_job_id := add_job('PARTMAN UNDO PARTITIONING: '||p_parent_table); 886 v_step_id := add_step(v_job_id, 'Undoing partitioning for table '||p_parent_table); 887END IF; 888 889IF p_batch_interval IS NULL THEN 890 p_batch_interval := v_part_interval; 891END IF; 892 893-- Stops new time partitions from being made as well as stopping child tables from being dropped if they were configured with a retention period. 894UPDATE @extschema@.part_config SET undo_in_progress = true WHERE parent_table = p_parent_table; 895-- Stop data going into child tables and stop new id partitions from being made. 896v_tablename := substring(p_parent_table from position('.' in p_parent_table)+1); 897EXECUTE 'DROP TRIGGER IF EXISTS '||v_tablename||'_part_trig ON '||p_parent_table; 898EXECUTE 'DROP FUNCTION IF EXISTS '||p_parent_table||'_part_trig_func()'; 899 900IF v_jobmon_schema IS NOT NULL THEN 901 PERFORM update_step(v_step_id, 'OK', 'Stopped partition creation process. Removed trigger & trigger function'); 902END IF; 903 904<<outer_child_loop>> 905WHILE v_batch_loop_count < p_batch_count LOOP 906 SELECT n.nspname||'.'||c.relname INTO v_child_table 907 FROM pg_inherits i 908 JOIN pg_class c ON i.inhrelid = c.oid 909 JOIN pg_namespace n ON c.relnamespace = n.oid 910 WHERE i.inhparent::regclass = p_parent_table::regclass 911 ORDER BY i.inhrelid ASC; 912 913 EXIT WHEN v_child_table IS NULL; 914 915 IF v_jobmon_schema IS NOT NULL THEN 916 v_step_id := add_step(v_job_id, 'Removing child partition: '||v_child_table); 917 END IF; 918 919 EXECUTE 'SELECT min('||v_control||') FROM '||v_child_table INTO v_child_min; 920 IF v_child_min IS NULL THEN 921 -- No rows left in this child table. Remove from partition set. 922 EXECUTE 'ALTER TABLE '||v_child_table||' NO INHERIT ' || p_parent_table; 923 IF p_keep_table = false THEN 924 EXECUTE 'DROP TABLE '||v_child_table; 925 IF v_jobmon_schema IS NOT NULL THEN 926 PERFORM update_step(v_step_id, 'OK', 'Child table DROPPED. Moved '||v_child_loop_total||' rows to parent'); 927 END IF; 928 ELSE 929 IF v_jobmon_schema IS NOT NULL THEN 930 PERFORM update_step(v_step_id, 'OK', 'Child table UNINHERITED, not DROPPED. Moved '||v_child_loop_total||' rows to parent'); 931 END IF; 932 END IF; 933 v_undo_count := v_undo_count + 1; 934 CONTINUE outer_child_loop; 935 END IF; 936 v_inner_loop_count := 1; 937 v_child_loop_total := 0; 938 <<inner_child_loop>> 939 LOOP 940 -- Get everything from the current child minimum up to the multiples of the given interval 941 v_move_sql := 'WITH move_data AS (DELETE FROM '||v_child_table|| 942 ' WHERE '||v_control||' <= '||quote_literal(v_child_min + (p_batch_interval * v_inner_loop_count))||' RETURNING *) 943 INSERT INTO '||p_parent_table||' SELECT * FROM move_data'; 944 EXECUTE v_move_sql; 945 GET DIAGNOSTICS v_rowcount = ROW_COUNT; 946 v_total := v_total + v_rowcount; 947 v_child_loop_total := v_child_loop_total + v_rowcount; 948 IF v_jobmon_schema IS NOT NULL THEN 949 PERFORM update_step(v_step_id, 'OK', 'Moved '||v_child_loop_total||' rows to parent.'); 950 END IF; 951 EXIT inner_child_loop WHEN v_rowcount = 0; -- exit before loop incr if table is empty 952 v_inner_loop_count := v_inner_loop_count + 1; 953 v_batch_loop_count := v_batch_loop_count + 1; 954 EXIT outer_child_loop WHEN v_batch_loop_count >= p_batch_count; -- Exit outer FOR loop if p_batch_count is reached 955 END LOOP inner_child_loop; 956END LOOP outer_child_loop; 957 958IF v_batch_loop_count < p_batch_count THEN 959 -- FOR loop never ran, so there's no child tables left. 960 DELETE FROM @extschema@.part_config WHERE parent_table = p_parent_table; 961 IF v_jobmon_schema IS NOT NULL THEN 962 v_step_id := add_step(v_job_id, 'Removing config from pg_partman'); 963 PERFORM update_step(v_step_id, 'OK', 'Done'); 964 END IF; 965END IF; 966 967RAISE NOTICE 'Copied % row(s) to the parent. Removed % partitions.', v_total, v_undo_count; 968IF v_jobmon_schema IS NOT NULL THEN 969 v_step_id := add_step(v_job_id, 'Final stats'); 970 PERFORM update_step(v_step_id, 'OK', 'Copied '||v_total||' row(s) to the parent. Removed '||v_undo_count||' partitions.'); 971END IF; 972 973IF v_jobmon_schema IS NOT NULL THEN 974 PERFORM close_job(v_job_id); 975 EXECUTE 'SELECT set_config(''search_path'','''||v_old_search_path||''',''false'')'; 976END IF; 977 978PERFORM pg_advisory_unlock(hashtext('pg_partman undo_time_partition')); 979 980RETURN v_total; 981 982EXCEPTION 983 WHEN OTHERS THEN 984 IF v_jobmon_schema IS NOT NULL THEN 985 EXECUTE 'SELECT set_config(''search_path'',''@extschema@,'||v_jobmon_schema||''',''false'')'; 986 IF v_job_id IS NULL THEN 987 v_job_id := add_job('PARTMAN UNDO PARTITIONING: '||p_parent_table); 988 v_step_id := add_step(v_job_id, 'Partition function maintenance for table '||p_parent_table||' failed'); 989 ELSIF v_step_id IS NULL THEN 990 v_step_id := add_step(v_job_id, 'EXCEPTION before first step logged'); 991 END IF; 992 PERFORM update_step(v_step_id, 'CRITICAL', 'ERROR: '||coalesce(SQLERRM,'unknown')); 993 PERFORM fail_job(v_job_id); 994 EXECUTE 'SELECT set_config(''search_path'','''||v_old_search_path||''',''false'')'; 995 END IF; 996 RAISE EXCEPTION '%', SQLERRM; 997END 998$$; 999 1000 1001/* 1002 * Function to manage pre-creation of the next partitions in a time-based partition set. 1003 * Also manages dropping old partitions if the retention option is set. 1004 */ 1005CREATE OR REPLACE FUNCTION run_maintenance() RETURNS void 1006 LANGUAGE plpgsql SECURITY DEFINER 1007 AS $$ 1008DECLARE 1009 1010v_adv_lock boolean; 1011v_create_count int := 0; 1012v_current_partition_timestamp timestamp; 1013v_datetime_string text; 1014v_drop_count int := 0; 1015v_job_id bigint; 1016v_jobmon_schema text; 1017v_last_partition_timestamp timestamp; 1018v_old_search_path text; 1019v_premade_count real; 1020v_quarter text; 1021v_step_id bigint; 1022v_row record; 1023v_year text; 1024 1025BEGIN 1026 1027v_adv_lock := pg_try_advisory_lock(hashtext('pg_partman run_maintenance')); 1028IF v_adv_lock = 'false' THEN 1029 RAISE NOTICE 'Partman maintenance already running.'; 1030 RETURN; 1031END IF; 1032 1033SELECT nspname INTO v_jobmon_schema FROM pg_namespace n, pg_extension e WHERE e.extname = 'pg_jobmon' AND e.extnamespace = n.oid; 1034IF v_jobmon_schema IS NOT NULL THEN 1035 SELECT current_setting('search_path') INTO v_old_search_path; 1036 EXECUTE 'SELECT set_config(''search_path'',''@extschema@,'||v_jobmon_schema||''',''false'')'; 1037END IF; 1038 1039IF v_jobmon_schema IS NOT NULL THEN 1040 v_job_id := add_job('PARTMAN RUN MAINTENANCE'); 1041 v_step_id := add_step(v_job_id, 'Running maintenance loop'); 1042END IF; 1043 1044FOR v_row IN 1045SELECT parent_table 1046 , type 1047 , part_interval::interval 1048 , control 1049 , premake 1050 , datetime_string 1051 , last_partition 1052 , undo_in_progress 1053FROM @extschema@.part_config WHERE type = 'time-static' OR type = 'time-dynamic' 1054LOOP 1055 1056 CONTINUE WHEN v_row.undo_in_progress; 1057 1058 CASE 1059 WHEN v_row.part_interval = '15 mins' THEN 1060 v_current_partition_timestamp := date_trunc('hour', CURRENT_TIMESTAMP) + 1061 '15min'::interval * floor(date_part('minute', CURRENT_TIMESTAMP) / 15.0); 1062 WHEN v_row.part_interval = '30 mins' THEN 1063 v_current_partition_timestamp := date_trunc('hour', CURRENT_TIMESTAMP) + 1064 '30min'::interval * floor(date_part('minute', CURRENT_TIMESTAMP) / 30.0); 1065 WHEN v_row.part_interval = '1 hour' THEN 1066 v_current_partition_timestamp := date_trunc('hour', CURRENT_TIMESTAMP); 1067 WHEN v_row.part_interval = '1 day' THEN 1068 v_current_partition_timestamp := date_trunc('day', CURRENT_TIMESTAMP); 1069 WHEN v_row.part_interval = '1 week' THEN 1070 v_current_partition_timestamp := date_trunc('week', CURRENT_TIMESTAMP); 1071 WHEN v_row.part_interval = '1 month' THEN 1072 v_current_partition_timestamp := date_trunc('month', CURRENT_TIMESTAMP); 1073 WHEN v_row.part_interval = '3 months' THEN 1074 v_current_partition_timestamp := date_trunc('quarter', CURRENT_TIMESTAMP); 1075 WHEN v_row.part_interval = '1 year' THEN 1076 v_current_partition_timestamp := date_trunc('year', CURRENT_TIMESTAMP); 1077 END CASE; 1078 1079 IF v_row.part_interval != '3 months' THEN 1080 v_last_partition_timestamp := to_timestamp(substring(v_row.last_partition from char_length(v_row.parent_table||'_p')+1), v_row.datetime_string); 1081 ELSE 1082 -- to_timestamp doesn't recognize 'Q' date string formater. Handle it 1083 v_year := split_part(substring(v_row.last_partition from char_length(v_row.parent_table||'_p')+1), 'q', 1); 1084 v_quarter := split_part(substring(v_row.last_partition from char_length(v_row.parent_table||'_p')+1), 'q', 2); 1085 CASE 1086 WHEN v_quarter = '1' THEN 1087 v_last_partition_timestamp := to_timestamp(v_year || '-01-01', 'YYYY-MM-DD'); 1088 WHEN v_quarter = '2' THEN 1089 v_last_partition_timestamp := to_timestamp(v_year || '-04-01', 'YYYY-MM-DD'); 1090 WHEN v_quarter = '3' THEN 1091 v_last_partition_timestamp := to_timestamp(v_year || '-07-01', 'YYYY-MM-DD'); 1092 WHEN v_quarter = '4' THEN 1093 v_last_partition_timestamp := to_timestamp(v_year || '-10-01', 'YYYY-MM-DD'); 1094 END CASE; 1095 END IF; 1096 1097 -- Check and see how many premade partitions there are. If it's less than premake in config table, make another 1098 v_premade_count = EXTRACT('epoch' FROM age(v_last_partition_timestamp, v_current_partition_timestamp)) / EXTRACT('epoch' FROM v_row.part_interval::interval); 1099 1100 -- Loop premaking until config setting is met. Allows it to catch up if it fell behind or if premake changed. 1101 WHILE v_premade_count < v_row.premake LOOP 1102 EXECUTE 'SELECT @extschema@.create_next_time_partition('||quote_literal(v_row.parent_table)||')'; 1103 v_create_count := v_create_count + 1; 1104 IF v_row.type = 'time-static' THEN 1105 EXECUTE 'SELECT @extschema@.create_time_function('||quote_literal(v_row.parent_table)||')'; 1106 END IF; 1107 v_last_partition_timestamp := v_last_partition_timestamp + v_row.part_interval; 1108 v_premade_count = EXTRACT('epoch' FROM age(v_last_partition_timestamp, v_current_partition_timestamp)) / EXTRACT('epoch' FROM v_row.part_interval::interval); 1109 END LOOP; 1110 1111END LOOP; -- end of creation loop 1112 1113-- Manage dropping old partitions if retention option is set 1114FOR v_row IN 1115 SELECT parent_table FROM @extschema@.part_config WHERE retention IS NOT NULL AND undo_in_progress = false AND (type = 'time-static' OR type = 'time-dynamic') 1116LOOP 1117 v_drop_count := v_drop_count + @extschema@.drop_partition_time(v_row.parent_table); 1118END LOOP; 1119FOR v_row IN 1120 SELECT parent_table FROM @extschema@.part_config WHERE retention IS NOT NULL AND undo_in_progress = false AND (type = 'id-static' OR type = 'id-dynamic') 1121LOOP 1122 v_drop_count := v_drop_count + @extschema@.drop_partition_id(v_row.parent_table); 1123END LOOP; 1124 1125IF v_jobmon_schema IS NOT NULL THEN 1126 PERFORM update_step(v_step_id, 'OK', 'Partition maintenance finished. '||v_create_count||' partitions made. '||v_drop_count||' partitions dropped.'); 1127 PERFORM close_job(v_job_id); 1128 EXECUTE 'SELECT set_config(''search_path'','''||v_old_search_path||''',''false'')'; 1129END IF; 1130 1131PERFORM pg_advisory_unlock(hashtext('pg_partman run_maintenance')); 1132 1133EXCEPTION 1134 WHEN QUERY_CANCELED THEN 1135 PERFORM pg_advisory_unlock(hashtext('pg_partman run_maintenance')); 1136 RAISE EXCEPTION '%', SQLERRM; 1137 WHEN OTHERS THEN 1138 IF v_jobmon_schema IS NOT NULL THEN 1139 EXECUTE 'SELECT set_config(''search_path'',''@extschema@,'||v_jobmon_schema||''',''false'')'; 1140 IF v_job_id IS NULL THEN 1141 v_job_id := add_job('PARTMAN RUN MAINTENANCE'); 1142 v_step_id := add_step(v_job_id, 'EXCEPTION before job logging started'); 1143 END IF; 1144 IF v_step_id IS NULL THEN 1145 v_step_id := add_step(v_job_id, 'EXCEPTION before first step logged'); 1146 END IF; 1147 PERFORM update_step(v_step_id, 'CRITICAL', 'ERROR: '||coalesce(SQLERRM,'unknown')); 1148 PERFORM fail_job(v_job_id); 1149 EXECUTE 'SELECT set_config(''search_path'','''||v_old_search_path||''',''false'')'; 1150 END IF; 1151 PERFORM pg_advisory_unlock(hashtext('pg_partman run_maintenance')); 1152 RAISE EXCEPTION '%', SQLERRM; 1153END 1154$$; 1155