1-- Fixed bug in partition_data_time() that would cause an infinite loop when moving data would require creating a new partition newer than the newest one. This would only occur when using a custom time interval. Infinite loop would also occur when using the partition_data.py python script (Github Issue #83). 2-- Properly handle the special PUBLIC role when granting/revoking privileges on child tables (Github Issue #66). 3-- Fixed python exception in reapply_indexes.py if the parent table has no indexes (Github Issue #86). 4-- Fixed all undo_partition functions to remove the child table in the same batch session when its rowcount reaches zero. Previously an extra batch may have been required to remove all child tables and remove config information after the last row was moved to the parent. 5-- Fixed undo_partition functions to be more strict on the triggers they drop and ensure they only drop the trigger on the target parent table. 6-- Fixed undo_partition() to properly remove entries from the custom_time_partitions config table if necessary. Also greatly simplified code in this function. 7-- Consolidated privilege management into new apply_privileges() internal function. 8-- Improved performance when applying parent privileges to child tables. Most noticable on larger partition sets with many grants. (Github Issue #78) 9-- Added a sort to the Makefile when creating the sql extension file. Allows more predictable output between builds (Github Push Request #16 from Mimeo extension). 10 11/* 12 * Apply privileges that exist on a given parent to the given child table 13 */ 14CREATE FUNCTION apply_privileges(p_parent_schema text, p_parent_tablename text, p_child_schema text, p_child_tablename text, p_job_id bigint DEFAULT NULL) RETURNS void 15 LANGUAGE plpgsql SECURITY DEFINER 16 AS $$ 17DECLARE 18 19ex_context text; 20ex_detail text; 21ex_hint text; 22ex_message text; 23v_all text[] := ARRAY['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER']; 24v_child_grant record; 25v_child_owner text; 26v_grantees text[]; 27v_job_id bigint; 28v_jobmon boolean; 29v_jobmon_schema text; 30v_match boolean; 31v_parent_grant record; 32v_parent_owner text; 33v_revoke text; 34v_row_revoke record; 35v_sql text; 36v_step_id bigint; 37 38BEGIN 39 40SELECT jobmon INTO v_jobmon FROM @extschema@.part_config WHERE parent_table = p_parent_schema ||'.'|| p_parent_tablename; 41IF v_jobmon IS NULL THEN 42 RAISE EXCEPTION 'Given table is not managed by this extention: %.%', p_parent_schema, p_parent_tablename; 43END IF; 44 45SELECT tableowner INTO v_parent_owner FROM pg_catalog.pg_tables WHERE schemaname = p_parent_schema AND tablename = p_parent_tablename; 46SELECT tableowner INTO v_child_owner FROM pg_tables WHERE schemaname = p_child_schema AND tablename = p_child_tablename; 47IF v_parent_owner IS NULL THEN 48 RAISE EXCEPTION 'Given parent table does not exist: %.%', v_parent_schema, v_parent_tablename; 49END IF; 50IF v_child_owner IS NULL THEN 51 RAISE EXCEPTION 'Given child table does not exist: %.%', v_child_schema, v_child_tablename; 52END IF; 53 54IF v_jobmon THEN 55 SELECT nspname INTO v_jobmon_schema FROM pg_namespace n, pg_extension e WHERE e.extname = 'pg_jobmon' AND e.extnamespace = n.oid; 56END IF; 57 58IF v_jobmon_schema IS NOT NULL THEN 59 IF p_job_id IS NULL THEN 60 EXECUTE format('SELECT %I.add_job(%L)', v_jobmon_schema, format('PARTMAN APPLYING PRIVILEGES TO CHILD TABLE: %s.%s', p_child_schema, p_child_tablename)) INTO v_job_id; 61 ELSE 62 v_job_id := p_job_id; 63 END IF; 64 EXECUTE format('SELECT %I.add_step(%L, %L)', v_jobmon_schema, v_job_id, format('Setting new child table privileges for %s.%s', p_child_schema, p_child_tablename)) INTO v_step_id; 65END IF; 66 67IF v_jobmon_schema IS NOT NULL THEN 68 69 EXECUTE format('SELECT %I.update_step(%L, %L, %L)' 70 , v_jobmon_schema 71 , v_step_id 72 , 'PENDING' 73 , format('Applying privileges on child partition: %s.%s' 74 , p_child_schema 75 , p_child_tablename) 76 ); 77END IF; 78 79FOR v_parent_grant IN 80 SELECT array_agg(DISTINCT privilege_type::text ORDER BY privilege_type::text) AS types 81 , grantee 82 FROM information_schema.table_privileges 83 WHERE table_schema = p_parent_schema AND table_name = p_parent_tablename 84 GROUP BY grantee 85LOOP 86 -- Compare parent & child grants. Don't re-apply if it already exists 87 v_match := false; 88 v_sql := NULL; 89 FOR v_child_grant IN 90 SELECT array_agg(DISTINCT privilege_type::text ORDER BY privilege_type::text) AS types 91 , grantee 92 FROM information_schema.table_privileges 93 WHERE table_schema = p_child_schema AND table_name = p_child_tablename 94 GROUP BY grantee 95 LOOP 96 IF v_parent_grant.types = v_child_grant.types AND v_parent_grant.grantee = v_child_grant.grantee THEN 97 v_match := true; 98 END IF; 99 END LOOP; 100 101 IF v_match = false THEN 102 IF v_parent_grant.grantee = 'PUBLIC' THEN 103 v_sql := 'GRANT %s ON %I.%I TO %s'; 104 ELSE 105 v_sql := 'GRANT %s ON %I.%I TO %I'; 106 END IF; 107 EXECUTE format(v_sql 108 , array_to_string(v_parent_grant.types, ',') 109 , p_child_schema 110 , p_child_tablename 111 , v_parent_grant.grantee); 112 v_sql := NULL; 113 SELECT string_agg(r, ',') INTO v_revoke FROM (SELECT unnest(v_all) AS r EXCEPT SELECT unnest(v_parent_grant.types)) x; 114 IF v_revoke IS NOT NULL THEN 115 IF v_parent_grant.grantee = 'PUBLIC' THEN 116 v_sql := 'REVOKE %s ON %I.%I FROM %s CASCADE'; 117 ELSE 118 v_sql := 'REVOKE %s ON %I.%I FROM %I CASCADE'; 119 END IF; 120 EXECUTE format(v_sql 121 , v_revoke 122 , p_child_schema 123 , p_child_tablename 124 , v_parent_grant.grantee); 125 v_sql := NULL; 126 END IF; 127 END IF; 128 129 v_grantees := array_append(v_grantees, v_parent_grant.grantee::text); 130 131END LOOP; 132 133-- Revoke all privileges from roles that have none on the parent 134IF v_grantees IS NOT NULL THEN 135 FOR v_row_revoke IN 136 SELECT role FROM ( 137 SELECT DISTINCT grantee::text AS role FROM information_schema.table_privileges WHERE table_schema = p_child_schema AND table_name = p_child_tablename 138 EXCEPT 139 SELECT unnest(v_grantees)) x 140 LOOP 141 IF v_row_revoke.role IS NOT NULL THEN 142 IF v_row_revoke.role = 'PUBLIC' THEN 143 v_sql := 'REVOKE ALL ON %I.%I FROM %s'; 144 ELSE 145 v_sql := 'REVOKE ALL ON %I.%I FROM %I'; 146 END IF; 147 EXECUTE format(v_sql 148 , p_child_schema 149 , p_child_tablename 150 , v_row_revoke.role); 151 END IF; 152 END LOOP; 153 154END IF; 155 156IF v_parent_owner <> v_child_owner THEN 157 EXECUTE format('ALTER TABLE %I.%I OWNER TO %I' 158 , p_child_schema 159 , p_child_tablename 160 , v_parent_owner); 161END IF; 162 163IF v_jobmon_schema IS NOT NULL THEN 164 EXECUTE format('SELECT %I.update_step(%L, %L, %L)', v_jobmon_schema, v_step_id, 'OK', 'Done'); 165 IF p_job_id IS NULL THEN 166 EXECUTE format('SELECT %I.close_job(%L)', v_jobmon_schema, v_job_id); 167 END IF; 168END IF; 169 170EXCEPTION 171 WHEN OTHERS THEN 172 GET STACKED DIAGNOSTICS ex_message = MESSAGE_TEXT, 173 ex_context = PG_EXCEPTION_CONTEXT, 174 ex_detail = PG_EXCEPTION_DETAIL, 175 ex_hint = PG_EXCEPTION_HINT; 176 IF v_jobmon_schema IS NOT NULL THEN 177 IF v_job_id IS NULL THEN 178 EXECUTE format('SELECT %I.add_job(''PARTMAN RE-APPLYING PRIVILEGES TO ALL CHILD TABLES OF: %s'')', v_jobmon_schema, p_parent_table) INTO v_job_id; 179 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; 180 ELSIF v_step_id IS NULL THEN 181 EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before first step logged'')', v_jobmon_schema, v_job_id) INTO v_step_id; 182 END IF; 183 EXECUTE format('SELECT %I.update_step(%s, ''CRITICAL'', %L)', v_jobmon_schema, v_step_id, 'ERROR: '||coalesce(SQLERRM,'unknown')); 184 EXECUTE format('SELECT %I.fail_job(%s)', v_jobmon_schema, v_job_id); 185 END IF; 186 RAISE EXCEPTION '% 187CONTEXT: % 188DETAIL: % 189HINT: %', ex_message, ex_context, ex_detail, ex_hint; 190END 191$$; 192 193 194/* 195 * Function to re-apply ownership & privileges on all child tables in a partition set using parent table as reference 196 */ 197CREATE OR REPLACE FUNCTION reapply_privileges(p_parent_table text) RETURNS void 198 LANGUAGE plpgsql SECURITY DEFINER 199 AS $$ 200DECLARE 201 202ex_context text; 203ex_detail text; 204ex_hint text; 205ex_message text; 206v_job_id bigint; 207v_jobmon boolean; 208v_jobmon_schema text; 209v_parent_schema text; 210v_parent_tablename text; 211v_row record; 212v_step_id bigint; 213 214BEGIN 215 216SELECT jobmon INTO v_jobmon FROM @extschema@.part_config WHERE parent_table = p_parent_table; 217IF v_jobmon IS NULL THEN 218 RAISE EXCEPTION 'Given table is not managed by this extention: %', p_parent_table; 219END IF; 220 221IF v_jobmon THEN 222 SELECT nspname INTO v_jobmon_schema FROM pg_namespace n, pg_extension e WHERE e.extname = 'pg_jobmon' AND e.extnamespace = n.oid; 223END IF; 224 225SELECT schemaname, tablename INTO v_parent_schema, v_parent_tablename FROM pg_catalog.pg_tables WHERE schemaname ||'.'|| tablename = p_parent_table; 226IF v_parent_tablename IS NULL THEN 227 RAISE EXCEPTION 'Given parent table does not exist: %', p_parent_table; 228END IF; 229 230 231IF v_jobmon_schema IS NOT NULL THEN 232 EXECUTE format('SELECT %I.add_job(%L)', v_jobmon_schema, format('PARTMAN RE-APPLYING PRIVILEGES TO ALL CHILD TABLES OF: %s', p_parent_table)) INTO v_job_id; 233END IF; 234 235FOR v_row IN 236 SELECT partition_schemaname, partition_tablename FROM @extschema@.show_partitions(p_parent_table, 'ASC') 237LOOP 238 PERFORM @extschema@.apply_privileges(v_parent_schema, v_parent_tablename, v_row.partition_schemaname, v_row.partition_tablename, v_job_id); 239END LOOP; 240 241IF v_jobmon_schema IS NOT NULL THEN 242 EXECUTE format('SELECT %I.close_job(%L)', v_jobmon_schema, v_job_id); 243END IF; 244 245EXCEPTION 246 WHEN OTHERS THEN 247 GET STACKED DIAGNOSTICS ex_message = MESSAGE_TEXT, 248 ex_context = PG_EXCEPTION_CONTEXT, 249 ex_detail = PG_EXCEPTION_DETAIL, 250 ex_hint = PG_EXCEPTION_HINT; 251 IF v_jobmon_schema IS NOT NULL THEN 252 IF v_job_id IS NULL THEN 253 EXECUTE format('SELECT %I.add_job(''PARTMAN RE-APPLYING PRIVILEGES TO ALL CHILD TABLES OF: %s'')', v_jobmon_schema, p_parent_table) INTO v_job_id; 254 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; 255 ELSIF v_step_id IS NULL THEN 256 EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before first step logged'')', v_jobmon_schema, v_job_id) INTO v_step_id; 257 END IF; 258 EXECUTE format('SELECT %I.update_step(%s, ''CRITICAL'', %L)', v_jobmon_schema, v_step_id, 'ERROR: '||coalesce(SQLERRM,'unknown')); 259 EXECUTE format('SELECT %I.fail_job(%s)', v_jobmon_schema, v_job_id); 260 END IF; 261 RAISE EXCEPTION '% 262CONTEXT: % 263DETAIL: % 264HINT: %', ex_message, ex_context, ex_detail, ex_hint; 265END 266$$; 267 268 269/* 270 * Populate the child table(s) of a time-based partition set with old data from the original parent 271 */ 272CREATE OR REPLACE FUNCTION partition_data_time(p_parent_table text, p_batch_count int DEFAULT 1, p_batch_interval interval DEFAULT NULL, p_lock_wait numeric DEFAULT 0, p_order text DEFAULT 'ASC') RETURNS bigint 273 LANGUAGE plpgsql SECURITY DEFINER 274 AS $$ 275DECLARE 276 277v_control text; 278v_datetime_string text; 279v_current_partition_name text; 280v_epoch boolean; 281v_last_partition text; 282v_lock_iter int := 1; 283v_lock_obtained boolean := FALSE; 284v_max_partition_timestamp timestamp; 285v_min_partition_timestamp timestamp; 286v_parent_schema text; 287v_parent_tablename text; 288v_partition_interval interval; 289v_partition_suffix text; 290v_partition_timestamp timestamp[]; 291v_quarter text; 292v_rowcount bigint; 293v_sql text; 294v_start_control timestamp; 295v_time_position int; 296v_total_rows bigint := 0; 297v_type text; 298v_year text; 299 300BEGIN 301 302SELECT partition_type 303 , partition_interval::interval 304 , control 305 , datetime_string 306 , epoch 307INTO v_type 308 , v_partition_interval 309 , v_control 310 , v_datetime_string 311 , v_epoch 312FROM @extschema@.part_config 313WHERE parent_table = p_parent_table 314AND (partition_type = 'time' OR partition_type = 'time-custom'); 315IF NOT FOUND THEN 316 RAISE EXCEPTION 'ERROR: no config found for %', p_parent_table; 317END IF; 318 319IF p_batch_interval IS NULL OR p_batch_interval > v_partition_interval THEN 320 p_batch_interval := v_partition_interval; 321END IF; 322 323SELECT partition_tablename INTO v_last_partition FROM @extschema@.show_partitions(p_parent_table, 'DESC') LIMIT 1; 324SELECT schemaname, tablename INTO v_parent_schema, v_parent_tablename FROM pg_catalog.pg_tables WHERE schemaname ||'.'|| tablename = p_parent_table; 325 326FOR i IN 1..p_batch_count LOOP 327 328 IF v_epoch = false THEN 329 IF p_order = 'ASC' THEN 330 EXECUTE format('SELECT min(%I) FROM ONLY %I.%I', v_control, v_parent_schema, v_parent_tablename) INTO v_start_control; 331 ELSIF p_order = 'DESC' THEN 332 EXECUTE format('SELECT max(%I) FROM ONLY %I.%I', v_control, v_parent_schema, v_parent_tablename) INTO v_start_control; 333 ELSE 334 RAISE EXCEPTION 'Invalid value for p_order. Must be ASC or DESC'; 335 END IF; 336 ELSE 337 IF p_order = 'ASC' THEN 338 EXECUTE format('SELECT to_timestamp(min(%I)) FROM ONLY %I.%I', v_control, v_parent_schema, v_parent_tablename) INTO v_start_control; 339 ELSIF p_order = 'DESC' THEN 340 EXECUTE format('SELECT to_timestamp(max(%I)) FROM ONLY %I.%I', v_control, v_parent_schema, v_parent_tablename) INTO v_start_control; 341 ELSE 342 RAISE EXCEPTION 'Invalid value for p_order. Must be ASC or DESC'; 343 END IF; 344 END IF; 345 346 IF v_start_control IS NULL THEN 347 EXIT; 348 END IF; 349 350 IF v_type = 'time' THEN 351 CASE 352 WHEN v_partition_interval = '15 mins' THEN 353 v_min_partition_timestamp := date_trunc('hour', v_start_control) + 354 '15min'::interval * floor(date_part('minute', v_start_control) / 15.0); 355 WHEN v_partition_interval = '30 mins' THEN 356 v_min_partition_timestamp := date_trunc('hour', v_start_control) + 357 '30min'::interval * floor(date_part('minute', v_start_control) / 30.0); 358 WHEN v_partition_interval = '1 hour' THEN 359 v_min_partition_timestamp := date_trunc('hour', v_start_control); 360 WHEN v_partition_interval = '1 day' THEN 361 v_min_partition_timestamp := date_trunc('day', v_start_control); 362 WHEN v_partition_interval = '1 week' THEN 363 v_min_partition_timestamp := date_trunc('week', v_start_control); 364 WHEN v_partition_interval = '1 month' THEN 365 v_min_partition_timestamp := date_trunc('month', v_start_control); 366 WHEN v_partition_interval = '3 months' THEN 367 v_min_partition_timestamp := date_trunc('quarter', v_start_control); 368 WHEN v_partition_interval = '1 year' THEN 369 v_min_partition_timestamp := date_trunc('year', v_start_control); 370 END CASE; 371 ELSIF v_type = 'time-custom' THEN 372 v_time_position := (length(v_last_partition) - position('p_' in reverse(v_last_partition))) + 2; 373 v_min_partition_timestamp := to_timestamp(substring(v_last_partition from v_time_position), v_datetime_string); 374 v_max_partition_timestamp := v_min_partition_timestamp + v_partition_interval; 375 LOOP 376 IF v_start_control >= v_min_partition_timestamp AND v_start_control < v_max_partition_timestamp THEN 377 EXIT; 378 ELSE 379 BEGIN 380 IF v_start_control > v_max_partition_timestamp THEN 381 -- Keep going forward in time, checking if child partition time interval encompasses the current v_start_control value 382 v_min_partition_timestamp := v_max_partition_timestamp; 383 v_max_partition_timestamp := v_max_partition_timestamp + v_partition_interval; 384 385 ELSE 386 -- Keep going backwards in time, checking if child partition time interval encompasses the current v_start_control value 387 v_max_partition_timestamp := v_min_partition_timestamp; 388 v_min_partition_timestamp := v_min_partition_timestamp - v_partition_interval; 389 END IF; 390 EXCEPTION WHEN datetime_field_overflow THEN 391 RAISE EXCEPTION 'Attempted partition time interval is outside PostgreSQL''s supported time range. 392 Unable to create partition with interval before timestamp % ', v_min_partition_interval; 393 END; 394 END IF; 395 END LOOP; 396 397 END IF; 398 399 v_partition_timestamp := ARRAY[v_min_partition_timestamp]; 400 IF p_order = 'ASC' THEN 401 -- Ensure batch interval given as parameter doesn't cause maximum to overflow the current partition maximum 402 IF (v_start_control + p_batch_interval) >= (v_min_partition_timestamp + v_partition_interval) THEN 403 v_max_partition_timestamp := v_min_partition_timestamp + v_partition_interval; 404 ELSE 405 v_max_partition_timestamp := v_start_control + p_batch_interval; 406 END IF; 407 ELSIF p_order = 'DESC' THEN 408 -- Must be greater than max value still in parent table since query below grabs < max 409 v_max_partition_timestamp := v_min_partition_timestamp + v_partition_interval; 410 -- Ensure batch interval given as parameter doesn't cause minimum to underflow current partition minimum 411 IF (v_start_control - p_batch_interval) >= v_min_partition_timestamp THEN 412 v_min_partition_timestamp = v_start_control - p_batch_interval; 413 END IF; 414 ELSE 415 RAISE EXCEPTION 'Invalid value for p_order. Must be ASC or DESC'; 416 END IF; 417 418-- do some locking with timeout, if required 419 IF p_lock_wait > 0 THEN 420 v_lock_iter := 0; 421 WHILE v_lock_iter <= 5 LOOP 422 v_lock_iter := v_lock_iter + 1; 423 BEGIN 424 IF v_epoch = false THEN 425 v_sql := format('SELECT * FROM ONLY %I.%I WHERE %I >= %L AND %I < %L FOR UPDATE NOWAIT' 426 , v_parent_schema 427 , v_parent_tablename 428 , v_control 429 , v_min_partition_timestamp 430 , v_control 431 , v_max_partition_timestamp); 432 ELSE 433 v_sql := format('SELECT * FROM ONLY %I.%I WHERE to_timestamp(%I) >= %L AND to_timestamp(%I) < %L FOR UPDATE NOWAIT' 434 , v_parent_schema 435 , v_parent_tablename 436 , v_control 437 , v_min_partition_timestamp 438 , v_control 439 , v_max_partition_timestamp); 440 END IF; 441 EXECUTE v_sql; 442 v_lock_obtained := TRUE; 443 EXCEPTION 444 WHEN lock_not_available THEN 445 PERFORM pg_sleep( p_lock_wait / 5.0 ); 446 CONTINUE; 447 END; 448 EXIT WHEN v_lock_obtained; 449 END LOOP; 450 IF NOT v_lock_obtained THEN 451 RETURN -1; 452 END IF; 453 END IF; 454 455 PERFORM @extschema@.create_partition_time(p_parent_table, v_partition_timestamp); 456 -- This suffix generation code is in create_partition_time() as well 457 v_partition_suffix := to_char(v_min_partition_timestamp, v_datetime_string); 458 v_current_partition_name := @extschema@.check_name_length(v_parent_tablename, v_partition_suffix, TRUE); 459 460 IF v_epoch = false THEN 461 v_sql := format('WITH partition_data AS ( 462 DELETE FROM ONLY %I.%I WHERE %I >= %L AND %I < %L RETURNING *) 463 INSERT INTO %I.%I SELECT * FROM partition_data' 464 , v_parent_schema 465 , v_parent_tablename 466 , v_control 467 , v_min_partition_timestamp 468 , v_control 469 , v_max_partition_timestamp 470 , v_parent_schema 471 , v_current_partition_name); 472 ELSE 473 v_sql := format('WITH partition_data AS ( 474 DELETE FROM ONLY %I.%I WHERE to_timestamp(%I) >= %L AND to_timestamp(%I) < %L RETURNING *) 475 INSERT INTO %I.%I SELECT * FROM partition_data' 476 , v_parent_schema 477 , v_parent_tablename 478 , v_control 479 , v_min_partition_timestamp 480 , v_control 481 , v_max_partition_timestamp 482 , v_parent_schema 483 , v_current_partition_name); 484 END IF; 485 EXECUTE v_sql; 486 GET DIAGNOSTICS v_rowcount = ROW_COUNT; 487 v_total_rows := v_total_rows + v_rowcount; 488 IF v_rowcount = 0 THEN 489 EXIT; 490 END IF; 491 492END LOOP; 493 494PERFORM @extschema@.create_function_time(p_parent_table); 495 496RETURN v_total_rows; 497 498END 499$$; 500 501 502/* 503 * Function to create id partitions 504 */ 505CREATE OR REPLACE FUNCTION create_partition_id(p_parent_table text, p_partition_ids bigint[], p_analyze boolean DEFAULT true) RETURNS boolean 506 LANGUAGE plpgsql SECURITY DEFINER 507 AS $$ 508DECLARE 509 510ex_context text; 511ex_detail text; 512ex_hint text; 513ex_message text; 514v_all text[] := ARRAY['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER']; 515v_analyze boolean := FALSE; 516v_control text; 517v_exists text; 518v_grantees text[]; 519v_hasoids boolean; 520v_id bigint; 521v_inherit_fk boolean; 522v_job_id bigint; 523v_jobmon boolean; 524v_jobmon_schema text; 525v_old_search_path text; 526v_parent_grant record; 527v_parent_owner text; 528v_parent_schema text; 529v_parent_tablename text; 530v_parent_tablespace text; 531v_partition_interval bigint; 532v_partition_created boolean := false; 533v_partition_name text; 534v_revoke text; 535v_row record; 536v_sql text; 537v_step_id bigint; 538v_sub_id_max bigint; 539v_sub_id_min bigint; 540v_unlogged char; 541 542BEGIN 543 544SELECT control 545 , partition_interval 546 , inherit_fk 547 , jobmon 548INTO v_control 549 , v_partition_interval 550 , v_inherit_fk 551 , v_jobmon 552FROM @extschema@.part_config 553WHERE parent_table = p_parent_table 554AND partition_type = 'id'; 555 556IF NOT FOUND THEN 557 RAISE EXCEPTION 'ERROR: no config found for %', p_parent_table; 558END IF; 559 560IF v_jobmon THEN 561 SELECT nspname INTO v_jobmon_schema FROM pg_namespace n, pg_extension e WHERE e.extname = 'pg_jobmon' AND e.extnamespace = n.oid; 562 IF v_jobmon_schema IS NOT NULL THEN 563 SELECT current_setting('search_path') INTO v_old_search_path; 564 EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', '@extschema@,'||v_jobmon_schema, 'false'); 565 END IF; 566END IF; 567 568-- Determine if this table is a child of a subpartition parent. If so, get limits of what child tables can be created based on parent suffix 569SELECT sub_min::bigint, sub_max::bigint INTO v_sub_id_min, v_sub_id_max FROM @extschema@.check_subpartition_limits(p_parent_table, 'id'); 570 571SELECT tableowner, schemaname, tablename, tablespace INTO v_parent_owner, v_parent_schema, v_parent_tablename, v_parent_tablespace FROM pg_tables WHERE schemaname ||'.'|| tablename = p_parent_table; 572 573IF v_jobmon_schema IS NOT NULL THEN 574 v_job_id := add_job(format('PARTMAN CREATE TABLE: %s', p_parent_table)); 575END IF; 576 577FOREACH v_id IN ARRAY p_partition_ids LOOP 578-- Do not create the child table if it's outside the bounds of the top parent. 579 IF v_sub_id_min IS NOT NULL THEN 580 IF v_id < v_sub_id_min OR v_id > v_sub_id_max THEN 581 CONTINUE; 582 END IF; 583 END IF; 584 585 v_partition_name := @extschema@.check_name_length(v_parent_tablename, v_id::text, TRUE); 586 -- If child table already exists, skip creation 587 SELECT tablename INTO v_exists FROM pg_catalog.pg_tables WHERE schemaname = v_parent_schema AND tablename = v_partition_name; 588 IF v_exists IS NOT NULL THEN 589 CONTINUE; 590 END IF; 591 592 -- Ensure analyze is run if a new partition is created. Otherwise if one isn't, will be false and analyze will be skipped 593 v_analyze := TRUE; 594 595 IF v_jobmon_schema IS NOT NULL THEN 596 v_step_id := add_step(v_job_id, 'Creating new partition '||v_partition_name||' with interval from '||v_id||' to '||(v_id + v_partition_interval)-1); 597 END IF; 598 599 SELECT relpersistence INTO v_unlogged 600 FROM pg_catalog.pg_class c 601 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 602 WHERE c.relname = v_parent_tablename 603 AND n.nspname = v_parent_schema; 604 v_sql := 'CREATE'; 605 IF v_unlogged = 'u' THEN 606 v_sql := v_sql || ' UNLOGGED'; 607 END IF; 608 v_sql := v_sql || format(' TABLE %I.%I (LIKE %I.%I INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING STORAGE INCLUDING COMMENTS)' 609 , v_parent_schema 610 , v_partition_name 611 , v_parent_schema 612 , v_parent_tablename); 613 SELECT relhasoids INTO v_hasoids 614 FROM pg_catalog.pg_class c 615 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 616 WHERE c.relname = v_parent_tablename 617 AND n.nspname = v_parent_schema; 618 IF v_hasoids IS TRUE THEN 619 v_sql := v_sql || ' WITH (OIDS)'; 620 END IF; 621 EXECUTE v_sql; 622 IF v_parent_tablespace IS NOT NULL THEN 623 EXECUTE format('ALTER TABLE %I.%I SET TABLESPACE %I', v_parent_schema, v_partition_name, v_parent_tablespace); 624 END IF; 625 EXECUTE format('ALTER TABLE %I.%I ADD CONSTRAINT %I CHECK (%I >= %s AND %I < %s )' 626 , v_parent_schema 627 , v_partition_name 628 , v_partition_name||'_partition_check' 629 , v_control 630 , v_id 631 , v_control 632 , v_id + v_partition_interval); 633 EXECUTE format('ALTER TABLE %I.%I INHERIT %I.%I', v_parent_schema, v_partition_name, v_parent_schema, v_parent_tablename); 634 635 PERFORM @extschema@.apply_privileges(v_parent_schema, v_parent_tablename, v_parent_schema, v_partition_name, v_job_id); 636 637 IF v_inherit_fk THEN 638 PERFORM @extschema@.apply_foreign_keys(p_parent_table, v_parent_schema||'.'||v_partition_name, v_job_id); 639 END IF; 640 641 IF v_jobmon_schema IS NOT NULL THEN 642 PERFORM update_step(v_step_id, 'OK', 'Done'); 643 END IF; 644 645 -- Will only loop once and only if sub_partitioning is actually configured 646 -- This seemed easier than assigning a bunch of variables then doing an IF condition 647 FOR v_row IN 648 SELECT sub_parent 649 , sub_control 650 , sub_partition_type 651 , sub_partition_interval 652 , sub_constraint_cols 653 , sub_premake 654 , sub_inherit_fk 655 , sub_retention 656 , sub_retention_schema 657 , sub_retention_keep_table 658 , sub_retention_keep_index 659 , sub_use_run_maintenance 660 , sub_epoch 661 , sub_optimize_trigger 662 , sub_optimize_constraint 663 , sub_jobmon 664 FROM @extschema@.part_config_sub 665 WHERE sub_parent = p_parent_table 666 LOOP 667 IF v_jobmon_schema IS NOT NULL THEN 668 v_step_id := add_step(v_job_id, 'Subpartitioning '||v_partition_name); 669 END IF; 670 v_sql := format('SELECT @extschema@.create_parent( 671 p_parent_table := %L 672 , p_control := %L 673 , p_type := %L 674 , p_interval := %L 675 , p_constraint_cols := %L 676 , p_premake := %L 677 , p_use_run_maintenance := %L 678 , p_inherit_fk := %L 679 , p_epoch := %L 680 , p_jobmon := %L )' 681 , v_parent_schema||'.'||v_partition_name 682 , v_row.sub_control 683 , v_row.sub_partition_type 684 , v_row.sub_partition_interval 685 , v_row.sub_constraint_cols 686 , v_row.sub_premake 687 , v_row.sub_use_run_maintenance 688 , v_row.sub_inherit_fk 689 , v_row.sub_epoch 690 , v_row.sub_jobmon); 691 EXECUTE v_sql; 692 693 UPDATE @extschema@.part_config SET 694 retention_schema = v_row.sub_retention_schema 695 , retention_keep_table = v_row.sub_retention_keep_table 696 , retention_keep_index = v_row.sub_retention_keep_index 697 , optimize_trigger = v_row.sub_optimize_trigger 698 , optimize_constraint = v_row.sub_optimize_constraint 699 WHERE parent_table = v_parent_schema||'.'||v_partition_name; 700 701 IF v_jobmon_schema IS NOT NULL THEN 702 PERFORM update_step(v_step_id, 'OK', 'Done'); 703 END IF; 704 705 END LOOP; -- end sub partitioning LOOP 706 707 v_partition_created := true; 708 709END LOOP; 710 711-- v_analyze is a local check if a new table is made. 712-- p_analyze is a parameter to say whether to run the analyze at all. Used by create_parent() to avoid long exclusive lock or run_maintenence() to avoid long creation runs. 713IF v_analyze AND p_analyze THEN 714 IF v_jobmon_schema IS NOT NULL THEN 715 v_step_id := add_step(v_job_id, format('Analyzing partition set: %s', p_parent_table)); 716 END IF; 717 718 EXECUTE format('ANALYZE %I.%I', v_parent_schema, v_parent_tablename); 719 720 IF v_jobmon_schema IS NOT NULL THEN 721 PERFORM update_step(v_step_id, 'OK', 'Done'); 722 END IF; 723END IF; 724 725IF v_jobmon_schema IS NOT NULL THEN 726 IF v_partition_created = false THEN 727 v_step_id := add_step(v_job_id, format('No partitions created for partition set: %s', p_parent_table)); 728 PERFORM update_step(v_step_id, 'OK', 'Done'); 729 END IF; 730 731 PERFORM close_job(v_job_id); 732END IF; 733 734IF v_jobmon_schema IS NOT NULL THEN 735 EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false'); 736END IF; 737 738RETURN v_partition_created; 739 740EXCEPTION 741 WHEN OTHERS THEN 742 GET STACKED DIAGNOSTICS ex_message = MESSAGE_TEXT, 743 ex_context = PG_EXCEPTION_CONTEXT, 744 ex_detail = PG_EXCEPTION_DETAIL, 745 ex_hint = PG_EXCEPTION_HINT; 746 IF v_jobmon_schema IS NOT NULL THEN 747 IF v_job_id IS NULL THEN 748 EXECUTE format('SELECT %I.add_job(''PARTMAN CREATE TABLE: %s'')', v_jobmon_schema, p_parent_table) INTO v_job_id; 749 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; 750 ELSIF v_step_id IS NULL THEN 751 EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before first step logged'')', v_jobmon_schema, v_job_id) INTO v_step_id; 752 END IF; 753 EXECUTE format('SELECT %I.update_step(%s, ''CRITICAL'', %L)', v_jobmon_schema, v_step_id, 'ERROR: '||coalesce(SQLERRM,'unknown')); 754 EXECUTE format('SELECT %I.fail_job(%s)', v_jobmon_schema, v_job_id); 755 END IF; 756 RAISE EXCEPTION '% 757CONTEXT: % 758DETAIL: % 759HINT: %', ex_message, ex_context, ex_detail, ex_hint; 760END 761$$; 762 763 764/* 765 * Function to create a child table in a time-based partition set 766 */ 767CREATE OR REPLACE FUNCTION create_partition_time(p_parent_table text, p_partition_times timestamp[], p_analyze boolean DEFAULT true) 768RETURNS boolean 769 LANGUAGE plpgsql SECURITY DEFINER 770 AS $$ 771DECLARE 772 773ex_context text; 774ex_detail text; 775ex_hint text; 776ex_message text; 777v_all text[] := ARRAY['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER']; 778v_analyze boolean := FALSE; 779v_control text; 780v_datetime_string text; 781v_exists text; 782v_epoch boolean; 783v_grantees text[]; 784v_hasoids boolean; 785v_inherit_fk boolean; 786v_job_id bigint; 787v_jobmon boolean; 788v_jobmon_schema text; 789v_old_search_path text; 790v_parent_grant record; 791v_parent_owner text; 792v_parent_schema text; 793v_parent_tablename text; 794v_partition_created boolean := false; 795v_partition_name text; 796v_partition_suffix text; 797v_parent_tablespace text; 798v_partition_interval interval; 799v_partition_timestamp_end timestamp; 800v_partition_timestamp_start timestamp; 801v_quarter text; 802v_revoke text; 803v_row record; 804v_sql text; 805v_step_id bigint; 806v_step_overflow_id bigint; 807v_sub_timestamp_max timestamp; 808v_sub_timestamp_min timestamp; 809v_trunc_value text; 810v_time timestamp; 811v_type text; 812v_unlogged char; 813v_year text; 814 815BEGIN 816 817SELECT partition_type 818 , control 819 , partition_interval 820 , epoch 821 , inherit_fk 822 , jobmon 823 , datetime_string 824INTO v_type 825 , v_control 826 , v_partition_interval 827 , v_epoch 828 , v_inherit_fk 829 , v_jobmon 830 , v_datetime_string 831FROM @extschema@.part_config 832WHERE parent_table = p_parent_table 833AND partition_type = 'time' OR partition_type = 'time-custom'; 834 835IF NOT FOUND THEN 836 RAISE EXCEPTION 'ERROR: no config found for %', p_parent_table; 837END IF; 838 839IF v_jobmon THEN 840 SELECT nspname INTO v_jobmon_schema FROM pg_catalog.pg_namespace n, pg_catalog.pg_extension e WHERE e.extname = 'pg_jobmon' AND e.extnamespace = n.oid; 841 IF v_jobmon_schema IS NOT NULL THEN 842 SELECT current_setting('search_path') INTO v_old_search_path; 843 EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', '@extschema@,'||v_jobmon_schema, 'false'); 844 END IF; 845END IF; 846 847-- Determine if this table is a child of a subpartition parent. If so, get limits of what child tables can be created based on parent suffix 848SELECT sub_min::timestamp, sub_max::timestamp INTO v_sub_timestamp_min, v_sub_timestamp_max FROM @extschema@.check_subpartition_limits(p_parent_table, 'time'); 849 850SELECT tableowner, schemaname, tablename, tablespace INTO v_parent_owner, v_parent_schema, v_parent_tablename, v_parent_tablespace FROM pg_catalog.pg_tables WHERE schemaname ||'.'|| tablename = p_parent_table; 851 852IF v_jobmon_schema IS NOT NULL THEN 853 v_job_id := add_job(format('PARTMAN CREATE TABLE: %s', p_parent_table)); 854END IF; 855 856FOREACH v_time IN ARRAY p_partition_times LOOP 857 v_partition_timestamp_start := v_time; 858 BEGIN 859 v_partition_timestamp_end := v_time + v_partition_interval; 860 EXCEPTION WHEN datetime_field_overflow THEN 861 RAISE WARNING 'Attempted partition time interval is outside PostgreSQL''s supported time range. 862 Child partition creation after time % skipped', v_time; 863 v_step_overflow_id := add_step(v_job_id, 'Attempted partition time interval is outside PostgreSQL''s supported time range.'); 864 PERFORM update_step(v_step_overflow_id, 'CRITICAL', 'Child partition creation after time '||v_time||' skipped'); 865 CONTINUE; 866 END; 867 868 -- Do not create the child table if it's outside the bounds of the top parent. 869 IF v_sub_timestamp_min IS NOT NULL THEN 870 IF v_time < v_sub_timestamp_min OR v_time > v_sub_timestamp_max THEN 871 CONTINUE; 872 END IF; 873 END IF; 874 875 -- This suffix generation code is in partition_data_time() as well 876 v_partition_suffix := to_char(v_time, v_datetime_string); 877 v_partition_name := @extschema@.check_name_length(v_parent_tablename, v_partition_suffix, TRUE); 878 SELECT tablename INTO v_exists FROM pg_catalog.pg_tables WHERE schemaname = v_parent_schema AND tablename = v_partition_name; 879 IF v_exists IS NOT NULL THEN 880 CONTINUE; 881 END IF; 882 883 -- Ensure analyze is run if a new partition is created. Otherwise if one isn't, will be false and analyze will be skipped 884 v_analyze := TRUE; 885 886 IF v_jobmon_schema IS NOT NULL THEN 887 v_step_id := add_step(v_job_id, format('Creating new partition %s.%s with interval from %s to %s' 888 , v_parent_schema 889 , v_partition_name 890 , v_partition_timestamp_start 891 , v_partition_timestamp_end-'1sec'::interval)); 892 END IF; 893 894 SELECT relpersistence INTO v_unlogged 895 FROM pg_catalog.pg_class c 896 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 897 WHERE c.relname = v_parent_tablename 898 AND n.nspname = v_parent_schema; 899 v_sql := 'CREATE'; 900 IF v_unlogged = 'u' THEN 901 v_sql := v_sql || ' UNLOGGED'; 902 END IF; 903 v_sql := v_sql || format(' TABLE %I.%I (LIKE %I.%I INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING STORAGE INCLUDING COMMENTS)' 904 , v_parent_schema 905 , v_partition_name 906 , v_parent_schema 907 , v_parent_tablename); 908 SELECT relhasoids INTO v_hasoids 909 FROM pg_catalog.pg_class c 910 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 911 WHERE c.relname = v_parent_tablename 912 AND n.nspname = v_parent_schema; 913 IF v_hasoids IS TRUE THEN 914 v_sql := v_sql || ' WITH (OIDS)'; 915 END IF; 916 EXECUTE v_sql; 917 IF v_parent_tablespace IS NOT NULL THEN 918 EXECUTE format('ALTER TABLE %I.%I SET TABLESPACE %I', v_parent_schema, v_partition_name, v_parent_tablespace); 919 END IF; 920 IF v_epoch = false THEN 921 EXECUTE format('ALTER TABLE %I.%I ADD CONSTRAINT %I CHECK (%I >= %L AND %I < %L)' 922 , v_parent_schema 923 , v_partition_name 924 , v_partition_name||'_partition_check' 925 , v_control 926 , v_partition_timestamp_start 927 , v_control 928 , v_partition_timestamp_end); 929 ELSE 930 EXECUTE format('ALTER TABLE %I.%I ADD CONSTRAINT %I CHECK (to_timestamp(%I) >= %L AND to_timestamp(%I) < %L)' 931 , v_parent_schema 932 , v_partition_name 933 , v_partition_name||'_partition_check' 934 , v_control 935 , v_partition_timestamp_start 936 , v_control 937 , v_partition_timestamp_end); 938 END IF; 939 940 EXECUTE format('ALTER TABLE %I.%I INHERIT %I.%I' 941 , v_parent_schema 942 , v_partition_name 943 , v_parent_schema 944 , v_parent_tablename); 945 946 -- If custom time, set extra config options. 947 IF v_type = 'time-custom' THEN 948 INSERT INTO @extschema@.custom_time_partitions (parent_table, child_table, partition_range) 949 VALUES ( p_parent_table, v_parent_schema||'.'||v_partition_name, tstzrange(v_partition_timestamp_start, v_partition_timestamp_end, '[)') ); 950 END IF; 951 952 PERFORM @extschema@.apply_privileges(v_parent_schema, v_parent_tablename, v_parent_schema, v_partition_name, v_job_id); 953 954 IF v_inherit_fk THEN 955 PERFORM @extschema@.apply_foreign_keys(p_parent_table, v_parent_schema||'.'||v_partition_name, v_job_id); 956 END IF; 957 958 IF v_jobmon_schema IS NOT NULL THEN 959 PERFORM update_step(v_step_id, 'OK', 'Done'); 960 END IF; 961 962 -- Will only loop once and only if sub_partitioning is actually configured 963 -- This seemed easier than assigning a bunch of variables then doing an IF condition 964 FOR v_row IN 965 SELECT sub_parent 966 , sub_control 967 , sub_partition_type 968 , sub_partition_interval 969 , sub_constraint_cols 970 , sub_premake 971 , sub_inherit_fk 972 , sub_retention 973 , sub_retention_schema 974 , sub_retention_keep_table 975 , sub_retention_keep_index 976 , sub_use_run_maintenance 977 , sub_epoch 978 , sub_optimize_trigger 979 , sub_optimize_constraint 980 , sub_jobmon 981 FROM @extschema@.part_config_sub 982 WHERE sub_parent = p_parent_table 983 LOOP 984 IF v_jobmon_schema IS NOT NULL THEN 985 v_step_id := add_step(v_job_id, format('Subpartitioning %s.%s', v_parent_schema, v_partition_name)); 986 END IF; 987 v_sql := format('SELECT @extschema@.create_parent( 988 p_parent_table := %L 989 , p_control := %L 990 , p_type := %L 991 , p_interval := %L 992 , p_constraint_cols := %L 993 , p_premake := %L 994 , p_use_run_maintenance := %L 995 , p_inherit_fk := %L 996 , p_epoch := %L 997 , p_jobmon := %L )' 998 , v_parent_schema||'.'||v_partition_name 999 , v_row.sub_control 1000 , v_row.sub_partition_type 1001 , v_row.sub_partition_interval 1002 , v_row.sub_constraint_cols 1003 , v_row.sub_premake 1004 , v_row.sub_use_run_maintenance 1005 , v_row.sub_inherit_fk 1006 , v_row.sub_epoch 1007 , v_row.sub_jobmon); 1008 EXECUTE v_sql; 1009 1010 UPDATE @extschema@.part_config SET 1011 retention_schema = v_row.sub_retention_schema 1012 , retention_keep_table = v_row.sub_retention_keep_table 1013 , retention_keep_index = v_row.sub_retention_keep_index 1014 , optimize_trigger = v_row.sub_optimize_trigger 1015 , optimize_constraint = v_row.sub_optimize_constraint 1016 WHERE parent_table = v_parent_schema||'.'||v_partition_name; 1017 1018 END LOOP; -- end sub partitioning LOOP 1019 1020 v_partition_created := true; 1021 1022END LOOP; 1023 1024-- v_analyze is a local check if a new table is made. 1025-- p_analyze is a parameter to say whether to run the analyze at all. Used by create_parent() to avoid long exclusive lock or run_maintenence() to avoid long creation runs. 1026IF v_analyze AND p_analyze THEN 1027 IF v_jobmon_schema IS NOT NULL THEN 1028 v_step_id := add_step(v_job_id, format('Analyzing partition set: %s', p_parent_table)); 1029 END IF; 1030 1031 EXECUTE format('ANALYZE %I.%I', v_parent_schema, v_parent_tablename); 1032 1033 IF v_jobmon_schema IS NOT NULL THEN 1034 PERFORM update_step(v_step_id, 'OK', 'Done'); 1035 END IF; 1036END IF; 1037 1038IF v_jobmon_schema IS NOT NULL THEN 1039 IF v_partition_created = false THEN 1040 v_step_id := add_step(v_job_id, format('No partitions created for partition set: %s. Attempted intervals: %s', p_parent_table, p_partition_times)); 1041 PERFORM update_step(v_step_id, 'OK', 'Done'); 1042 END IF; 1043 1044 IF v_step_overflow_id IS NOT NULL THEN 1045 PERFORM fail_job(v_job_id); 1046 ELSE 1047 PERFORM close_job(v_job_id); 1048 END IF; 1049END IF; 1050 1051IF v_jobmon_schema IS NOT NULL THEN 1052 EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false'); 1053END IF; 1054 1055RETURN v_partition_created; 1056 1057EXCEPTION 1058 WHEN OTHERS THEN 1059 GET STACKED DIAGNOSTICS ex_message = MESSAGE_TEXT, 1060 ex_context = PG_EXCEPTION_CONTEXT, 1061 ex_detail = PG_EXCEPTION_DETAIL, 1062 ex_hint = PG_EXCEPTION_HINT; 1063 IF v_jobmon_schema IS NOT NULL THEN 1064 IF v_job_id IS NULL THEN 1065 EXECUTE format('SELECT %I.add_job(''PARTMAN CREATE TABLE: %s'')', v_jobmon_schema, p_parent_table) INTO v_job_id; 1066 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; 1067 ELSIF v_step_id IS NULL THEN 1068 EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before first step logged'')', v_jobmon_schema, v_job_id) INTO v_step_id; 1069 END IF; 1070 EXECUTE format('SELECT %I.update_step(%s, ''CRITICAL'', %L)', v_jobmon_schema, v_step_id, 'ERROR: '||coalesce(SQLERRM,'unknown')); 1071 EXECUTE format('SELECT %I.fail_job(%s)', v_jobmon_schema, v_job_id); 1072 END IF; 1073 RAISE EXCEPTION '% 1074CONTEXT: % 1075DETAIL: % 1076HINT: %', ex_message, ex_context, ex_detail, ex_hint; 1077END 1078$$; 1079 1080 1081/* 1082 * Function to undo partitioning. 1083 * Will actually work on any parent/child table set, not just ones created by pg_partman. 1084 */ 1085CREATE OR REPLACE FUNCTION undo_partition(p_parent_table text, p_batch_count int DEFAULT 1, p_keep_table boolean DEFAULT true, p_jobmon boolean DEFAULT true, p_lock_wait numeric DEFAULT 0) RETURNS bigint 1086 LANGUAGE plpgsql SECURITY DEFINER 1087 AS $$ 1088DECLARE 1089 1090ex_context text; 1091ex_detail text; 1092ex_hint text; 1093ex_message text; 1094v_adv_lock boolean; 1095v_batch_loop_count bigint := 0; 1096v_child_count bigint; 1097v_child_table text; 1098v_copy_sql text; 1099v_function_name text; 1100v_job_id bigint; 1101v_jobmon_schema text; 1102v_lock_iter int := 1; 1103v_lock_obtained boolean := FALSE; 1104v_old_search_path text; 1105v_parent_schema text; 1106v_parent_tablename text; 1107v_partition_interval interval; 1108v_rowcount bigint; 1109v_step_id bigint; 1110v_total bigint := 0; 1111v_trig_name text; 1112v_type text; 1113v_undo_count int := 0; 1114 1115BEGIN 1116 1117v_adv_lock := pg_try_advisory_xact_lock(hashtext('pg_partman undo_partition')); 1118IF v_adv_lock = 'false' THEN 1119 RAISE NOTICE 'undo_partition already running.'; 1120 RETURN 0; 1121END IF; 1122 1123IF p_jobmon THEN 1124 SELECT nspname INTO v_jobmon_schema FROM pg_namespace n, pg_extension e WHERE e.extname = 'pg_jobmon' AND e.extnamespace = n.oid; 1125 IF v_jobmon_schema IS NOT NULL THEN 1126 SELECT current_setting('search_path') INTO v_old_search_path; 1127 EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', '@extschema@,'||v_jobmon_schema, 'false'); 1128 END IF; 1129END IF; 1130 1131IF v_jobmon_schema IS NOT NULL THEN 1132 v_job_id := add_job(format('PARTMAN UNDO PARTITIONING: %s', p_parent_table)); 1133 v_step_id := add_step(v_job_id, format('Undoing partitioning for table %s', p_parent_table)); 1134END IF; 1135 1136-- Stops new time partitions from being made as well as stopping child tables from being dropped if they were configured with a retention period. 1137UPDATE @extschema@.part_config SET undo_in_progress = true WHERE parent_table = p_parent_table; 1138-- Stop data going into child tables and stop new id partitions from being made. 1139SELECT schemaname, tablename INTO v_parent_schema, v_parent_tablename FROM pg_catalog.pg_tables WHERE schemaname ||'.'|| tablename = p_parent_table; 1140v_trig_name := @extschema@.check_name_length(p_object_name := v_parent_tablename, p_suffix := '_part_trig'); 1141v_function_name := @extschema@.check_name_length(v_parent_tablename, '_part_trig_func', FALSE); 1142 1143SELECT tgname INTO v_trig_name 1144FROM pg_catalog.pg_trigger t 1145JOIN pg_catalog.pg_class c ON t.tgrelid = c.oid 1146WHERE tgname = v_trig_name 1147AND c.relname = v_parent_tablename; 1148 1149SELECT proname INTO v_function_name FROM pg_catalog.pg_proc p JOIN pg_catalog.pg_namespace n ON p.pronamespace = n.oid WHERE n.nspname = v_parent_schema AND proname = v_function_name; 1150 1151IF v_trig_name IS NOT NULL THEN 1152 -- lockwait for trigger drop 1153 IF p_lock_wait > 0 THEN 1154 v_lock_iter := 0; 1155 WHILE v_lock_iter <= 5 LOOP 1156 v_lock_iter := v_lock_iter + 1; 1157 BEGIN 1158 EXECUTE format('LOCK TABLE ONLY %I.%I IN ACCESS EXCLUSIVE MODE NOWAIT', v_parent_schema, v_parent_tablename); 1159 v_lock_obtained := TRUE; 1160 EXCEPTION 1161 WHEN lock_not_available THEN 1162 PERFORM pg_sleep( p_lock_wait / 5.0 ); 1163 CONTINUE; 1164 END; 1165 EXIT WHEN v_lock_obtained; 1166 END LOOP; 1167 IF NOT v_lock_obtained THEN 1168 RAISE NOTICE 'Unable to obtain lock on parent table to remove trigger'; 1169 RETURN -1; 1170 END IF; 1171 END IF; -- END p_lock_wait IF 1172 EXECUTE format('DROP TRIGGER IF EXISTS %I ON %I.%I', v_trig_name, v_parent_schema, v_parent_tablename); 1173END IF; -- END trigger IF 1174v_lock_obtained := FALSE; -- reset for reuse later 1175 1176IF v_function_name IS NOT NULL THEN 1177 EXECUTE format('DROP FUNCTION IF EXISTS %I.%I()', v_parent_schema, v_function_name); 1178END IF; 1179 1180IF v_jobmon_schema IS NOT NULL THEN 1181 IF (v_trig_name IS NOT NULL OR v_function_name IS NOT NULL) THEN 1182 PERFORM update_step(v_step_id, 'OK', 'Stopped partition creation process. Removed trigger & trigger function'); 1183 ELSE 1184 PERFORM update_step(v_step_id, 'OK', 'Stopped partition creation process.'); 1185 END IF; 1186END IF; 1187 1188WHILE v_batch_loop_count < p_batch_count LOOP 1189 -- Get ordered list of child table in set. Store in variable one at a time per loop until none are left. 1190 -- Not using show_partitions() so it can work on non-pg_partman partition sets 1191 WITH parent_info AS ( 1192 SELECT c1.oid 1193 FROM pg_catalog.pg_class c1 1194 JOIN pg_catalog.pg_namespace n1 ON c1.relnamespace = n1.oid 1195 WHERE c1.relname = v_parent_tablename 1196 AND n1.nspname = v_parent_schema 1197 ) 1198 SELECT c.relname INTO v_child_table 1199 FROM pg_catalog.pg_inherits i 1200 JOIN pg_catalog.pg_class c ON i.inhrelid = c.oid 1201 JOIN parent_info p ON i.inhparent = p.oid 1202 ORDER BY i.inhrelid ASC; 1203 1204 EXIT WHEN v_child_table IS NULL; 1205 1206 IF v_jobmon_schema IS NOT NULL THEN 1207 v_step_id := add_step(v_job_id, format('Removing child partition: %s.%s', v_parent_schema, v_child_table)); 1208 END IF; 1209 1210 -- lockwait timeout for table drop 1211 v_lock_obtained := FALSE; -- reset for reuse in loop 1212 IF p_lock_wait > 0 THEN 1213 v_lock_iter := 0; 1214 WHILE v_lock_iter <= 5 LOOP 1215 v_lock_iter := v_lock_iter + 1; 1216 BEGIN 1217 EXECUTE format('LOCK TABLE ONLY %I.%I IN ACCESS EXCLUSIVE MODE NOWAIT', v_parent_schema, v_child_table); 1218 v_lock_obtained := TRUE; 1219 EXCEPTION 1220 WHEN lock_not_available THEN 1221 PERFORM pg_sleep( p_lock_wait / 5.0 ); 1222 CONTINUE; 1223 END; 1224 EXIT WHEN v_lock_obtained; 1225 END LOOP; 1226 IF NOT v_lock_obtained THEN 1227 RAISE NOTICE 'Unable to obtain lock on child table for removal from partition set'; 1228 RETURN -1; 1229 END IF; 1230 END IF; -- END p_lock_wait IF 1231 1232 v_copy_sql := format('INSERT INTO %I.%I SELECT * FROM %I.%I' 1233 , v_parent_schema 1234 , v_parent_tablename 1235 , v_parent_schema 1236 , v_child_table); 1237 EXECUTE v_copy_sql; 1238 GET DIAGNOSTICS v_rowcount = ROW_COUNT; 1239 v_total := v_total + v_rowcount; 1240 1241 EXECUTE format('ALTER TABLE %I.%I NO INHERIT %I.%I' 1242 , v_parent_schema 1243 , v_child_table 1244 , v_parent_schema 1245 , v_parent_tablename); 1246 IF p_keep_table = false THEN 1247 EXECUTE format('DROP TABLE %I.%I', v_parent_schema, v_child_table); 1248 IF v_jobmon_schema IS NOT NULL THEN 1249 PERFORM update_step(v_step_id, 'OK', format('Child table DROPPED. Moved %s rows to parent', v_rowcount)); 1250 END IF; 1251 ELSE 1252 IF v_jobmon_schema IS NOT NULL THEN 1253 PERFORM update_step(v_step_id, 'OK', format('Child table UNINHERITED, not DROPPED. Copied %s rows to parent', v_rowcount)); 1254 END IF; 1255 END IF; 1256 1257 SELECT partition_type INTO v_type FROM @extschema@.part_config WHERE parent_table = p_parent_table; 1258 IF v_type = 'time-custom' THEN 1259 DELETE FROM @extschema@.custom_time_partitions WHERE parent_table = p_parent_table AND child_table = v_parent_schema||'.'||v_child_table; 1260 END IF; 1261 1262 v_batch_loop_count := v_batch_loop_count + 1; 1263 v_undo_count := v_undo_count + 1; 1264END LOOP; -- v_batch_loop_count 1265 1266IF v_undo_count = 0 THEN 1267 -- FOR loop never ran, so there's no child tables left. 1268 DELETE FROM @extschema@.part_config WHERE parent_table = p_parent_table; 1269 IF v_jobmon_schema IS NOT NULL THEN 1270 v_step_id := add_step(v_job_id, 'Removing config from pg_partman (if it existed)'); 1271 PERFORM update_step(v_step_id, 'OK', 'Done'); 1272 END IF; 1273END IF; 1274 1275RAISE NOTICE 'Copied % row(s) from % child table(s) to the parent: %', v_total, v_undo_count, p_parent_table; 1276IF v_jobmon_schema IS NOT NULL THEN 1277 v_step_id := add_step(v_job_id, 'Final stats'); 1278 PERFORM update_step(v_step_id, 'OK', format('Copied %s row(s) from %s child table(s) to the parent', v_total, v_undo_count)); 1279END IF; 1280 1281IF v_jobmon_schema IS NOT NULL THEN 1282 PERFORM close_job(v_job_id); 1283 EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false'); 1284END IF; 1285 1286RETURN v_total; 1287 1288EXCEPTION 1289 WHEN OTHERS THEN 1290 GET STACKED DIAGNOSTICS ex_message = MESSAGE_TEXT, 1291 ex_context = PG_EXCEPTION_CONTEXT, 1292 ex_detail = PG_EXCEPTION_DETAIL, 1293 ex_hint = PG_EXCEPTION_HINT; 1294 IF v_jobmon_schema IS NOT NULL THEN 1295 IF v_job_id IS NULL THEN 1296 EXECUTE format('SELECT %I.add_job(''PARTMAN UNDO PARTITIONING: %s'')', v_jobmon_schema, p_parent_table) INTO v_job_id; 1297 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; 1298 ELSIF v_step_id IS NULL THEN 1299 EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before first step logged'')', v_jobmon_schema, v_job_id) INTO v_step_id; 1300 END IF; 1301 EXECUTE format('SELECT %I.update_step(%s, ''CRITICAL'', %L)', v_jobmon_schema, v_step_id, 'ERROR: '||coalesce(SQLERRM,'unknown')); 1302 EXECUTE format('SELECT %I.fail_job(%s)', v_jobmon_schema, v_job_id); 1303 END IF; 1304 RAISE EXCEPTION '% 1305CONTEXT: % 1306DETAIL: % 1307HINT: %', ex_message, ex_context, ex_detail, ex_hint; 1308END 1309$$; 1310 1311 1312/* 1313 * Function to undo time-based partitioning created by this extension 1314 */ 1315CREATE 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, p_lock_wait numeric DEFAULT 0) RETURNS bigint 1316 LANGUAGE plpgsql SECURITY DEFINER 1317 AS $$ 1318DECLARE 1319 1320ex_context text; 1321ex_detail text; 1322ex_hint text; 1323ex_message text; 1324v_adv_lock boolean; 1325v_batch_loop_count int := 0; 1326v_child_min timestamptz; 1327v_child_loop_total bigint := 0; 1328v_child_table text; 1329v_control text; 1330v_epoch boolean; 1331v_function_name text; 1332v_inner_loop_count int; 1333v_lock_iter int := 1; 1334v_lock_obtained boolean := FALSE; 1335v_job_id bigint; 1336v_jobmon boolean; 1337v_jobmon_schema text; 1338v_move_sql text; 1339v_old_search_path text; 1340v_parent_schema text; 1341v_parent_tablename text; 1342v_partition_interval interval; 1343v_row record; 1344v_rowcount bigint; 1345v_step_id bigint; 1346v_sub_count int; 1347v_total bigint := 0; 1348v_trig_name text; 1349v_type text; 1350v_undo_count int := 0; 1351 1352BEGIN 1353 1354v_adv_lock := pg_try_advisory_xact_lock(hashtext('pg_partman undo_partition_time')); 1355IF v_adv_lock = 'false' THEN 1356 RAISE NOTICE 'undo_partition_time already running.'; 1357 RETURN 0; 1358END IF; 1359 1360SELECT partition_type 1361 , partition_interval::interval 1362 , control 1363 , jobmon 1364 , epoch 1365INTO v_type 1366 , v_partition_interval 1367 , v_control 1368 , v_jobmon 1369 , v_epoch 1370FROM @extschema@.part_config 1371WHERE parent_table = p_parent_table 1372AND (partition_type = 'time' OR partition_type = 'time-custom'); 1373 1374IF v_partition_interval IS NULL THEN 1375 RAISE EXCEPTION 'Configuration for given parent table not found: %', p_parent_table; 1376END IF; 1377 1378-- Check if any child tables are themselves partitioned or part of an inheritance tree. Prevent undo at this level if so. 1379-- Need to either lock child tables at all levels or handle the proper removal of triggers on all child tables first 1380-- before multi-level undo can be performed safely. 1381FOR v_row IN 1382 SELECT partition_schemaname, partition_tablename FROM @extschema@.show_partitions(p_parent_table) 1383LOOP 1384 SELECT count(*) INTO v_sub_count 1385 FROM pg_catalog.pg_inherits i 1386 JOIN pg_catalog.pg_class c ON i.inhparent = c.oid 1387 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 1388 WHERE c.relname = v_row.partition_tablename 1389 AND n.nspname = v_row.partition_schemaname; 1390 IF v_sub_count > 0 THEN 1391 RAISE EXCEPTION 'Child table for this parent has child table(s) itself (%). Run undo partitioning on this table or remove inheritance first to ensure all data is properly moved to parent', v_row.partition_schemaname||'.'||v_row.partition_tablename; 1392 END IF; 1393END LOOP; 1394 1395IF v_jobmon THEN 1396 SELECT nspname INTO v_jobmon_schema FROM pg_namespace n, pg_extension e WHERE e.extname = 'pg_jobmon' AND e.extnamespace = n.oid; 1397 IF v_jobmon_schema IS NOT NULL THEN 1398 SELECT current_setting('search_path') INTO v_old_search_path; 1399 EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', '@extschema@,'||v_jobmon_schema, 'false'); 1400 END IF; 1401END IF; 1402 1403IF v_jobmon_schema IS NOT NULL THEN 1404 v_job_id := add_job(format('PARTMAN UNDO PARTITIONING: %s', p_parent_table)); 1405 v_step_id := add_step(v_job_id, format('Undoing partitioning for table %s', p_parent_table)); 1406END IF; 1407 1408IF p_batch_interval IS NULL THEN 1409 p_batch_interval := v_partition_interval; 1410END IF; 1411 1412-- Stops new time partitions from being made as well as stopping child tables from being dropped if they were configured with a retention period. 1413UPDATE @extschema@.part_config SET undo_in_progress = true WHERE parent_table = p_parent_table; 1414-- Stop data going into child tables. 1415SELECT schemaname, tablename INTO v_parent_schema, v_parent_tablename FROM pg_tables WHERE schemaname ||'.'|| tablename = p_parent_table; 1416v_trig_name := @extschema@.check_name_length(p_object_name := v_parent_tablename, p_suffix := '_part_trig'); 1417v_function_name := @extschema@.check_name_length(v_parent_tablename, '_part_trig_func', FALSE); 1418 1419SELECT tgname INTO v_trig_name 1420FROM pg_catalog.pg_trigger t 1421JOIN pg_catalog.pg_class c ON t.tgrelid = c.oid 1422WHERE tgname = v_trig_name 1423AND c.relname = v_parent_tablename; 1424 1425SELECT proname INTO v_function_name FROM pg_catalog.pg_proc p JOIN pg_catalog.pg_namespace n ON p.pronamespace = n.oid WHERE n.nspname = v_parent_schema AND proname = v_function_name; 1426 1427IF v_trig_name IS NOT NULL THEN 1428 -- lockwait for trigger drop 1429 IF p_lock_wait > 0 THEN 1430 v_lock_iter := 0; 1431 WHILE v_lock_iter <= 5 LOOP 1432 v_lock_iter := v_lock_iter + 1; 1433 BEGIN 1434 EXECUTE format('LOCK TABLE ONLY %I.%I IN ACCESS EXCLUSIVE MODE NOWAIT', v_parent_schema, v_parent_tablename); 1435 v_lock_obtained := TRUE; 1436 EXCEPTION 1437 WHEN lock_not_available THEN 1438 PERFORM pg_sleep( p_lock_wait / 5.0 ); 1439 CONTINUE; 1440 END; 1441 EXIT WHEN v_lock_obtained; 1442 END LOOP; 1443 IF NOT v_lock_obtained THEN 1444 RAISE NOTICE 'Unable to obtain lock on parent table to remove trigger'; 1445 RETURN -1; 1446 END IF; 1447 END IF; -- END p_lock_wait IF 1448 EXECUTE format('DROP TRIGGER IF EXISTS %I ON %I.%I', v_trig_name, v_parent_schema, v_parent_tablename); 1449END IF; -- END trigger IF 1450v_lock_obtained := FALSE; -- reset for reuse later 1451 1452IF v_function_name IS NOT NULL THEN 1453 EXECUTE format('DROP FUNCTION IF EXISTS %I.%I()', v_parent_schema, v_function_name); 1454END IF; 1455 1456IF v_jobmon_schema IS NOT NULL THEN 1457 IF (v_trig_name IS NOT NULL OR v_function_name IS NOT NULL) THEN 1458 PERFORM update_step(v_step_id, 'OK', 'Stopped partition creation process. Removed trigger & trigger function'); 1459 ELSE 1460 PERFORM update_step(v_step_id, 'OK', 'Stopped partition creation process.'); 1461 END IF; 1462END IF; 1463 1464<<outer_child_loop>> 1465LOOP 1466 SELECT partition_tablename INTO v_child_table FROM @extschema@.show_partitions(p_parent_table, 'ASC'); 1467 1468 EXIT outer_child_loop WHEN v_child_table IS NULL; 1469 1470 IF v_jobmon_schema IS NOT NULL THEN 1471 v_step_id := add_step(v_job_id, format('Removing child partition: %s.%s', v_parent_schema, v_child_table)); 1472 END IF; 1473 1474 IF v_epoch = false THEN 1475 EXECUTE format('SELECT min(%I) FROM %I.%I', v_control, v_parent_schema, v_child_table) INTO v_child_min; 1476 ELSE 1477 EXECUTE format('SELECT to_timestamp(min(%I)) FROM %I.%I', v_control, v_parent_schema, v_child_table) INTO v_child_min; 1478 END IF; 1479 IF v_child_min IS NULL THEN 1480 -- No rows left in this child table. Remove from partition set. 1481 1482 -- lockwait timeout for table drop 1483 IF p_lock_wait > 0 THEN 1484 v_lock_iter := 0; 1485 WHILE v_lock_iter <= 5 LOOP 1486 v_lock_iter := v_lock_iter + 1; 1487 BEGIN 1488 EXECUTE format('LOCK TABLE ONLY %I.%I IN ACCESS EXCLUSIVE MODE NOWAIT', v_parent_schema, v_child_table); 1489 v_lock_obtained := TRUE; 1490 EXCEPTION 1491 WHEN lock_not_available THEN 1492 PERFORM pg_sleep( p_lock_wait / 5.0 ); 1493 CONTINUE; 1494 END; 1495 EXIT WHEN v_lock_obtained; 1496 END LOOP; 1497 IF NOT v_lock_obtained THEN 1498 RAISE NOTICE 'Unable to obtain lock on child table for removal from partition set'; 1499 RETURN -1; 1500 END IF; 1501 END IF; -- END p_lock_wait IF 1502 v_lock_obtained := FALSE; -- reset for reuse later 1503 1504 EXECUTE format('ALTER TABLE %I.%I NO INHERIT %I.%I' 1505 , v_parent_schema 1506 , v_child_table 1507 , v_parent_schema 1508 , v_parent_tablename); 1509 IF p_keep_table = false THEN 1510 EXECUTE format('DROP TABLE %I.%I', v_parent_schema, v_child_table); 1511 IF v_jobmon_schema IS NOT NULL THEN 1512 PERFORM update_step(v_step_id, 'OK', format('Child table DROPPED. Moved %s rows to parent', v_child_loop_total)); 1513 END IF; 1514 ELSE 1515 IF v_jobmon_schema IS NOT NULL THEN 1516 PERFORM update_step(v_step_id, 'OK', format('Child table UNINHERITED, not DROPPED. Moved %s rows to parent', v_child_loop_total)); 1517 END IF; 1518 END IF; 1519 IF v_type = 'time-custom' THEN 1520 DELETE FROM @extschema@.custom_time_partitions WHERE parent_table = p_parent_table AND child_table = v_parent_schema||'.'||v_child_table; 1521 END IF; 1522 v_undo_count := v_undo_count + 1; 1523 EXIT outer_child_loop WHEN v_batch_loop_count >= p_batch_count; -- Exit outer FOR loop if p_batch_count is reached 1524 CONTINUE outer_child_loop; -- skip data moving steps below 1525 END IF; 1526 v_inner_loop_count := 1; 1527 v_child_loop_total := 0; 1528 <<inner_child_loop>> 1529 LOOP 1530 -- do some locking with timeout, if required 1531 IF p_lock_wait > 0 THEN 1532 v_lock_iter := 0; 1533 WHILE v_lock_iter <= 5 LOOP 1534 v_lock_iter := v_lock_iter + 1; 1535 BEGIN 1536 EXECUTE format('SELECT * FROM %I.%I WHERE %I <= %L FOR UPDATE NOWAIT' 1537 , v_parent_schema 1538 , v_child_table 1539 , v_control 1540 , v_child_min + (p_batch_interval * v_inner_loop_count)); 1541 v_lock_obtained := TRUE; 1542 EXCEPTION 1543 WHEN lock_not_available THEN 1544 PERFORM pg_sleep( p_lock_wait / 5.0 ); 1545 CONTINUE; 1546 END; 1547 EXIT WHEN v_lock_obtained; 1548 END LOOP; 1549 IF NOT v_lock_obtained THEN 1550 RAISE NOTICE 'Unable to obtain lock on batch of rows to move'; 1551 RETURN -1; 1552 END IF; 1553 END IF; 1554 1555 -- Get everything from the current child minimum up to the multiples of the given interval 1556 IF v_epoch = false THEN 1557 v_move_sql := format('WITH move_data AS ( 1558 DELETE FROM %I.%I WHERE %I <= %L RETURNING *) 1559 INSERT INTO %I.%I SELECT * FROM move_data' 1560 , v_parent_schema 1561 , v_child_table 1562 , v_control 1563 , v_child_min + (p_batch_interval * v_inner_loop_count) 1564 , v_parent_schema 1565 , v_parent_tablename); 1566 ELSE 1567 v_move_sql := format('WITH move_data AS ( 1568 DELETE FROM %I.%I WHERE to_timestamp(%I) <= %L RETURNING *) 1569 INSERT INTO %I.%I SELECT * FROM move_data' 1570 , v_parent_schema 1571 , v_child_table 1572 , v_control 1573 , v_child_min + (p_batch_interval * v_inner_loop_count) 1574 , v_parent_schema 1575 , v_parent_tablename); 1576 END IF; 1577 EXECUTE v_move_sql; 1578 GET DIAGNOSTICS v_rowcount = ROW_COUNT; 1579 v_total := v_total + v_rowcount; 1580 v_child_loop_total := v_child_loop_total + v_rowcount; 1581 IF v_jobmon_schema IS NOT NULL THEN 1582 PERFORM update_step(v_step_id, 'OK', format('Moved %s rows to parent.', v_child_loop_total)); 1583 END IF; 1584 EXIT inner_child_loop WHEN v_rowcount = 0; -- exit before loop incr if table is empty 1585 v_inner_loop_count := v_inner_loop_count + 1; 1586 v_batch_loop_count := v_batch_loop_count + 1; 1587 1588 -- Check again if table is empty and go to outer loop again to drop it if so 1589 IF v_epoch = false THEN 1590 EXECUTE format('SELECT min(%I) FROM %I.%I', v_control, v_parent_schema, v_child_table) INTO v_child_min; 1591 ELSE 1592 EXECUTE format('SELECT to_timestamp(min(%I)) FROM %I.%I', v_control, v_parent_schema, v_child_table) INTO v_child_min; 1593 END IF; 1594 CONTINUE outer_child_loop WHEN v_child_min IS NULL; 1595 1596 EXIT outer_child_loop WHEN v_batch_loop_count >= p_batch_count; -- Exit outer FOR loop if p_batch_count is reached 1597 END LOOP inner_child_loop; 1598END LOOP outer_child_loop; 1599 1600SELECT partition_tablename INTO v_child_table FROM @extschema@.show_partitions(p_parent_table, 'ASC') LIMIT 1; 1601IF v_child_table IS NULL THEN 1602 DELETE FROM @extschema@.part_config WHERE parent_table = p_parent_table; 1603 IF v_jobmon_schema IS NOT NULL THEN 1604 v_step_id := add_step(v_job_id, 'Removing config from pg_partman'); 1605 PERFORM update_step(v_step_id, 'OK', 'Done'); 1606 END IF; 1607END IF; 1608 1609RAISE NOTICE 'Copied % row(s) to the parent. Removed % partitions.', v_total, v_undo_count; 1610IF v_jobmon_schema IS NOT NULL THEN 1611 v_step_id := add_step(v_job_id, 'Final stats'); 1612 PERFORM update_step(v_step_id, 'OK', format('Copied %s row(s) to the parent. Removed %s partitions.', v_total, v_undo_count)); 1613END IF; 1614 1615IF v_jobmon_schema IS NOT NULL THEN 1616 PERFORM close_job(v_job_id); 1617 EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false'); 1618END IF; 1619 1620RETURN v_total; 1621 1622EXCEPTION 1623 WHEN OTHERS THEN 1624 GET STACKED DIAGNOSTICS ex_message = MESSAGE_TEXT, 1625 ex_context = PG_EXCEPTION_CONTEXT, 1626 ex_detail = PG_EXCEPTION_DETAIL, 1627 ex_hint = PG_EXCEPTION_HINT; 1628 IF v_jobmon_schema IS NOT NULL THEN 1629 IF v_job_id IS NULL THEN 1630 EXECUTE format('SELECT %I.add_job(''PARTMAN UNDO PARTITIONING: %s'')', v_jobmon_schema, p_parent_table) INTO v_job_id; 1631 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; 1632 ELSIF v_step_id IS NULL THEN 1633 EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before first step logged'')', v_jobmon_schema, v_job_id) INTO v_step_id; 1634 END IF; 1635 EXECUTE format('SELECT %I.update_step(%s, ''CRITICAL'', %L)', v_jobmon_schema, v_step_id, 'ERROR: '||coalesce(SQLERRM,'unknown')); 1636 EXECUTE format('SELECT %I.fail_job(%s)', v_jobmon_schema, v_job_id); 1637 END IF; 1638 RAISE EXCEPTION '% 1639CONTEXT: % 1640DETAIL: % 1641HINT: %', ex_message, ex_context, ex_detail, ex_hint; 1642END 1643$$; 1644 1645 1646/* 1647 * Function to undo id-based partitioning created by this extension 1648 */ 1649CREATE 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, p_lock_wait numeric DEFAULT 0) RETURNS bigint 1650 LANGUAGE plpgsql SECURITY DEFINER 1651 AS $$ 1652DECLARE 1653 1654ex_context text; 1655ex_detail text; 1656ex_hint text; 1657ex_message text; 1658v_adv_lock boolean; 1659v_batch_loop_count int := 0; 1660v_child_loop_total bigint := 0; 1661v_child_min bigint; 1662v_child_table text; 1663v_control text; 1664v_exists int; 1665v_function_name text; 1666v_inner_loop_count int; 1667v_job_id bigint; 1668v_jobmon boolean; 1669v_jobmon_schema text; 1670v_lock_iter int := 1; 1671v_lock_obtained boolean := FALSE; 1672v_move_sql text; 1673v_old_search_path text; 1674v_parent_schema text; 1675v_parent_tablename text; 1676v_partition_interval bigint; 1677v_row record; 1678v_rowcount bigint; 1679v_step_id bigint; 1680v_sub_count int; 1681v_trig_name text; 1682v_total bigint := 0; 1683v_undo_count int := 0; 1684 1685BEGIN 1686 1687v_adv_lock := pg_try_advisory_xact_lock(hashtext('pg_partman undo_partition_id')); 1688IF v_adv_lock = 'false' THEN 1689 RAISE NOTICE 'undo_partition_id already running.'; 1690 RETURN 0; 1691END IF; 1692 1693SELECT partition_interval::bigint 1694 , control 1695 , jobmon 1696INTO v_partition_interval 1697 , v_control 1698 , v_jobmon 1699FROM @extschema@.part_config 1700WHERE parent_table = p_parent_table 1701AND partition_type = 'id'; 1702 1703IF v_partition_interval IS NULL THEN 1704 RAISE EXCEPTION 'Configuration for given parent table not found: %', p_parent_table; 1705END IF; 1706 1707-- Check if any child tables are themselves partitioned or part of an inheritance tree. Prevent undo at this level if so. 1708-- Need to either lock child tables at all levels or handle the proper removal of triggers on all child tables first 1709-- before multi-level undo can be performed safely. 1710FOR v_row IN 1711 SELECT partition_schemaname, partition_tablename FROM @extschema@.show_partitions(p_parent_table) 1712LOOP 1713 SELECT count(*) INTO v_sub_count 1714 FROM pg_catalog.pg_inherits i 1715 JOIN pg_catalog.pg_class c ON i.inhparent = c.oid 1716 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 1717 WHERE c.relname = v_row.partition_tablename 1718 AND n.nspname = v_row.partition_schemaname; 1719 IF v_sub_count > 0 THEN 1720 RAISE EXCEPTION 'Child table for this parent has child table(s) itself (%). Run undo partitioning on this table or remove inheritance first to ensure all data is properly moved to parent', v_row.partition_schemaname||'.'||v_row.partition_tablename; 1721 END IF; 1722END LOOP; 1723 1724IF v_jobmon THEN 1725 SELECT nspname INTO v_jobmon_schema FROM pg_namespace n, pg_extension e WHERE e.extname = 'pg_jobmon' AND e.extnamespace = n.oid; 1726 IF v_jobmon_schema IS NOT NULL THEN 1727 SELECT current_setting('search_path') INTO v_old_search_path; 1728 EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', '@extschema@,'||v_jobmon_schema, 'false'); 1729 END IF; 1730END IF; 1731 1732IF v_jobmon_schema IS NOT NULL THEN 1733 v_job_id := add_job(format('PARTMAN UNDO PARTITIONING: %s', p_parent_table)); 1734 v_step_id := add_step(v_job_id, format('Undoing partitioning for table %s', p_parent_table)); 1735END IF; 1736 1737IF p_batch_interval IS NULL THEN 1738 p_batch_interval := v_partition_interval; 1739END IF; 1740 1741-- Stops new time partitions from being made as well as stopping child tables from being dropped if they were configured with a retention period. 1742UPDATE @extschema@.part_config SET undo_in_progress = true WHERE parent_table = p_parent_table; 1743-- Stop data going into child tables and stop new id partitions from being made. 1744SELECT schemaname, tablename INTO v_parent_schema, v_parent_tablename FROM pg_catalog.pg_tables WHERE schemaname ||'.'|| tablename = p_parent_table; 1745v_trig_name := @extschema@.check_name_length(p_object_name := v_parent_tablename, p_suffix := '_part_trig'); 1746v_function_name := @extschema@.check_name_length(v_parent_tablename, '_part_trig_func', FALSE); 1747 1748SELECT tgname INTO v_trig_name 1749FROM pg_catalog.pg_trigger t 1750JOIN pg_catalog.pg_class c ON t.tgrelid = c.oid 1751WHERE tgname = v_trig_name 1752AND c.relname = v_parent_tablename; 1753 1754SELECT proname INTO v_function_name FROM pg_catalog.pg_proc p JOIN pg_catalog.pg_namespace n ON p.pronamespace = n.oid WHERE n.nspname = v_parent_schema AND proname = v_function_name; 1755 1756IF v_trig_name IS NOT NULL THEN 1757 -- lockwait for trigger drop 1758 IF p_lock_wait > 0 THEN 1759 v_lock_iter := 0; 1760 WHILE v_lock_iter <= 5 LOOP 1761 v_lock_iter := v_lock_iter + 1; 1762 BEGIN 1763 EXECUTE format('LOCK TABLE ONLY %I.%I IN ACCESS EXCLUSIVE MODE NOWAIT', v_parent_schema, v_parent_tablename); 1764 v_lock_obtained := TRUE; 1765 EXCEPTION 1766 WHEN lock_not_available THEN 1767 PERFORM pg_sleep( p_lock_wait / 5.0 ); 1768 CONTINUE; 1769 END; 1770 EXIT WHEN v_lock_obtained; 1771 END LOOP; 1772 IF NOT v_lock_obtained THEN 1773 RAISE NOTICE 'Unable to obtain lock on parent table to remove trigger'; 1774 RETURN -1; 1775 END IF; 1776 END IF; -- END p_lock_wait IF 1777 EXECUTE format('DROP TRIGGER IF EXISTS %I ON %I.%I', v_trig_name, v_parent_schema, v_parent_tablename); 1778END IF; -- END trigger IF 1779v_lock_obtained := FALSE; -- reset for reuse later 1780 1781IF v_function_name IS NOT NULL THEN 1782 EXECUTE format('DROP FUNCTION IF EXISTS %I.%I()', v_parent_schema, v_function_name); 1783END IF; 1784 1785IF v_jobmon_schema IS NOT NULL THEN 1786 IF (v_trig_name IS NOT NULL OR v_function_name IS NOT NULL) THEN 1787 PERFORM update_step(v_step_id, 'OK', 'Stopped partition creation process. Removed trigger & trigger function'); 1788 ELSE 1789 PERFORM update_step(v_step_id, 'OK', 'Stopped partition creation process.'); 1790 END IF; 1791END IF; 1792 1793<<outer_child_loop>> 1794LOOP 1795 -- Get ordered list of child table in set. Store in variable one at a time per loop until none are left or batch count is reached. 1796 -- This easily allows it to loop over same child table until empty or move onto next child table after it's dropped 1797 SELECT partition_tablename INTO v_child_table FROM @extschema@.show_partitions(p_parent_table, 'ASC'); 1798 1799 EXIT WHEN v_child_table IS NULL; 1800 1801 IF v_jobmon_schema IS NOT NULL THEN 1802 v_step_id := add_step(v_job_id, format('Removing child partition: %s.%s', v_parent_schema, v_child_table)); 1803 END IF; 1804 1805 EXECUTE format('SELECT min(%I) FROM %I.%I', v_control, v_parent_schema, v_child_table) INTO v_child_min; 1806 IF v_child_min IS NULL THEN 1807 -- No rows left in this child table. Remove from partition set. 1808 1809 -- lockwait timeout for table drop 1810 IF p_lock_wait > 0 THEN 1811 v_lock_iter := 0; 1812 WHILE v_lock_iter <= 5 LOOP 1813 v_lock_iter := v_lock_iter + 1; 1814 BEGIN 1815 EXECUTE format('LOCK TABLE ONLY %I.%I IN ACCESS EXCLUSIVE MODE NOWAIT', v_parent_schema, v_child_table); 1816 v_lock_obtained := TRUE; 1817 EXCEPTION 1818 WHEN lock_not_available THEN 1819 PERFORM pg_sleep( p_lock_wait / 5.0 ); 1820 CONTINUE; 1821 END; 1822 EXIT WHEN v_lock_obtained; 1823 END LOOP; 1824 IF NOT v_lock_obtained THEN 1825 RAISE NOTICE 'Unable to obtain lock on child table for removal from partition set'; 1826 RETURN -1; 1827 END IF; 1828 END IF; -- END p_lock_wait IF 1829 v_lock_obtained := FALSE; -- reset for reuse later 1830 1831 EXECUTE format('ALTER TABLE %I.%I NO INHERIT %I.%I' 1832 , v_parent_schema 1833 , v_child_table 1834 , v_parent_schema 1835 , v_parent_tablename); 1836 IF p_keep_table = false THEN 1837 EXECUTE format('DROP TABLE %I.%I', v_parent_schema, v_child_table); 1838 IF v_jobmon_schema IS NOT NULL THEN 1839 PERFORM update_step(v_step_id, 'OK', format('Child table DROPPED. Moved %s rows to parent', v_child_loop_total)); 1840 END IF; 1841 ELSE 1842 IF v_jobmon_schema IS NOT NULL THEN 1843 PERFORM update_step(v_step_id, 'OK', format('Child table UNINHERITED, not DROPPED. Moved %s rows to parent', v_child_loop_total)); 1844 END IF; 1845 END IF; 1846 v_undo_count := v_undo_count + 1; 1847 EXIT outer_child_loop WHEN v_batch_loop_count >= p_batch_count; -- Exit outer FOR loop if p_batch_count is reached 1848 CONTINUE outer_child_loop; -- skip data moving steps below 1849 END IF; 1850 v_inner_loop_count := 1; 1851 v_child_loop_total := 0; 1852 <<inner_child_loop>> 1853 LOOP 1854 -- lockwait timeout for row batches 1855 IF p_lock_wait > 0 THEN 1856 v_lock_iter := 0; 1857 WHILE v_lock_iter <= 5 LOOP 1858 v_lock_iter := v_lock_iter + 1; 1859 BEGIN 1860 EXECUTE format('SELECT * FROM %I.%I WHERE %I <= %s FOR UPDATE NOWAIT' 1861 , v_parent_schema 1862 , v_child_table 1863 , v_control 1864 , v_child_min + (p_batch_interval * v_inner_loop_count)); 1865 v_lock_obtained := TRUE; 1866 EXCEPTION 1867 WHEN lock_not_available THEN 1868 PERFORM pg_sleep( p_lock_wait / 5.0 ); 1869 CONTINUE; 1870 END; 1871 EXIT WHEN v_lock_obtained; 1872 END LOOP; 1873 IF NOT v_lock_obtained THEN 1874 RAISE NOTICE 'Unable to obtain lock on batch of rows to move'; 1875 RETURN -1; 1876 END IF; 1877 END IF; 1878 1879 -- Get everything from the current child minimum up to the multiples of the given interval 1880 v_move_sql := format('WITH move_data AS ( 1881 DELETE FROM %I.%I WHERE %I <= %s RETURNING *) 1882 INSERT INTO %I.%I SELECT * FROM move_data' 1883 , v_parent_schema 1884 , v_child_table 1885 , v_control 1886 , v_child_min + (p_batch_interval * v_inner_loop_count) 1887 , v_parent_schema 1888 , v_parent_tablename); 1889 EXECUTE v_move_sql; 1890 GET DIAGNOSTICS v_rowcount = ROW_COUNT; 1891 v_total := v_total + v_rowcount; 1892 v_child_loop_total := v_child_loop_total + v_rowcount; 1893 IF v_jobmon_schema IS NOT NULL THEN 1894 PERFORM update_step(v_step_id, 'OK', format('Moved %s rows to parent.', v_child_loop_total)); 1895 END IF; 1896 EXIT inner_child_loop WHEN v_rowcount = 0; -- exit before loop incr if table is empty 1897 v_inner_loop_count := v_inner_loop_count + 1; 1898 v_batch_loop_count := v_batch_loop_count + 1; 1899 1900 -- Check again if table is empty and go to outer loop again to drop it if so 1901 EXECUTE format('SELECT min(%I) FROM %I.%I', v_control, v_parent_schema, v_child_table) INTO v_child_min; 1902 CONTINUE outer_child_loop WHEN v_child_min IS NULL; 1903 1904 EXIT outer_child_loop WHEN v_batch_loop_count >= p_batch_count; -- Exit outer FOR loop if p_batch_count is reached 1905 END LOOP inner_child_loop; 1906END LOOP outer_child_loop; 1907 1908SELECT partition_tablename INTO v_child_table FROM @extschema@.show_partitions(p_parent_table, 'ASC') LIMIT 1; 1909IF v_child_table IS NULL THEN 1910 DELETE FROM @extschema@.part_config WHERE parent_table = p_parent_table; 1911 IF v_jobmon_schema IS NOT NULL THEN 1912 v_step_id := add_step(v_job_id, 'Removing config from pg_partman'); 1913 PERFORM update_step(v_step_id, 'OK', 'Done'); 1914 END IF; 1915END IF; 1916 1917RAISE NOTICE 'Copied % row(s) to the parent. Removed % partitions.', v_total, v_undo_count; 1918IF v_jobmon_schema IS NOT NULL THEN 1919 v_step_id := add_step(v_job_id, 'Final stats'); 1920 PERFORM update_step(v_step_id, 'OK', format('Copied %s row(s) to the parent. Removed %s partitions.', v_total, v_undo_count)); 1921END IF; 1922 1923IF v_jobmon_schema IS NOT NULL THEN 1924 PERFORM close_job(v_job_id); 1925 EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false'); 1926END IF; 1927 1928RETURN v_total; 1929 1930EXCEPTION 1931 WHEN OTHERS THEN 1932 GET STACKED DIAGNOSTICS ex_message = MESSAGE_TEXT, 1933 ex_context = PG_EXCEPTION_CONTEXT, 1934 ex_detail = PG_EXCEPTION_DETAIL, 1935 ex_hint = PG_EXCEPTION_HINT; 1936 IF v_jobmon_schema IS NOT NULL THEN 1937 IF v_job_id IS NULL THEN 1938 EXECUTE format('SELECT %I.add_job(''PARTMAN UNDO PARTITIONING: %s'')', v_jobmon_schema, p_parent_table) INTO v_job_id; 1939 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; 1940 ELSIF v_step_id IS NULL THEN 1941 EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before first step logged'')', v_jobmon_schema, v_job_id) INTO v_step_id; 1942 END IF; 1943 EXECUTE format('SELECT %I.update_step(%s, ''CRITICAL'', %L)', v_jobmon_schema, v_step_id, 'ERROR: '||coalesce(SQLERRM,'unknown')); 1944 EXECUTE format('SELECT %I.fail_job(%s)', v_jobmon_schema, v_job_id); 1945 END IF; 1946 RAISE EXCEPTION '% 1947CONTEXT: % 1948DETAIL: % 1949HINT: %', ex_message, ex_context, ex_detail, ex_hint; 1950END 1951$$; 1952 1953