1-- Fixed issue where additional constraints would not get applied on all older partitions if more than one new partition was made for a single partition set in a single maintenance run. You can fix this by using the reapply_constraints.py script to remove then reapply all additional constraints on older tables. Or if you know the specific children that are missing a constraint, you can run apply_constraints() and pass it the child table argument. 2 3-- Improved performance of create_parent() when p_start_partition is set. Was previously doing a full seq scan of the parent table which could keep it locked longer than intended (Github Pull Reqest #99). 4 5-- New function that returns partition data about a given child name: show_partition_info(). 6 -- Returns start/end values that are contained in a given child table. 7 -- Can pass an optional interval value to see start/end values that are different than the currently configured interval. 8 9-- Added new options to vacuum_maintenance.py script 10 -- New --all option tells it to run against all tables managed by pg_partman. 11 -- New --type option sets whether to run against time or id based partition sets managed by pg_partman. 12 -- New --interval option tells the script to only run against child tables older than the given interval. See the script's --help for more info on how this works. 13 -- All these options only work on partition sets managed by pg_partman. If none of these options are set, script still works on non-pg_partman inheritance sets. 14 15-- Disallow weekly partitioning to be a subpartition of anything else. Alignment of ISO weeks does not always match up with larger intervals and can cause constraint conflicts. 16-- Also disallow subpartition interval to be equal to or greater than parent. 17-- Set part_config_sub & custom_time_partition config tables to dump their contents with pg_dump to ensure all config data is saved. 18 19CREATE TEMP TABLE partman_preserve_privs_temp (statement text); 20 21INSERT INTO partman_preserve_privs_temp 22SELECT 'GRANT EXECUTE ON FUNCTION @extschema@.create_partition_id(text, bigint[], boolean, boolean) TO '||array_to_string(array_agg(grantee::text), ',')||';' 23FROM information_schema.routine_privileges 24WHERE routine_schema = '@extschema@' 25AND routine_name = 'create_partition_id'; 26 27INSERT INTO partman_preserve_privs_temp 28SELECT 'GRANT EXECUTE ON FUNCTION @extschema@.create_partition_time(text, timestamp[], boolean, boolean) TO '||array_to_string(array_agg(grantee::text), ',')||';' 29FROM information_schema.routine_privileges 30WHERE routine_schema = '@extschema@' 31AND routine_name = 'create_partition_time'; 32 33DROP FUNCTION @extschema@.create_partition_id(text, bigint[], boolean); 34DROP FUNCTION @extschema@.create_partition_time(text, timestamp[], boolean); 35 36SELECT pg_catalog.pg_extension_config_dump('part_config_sub', ''); 37SELECT pg_catalog.pg_extension_config_dump('custom_time_partitions', ''); 38 39CREATE FUNCTION show_partition_info(p_child_table text 40 , p_partition_interval text DEFAULT NULL 41 , p_parent_table text DEFAULT NULL 42 , OUT child_start_time timestamp 43 , OUT child_end_time timestamp 44 , OUT child_start_id bigint 45 , OUT child_end_id bigint 46 , OUT suffix text) 47RETURNS record 48 LANGUAGE plpgsql SECURITY DEFINER 49 AS $$ 50DECLARE 51 52v_child_schema text; 53v_child_tablename text; 54v_datetime_string text; 55v_parent_table text; 56v_partition_interval text; 57v_partition_type text; 58v_quarter text; 59v_suffix text; 60v_suffix_position int; 61v_year text; 62 63BEGIN 64 65SELECT schemaname, tablename INTO v_child_schema, v_child_tablename 66FROM pg_catalog.pg_tables 67WHERE schemaname = split_part(p_child_table, '.', 1) 68AND tablename = split_part(p_child_table, '.', 2); 69IF v_child_tablename IS NULL THEN 70 RAISE EXCEPTION 'Child table given does not exist (%)', p_child_table; 71END IF; 72 73IF p_parent_table IS NULL THEN 74 SELECT n.nspname||'.'|| c.relname INTO v_parent_table 75 FROM pg_catalog.pg_inherits h 76 JOIN pg_catalog.pg_class c ON c.oid = h.inhparent 77 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 78 WHERE h.inhrelid::regclass = p_child_table::regclass; 79ELSE 80 v_parent_table := p_parent_table; 81END IF; 82 83IF p_partition_interval IS NULL THEN 84 SELECT partition_interval, partition_type, datetime_string INTO v_partition_interval, v_partition_type, v_datetime_string 85 FROM @extschema@.part_config WHERE parent_table = v_parent_table; 86ELSE 87 v_partition_interval := p_partition_interval; 88 SELECT partition_type, datetime_string INTO v_partition_type, v_datetime_string 89 FROM @extschema@.part_config WHERE parent_table = v_parent_table; 90END IF; 91 92IF v_partition_type IS NULL THEN 93 RAISE EXCEPTION 'Parent table of given child not managed by pg_partman: %', v_parent_table; 94END IF; 95 96v_suffix_position := (length(v_child_tablename) - position('p_' in reverse(v_child_tablename))) + 2; 97v_suffix := substring(v_child_tablename from v_suffix_position); 98 99IF v_partition_type = 'time' OR v_partition_type = 'time-custom' THEN 100 101 IF v_partition_interval::interval <> '3 months' OR (v_partition_interval::interval = '3 months' AND v_partition_type = 'time-custom') THEN 102 child_start_time := to_timestamp(v_suffix, v_datetime_string); 103 ELSE 104 -- to_timestamp doesn't recognize 'Q' date string formater. Handle it 105 v_year := split_part(v_suffix, 'q', 1); 106 v_quarter := split_part(v_suffix, 'q', 2); 107 CASE 108 WHEN v_quarter = '1' THEN 109 child_start_time := to_timestamp(v_year || '-01-01', 'YYYY-MM-DD'); 110 WHEN v_quarter = '2' THEN 111 child_start_time := to_timestamp(v_year || '-04-01', 'YYYY-MM-DD'); 112 WHEN v_quarter = '3' THEN 113 child_start_time := to_timestamp(v_year || '-07-01', 'YYYY-MM-DD'); 114 WHEN v_quarter = '4' THEN 115 child_start_time := to_timestamp(v_year || '-10-01', 'YYYY-MM-DD'); 116 END CASE; 117 END IF; 118 119 child_end_time := (child_start_time + v_partition_interval::interval) - '1 second'::interval; 120 121ELSIF v_partition_type = 'id' THEN 122 123 child_start_id := v_suffix::bigint; 124 child_end_id := (child_start_id + v_partition_interval::bigint) - 1; 125 126ELSE 127 RAISE EXCEPTION 'Invalid partition type encountered in show_partition_info()'; 128END IF; 129 130suffix = v_suffix; 131 132RETURN; 133 134END 135$$; 136 137 138/* 139 * Create a partition set that is a subpartition of an already existing partition set. 140 * Given the parent table of any current partition set, it will turn all existing children into parent tables of their own partition sets 141 * using the configuration options given as parameters to this function. 142 * Uses another config table that allows for turning all future child partitions into a new parent automatically. 143 * To avoid logical complications and contention issues, ALL subpartitions must be maintained using run_maintenance(). 144 * This means the automatic, trigger based partition creation for serial partitioning will not work if it is a subpartition. 145 */ 146CREATE OR REPLACE FUNCTION create_sub_parent( 147 p_top_parent text 148 , p_control text 149 , p_type text 150 , p_interval text 151 , p_constraint_cols text[] DEFAULT NULL 152 , p_premake int DEFAULT 4 153 , p_start_partition text DEFAULT NULL 154 , p_inherit_fk boolean DEFAULT true 155 , p_epoch boolean DEFAULT false 156 , p_jobmon boolean DEFAULT true 157 , p_debug boolean DEFAULT false) 158RETURNS boolean 159 LANGUAGE plpgsql SECURITY DEFINER 160 AS $$ 161DECLARE 162 163v_last_partition text; 164v_parent_interval text; 165v_parent_type text; 166v_row record; 167v_row_last_part record; 168v_run_maint boolean; 169v_sql text; 170v_success boolean := false; 171v_top_type text; 172 173BEGIN 174 175SELECT use_run_maintenance INTO v_run_maint FROM @extschema@.part_config WHERE parent_table = p_top_parent; 176IF v_run_maint IS NULL THEN 177 RAISE EXCEPTION 'Cannot subpartition a table that is not managed by pg_partman already. Given top parent table not found in @extschema@.part_config: %', p_top_parent; 178ELSIF v_run_maint = false THEN 179 RAISE EXCEPTION 'Any parent table that will be part of a sub-partitioned set (on any level) must have use_run_maintenance set to true in part_config table, even for serial partitioning. See documentation for more info.'; 180END IF; 181 182FOR v_row IN 183 -- Loop through all current children to turn them into partitioned tables 184 SELECT partition_schemaname||'.'||partition_tablename AS child_table FROM @extschema@.show_partitions(p_top_parent) 185LOOP 186 SELECT partition_type, partition_interval INTO v_parent_type, v_parent_interval FROM @extschema@.part_config WHERE parent_table = v_row.child_table; 187 188 IF v_parent_interval = p_interval THEN 189 RAISE EXCEPTION 'Sub-partition interval cannot be equal to parent interval'; 190 END IF; 191 192 IF (v_parent_type = 'time' OR v_parent_type = 'time-custom') 193 AND (p_type = 'time' OR p_type = 'time-custom') 194 THEN 195 IF p_interval::interval > v_parent_interval::interval THEN 196 RAISE EXCEPTION 'Sub-partition interval cannot be greater than the given parent interval'; 197 END IF; 198 IF p_interval = 'weekly' AND v_parent_interval::interval > '1 week'::interval THEN 199 RAISE EXCEPTION 'Due to conflicting data boundaries between ISO weeks and any larger interval of time, pg_partman cannot support a sub-partition interval of weekly'; 200 END IF; 201 ELSIF v_parent_type = 'id' THEN 202 IF p_interval::bigint > v_parent_interval::bigint THEN 203 RAISE EXCEPTION 'Sub-partition interval cannot be greater than the given parent interval'; 204 END IF; 205 END IF; 206 207 -- Just call existing create_parent() function but add the given parameters to the part_config_sub table as well 208 v_sql := format('SELECT @extschema@.create_parent( 209 p_parent_table := %L 210 , p_control := %L 211 , p_type := %L 212 , p_interval := %L 213 , p_constraint_cols := %L 214 , p_premake := %L 215 , p_use_run_maintenance := %L 216 , p_start_partition := %L 217 , p_inherit_fk := %L 218 , p_epoch := %L 219 , p_jobmon := %L 220 , p_debug := %L )' 221 , v_row.child_table 222 , p_control 223 , p_type 224 , p_interval 225 , p_constraint_cols 226 , p_premake 227 , true 228 , p_start_partition 229 , p_inherit_fk 230 , p_epoch 231 , p_jobmon 232 , p_debug); 233 EXECUTE v_sql; 234 235END LOOP; 236 237INSERT INTO @extschema@.part_config_sub ( 238 sub_parent 239 , sub_control 240 , sub_partition_type 241 , sub_partition_interval 242 , sub_constraint_cols 243 , sub_premake 244 , sub_inherit_fk 245 , sub_use_run_maintenance 246 , sub_epoch 247 , sub_jobmon) 248VALUES ( 249 p_top_parent 250 , p_control 251 , p_type 252 , p_interval 253 , p_constraint_cols 254 , p_premake 255 , p_inherit_fk 256 , true 257 , p_epoch 258 , p_jobmon); 259 260v_success := true; 261 262RETURN v_success; 263 264END 265$$; 266 267 268/* 269 * Function to manage pre-creation of the next partitions in a set. 270 * Also manages dropping old partitions if the retention option is set. 271 * If p_parent_table is passed, will only run run_maintenance() on that one table (no matter what the configuration table may have set for it) 272 * Otherwise, will run on all tables in the config table with p_run_maintenance() set to true. 273 * For large partition sets, running analyze can cause maintenance to take longer than expected. Can set p_analyze to false to avoid a forced analyze run. 274 * Be aware that constraint exclusion may not work properly until an analyze on the partition set is run. 275 */ 276CREATE OR REPLACE FUNCTION run_maintenance(p_parent_table text DEFAULT NULL, p_analyze boolean DEFAULT true, p_jobmon boolean DEFAULT true, p_debug boolean DEFAULT false) RETURNS void 277 LANGUAGE plpgsql SECURITY DEFINER 278 AS $$ 279DECLARE 280 281ex_context text; 282ex_detail text; 283ex_hint text; 284ex_message text; 285v_adv_lock boolean; 286v_check_subpart int; 287v_create_count int := 0; 288v_current_partition text; 289v_current_partition_id bigint; 290v_current_partition_timestamp timestamp; 291v_datetime_string text; 292v_drop_count int := 0; 293v_job_id bigint; 294v_jobmon boolean; 295v_jobmon_schema text; 296v_last_partition text; 297v_last_partition_created boolean; 298v_last_partition_id bigint; 299v_last_partition_timestamp timestamp; 300v_max_id_parent bigint; 301v_max_time_parent timestamp; 302v_next_partition_id bigint; 303v_next_partition_timestamp timestamp; 304v_parent_schema text; 305v_parent_tablename text; 306v_premade_count int; 307v_premake_id_max bigint; 308v_premake_id_min bigint; 309v_premake_timestamp_min timestamp; 310v_premake_timestamp_max timestamp; 311v_row record; 312v_row_max_id record; 313v_row_max_time record; 314v_row_sub record; 315v_skip_maint boolean; 316v_step_id bigint; 317v_step_overflow_id bigint; 318v_step_serial_id bigint; 319v_sub_id_max bigint; 320v_sub_id_max_suffix bigint; 321v_sub_id_min bigint; 322v_sub_parent text; 323v_sub_timestamp_max timestamp; 324v_sub_timestamp_max_suffix timestamp; 325v_sub_timestamp_min timestamp; 326v_tablename text; 327v_tables_list_sql text; 328 329BEGIN 330 331v_adv_lock := pg_try_advisory_xact_lock(hashtext('pg_partman run_maintenance')); 332IF v_adv_lock = 'false' THEN 333 RAISE NOTICE 'Partman maintenance already running.'; 334 RETURN; 335END IF; 336 337IF p_jobmon THEN 338 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; 339END IF; 340 341IF v_jobmon_schema IS NOT NULL THEN 342 EXECUTE format('SELECT %I.add_job(%L)', v_jobmon_schema, 'PARTMAN RUN MAINTENANCE') INTO v_job_id; 343 EXECUTE format('SELECT %I.add_step(%L, %L)', v_jobmon_schema, v_job_id, 'Running maintenance loop') INTO v_step_id; 344END IF; 345 346-- Check for consistent data in part_config_sub table. Was unable to get this working properly as either a constraint or trigger. 347-- Would either delay raising an error until the next write (which I cannot predict) or disallow future edits to update a sub-partition set's configuration. 348-- This way at least provides a consistent way to check that I know will run. If anyone can get a working constraint/trigger, please help! 349-- Don't have to worry about this in the serial trigger maintenance since subpartitioning requires run_maintenance(). 350FOR v_row IN 351 SELECT sub_parent FROM @extschema@.part_config_sub 352LOOP 353 SELECT count(*) INTO v_check_subpart FROM @extschema@.check_subpart_sameconfig(v_row.sub_parent); 354 IF v_check_subpart > 1 THEN 355 RAISE EXCEPTION 'Inconsistent data in part_config_sub table. Sub-partition tables that are themselves sub-partitions cannot have differing configuration values among their siblings. 356 Run this query: "SELECT * FROM @extschema@.check_subpart_sameconfig(''%'');" This should only return a single row or nothing. 357 If multiple rows are returned, results are all children of the given parent. Update the differing values to be consistent for your desired values.', v_row.sub_parent; 358 END IF; 359END LOOP; 360 361v_row := NULL; -- Ensure it's reset 362 363v_tables_list_sql := 'SELECT parent_table 364 , partition_type 365 , partition_interval 366 , control 367 , premake 368 , datetime_string 369 , undo_in_progress 370 , sub_partition_set_full 371 , epoch 372 , infinite_time_partitions 373 FROM @extschema@.part_config 374 WHERE sub_partition_set_full = false'; 375 376IF p_parent_table IS NULL THEN 377 v_tables_list_sql := v_tables_list_sql || ' AND use_run_maintenance = true'; 378ELSE 379 v_tables_list_sql := v_tables_list_sql || format(' AND parent_table = %L', p_parent_table); 380END IF; 381 382FOR v_row IN EXECUTE v_tables_list_sql 383LOOP 384 385 CONTINUE WHEN v_row.undo_in_progress; 386 v_skip_maint := true; -- reset every loop 387 388 SELECT schemaname, tablename 389 INTO v_parent_schema, v_parent_tablename 390 FROM pg_catalog.pg_tables 391 WHERE schemaname = split_part(v_row.parent_table, '.', 1) 392 AND tablename = split_part(v_row.parent_table, '.', 2); 393 394 SELECT partition_tablename INTO v_last_partition FROM @extschema@.show_partitions(v_row.parent_table, 'DESC') LIMIT 1; 395 IF p_debug THEN 396 RAISE NOTICE 'run_maint: parent_table: %, v_last_partition: %', v_row.parent_table, v_last_partition; 397 END IF; 398 399 IF v_row.partition_type = 'time' OR v_row.partition_type = 'time-custom' THEN 400 401 SELECT child_start_time INTO v_last_partition_timestamp 402 FROM @extschema@.show_partition_info(v_parent_schema||'.'||v_last_partition, v_row.partition_interval, v_row.parent_table); 403 404 -- Loop through child tables starting from highest to get current max value in partition set 405 -- Avoids doing a scan on entire partition set and/or getting any values accidentally in parent. 406 FOR v_row_max_time IN 407 SELECT partition_schemaname, partition_tablename FROM @extschema@.show_partitions(v_row.parent_table, 'DESC') 408 LOOP 409 IF v_row.epoch = false THEN 410 EXECUTE format('SELECT max(%I)::text FROM %I.%I' 411 , v_row.control 412 , v_row_max_time.partition_schemaname 413 , v_row_max_time.partition_tablename 414 ) INTO v_current_partition_timestamp; 415 ELSE 416 EXECUTE format('SELECT to_timestamp(max(%I))::text FROM %I.%I' 417 , v_row.control 418 , v_row_max_time.partition_schemaname 419 , v_row_max_time.partition_tablename 420 ) INTO v_current_partition_timestamp; 421 END IF; 422 IF v_current_partition_timestamp IS NOT NULL THEN 423 SELECT suffix_timestamp INTO v_current_partition_timestamp FROM @extschema@.show_partition_name(v_row.parent_table, v_current_partition_timestamp::text); 424 EXIT; 425 END IF; 426 END LOOP; 427 -- Check for values in the parent table. If they are there and greater than all child values, use that instead 428 -- This allows maintenance to continue working properly if there is a large gap in data insertion. Data will remain in parent, but new tables will be created 429 IF v_row.epoch = false THEN 430 EXECUTE format('SELECT max(%I) FROM ONLY %I.%I', v_row.control, v_parent_schema, v_parent_tablename) INTO v_max_time_parent; 431 ELSE 432 EXECUTE format('SELECT to_timestamp(max(%I)) FROM ONLY %I.%I', v_row.control, v_parent_schema, v_parent_tablename) INTO v_max_time_parent; 433 END IF; 434 IF p_debug THEN 435 RAISE NOTICE 'run_maint: v_current_partition_timestamp: %, v_max_time_parent: %', v_current_partition_timestamp, v_max_time_parent; 436 END IF; 437 IF v_max_time_parent > v_current_partition_timestamp THEN 438 SELECT suffix_timestamp INTO v_current_partition_timestamp FROM @extschema@.show_partition_name(v_row.parent_table, v_max_time_parent::text); 439 END IF; 440 IF v_current_partition_timestamp IS NULL THEN -- Partition set is completely empty 441 IF v_row.infinite_time_partitions IS TRUE THEN 442 -- Set it to now so new partitions continue to be created 443 v_current_partition_timestamp = CURRENT_TIMESTAMP; 444 ELSE 445 -- Nothing to do 446 CONTINUE; 447 END IF; 448 END IF; 449 450 -- If this is a subpartition, determine if the last child table has been made. If so, mark it as full so future maintenance runs can skip it 451 SELECT sub_min::timestamp, sub_max::timestamp INTO v_sub_timestamp_min, v_sub_timestamp_max FROM @extschema@.check_subpartition_limits(v_row.parent_table, 'time'); 452 IF v_sub_timestamp_max IS NOT NULL THEN 453 SELECT suffix_timestamp INTO v_sub_timestamp_max_suffix FROM @extschema@.show_partition_name(v_row.parent_table, v_sub_timestamp_max::text); 454 IF v_sub_timestamp_max_suffix = v_last_partition_timestamp THEN 455 -- Final partition for this set is created. Set full and skip it 456 UPDATE @extschema@.part_config SET sub_partition_set_full = true WHERE parent_table = v_row.parent_table; 457 CONTINUE; 458 END IF; 459 END IF; 460 461 -- Check and see how many premade partitions there are. 462 v_premade_count = round(EXTRACT('epoch' FROM age(v_last_partition_timestamp, v_current_partition_timestamp)) / EXTRACT('epoch' FROM v_row.partition_interval::interval)); 463 v_next_partition_timestamp := v_last_partition_timestamp; 464 IF p_debug THEN 465 RAISE NOTICE 'run_maint before loop: current_partition_timestamp: %, v_premade_count: %, v_sub_timestamp_min: %, v_sub_timestamp_max: %' 466 , v_current_partition_timestamp 467 , v_premade_count 468 , v_sub_timestamp_min 469 , v_sub_timestamp_max; 470 END IF; 471 -- Loop premaking until config setting is met. Allows it to catch up if it fell behind or if premake changed 472 WHILE (v_premade_count < v_row.premake) LOOP 473 IF p_debug THEN 474 RAISE NOTICE 'run_maint: parent_table: %, v_premade_count: %, v_next_partition_timestamp: %', v_row.parent_table, v_premade_count, v_next_partition_timestamp; 475 END IF; 476 IF v_next_partition_timestamp < v_sub_timestamp_min OR v_next_partition_timestamp > v_sub_timestamp_max THEN 477 -- With subpartitioning, no need to run if the timestamp is not in the parent table's range 478 EXIT; 479 END IF; 480 BEGIN 481 v_next_partition_timestamp := v_next_partition_timestamp + v_row.partition_interval::interval; 482 EXCEPTION WHEN datetime_field_overflow THEN 483 v_premade_count := v_row.premake; -- do this so it can exit the premake check loop and continue in the outer for loop 484 IF v_jobmon_schema IS NOT NULL THEN 485 EXECUTE format('SELECT %I.add_step(%L, %L)', v_jobmon_schema, v_job_id, 'Attempted partition time interval is outside PostgreSQL''s supported time range.') INTO v_step_overflow_id; 486 EXECUTE format('SELECT %I.update_step(%L, %L, %L)', v_jobmon_schema, v_step_overflow_id, 'CRITICAL', 'Child partition creation skippd for parent table '||v_partition_time); 487 END IF; 488 RAISE WARNING 'Attempted partition time interval is outside PostgreSQL''s supported time range. Child partition creation skipped for parent table %', v_row.parent_table; 489 CONTINUE; 490 END; 491 v_last_partition_created := @extschema@.create_partition_time(v_row.parent_table, ARRAY[v_next_partition_timestamp], p_analyze); 492 IF v_last_partition_created THEN 493 v_create_count := v_create_count + 1; 494 PERFORM @extschema@.create_function_time(v_row.parent_table, v_job_id); 495 END IF; 496 497 v_premade_count = round(EXTRACT('epoch' FROM age(v_next_partition_timestamp, v_current_partition_timestamp)) / EXTRACT('epoch' FROM v_row.partition_interval::interval)); 498 END LOOP; 499 ELSIF v_row.partition_type = 'id' THEN 500 -- Loop through child tables starting from highest to get current max value in partition set 501 -- Avoids doing a scan on entire partition set and/or getting any values accidentally in parent. 502 FOR v_row_max_id IN 503 SELECT partition_schemaname, partition_tablename FROM @extschema@.show_partitions(v_row.parent_table, 'DESC') 504 LOOP 505 EXECUTE format('SELECT max(%I)::text FROM %I.%I' 506 , v_row.control 507 , v_row_max_id.partition_schemaname 508 , v_row_max_id.partition_tablename) INTO v_current_partition_id; 509 IF v_current_partition_id IS NOT NULL THEN 510 SELECT suffix_id INTO v_current_partition_id FROM @extschema@.show_partition_name(v_row.parent_table, v_current_partition_id::text); 511 EXIT; 512 END IF; 513 END LOOP; 514 -- Check for values in the parent table. If they are there and greater than all child values, use that instead 515 -- This allows maintenance to continue working properly if there is a large gap in data insertion. Data will remain in parent, but new tables will be created 516 EXECUTE format('SELECT max(%I) FROM ONLY %I.%I', v_row.control, v_parent_schema, v_parent_tablename) INTO v_max_id_parent; 517 IF v_max_id_parent > v_current_partition_id THEN 518 SELECT suffix_id INTO v_current_partition_id FROM @extschema@.show_partition_name(v_row.parent_table, v_max_id_parent::text); 519 END IF; 520 IF v_current_partition_id IS NULL THEN 521 -- Partition set is completely empty. Nothing to do 522 CONTINUE; 523 END IF; 524 525 SELECT child_start_id INTO v_last_partition_id 526 FROM @extschema@.show_partition_info(v_parent_schema||'.'||v_last_partition, v_row.partition_interval, v_row.parent_table); 527 -- Determine if this table is a child of a subpartition parent. If so, get limits to see if run_maintenance even needs to run for it. 528 SELECT sub_min::bigint, sub_max::bigint INTO v_sub_id_min, v_sub_id_max FROM @extschema@.check_subpartition_limits(v_row.parent_table, 'id'); 529 IF v_sub_id_max IS NOT NULL THEN 530 SELECT suffix_id INTO v_sub_id_max_suffix FROM @extschema@.show_partition_name(v_row.parent_table, v_sub_id_max::text); 531 IF v_sub_id_max_suffix = v_last_partition_id THEN 532 -- Final partition for this set is created. Set full and skip it 533 UPDATE @extschema@.part_config SET sub_partition_set_full = true WHERE parent_table = v_row.parent_table; 534 CONTINUE; 535 END IF; 536 END IF; 537 538 v_next_partition_id := v_last_partition_id; 539 v_premade_count := ((v_last_partition_id - v_current_partition_id) / v_row.partition_interval::bigint); 540 -- Loop premaking until config setting is met. Allows it to catch up if it fell behind or if premake changed. 541 WHILE (v_premade_count < v_row.premake) LOOP 542 IF p_debug THEN 543 RAISE NOTICE 'run_maint: parent_table: %, v_premade_count: %, v_next_partition_id: %', v_row.parent_table, v_premade_count, v_next_partition_id; 544 END IF; 545 IF v_next_partition_id < v_sub_id_min OR v_next_partition_id > v_sub_id_max THEN 546 -- With subpartitioning, no need to run if the id is not in the parent table's range 547 EXIT; 548 END IF; 549 v_next_partition_id := v_next_partition_id + v_row.partition_interval::bigint; 550 v_last_partition_created := @extschema@.create_partition_id(v_row.parent_table, ARRAY[v_next_partition_id], p_analyze); 551 IF v_last_partition_created THEN 552 v_create_count := v_create_count + 1; 553 PERFORM @extschema@.create_function_id(v_row.parent_table, v_job_id); 554 END IF; 555 v_premade_count := ((v_next_partition_id - v_current_partition_id) / v_row.partition_interval::bigint); 556 END LOOP; 557 558 END IF; -- end main IF check for time or id 559 560END LOOP; -- end of creation loop 561 562-- Manage dropping old partitions if retention option is set 563FOR v_row IN 564 SELECT parent_table FROM @extschema@.part_config WHERE retention IS NOT NULL AND undo_in_progress = false AND 565 (partition_type = 'time' OR partition_type = 'time-custom') 566LOOP 567 IF p_parent_table IS NULL THEN 568 v_drop_count := v_drop_count + @extschema@.drop_partition_time(v_row.parent_table); 569 ELSE -- Only run retention on table given in parameter 570 IF p_parent_table <> v_row.parent_table THEN 571 CONTINUE; 572 ELSE 573 v_drop_count := v_drop_count + @extschema@.drop_partition_time(v_row.parent_table); 574 END IF; 575 END IF; 576 IF v_drop_count > 0 THEN 577 PERFORM @extschema@.create_function_time(v_row.parent_table, v_job_id); 578 END IF; 579END LOOP; 580FOR v_row IN 581 SELECT parent_table FROM @extschema@.part_config WHERE retention IS NOT NULL AND undo_in_progress = false AND partition_type = 'id' 582LOOP 583 IF p_parent_table IS NULL THEN 584 v_drop_count := v_drop_count + @extschema@.drop_partition_id(v_row.parent_table); 585 ELSE -- Only run retention on table given in parameter 586 IF p_parent_table <> v_row.parent_table THEN 587 CONTINUE; 588 ELSE 589 v_drop_count := v_drop_count + @extschema@.drop_partition_id(v_row.parent_table); 590 END IF; 591 END IF; 592 IF v_drop_count > 0 THEN 593 PERFORM @extschema@.create_function_id(v_row.parent_table, v_job_id); 594 END IF; 595END LOOP; 596 597IF v_jobmon_schema IS NOT NULL THEN 598 EXECUTE format('SELECT %I.update_step(%L, %L, ''Partition maintenance finished. %s partitions made. %s partitions dropped.'')' 599 , v_jobmon_schema 600 , v_step_id 601 , 'OK' 602 , v_create_count 603 , v_drop_count); 604 IF v_step_overflow_id IS NOT NULL OR v_step_serial_id IS NOT NULL THEN 605 EXECUTE format('SELECT %I.fail_job(%L)', v_jobmon_schema, v_job_id); 606 ELSE 607 EXECUTE format('SELECT %I.close_job(%L)', v_jobmon_schema, v_job_id); 608 END IF; 609END IF; 610 611EXCEPTION 612 WHEN OTHERS THEN 613 GET STACKED DIAGNOSTICS ex_message = MESSAGE_TEXT, 614 ex_context = PG_EXCEPTION_CONTEXT, 615 ex_detail = PG_EXCEPTION_DETAIL, 616 ex_hint = PG_EXCEPTION_HINT; 617 IF v_jobmon_schema IS NOT NULL THEN 618 IF v_job_id IS NULL THEN 619 EXECUTE format('SELECT %I.add_job(''PARTMAN RUN MAINTENANCE'')', v_jobmon_schema) INTO v_job_id; 620 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; 621 ELSIF v_step_id IS NULL THEN 622 EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before first step logged'')', v_jobmon_schema, v_job_id) INTO v_step_id; 623 END IF; 624 EXECUTE format('SELECT %I.update_step(%s, ''CRITICAL'', %L)', v_jobmon_schema, v_step_id, 'ERROR: '||coalesce(SQLERRM,'unknown')); 625 EXECUTE format('SELECT %I.fail_job(%s)', v_jobmon_schema, v_job_id); 626 END IF; 627 RAISE EXCEPTION '% 628CONTEXT: % 629DETAIL: % 630HINT: %', ex_message, ex_context, ex_detail, ex_hint; 631END 632$$; 633 634 635/* 636 * Function to create id partitions 637 */ 638CREATE FUNCTION create_partition_id(p_parent_table text, p_partition_ids bigint[], p_analyze boolean DEFAULT true, p_debug boolean DEFAULT false) RETURNS boolean 639 LANGUAGE plpgsql SECURITY DEFINER 640 AS $$ 641DECLARE 642 643ex_context text; 644ex_detail text; 645ex_hint text; 646ex_message text; 647v_all text[] := ARRAY['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER']; 648v_analyze boolean := FALSE; 649v_control text; 650v_exists text; 651v_grantees text[]; 652v_hasoids boolean; 653v_id bigint; 654v_inherit_fk boolean; 655v_job_id bigint; 656v_jobmon boolean; 657v_jobmon_schema text; 658v_old_search_path text; 659v_parent_grant record; 660v_parent_owner text; 661v_parent_schema text; 662v_parent_tablename text; 663v_parent_tablespace text; 664v_partition_interval bigint; 665v_partition_created boolean := false; 666v_partition_name text; 667v_revoke text; 668v_row record; 669v_sql text; 670v_step_id bigint; 671v_sub_id_max bigint; 672v_sub_id_min bigint; 673v_unlogged char; 674 675BEGIN 676 677SELECT control 678 , partition_interval 679 , inherit_fk 680 , jobmon 681INTO v_control 682 , v_partition_interval 683 , v_inherit_fk 684 , v_jobmon 685FROM @extschema@.part_config 686WHERE parent_table = p_parent_table 687AND partition_type = 'id'; 688 689IF NOT FOUND THEN 690 RAISE EXCEPTION 'ERROR: no config found for %', p_parent_table; 691END IF; 692 693IF v_jobmon THEN 694 SELECT nspname INTO v_jobmon_schema FROM pg_namespace n, pg_extension e WHERE e.extname = 'pg_jobmon' AND e.extnamespace = n.oid; 695 IF v_jobmon_schema IS NOT NULL THEN 696 SELECT current_setting('search_path') INTO v_old_search_path; 697 EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', '@extschema@,'||v_jobmon_schema, 'false'); 698 END IF; 699END IF; 700 701-- 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 702SELECT sub_min::bigint, sub_max::bigint INTO v_sub_id_min, v_sub_id_max FROM @extschema@.check_subpartition_limits(p_parent_table, 'id'); 703 704SELECT tableowner, schemaname, tablename, tablespace 705INTO v_parent_owner, v_parent_schema, v_parent_tablename, v_parent_tablespace 706FROM pg_catalog.pg_tables 707WHERE schemaname = split_part(p_parent_table, '.', 1) 708AND tablename = split_part(p_parent_table, '.', 2); 709 710IF v_jobmon_schema IS NOT NULL THEN 711 v_job_id := add_job(format('PARTMAN CREATE TABLE: %s', p_parent_table)); 712END IF; 713 714FOREACH v_id IN ARRAY p_partition_ids LOOP 715-- Do not create the child table if it's outside the bounds of the top parent. 716 IF v_sub_id_min IS NOT NULL THEN 717 IF v_id < v_sub_id_min OR v_id > v_sub_id_max THEN 718 CONTINUE; 719 END IF; 720 END IF; 721 722 v_partition_name := @extschema@.check_name_length(v_parent_tablename, v_id::text, TRUE); 723 -- If child table already exists, skip creation 724 SELECT tablename INTO v_exists FROM pg_catalog.pg_tables WHERE schemaname = v_parent_schema AND tablename = v_partition_name; 725 IF v_exists IS NOT NULL THEN 726 CONTINUE; 727 END IF; 728 729 -- Ensure analyze is run if a new partition is created. Otherwise if one isn't, will be false and analyze will be skipped 730 v_analyze := TRUE; 731 732 IF v_jobmon_schema IS NOT NULL THEN 733 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); 734 END IF; 735 736 SELECT relpersistence INTO v_unlogged 737 FROM pg_catalog.pg_class c 738 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 739 WHERE c.relname = v_parent_tablename 740 AND n.nspname = v_parent_schema; 741 v_sql := 'CREATE'; 742 IF v_unlogged = 'u' THEN 743 v_sql := v_sql || ' UNLOGGED'; 744 END IF; 745 v_sql := v_sql || format(' TABLE %I.%I (LIKE %I.%I INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING STORAGE INCLUDING COMMENTS)' 746 , v_parent_schema 747 , v_partition_name 748 , v_parent_schema 749 , v_parent_tablename); 750 SELECT relhasoids INTO v_hasoids 751 FROM pg_catalog.pg_class c 752 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 753 WHERE c.relname = v_parent_tablename 754 AND n.nspname = v_parent_schema; 755 IF v_hasoids IS TRUE THEN 756 v_sql := v_sql || ' WITH (OIDS)'; 757 END IF; 758 EXECUTE v_sql; 759 IF v_parent_tablespace IS NOT NULL THEN 760 EXECUTE format('ALTER TABLE %I.%I SET TABLESPACE %I', v_parent_schema, v_partition_name, v_parent_tablespace); 761 END IF; 762 EXECUTE format('ALTER TABLE %I.%I ADD CONSTRAINT %I CHECK (%I >= %s AND %I < %s )' 763 , v_parent_schema 764 , v_partition_name 765 , v_partition_name||'_partition_check' 766 , v_control 767 , v_id 768 , v_control 769 , v_id + v_partition_interval); 770 EXECUTE format('ALTER TABLE %I.%I INHERIT %I.%I', v_parent_schema, v_partition_name, v_parent_schema, v_parent_tablename); 771 772 PERFORM @extschema@.apply_privileges(v_parent_schema, v_parent_tablename, v_parent_schema, v_partition_name, v_job_id); 773 774 PERFORM @extschema@.apply_cluster(v_parent_schema, v_parent_tablename, v_parent_schema, v_partition_name); 775 776 IF v_inherit_fk THEN 777 PERFORM @extschema@.apply_foreign_keys(p_parent_table, v_parent_schema||'.'||v_partition_name, v_job_id); 778 END IF; 779 780 IF v_jobmon_schema IS NOT NULL THEN 781 PERFORM update_step(v_step_id, 'OK', 'Done'); 782 END IF; 783 784 -- Will only loop once and only if sub_partitioning is actually configured 785 -- This seemed easier than assigning a bunch of variables then doing an IF condition 786 FOR v_row IN 787 SELECT sub_parent 788 , sub_partition_type 789 , sub_control 790 , sub_partition_interval 791 , sub_constraint_cols 792 , sub_premake 793 , sub_optimize_trigger 794 , sub_optimize_constraint 795 , sub_epoch 796 , sub_inherit_fk 797 , sub_retention 798 , sub_retention_schema 799 , sub_retention_keep_table 800 , sub_retention_keep_index 801 , sub_use_run_maintenance 802 , sub_infinite_time_partitions 803 , sub_jobmon 804 FROM @extschema@.part_config_sub 805 WHERE sub_parent = p_parent_table 806 LOOP 807 IF v_jobmon_schema IS NOT NULL THEN 808 v_step_id := add_step(v_job_id, 'Subpartitioning '||v_partition_name); 809 END IF; 810 v_sql := format('SELECT @extschema@.create_parent( 811 p_parent_table := %L 812 , p_control := %L 813 , p_type := %L 814 , p_interval := %L 815 , p_constraint_cols := %L 816 , p_premake := %L 817 , p_use_run_maintenance := %L 818 , p_inherit_fk := %L 819 , p_epoch := %L 820 , p_jobmon := %L )' 821 , v_parent_schema||'.'||v_partition_name 822 , v_row.sub_control 823 , v_row.sub_partition_type 824 , v_row.sub_partition_interval 825 , v_row.sub_constraint_cols 826 , v_row.sub_premake 827 , v_row.sub_use_run_maintenance 828 , v_row.sub_inherit_fk 829 , v_row.sub_epoch 830 , v_row.sub_jobmon); 831 EXECUTE v_sql; 832 833 UPDATE @extschema@.part_config SET 834 retention_schema = v_row.sub_retention_schema 835 , retention_keep_table = v_row.sub_retention_keep_table 836 , retention_keep_index = v_row.sub_retention_keep_index 837 , optimize_trigger = v_row.sub_optimize_trigger 838 , optimize_constraint = v_row.sub_optimize_constraint 839 , infinite_time_partitions = v_row.sub_infinite_time_partitions 840 WHERE parent_table = v_parent_schema||'.'||v_partition_name; 841 842 IF v_jobmon_schema IS NOT NULL THEN 843 PERFORM update_step(v_step_id, 'OK', 'Done'); 844 END IF; 845 846 END LOOP; -- end sub partitioning LOOP 847 848 -- Manage additonal constraints if set 849 PERFORM @extschema@.apply_constraints(p_parent_table, p_job_id := v_job_id, p_debug := p_debug); 850 851 v_partition_created := true; 852 853END LOOP; 854 855-- v_analyze is a local check if a new table is made. 856-- 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. 857IF v_analyze AND p_analyze THEN 858 IF v_jobmon_schema IS NOT NULL THEN 859 v_step_id := add_step(v_job_id, format('Analyzing partition set: %s', p_parent_table)); 860 END IF; 861 862 EXECUTE format('ANALYZE %I.%I', v_parent_schema, v_parent_tablename); 863 864 IF v_jobmon_schema IS NOT NULL THEN 865 PERFORM update_step(v_step_id, 'OK', 'Done'); 866 END IF; 867END IF; 868 869IF v_jobmon_schema IS NOT NULL THEN 870 IF v_partition_created = false THEN 871 v_step_id := add_step(v_job_id, format('No partitions created for partition set: %s', p_parent_table)); 872 PERFORM update_step(v_step_id, 'OK', 'Done'); 873 END IF; 874 875 PERFORM close_job(v_job_id); 876END IF; 877 878IF v_jobmon_schema IS NOT NULL THEN 879 EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false'); 880END IF; 881 882RETURN v_partition_created; 883 884EXCEPTION 885 WHEN OTHERS THEN 886 GET STACKED DIAGNOSTICS ex_message = MESSAGE_TEXT, 887 ex_context = PG_EXCEPTION_CONTEXT, 888 ex_detail = PG_EXCEPTION_DETAIL, 889 ex_hint = PG_EXCEPTION_HINT; 890 IF v_jobmon_schema IS NOT NULL THEN 891 IF v_job_id IS NULL THEN 892 EXECUTE format('SELECT %I.add_job(''PARTMAN CREATE TABLE: %s'')', v_jobmon_schema, p_parent_table) INTO v_job_id; 893 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; 894 ELSIF v_step_id IS NULL THEN 895 EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before first step logged'')', v_jobmon_schema, v_job_id) INTO v_step_id; 896 END IF; 897 EXECUTE format('SELECT %I.update_step(%s, ''CRITICAL'', %L)', v_jobmon_schema, v_step_id, 'ERROR: '||coalesce(SQLERRM,'unknown')); 898 EXECUTE format('SELECT %I.fail_job(%s)', v_jobmon_schema, v_job_id); 899 END IF; 900 RAISE EXCEPTION '% 901CONTEXT: % 902DETAIL: % 903HINT: %', ex_message, ex_context, ex_detail, ex_hint; 904END 905$$; 906 907 908/* 909 * Function to create a child table in a time-based partition set 910 */ 911CREATE FUNCTION create_partition_time(p_parent_table text, p_partition_times timestamp[], p_analyze boolean DEFAULT true, p_debug boolean DEFAULT false) 912RETURNS boolean 913 LANGUAGE plpgsql SECURITY DEFINER 914 AS $$ 915DECLARE 916 917ex_context text; 918ex_detail text; 919ex_hint text; 920ex_message text; 921v_all text[] := ARRAY['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER']; 922v_analyze boolean := FALSE; 923v_control text; 924v_datetime_string text; 925v_exists text; 926v_epoch boolean; 927v_grantees text[]; 928v_hasoids boolean; 929v_inherit_fk boolean; 930v_job_id bigint; 931v_jobmon boolean; 932v_jobmon_schema text; 933v_old_search_path text; 934v_parent_grant record; 935v_parent_owner text; 936v_parent_schema text; 937v_parent_tablename text; 938v_partition_created boolean := false; 939v_partition_name text; 940v_partition_suffix text; 941v_parent_tablespace text; 942v_partition_interval interval; 943v_partition_timestamp_end timestamp; 944v_partition_timestamp_start timestamp; 945v_quarter text; 946v_revoke text; 947v_row record; 948v_sql text; 949v_step_id bigint; 950v_step_overflow_id bigint; 951v_sub_timestamp_max timestamp; 952v_sub_timestamp_min timestamp; 953v_trunc_value text; 954v_time timestamp; 955v_type text; 956v_unlogged char; 957v_year text; 958 959BEGIN 960 961SELECT partition_type 962 , control 963 , partition_interval 964 , epoch 965 , inherit_fk 966 , jobmon 967 , datetime_string 968INTO v_type 969 , v_control 970 , v_partition_interval 971 , v_epoch 972 , v_inherit_fk 973 , v_jobmon 974 , v_datetime_string 975FROM @extschema@.part_config 976WHERE parent_table = p_parent_table 977AND partition_type = 'time' OR partition_type = 'time-custom'; 978 979IF NOT FOUND THEN 980 RAISE EXCEPTION 'ERROR: no config found for %', p_parent_table; 981END IF; 982 983IF v_jobmon THEN 984 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; 985 IF v_jobmon_schema IS NOT NULL THEN 986 SELECT current_setting('search_path') INTO v_old_search_path; 987 EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', '@extschema@,'||v_jobmon_schema, 'false'); 988 END IF; 989END IF; 990 991-- 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 992SELECT sub_min::timestamp, sub_max::timestamp INTO v_sub_timestamp_min, v_sub_timestamp_max FROM @extschema@.check_subpartition_limits(p_parent_table, 'time'); 993 994SELECT tableowner, schemaname, tablename, tablespace 995INTO v_parent_owner, v_parent_schema, v_parent_tablename, v_parent_tablespace 996FROM pg_catalog.pg_tables 997WHERE schemaname = split_part(p_parent_table, '.', 1) 998AND tablename = split_part(p_parent_table, '.', 2); 999 1000IF v_jobmon_schema IS NOT NULL THEN 1001 v_job_id := add_job(format('PARTMAN CREATE TABLE: %s', p_parent_table)); 1002END IF; 1003 1004FOREACH v_time IN ARRAY p_partition_times LOOP 1005 v_partition_timestamp_start := v_time; 1006 BEGIN 1007 v_partition_timestamp_end := v_time + v_partition_interval; 1008 EXCEPTION WHEN datetime_field_overflow THEN 1009 RAISE WARNING 'Attempted partition time interval is outside PostgreSQL''s supported time range. 1010 Child partition creation after time % skipped', v_time; 1011 v_step_overflow_id := add_step(v_job_id, 'Attempted partition time interval is outside PostgreSQL''s supported time range.'); 1012 PERFORM update_step(v_step_overflow_id, 'CRITICAL', 'Child partition creation after time '||v_time||' skipped'); 1013 CONTINUE; 1014 END; 1015 1016 -- Do not create the child table if it's outside the bounds of the top parent. 1017 IF v_sub_timestamp_min IS NOT NULL THEN 1018 IF v_time < v_sub_timestamp_min OR v_time > v_sub_timestamp_max THEN 1019 CONTINUE; 1020 END IF; 1021 END IF; 1022 1023 -- This suffix generation code is in partition_data_time() as well 1024 v_partition_suffix := to_char(v_time, v_datetime_string); 1025 v_partition_name := @extschema@.check_name_length(v_parent_tablename, v_partition_suffix, TRUE); 1026 SELECT tablename INTO v_exists FROM pg_catalog.pg_tables WHERE schemaname = v_parent_schema AND tablename = v_partition_name; 1027 IF v_exists IS NOT NULL THEN 1028 CONTINUE; 1029 END IF; 1030 1031 -- Ensure analyze is run if a new partition is created. Otherwise if one isn't, will be false and analyze will be skipped 1032 v_analyze := TRUE; 1033 1034 IF v_jobmon_schema IS NOT NULL THEN 1035 v_step_id := add_step(v_job_id, format('Creating new partition %s.%s with interval from %s to %s' 1036 , v_parent_schema 1037 , v_partition_name 1038 , v_partition_timestamp_start 1039 , v_partition_timestamp_end-'1sec'::interval)); 1040 END IF; 1041 1042 SELECT relpersistence INTO v_unlogged 1043 FROM pg_catalog.pg_class c 1044 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 1045 WHERE c.relname = v_parent_tablename 1046 AND n.nspname = v_parent_schema; 1047 v_sql := 'CREATE'; 1048 IF v_unlogged = 'u' THEN 1049 v_sql := v_sql || ' UNLOGGED'; 1050 END IF; 1051 v_sql := v_sql || format(' TABLE %I.%I (LIKE %I.%I INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING STORAGE INCLUDING COMMENTS)' 1052 , v_parent_schema 1053 , v_partition_name 1054 , v_parent_schema 1055 , v_parent_tablename); 1056 SELECT relhasoids INTO v_hasoids 1057 FROM pg_catalog.pg_class c 1058 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 1059 WHERE c.relname = v_parent_tablename 1060 AND n.nspname = v_parent_schema; 1061 IF v_hasoids IS TRUE THEN 1062 v_sql := v_sql || ' WITH (OIDS)'; 1063 END IF; 1064 EXECUTE v_sql; 1065 IF v_parent_tablespace IS NOT NULL THEN 1066 EXECUTE format('ALTER TABLE %I.%I SET TABLESPACE %I', v_parent_schema, v_partition_name, v_parent_tablespace); 1067 END IF; 1068 IF v_epoch = false THEN 1069 EXECUTE format('ALTER TABLE %I.%I ADD CONSTRAINT %I CHECK (%I >= %L AND %I < %L)' 1070 , v_parent_schema 1071 , v_partition_name 1072 , v_partition_name||'_partition_check' 1073 , v_control 1074 , v_partition_timestamp_start 1075 , v_control 1076 , v_partition_timestamp_end); 1077 ELSE 1078 EXECUTE format('ALTER TABLE %I.%I ADD CONSTRAINT %I CHECK (to_timestamp(%I) >= %L AND to_timestamp(%I) < %L)' 1079 , v_parent_schema 1080 , v_partition_name 1081 , v_partition_name||'_partition_check' 1082 , v_control 1083 , v_partition_timestamp_start 1084 , v_control 1085 , v_partition_timestamp_end); 1086 END IF; 1087 1088 EXECUTE format('ALTER TABLE %I.%I INHERIT %I.%I' 1089 , v_parent_schema 1090 , v_partition_name 1091 , v_parent_schema 1092 , v_parent_tablename); 1093 1094 -- If custom time, set extra config options. 1095 IF v_type = 'time-custom' THEN 1096 INSERT INTO @extschema@.custom_time_partitions (parent_table, child_table, partition_range) 1097 VALUES ( p_parent_table, v_parent_schema||'.'||v_partition_name, tstzrange(v_partition_timestamp_start, v_partition_timestamp_end, '[)') ); 1098 END IF; 1099 1100 PERFORM @extschema@.apply_privileges(v_parent_schema, v_parent_tablename, v_parent_schema, v_partition_name, v_job_id); 1101 1102 PERFORM @extschema@.apply_cluster(v_parent_schema, v_parent_tablename, v_parent_schema, v_partition_name); 1103 1104 IF v_inherit_fk THEN 1105 PERFORM @extschema@.apply_foreign_keys(p_parent_table, v_parent_schema||'.'||v_partition_name, v_job_id); 1106 END IF; 1107 1108 IF v_jobmon_schema IS NOT NULL THEN 1109 PERFORM update_step(v_step_id, 'OK', 'Done'); 1110 END IF; 1111 1112 -- Will only loop once and only if sub_partitioning is actually configured 1113 -- This seemed easier than assigning a bunch of variables then doing an IF condition 1114 FOR v_row IN 1115 SELECT sub_parent 1116 , sub_partition_type 1117 , sub_control 1118 , sub_partition_interval 1119 , sub_constraint_cols 1120 , sub_premake 1121 , sub_optimize_trigger 1122 , sub_optimize_constraint 1123 , sub_epoch 1124 , sub_inherit_fk 1125 , sub_retention 1126 , sub_retention_schema 1127 , sub_retention_keep_table 1128 , sub_retention_keep_index 1129 , sub_use_run_maintenance 1130 , sub_infinite_time_partitions 1131 , sub_jobmon 1132 FROM @extschema@.part_config_sub 1133 WHERE sub_parent = p_parent_table 1134 LOOP 1135 IF v_jobmon_schema IS NOT NULL THEN 1136 v_step_id := add_step(v_job_id, format('Subpartitioning %s.%s', v_parent_schema, v_partition_name)); 1137 END IF; 1138 v_sql := format('SELECT @extschema@.create_parent( 1139 p_parent_table := %L 1140 , p_control := %L 1141 , p_type := %L 1142 , p_interval := %L 1143 , p_constraint_cols := %L 1144 , p_premake := %L 1145 , p_use_run_maintenance := %L 1146 , p_inherit_fk := %L 1147 , p_epoch := %L 1148 , p_jobmon := %L )' 1149 , v_parent_schema||'.'||v_partition_name 1150 , v_row.sub_control 1151 , v_row.sub_partition_type 1152 , v_row.sub_partition_interval 1153 , v_row.sub_constraint_cols 1154 , v_row.sub_premake 1155 , v_row.sub_use_run_maintenance 1156 , v_row.sub_inherit_fk 1157 , v_row.sub_epoch 1158 , v_row.sub_jobmon); 1159 EXECUTE v_sql; 1160 1161 UPDATE @extschema@.part_config SET 1162 retention_schema = v_row.sub_retention_schema 1163 , retention_keep_table = v_row.sub_retention_keep_table 1164 , retention_keep_index = v_row.sub_retention_keep_index 1165 , optimize_trigger = v_row.sub_optimize_trigger 1166 , optimize_constraint = v_row.sub_optimize_constraint 1167 , infinite_time_partitions = v_row.sub_infinite_time_partitions 1168 WHERE parent_table = v_parent_schema||'.'||v_partition_name; 1169 1170 END LOOP; -- end sub partitioning LOOP 1171 1172 -- Manage additonal constraints if set 1173 PERFORM @extschema@.apply_constraints(p_parent_table, p_job_id := v_job_id, p_debug := p_debug); 1174 1175 v_partition_created := true; 1176 1177END LOOP; 1178 1179-- v_analyze is a local check if a new table is made. 1180-- 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. 1181IF v_analyze AND p_analyze THEN 1182 IF v_jobmon_schema IS NOT NULL THEN 1183 v_step_id := add_step(v_job_id, format('Analyzing partition set: %s', p_parent_table)); 1184 END IF; 1185 1186 EXECUTE format('ANALYZE %I.%I', v_parent_schema, v_parent_tablename); 1187 1188 IF v_jobmon_schema IS NOT NULL THEN 1189 PERFORM update_step(v_step_id, 'OK', 'Done'); 1190 END IF; 1191END IF; 1192 1193IF v_jobmon_schema IS NOT NULL THEN 1194 IF v_partition_created = false THEN 1195 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)); 1196 PERFORM update_step(v_step_id, 'OK', 'Done'); 1197 END IF; 1198 1199 IF v_step_overflow_id IS NOT NULL THEN 1200 PERFORM fail_job(v_job_id); 1201 ELSE 1202 PERFORM close_job(v_job_id); 1203 END IF; 1204END IF; 1205 1206IF v_jobmon_schema IS NOT NULL THEN 1207 EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false'); 1208END IF; 1209 1210RETURN v_partition_created; 1211 1212EXCEPTION 1213 WHEN OTHERS THEN 1214 GET STACKED DIAGNOSTICS ex_message = MESSAGE_TEXT, 1215 ex_context = PG_EXCEPTION_CONTEXT, 1216 ex_detail = PG_EXCEPTION_DETAIL, 1217 ex_hint = PG_EXCEPTION_HINT; 1218 IF v_jobmon_schema IS NOT NULL THEN 1219 IF v_job_id IS NULL THEN 1220 EXECUTE format('SELECT %I.add_job(''PARTMAN CREATE TABLE: %s'')', v_jobmon_schema, p_parent_table) INTO v_job_id; 1221 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; 1222 ELSIF v_step_id IS NULL THEN 1223 EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before first step logged'')', v_jobmon_schema, v_job_id) INTO v_step_id; 1224 END IF; 1225 EXECUTE format('SELECT %I.update_step(%s, ''CRITICAL'', %L)', v_jobmon_schema, v_step_id, 'ERROR: '||coalesce(SQLERRM,'unknown')); 1226 EXECUTE format('SELECT %I.fail_job(%s)', v_jobmon_schema, v_job_id); 1227 END IF; 1228 RAISE EXCEPTION '% 1229CONTEXT: % 1230DETAIL: % 1231HINT: %', ex_message, ex_context, ex_detail, ex_hint; 1232END 1233$$; 1234 1235 1236 1237/* 1238 * Apply constraints managed by partman extension 1239 */ 1240CREATE OR REPLACE FUNCTION apply_constraints(p_parent_table text, p_child_table text DEFAULT NULL, p_analyze boolean DEFAULT FALSE, p_job_id bigint DEFAULT NULL, p_debug boolean DEFAULT FALSE) RETURNS void 1241 LANGUAGE plpgsql 1242 AS $$ 1243DECLARE 1244 1245ex_context text; 1246ex_detail text; 1247ex_hint text; 1248ex_message text; 1249v_child_exists text; 1250v_child_tablename text; 1251v_col text; 1252v_constraint_cols text[]; 1253v_constraint_col_type text; 1254v_constraint_name text; 1255v_constraint_values record; 1256v_control text; 1257v_datetime_string text; 1258v_existing_constraint_name text; 1259v_job_id bigint; 1260v_jobmon boolean; 1261v_jobmon_schema text; 1262v_last_partition text; 1263v_last_partition_id bigint; 1264v_last_partition_timestamp timestamp; 1265v_max_id bigint; 1266v_max_timestamp timestamp; 1267v_old_search_path text; 1268v_optimize_constraint int; 1269v_parent_schema text; 1270v_parent_table text; 1271v_parent_tablename text; 1272v_partition_interval text; 1273v_partition_suffix text; 1274v_premake int; 1275v_sql text; 1276v_step_id bigint; 1277v_suffix_position int; 1278v_type text; 1279 1280BEGIN 1281 1282SELECT parent_table 1283 , partition_type 1284 , control 1285 , premake 1286 , partition_interval 1287 , optimize_constraint 1288 , datetime_string 1289 , constraint_cols 1290 , jobmon 1291INTO v_parent_table 1292 , v_type 1293 , v_control 1294 , v_premake 1295 , v_partition_interval 1296 , v_optimize_constraint 1297 , v_datetime_string 1298 , v_constraint_cols 1299 , v_jobmon 1300FROM @extschema@.part_config 1301WHERE parent_table = p_parent_table 1302AND constraint_cols IS NOT NULL; 1303 1304IF v_constraint_cols IS NULL THEN 1305 IF p_debug THEN 1306 RAISE NOTICE 'Given parent table (%) not set up for constraint management (constraint_cols is NULL)', p_parent_table; 1307 END IF; 1308 -- Returns silently to allow this function to be simply called by maintenance processes without having to check if config options are set. 1309 RETURN; 1310END IF; 1311 1312SELECT schemaname, tablename 1313INTO v_parent_schema, v_parent_tablename 1314FROM pg_catalog.pg_tables 1315WHERE schemaname = split_part(v_parent_table, '.', 1) 1316AND tablename = split_part(v_parent_table, '.', 2); 1317 1318IF v_jobmon THEN 1319 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; 1320 IF v_jobmon_schema IS NOT NULL THEN 1321 SELECT current_setting('search_path') INTO v_old_search_path; 1322 EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', '@extschema@,'||v_jobmon_schema, 'false'); 1323 END IF; 1324END IF; 1325 1326IF v_jobmon_schema IS NOT NULL THEN 1327 IF p_job_id IS NULL THEN 1328 v_job_id := add_job(format('PARTMAN CREATE CONSTRAINT: %s', v_parent_table)); 1329 ELSE 1330 v_job_id = p_job_id; 1331 END IF; 1332END IF; 1333 1334-- If p_child_table is null, figure out the partition that is the one right before the optimize_constraint value backwards. 1335IF p_child_table IS NULL THEN 1336 IF v_jobmon_schema IS NOT NULL THEN 1337 v_step_id := add_step(v_job_id, 'Applying additional constraints: Automatically determining most recent child on which to apply constraints'); 1338 END IF; 1339 1340 SELECT partition_tablename INTO v_last_partition FROM @extschema@.show_partitions(v_parent_table, 'DESC') LIMIT 1; 1341 1342 IF v_type IN ('time', 'time-custom') THEN 1343 SELECT child_start_time INTO v_last_partition_timestamp FROM @extschema@.show_partition_info(v_parent_schema||'.'||v_last_partition, v_partition_interval, v_parent_table); 1344 v_partition_suffix := to_char(v_last_partition_timestamp - (v_partition_interval::interval * (v_optimize_constraint + v_premake + 1) ), v_datetime_string); 1345 ELSIF v_type = 'id' THEN 1346 SELECT child_start_id INTO v_last_partition_id FROM @extschema@.show_partition_info(v_parent_schema||'.'||v_last_partition, v_partition_interval, v_parent_table); 1347 v_partition_suffix := (v_last_partition_id - (v_partition_interval::int * (v_optimize_constraint + v_premake + 1) ))::text; 1348 END IF; 1349 1350 v_child_tablename := @extschema@.check_name_length(v_parent_tablename, v_partition_suffix, TRUE); 1351 1352 IF p_debug THEN 1353 RAISE NOTICE 'apply_constraint: v_parent_tablename: % , v_partition_suffix: %', v_parent_tablename, v_partition_suffix; 1354 END IF; 1355 1356 IF v_jobmon_schema IS NOT NULL THEN 1357 PERFORM update_step(v_step_id, 'OK', format('Target child table: %s.%s', v_parent_schema, v_child_tablename)); 1358 END IF; 1359ELSE 1360 v_child_tablename = split_part(p_child_table, '.', 2); 1361END IF; 1362 1363IF v_jobmon_schema IS NOT NULL THEN 1364 v_step_id := add_step(v_job_id, 'Applying additional constraints: Checking if target child table exists'); 1365END IF; 1366 1367SELECT tablename FROM pg_catalog.pg_tables INTO v_child_exists WHERE schemaname = v_parent_schema AND tablename = v_child_tablename; 1368IF v_child_exists IS NULL THEN 1369 IF v_jobmon_schema IS NOT NULL THEN 1370 PERFORM update_step(v_step_id, 'NOTICE', format('Target child table (%s) does not exist. Skipping constraint creation.', v_child_tablename)); 1371 IF p_job_id IS NULL THEN 1372 PERFORM close_job(v_job_id); 1373 END IF; 1374 EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false'); 1375 END IF; 1376 IF p_debug THEN 1377 RAISE NOTICE 'Target child table (%) does not exist. Skipping constraint creation.', v_child_tablename; 1378 END IF; 1379 RETURN; 1380ELSE 1381 IF v_jobmon_schema IS NOT NULL THEN 1382 PERFORM update_step(v_step_id, 'OK', 'Done'); 1383 END IF; 1384END IF; 1385 1386FOREACH v_col IN ARRAY v_constraint_cols 1387LOOP 1388 SELECT con.conname 1389 INTO v_existing_constraint_name 1390 FROM pg_catalog.pg_constraint con 1391 JOIN pg_class c ON c.oid = con.conrelid 1392 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 1393 JOIN pg_catalog.pg_attribute a ON con.conrelid = a.attrelid 1394 WHERE c.relname = v_child_tablename 1395 AND n.nspname = v_parent_schema 1396 AND con.conname LIKE 'partmanconstr_%' 1397 AND con.contype = 'c' 1398 AND a.attname = v_col 1399 AND ARRAY[a.attnum] OPERATOR(pg_catalog.<@) con.conkey 1400 AND a.attisdropped = false; 1401 1402 IF v_jobmon_schema IS NOT NULL THEN 1403 v_step_id := add_step(v_job_id, format('Applying additional constraints: Applying new constraint on column: %s', v_col)); 1404 END IF; 1405 1406 IF v_existing_constraint_name IS NOT NULL THEN 1407 IF v_jobmon_schema IS NOT NULL THEN 1408 PERFORM update_step(v_step_id, 'NOTICE', format('Partman managed constraint already exists on this table (%s) and column (%s). Skipping creation.', v_child_tablename, v_col)); 1409 END IF; 1410 IF p_debug THEN 1411 RAISE NOTICE 'Partman managed constraint already exists on this table (%) and column (%). Skipping creation.', v_child_tablename, v_col ; 1412 END IF; 1413 CONTINUE; 1414 END IF; 1415 1416 -- Ensure column name gets put on end of constraint name to help avoid naming conflicts 1417 v_constraint_name := @extschema@.check_name_length('partmanconstr_'||v_child_tablename, p_suffix := '_'||v_col); 1418 1419 EXECUTE format('SELECT min(%I)::text AS min, max(%I)::text AS max FROM %I.%I', v_col, v_col, v_parent_schema, v_child_tablename) INTO v_constraint_values; 1420 1421 IF v_constraint_values IS NOT NULL THEN 1422 v_sql := format('ALTER TABLE %I.%I ADD CONSTRAINT %I CHECK (%I >= %L AND %I <= %L)' 1423 , v_parent_schema 1424 , v_child_tablename 1425 , v_constraint_name 1426 , v_col 1427 , v_constraint_values.min 1428 , v_col 1429 , v_constraint_values.max); 1430 IF p_debug THEN 1431 RAISE NOTICE 'Constraint creation query: %', v_sql; 1432 END IF; 1433 EXECUTE v_sql; 1434 1435 IF v_jobmon_schema IS NOT NULL THEN 1436 PERFORM update_step(v_step_id, 'OK', format('New constraint created: %s', v_sql)); 1437 END IF; 1438 ELSE 1439 IF p_debug THEN 1440 RAISE NOTICE 'Given column (%) contains all NULLs. No constraint created', v_col; 1441 END IF; 1442 IF v_jobmon_schema IS NOT NULL THEN 1443 PERFORM update_step(v_step_id, 'NOTICE', format('Given column (%s) contains all NULLs. No constraint created', v_col)); 1444 END IF; 1445 END IF; 1446 1447END LOOP; 1448 1449IF p_analyze THEN 1450 IF v_jobmon_schema IS NOT NULL THEN 1451 v_step_id := add_step(v_job_id, format('Applying additional constraints: Running analyze on partition set: %s', v_parent_table)); 1452 END IF; 1453 IF p_debug THEN 1454 RAISE NOTICE 'Running analyze on partition set: %', v_parent_table; 1455 END IF; 1456 1457 EXECUTE format('ANALYZE %I.%I', v_parent_schema, v_parent_tablename); 1458 1459 IF v_jobmon_schema IS NOT NULL THEN 1460 PERFORM update_step(v_step_id, 'OK', 'Done'); 1461 END IF; 1462END IF; 1463 1464IF v_jobmon_schema IS NOT NULL THEN 1465 PERFORM close_job(v_job_id); 1466 EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false'); 1467END IF; 1468 1469EXCEPTION 1470 WHEN OTHERS THEN 1471 GET STACKED DIAGNOSTICS ex_message = MESSAGE_TEXT, 1472 ex_context = PG_EXCEPTION_CONTEXT, 1473 ex_detail = PG_EXCEPTION_DETAIL, 1474 ex_hint = PG_EXCEPTION_HINT; 1475 IF v_jobmon_schema IS NOT NULL THEN 1476 IF v_job_id IS NULL THEN 1477 EXECUTE format('SELECT %I.add_job(''PARTMAN CREATE CONSTRAINT: %s'')', v_jobmon_schema, p_parent_table) INTO v_job_id; 1478 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; 1479 ELSIF v_step_id IS NULL THEN 1480 EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before first step logged'')', v_jobmon_schema, v_job_id) INTO v_step_id; 1481 END IF; 1482 EXECUTE format('SELECT %I.update_step(%s, ''CRITICAL'', %L)', v_jobmon_schema, v_step_id, 'ERROR: '||coalesce(SQLERRM,'unknown')); 1483 EXECUTE format('SELECT %I.fail_job(%s)', v_jobmon_schema, v_job_id); 1484 END IF; 1485 RAISE EXCEPTION '% 1486CONTEXT: % 1487DETAIL: % 1488HINT: %', ex_message, ex_context, ex_detail, ex_hint; 1489END 1490$$; 1491 1492 1493/* 1494 * Check if parent table is a subpartition of an already existing partition set managed by pg_partman 1495 * If so, return the limits of what child tables can be created under the given parent table based on its own suffix 1496 */ 1497CREATE OR REPLACE FUNCTION check_subpartition_limits(p_parent_table text, p_type text, OUT sub_min text, OUT sub_max text) RETURNS record 1498 LANGUAGE plpgsql 1499 AS $$ 1500DECLARE 1501 1502v_datetime_string text; 1503v_id_position int; 1504v_parent_schema text; 1505v_parent_tablename text; 1506v_partition_interval interval; 1507v_quarter text; 1508v_sub_id_max bigint; 1509v_sub_id_min bigint; 1510v_sub_timestamp_max timestamp; 1511v_sub_timestamp_min timestamp; 1512v_time_position int; 1513v_top_datetime_string text; 1514v_top_interval text; 1515v_top_parent text; 1516v_top_type text; 1517v_year text; 1518 1519BEGIN 1520 1521SELECT schemaname, tablename INTO v_parent_schema, v_parent_tablename 1522FROM pg_catalog.pg_tables 1523WHERE schemaname = split_part(p_parent_table, '.', 1) 1524AND tablename = split_part(p_parent_table, '.', 2); 1525 1526-- CTE query is done individually for each type (time, id) because it should return NULL if the top parent is not the same type in a subpartition set (id->time or time->id) 1527 1528IF p_type = 'id' THEN 1529 1530 WITH top_oid AS ( 1531 SELECT i.inhparent AS top_parent_oid 1532 FROM pg_catalog.pg_class c 1533 JOIN pg_catalog.pg_inherits i ON c.oid = i.inhrelid 1534 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 1535 WHERE n.nspname = v_parent_schema 1536 AND c.relname = v_parent_tablename 1537 ) SELECT n.nspname||'.'||c.relname, p.datetime_string, p.partition_interval, p.partition_type 1538 INTO v_top_parent, v_top_datetime_string, v_top_interval, v_top_type 1539 FROM pg_catalog.pg_class c 1540 JOIN top_oid t ON c.oid = t.top_parent_oid 1541 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 1542 JOIN @extschema@.part_config p ON p.parent_table = n.nspname||'.'||c.relname 1543 WHERE c.oid = t.top_parent_oid 1544 AND p.partition_type = 'id'; 1545 1546 IF v_top_parent IS NOT NULL THEN 1547 SELECT child_start_id::text, child_end_id::text 1548 INTO sub_min, sub_max 1549 FROM @extschema@.show_partition_info(p_parent_table, v_top_interval, v_top_parent); 1550 END IF; 1551 1552ELSIF p_type = 'time' THEN 1553 1554 WITH top_oid AS ( 1555 SELECT i.inhparent AS top_parent_oid 1556 FROM pg_catalog.pg_class c 1557 JOIN pg_catalog.pg_inherits i ON c.oid = i.inhrelid 1558 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 1559 WHERE n.nspname = v_parent_schema 1560 AND c.relname = v_parent_tablename 1561 ) SELECT n.nspname||'.'||c.relname, p.datetime_string, p.partition_interval, p.partition_type 1562 INTO v_top_parent, v_top_datetime_string, v_top_interval, v_top_type 1563 FROM pg_catalog.pg_class c 1564 JOIN top_oid t ON c.oid = t.top_parent_oid 1565 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 1566 JOIN @extschema@.part_config p ON p.parent_table = n.nspname||'.'||c.relname 1567 WHERE c.oid = t.top_parent_oid 1568 AND p.partition_type = 'time' OR p.partition_type = 'time-custom'; 1569 1570 IF v_top_parent IS NOT NULL THEN 1571 SELECT child_start_time::text, child_end_time::text 1572 INTO sub_min, sub_max 1573 FROM @extschema@.show_partition_info(p_parent_table, v_top_interval, v_top_parent); 1574 END IF; 1575 1576ELSE 1577 RAISE EXCEPTION 'Invalid type given as parameter to check_subpartition_limits()'; 1578END IF; 1579 1580RETURN; 1581 1582END 1583$$; 1584 1585 1586/* 1587 * Function to turn a table into the parent of a partition set 1588 */ 1589CREATE OR REPLACE FUNCTION create_parent( 1590 p_parent_table text 1591 , p_control text 1592 , p_type text 1593 , p_interval text 1594 , p_constraint_cols text[] DEFAULT NULL 1595 , p_premake int DEFAULT 4 1596 , p_use_run_maintenance boolean DEFAULT NULL 1597 , p_start_partition text DEFAULT NULL 1598 , p_inherit_fk boolean DEFAULT true 1599 , p_epoch boolean DEFAULT false 1600 , p_jobmon boolean DEFAULT true 1601 , p_debug boolean DEFAULT false) 1602RETURNS boolean 1603 LANGUAGE plpgsql SECURITY DEFINER 1604 AS $$ 1605DECLARE 1606 1607ex_context text; 1608ex_detail text; 1609ex_hint text; 1610ex_message text; 1611v_base_timestamp timestamp; 1612v_count int := 1; 1613v_datetime_string text; 1614v_higher_parent_schema text := split_part(p_parent_table, '.', 1); 1615v_higher_parent_table text := split_part(p_parent_table, '.', 2); 1616v_id_interval bigint; 1617v_job_id bigint; 1618v_jobmon_schema text; 1619v_last_partition_created boolean; 1620v_max bigint; 1621v_notnull boolean; 1622v_old_search_path text; 1623v_parent_partition_id bigint; 1624v_parent_partition_timestamp timestamp; 1625v_parent_schema text; 1626v_parent_tablename text; 1627v_partition_time timestamp; 1628v_partition_time_array timestamp[]; 1629v_partition_id_array bigint[]; 1630v_row record; 1631v_run_maint boolean; 1632v_sql text; 1633v_start_time timestamp; 1634v_starting_partition_id bigint; 1635v_step_id bigint; 1636v_step_overflow_id bigint; 1637v_sub_parent text; 1638v_success boolean := false; 1639v_time_interval interval; 1640v_top_datetime_string text; 1641v_top_parent_schema text := split_part(p_parent_table, '.', 1); 1642v_top_parent_table text := split_part(p_parent_table, '.', 2); 1643 1644BEGIN 1645 1646IF position('.' in p_parent_table) = 0 THEN 1647 RAISE EXCEPTION 'Parent table must be schema qualified'; 1648END IF; 1649 1650SELECT schemaname, tablename INTO v_parent_schema, v_parent_tablename 1651FROM pg_catalog.pg_tables 1652WHERE schemaname = split_part(p_parent_table, '.', 1) 1653AND tablename = split_part(p_parent_table, '.', 2); 1654 IF v_parent_tablename IS NULL THEN 1655 RAISE EXCEPTION 'Unable to find given parent table in system catalogs. Please create parent table first: %', p_parent_table; 1656 END IF; 1657 1658SELECT attnotnull INTO v_notnull 1659FROM pg_catalog.pg_attribute a 1660JOIN pg_catalog.pg_class c ON a.attrelid = c.oid 1661JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 1662WHERE c.relname = v_parent_tablename 1663AND n.nspname = v_parent_schema 1664AND a.attname = p_control; 1665 IF v_notnull = false OR v_notnull IS NULL THEN 1666 RAISE EXCEPTION 'Control column given (%) for parent table (%) does not exist or must be set to NOT NULL', p_control, p_parent_table; 1667 END IF; 1668 1669IF p_type = 'id' AND p_epoch = true THEN 1670 RAISE EXCEPTION 'p_epoch can only be used with time-based partitioning'; 1671END IF; 1672 1673IF NOT @extschema@.check_partition_type(p_type) THEN 1674 RAISE EXCEPTION '% is not a valid partitioning type', p_type; 1675END IF; 1676 1677IF p_jobmon THEN 1678 SELECT nspname INTO v_jobmon_schema FROM pg_namespace n, pg_extension e WHERE e.extname = 'pg_jobmon' AND e.extnamespace = n.oid; 1679 IF v_jobmon_schema IS NOT NULL THEN 1680 SELECT current_setting('search_path') INTO v_old_search_path; 1681 EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', '@extschema@,'||v_jobmon_schema, 'false'); 1682 END IF; 1683END IF; 1684 1685IF p_use_run_maintenance IS NOT NULL THEN 1686 IF p_use_run_maintenance IS FALSE AND (p_type = 'time' OR p_type = 'time-custom') THEN 1687 RAISE EXCEPTION 'p_run_maintenance cannot be set to false for time based partitioning'; 1688 END IF; 1689 v_run_maint := p_use_run_maintenance; 1690ELSIF p_type = 'time' OR p_type = 'time-custom' THEN 1691 v_run_maint := TRUE; 1692ELSIF p_type = 'id' THEN 1693 v_run_maint := FALSE; 1694ELSE 1695 RAISE EXCEPTION 'use_run_maintenance value cannot be set NULL'; 1696END IF; 1697 1698EXECUTE format('LOCK TABLE %I.%I IN ACCESS EXCLUSIVE MODE', v_parent_schema, v_parent_tablename); 1699 1700IF v_jobmon_schema IS NOT NULL THEN 1701 v_job_id := add_job(format('PARTMAN SETUP PARENT: %s', p_parent_table)); 1702 v_step_id := add_step(v_job_id, format('Creating initial partitions on new parent table: %s', p_parent_table)); 1703END IF; 1704 1705-- If this parent table has siblings that are also partitioned (subpartitions), ensure this parent gets added to part_config_sub table so future maintenance will subpartition it 1706-- Just doing in a loop to avoid having to assign a bunch of variables (should only run once, if at all; constraint should enforce only one value.) 1707FOR v_row IN 1708 WITH parent_table AS ( 1709 SELECT h.inhparent AS parent_oid 1710 FROM pg_catalog.pg_inherits h 1711 JOIN pg_catalog.pg_class c ON h.inhrelid = c.oid 1712 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 1713 WHERE c.relname = v_parent_tablename 1714 AND n.nspname = v_parent_schema 1715 ), sibling_children AS ( 1716 SELECT i.inhrelid::regclass::text AS tablename 1717 FROM pg_inherits i 1718 JOIN parent_table p ON i.inhparent = p.parent_oid 1719 ) 1720 SELECT DISTINCT sub_partition_type 1721 , sub_control 1722 , sub_partition_interval 1723 , sub_constraint_cols 1724 , sub_premake 1725 , sub_inherit_fk 1726 , sub_retention 1727 , sub_retention_schema 1728 , sub_retention_keep_table 1729 , sub_retention_keep_index 1730 , sub_use_run_maintenance 1731 , sub_epoch 1732 , sub_optimize_trigger 1733 , sub_optimize_constraint 1734 , sub_infinite_time_partitions 1735 , sub_jobmon 1736 FROM @extschema@.part_config_sub a 1737 JOIN sibling_children b on a.sub_parent = b.tablename LIMIT 1 1738LOOP 1739 INSERT INTO @extschema@.part_config_sub ( 1740 sub_parent 1741 , sub_partition_type 1742 , sub_control 1743 , sub_partition_interval 1744 , sub_constraint_cols 1745 , sub_premake 1746 , sub_inherit_fk 1747 , sub_retention 1748 , sub_retention_schema 1749 , sub_retention_keep_table 1750 , sub_retention_keep_index 1751 , sub_use_run_maintenance 1752 , sub_epoch 1753 , sub_optimize_trigger 1754 , sub_optimize_constraint 1755 , sub_infinite_time_partitions 1756 , sub_jobmon) 1757 VALUES ( 1758 p_parent_table 1759 , v_row.sub_partition_type 1760 , v_row.sub_control 1761 , v_row.sub_partition_interval 1762 , v_row.sub_constraint_cols 1763 , v_row.sub_premake 1764 , v_row.sub_inherit_fk 1765 , v_row.sub_retention 1766 , v_row.sub_retention_schema 1767 , v_row.sub_retention_keep_table 1768 , v_row.sub_retention_keep_index 1769 , v_row.sub_use_run_maintenance 1770 , v_row.sub_epoch 1771 , v_row.sub_optimize_trigger 1772 , v_row.sub_optimize_constraint 1773 , v_row.sub_infinite_time_partitions 1774 , v_row.sub_jobmon); 1775END LOOP; 1776 1777IF p_type = 'time' OR p_type = 'time-custom' THEN 1778 1779 CASE 1780 WHEN p_interval = 'yearly' THEN 1781 v_time_interval := '1 year'; 1782 WHEN p_interval = 'quarterly' THEN 1783 v_time_interval := '3 months'; 1784 WHEN p_interval = 'monthly' THEN 1785 v_time_interval := '1 month'; 1786 WHEN p_interval = 'weekly' THEN 1787 v_time_interval := '1 week'; 1788 WHEN p_interval = 'daily' THEN 1789 v_time_interval := '1 day'; 1790 WHEN p_interval = 'hourly' THEN 1791 v_time_interval := '1 hour'; 1792 WHEN p_interval = 'half-hour' THEN 1793 v_time_interval := '30 mins'; 1794 WHEN p_interval = 'quarter-hour' THEN 1795 v_time_interval := '15 mins'; 1796 ELSE 1797 IF p_type <> 'time-custom' THEN 1798 RAISE EXCEPTION 'Must use a predefined time interval if not using type "time-custom". See documentation.'; 1799 END IF; 1800 v_time_interval := p_interval::interval; 1801 IF v_time_interval < '1 second'::interval THEN 1802 RAISE EXCEPTION 'Partitioning interval must be 1 second or greater'; 1803 END IF; 1804 END CASE; 1805 1806 -- First partition is either the min premake or p_start_partition 1807 v_start_time := COALESCE(p_start_partition::timestamp, CURRENT_TIMESTAMP - (v_time_interval * p_premake)); 1808 1809 IF v_time_interval >= '1 year' THEN 1810 v_base_timestamp := date_trunc('year', v_start_time); 1811 IF v_time_interval >= '10 years' THEN 1812 v_base_timestamp := date_trunc('decade', v_start_time); 1813 IF v_time_interval >= '100 years' THEN 1814 v_base_timestamp := date_trunc('century', v_start_time); 1815 IF v_time_interval >= '1000 years' THEN 1816 v_base_timestamp := date_trunc('millennium', v_start_time); 1817 END IF; -- 1000 1818 END IF; -- 100 1819 END IF; -- 10 1820 END IF; -- 1 1821 1822 v_datetime_string := 'YYYY'; 1823 IF v_time_interval < '1 year' THEN 1824 IF p_interval = 'quarterly' THEN 1825 v_base_timestamp := date_trunc('quarter', v_start_time); 1826 v_datetime_string = 'YYYY"q"Q'; 1827 ELSE 1828 v_base_timestamp := date_trunc('month', v_start_time); 1829 v_datetime_string := v_datetime_string || '_MM'; 1830 END IF; 1831 IF v_time_interval < '1 month' THEN 1832 IF p_interval = 'weekly' THEN 1833 v_base_timestamp := date_trunc('week', v_start_time); 1834 v_datetime_string := 'IYYY"w"IW'; 1835 ELSE 1836 v_base_timestamp := date_trunc('day', v_start_time); 1837 v_datetime_string := v_datetime_string || '_DD'; 1838 END IF; 1839 IF v_time_interval < '1 day' THEN 1840 v_base_timestamp := date_trunc('hour', v_start_time); 1841 v_datetime_string := v_datetime_string || '_HH24MI'; 1842 IF v_time_interval < '1 minute' THEN 1843 v_base_timestamp := date_trunc('minute', v_start_time); 1844 v_datetime_string := v_datetime_string || 'SS'; 1845 END IF; -- minute 1846 END IF; -- day 1847 END IF; -- month 1848 END IF; -- year 1849 1850 v_partition_time_array := array_append(v_partition_time_array, v_base_timestamp); 1851 LOOP 1852 -- If current loop value is less than or equal to the value of the max premake, add time to array. 1853 IF (v_base_timestamp + (v_time_interval * v_count)) < (CURRENT_TIMESTAMP + (v_time_interval * p_premake)) THEN 1854 BEGIN 1855 v_partition_time := (v_base_timestamp + (v_time_interval * v_count))::timestamp; 1856 v_partition_time_array := array_append(v_partition_time_array, v_partition_time); 1857 EXCEPTION WHEN datetime_field_overflow THEN 1858 RAISE WARNING 'Attempted partition time interval is outside PostgreSQL''s supported time range. 1859 Child partition creation after time % skipped', v_partition_time; 1860 v_step_overflow_id := add_step(v_job_id, 'Attempted partition time interval is outside PostgreSQL''s supported time range.'); 1861 PERFORM update_step(v_step_overflow_id, 'CRITICAL', 'Child partition creation after time '||v_partition_time||' skipped'); 1862 CONTINUE; 1863 END; 1864 ELSE 1865 EXIT; -- all needed partitions added to array. Exit the loop. 1866 END IF; 1867 v_count := v_count + 1; 1868 END LOOP; 1869 1870 INSERT INTO @extschema@.part_config ( 1871 parent_table 1872 , partition_type 1873 , partition_interval 1874 , epoch 1875 , control 1876 , premake 1877 , constraint_cols 1878 , datetime_string 1879 , use_run_maintenance 1880 , inherit_fk 1881 , jobmon) 1882 VALUES ( 1883 p_parent_table 1884 , p_type 1885 , v_time_interval 1886 , p_epoch 1887 , p_control 1888 , p_premake 1889 , p_constraint_cols 1890 , v_datetime_string 1891 , v_run_maint 1892 , p_inherit_fk 1893 , p_jobmon); 1894 v_last_partition_created := @extschema@.create_partition_time(p_parent_table, v_partition_time_array, false); 1895 1896 IF v_last_partition_created = false THEN 1897 -- This can happen with subpartitioning when future or past partitions prevent child creation because they're out of range of the parent 1898 -- First see if this parent is a subpartition managed by pg_partman 1899 WITH top_oid AS ( 1900 SELECT i.inhparent AS top_parent_oid 1901 FROM pg_catalog.pg_inherits i 1902 JOIN pg_catalog.pg_class c ON c.oid = i.inhrelid 1903 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 1904 WHERE c.relname = v_parent_tablename 1905 AND n.nspname = v_parent_schema 1906 ) SELECT n.nspname, c.relname 1907 INTO v_top_parent_schema, v_top_parent_table 1908 FROM pg_catalog.pg_class c 1909 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 1910 JOIN top_oid t ON c.oid = t.top_parent_oid 1911 JOIN @extschema@.part_config p ON p.parent_table = n.nspname||'.'||c.relname; 1912 IF v_top_parent_table IS NOT NULL THEN 1913 -- If so create the lowest possible partition that is within the boundary of the parent 1914 SELECT child_start_time INTO v_parent_partition_timestamp FROM @extschema@.show_partition_info(p_parent_table, p_parent_table := v_top_parent_schema||'.'||v_top_parent_table); 1915 IF v_base_timestamp >= v_parent_partition_timestamp THEN 1916 WHILE v_base_timestamp >= v_parent_partition_timestamp LOOP 1917 v_base_timestamp := v_base_timestamp - v_time_interval; 1918 END LOOP; 1919 v_base_timestamp := v_base_timestamp + v_time_interval; -- add one back since while loop set it one lower than is needed 1920 ELSIF v_base_timestamp < v_parent_partition_timestamp THEN 1921 WHILE v_base_timestamp < v_parent_partition_timestamp LOOP 1922 v_base_timestamp := v_base_timestamp + v_time_interval; 1923 END LOOP; 1924 -- Don't need to remove one since new starting time will fit in top parent interval 1925 END IF; 1926 v_partition_time_array := NULL; 1927 v_partition_time_array := array_append(v_partition_time_array, v_base_timestamp); 1928 v_last_partition_created := @extschema@.create_partition_time(p_parent_table, v_partition_time_array, false); 1929 ELSE 1930 -- Currently unknown edge case if code gets here 1931 RAISE EXCEPTION 'No child tables created. Unexpected edge case encountered. Please report this error to author with conditions that led to it.'; 1932 END IF; 1933 END IF; 1934 1935 IF v_jobmon_schema IS NOT NULL THEN 1936 PERFORM update_step(v_step_id, 'OK', format('Time partitions premade: %s', p_premake)); 1937 END IF; 1938END IF; 1939 1940IF p_type = 'id' THEN 1941 v_id_interval := p_interval::bigint; 1942 IF v_id_interval < 10 THEN 1943 RAISE EXCEPTION 'Interval for serial partitioning must be greater than or equal to 10'; 1944 END IF; 1945 1946 -- Check if parent table is a subpartition of an already existing id partition set managed by pg_partman. 1947 WHILE v_higher_parent_table IS NOT NULL LOOP -- initially set in DECLARE 1948 WITH top_oid AS ( 1949 SELECT i.inhparent AS top_parent_oid 1950 FROM pg_catalog.pg_inherits i 1951 JOIN pg_catalog.pg_class c ON c.oid = i.inhrelid 1952 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 1953 WHERE n.nspname = v_higher_parent_schema 1954 AND c.relname = v_higher_parent_table 1955 ) SELECT n.nspname, c.relname 1956 INTO v_higher_parent_schema, v_higher_parent_table 1957 FROM pg_catalog.pg_class c 1958 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 1959 JOIN top_oid t ON c.oid = t.top_parent_oid 1960 JOIN @extschema@.part_config p ON p.parent_table = n.nspname||'.'||c.relname 1961 WHERE p.partition_type = 'id'; 1962 1963 IF v_higher_parent_table IS NOT NULL THEN 1964 -- v_top_parent initially set in DECLARE 1965 v_top_parent_schema := v_higher_parent_schema; 1966 v_top_parent_table := v_higher_parent_table; 1967 END IF; 1968 END LOOP; 1969 1970 -- If custom start partition is set, use that. 1971 -- If custom start is not set and there is already data, start partitioning with the highest current value and ensure it's grabbed from highest top parent table 1972 IF p_start_partition IS NOT NULL THEN 1973 v_max := p_start_partition::bigint; 1974 ELSE 1975 v_sql := format('SELECT COALESCE(max(%I)::bigint, 0) FROM %I.%I LIMIT 1' 1976 , p_control 1977 , v_top_parent_schema 1978 , v_top_parent_table); 1979 EXECUTE v_sql INTO v_max; 1980 END IF; 1981 v_starting_partition_id := v_max - (v_max % v_id_interval); 1982 FOR i IN 0..p_premake LOOP 1983 -- Only make previous partitions if ID value is less than the starting value and positive (and custom start partition wasn't set) 1984 IF p_start_partition IS NULL AND 1985 (v_starting_partition_id - (v_id_interval*i)) > 0 AND 1986 (v_starting_partition_id - (v_id_interval*i)) < v_starting_partition_id 1987 THEN 1988 v_partition_id_array = array_append(v_partition_id_array, (v_starting_partition_id - v_id_interval*i)); 1989 END IF; 1990 v_partition_id_array = array_append(v_partition_id_array, (v_id_interval*i) + v_starting_partition_id); 1991 END LOOP; 1992 1993 INSERT INTO @extschema@.part_config ( 1994 parent_table 1995 , partition_type 1996 , partition_interval 1997 , control 1998 , premake 1999 , constraint_cols 2000 , use_run_maintenance 2001 , inherit_fk 2002 , jobmon) 2003 VALUES ( 2004 p_parent_table 2005 , p_type 2006 , v_id_interval 2007 , p_control 2008 , p_premake 2009 , p_constraint_cols 2010 , v_run_maint 2011 , p_inherit_fk 2012 , p_jobmon); 2013 v_last_partition_created := @extschema@.create_partition_id(p_parent_table, v_partition_id_array, false); 2014 IF v_last_partition_created = false THEN 2015 -- This can happen with subpartitioning when future or past partitions prevent child creation because they're out of range of the parent 2016 -- See if it's actually a subpartition of a parent id partition 2017 WITH top_oid AS ( 2018 SELECT i.inhparent AS top_parent_oid 2019 FROM pg_catalog.pg_inherits i 2020 JOIN pg_catalog.pg_class c ON c.oid = i.inhrelid 2021 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 2022 WHERE c.relname = v_parent_tablename 2023 AND n.nspname = v_parent_schema 2024 ) SELECT n.nspname||'.'||c.relname 2025 INTO v_top_parent_table 2026 FROM pg_catalog.pg_class c 2027 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 2028 JOIN top_oid t ON c.oid = t.top_parent_oid 2029 JOIN @extschema@.part_config p ON p.parent_table = n.nspname||'.'||c.relname 2030 WHERE p.partition_type = 'id'; 2031 IF v_top_parent_table IS NOT NULL THEN 2032 -- Create the lowest possible partition that is within the boundary of the parent 2033 SELECT child_start_id INTO v_parent_partition_id FROM @extschema@.show_partition_info(p_parent_table, p_parent_table := v_top_parent_table); 2034 IF v_starting_partition_id >= v_parent_partition_id THEN 2035 WHILE v_starting_partition_id >= v_parent_partition_id LOOP 2036 v_starting_partition_id := v_starting_partition_id - v_id_interval; 2037 END LOOP; 2038 v_starting_partition_id := v_starting_partition_id + v_id_interval; -- add one back since while loop set it one lower than is needed 2039 ELSIF v_starting_partition_id < v_parent_partition_id THEN 2040 WHILE v_starting_partition_id < v_parent_partition_id LOOP 2041 v_starting_partition_id := v_starting_partition_id + v_id_interval; 2042 END LOOP; 2043 -- Don't need to remove one since new starting id will fit in top parent interval 2044 END IF; 2045 v_partition_id_array = NULL; 2046 v_partition_id_array = array_append(v_partition_id_array, v_starting_partition_id); 2047 v_last_partition_created := @extschema@.create_partition_id(p_parent_table, v_partition_id_array, false); 2048 ELSE 2049 -- Currently unknown edge case if code gets here 2050 RAISE EXCEPTION 'No child tables created. Unexpected edge case encountered. Please report this error to author with conditions that led to it.'; 2051 END IF; 2052 END IF; 2053END IF; 2054 2055IF v_jobmon_schema IS NOT NULL THEN 2056 v_step_id := add_step(v_job_id, 'Creating partition function'); 2057END IF; 2058IF p_type = 'time' OR p_type = 'time-custom' THEN 2059 PERFORM @extschema@.create_function_time(p_parent_table, v_job_id); 2060 IF v_jobmon_schema IS NOT NULL THEN 2061 PERFORM update_step(v_step_id, 'OK', 'Time function created'); 2062 END IF; 2063ELSIF p_type = 'id' THEN 2064 PERFORM @extschema@.create_function_id(p_parent_table, v_job_id); 2065 IF v_jobmon_schema IS NOT NULL THEN 2066 PERFORM update_step(v_step_id, 'OK', 'ID function created'); 2067 END IF; 2068END IF; 2069 2070IF v_jobmon_schema IS NOT NULL THEN 2071 v_step_id := add_step(v_job_id, 'Creating partition trigger'); 2072END IF; 2073PERFORM @extschema@.create_trigger(p_parent_table); 2074 2075IF v_jobmon_schema IS NOT NULL THEN 2076 PERFORM update_step(v_step_id, 'OK', 'Done'); 2077 IF v_step_overflow_id IS NOT NULL THEN 2078 PERFORM fail_job(v_job_id); 2079 ELSE 2080 PERFORM close_job(v_job_id); 2081 END IF; 2082 EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false'); 2083END IF; 2084 2085v_success := true; 2086 2087RETURN v_success; 2088 2089EXCEPTION 2090 WHEN OTHERS THEN 2091 GET STACKED DIAGNOSTICS ex_message = MESSAGE_TEXT, 2092 ex_context = PG_EXCEPTION_CONTEXT, 2093 ex_detail = PG_EXCEPTION_DETAIL, 2094 ex_hint = PG_EXCEPTION_HINT; 2095 IF v_jobmon_schema IS NOT NULL THEN 2096 IF v_job_id IS NULL THEN 2097 EXECUTE format('SELECT %I.add_job(''PARTMAN CREATE PARENT: %s'')', v_jobmon_schema, p_parent_table) INTO v_job_id; 2098 EXECUTE format('SELECT %I.add_step(%s, ''Partition creation for table '||p_parent_table||' failed'')', v_jobmon_schema, v_job_id, p_parent_table) INTO v_step_id; 2099 ELSIF v_step_id IS NULL THEN 2100 EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before first step logged'')', v_jobmon_schema, v_job_id) INTO v_step_id; 2101 END IF; 2102 EXECUTE format('SELECT %I.update_step(%s, ''CRITICAL'', %L)', v_jobmon_schema, v_step_id, 'ERROR: '||coalesce(SQLERRM,'unknown')); 2103 EXECUTE format('SELECT %I.fail_job(%s)', v_jobmon_schema, v_job_id); 2104 END IF; 2105 RAISE EXCEPTION '% 2106CONTEXT: % 2107DETAIL: % 2108HINT: %', ex_message, ex_context, ex_detail, ex_hint; 2109END 2110$$; 2111 2112 2113/* 2114 * Function to drop child tables from an id-based partition set. 2115 * Options to move table to different schema, drop only indexes or actually drop the table from the database. 2116 */ 2117CREATE OR REPLACE 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, p_retention_schema text DEFAULT NULL) RETURNS int 2118 LANGUAGE plpgsql SECURITY DEFINER 2119 AS $$ 2120DECLARE 2121 2122ex_context text; 2123ex_detail text; 2124ex_hint text; 2125ex_message text; 2126v_adv_lock boolean; 2127v_control text; 2128v_drop_count int := 0; 2129v_index record; 2130v_job_id bigint; 2131v_jobmon boolean; 2132v_jobmon_schema text; 2133v_max bigint; 2134v_old_search_path text; 2135v_parent_schema text; 2136v_parent_tablename text; 2137v_partition_interval bigint; 2138v_partition_id bigint; 2139v_retention bigint; 2140v_retention_keep_index boolean; 2141v_retention_keep_table boolean; 2142v_retention_schema text; 2143v_row record; 2144v_row_max_id record; 2145v_step_id bigint; 2146 2147BEGIN 2148 2149v_adv_lock := pg_try_advisory_xact_lock(hashtext('pg_partman drop_partition_id')); 2150IF v_adv_lock = 'false' THEN 2151 RAISE NOTICE 'drop_partition_id already running.'; 2152 RETURN 0; 2153END IF; 2154 2155-- Allow override of configuration options 2156IF p_retention IS NULL THEN 2157 SELECT 2158 partition_interval::bigint 2159 , control 2160 , retention::bigint 2161 , retention_keep_table 2162 , retention_keep_index 2163 , retention_schema 2164 , jobmon 2165 INTO 2166 v_partition_interval 2167 , v_control 2168 , v_retention 2169 , v_retention_keep_table 2170 , v_retention_keep_index 2171 , v_retention_schema 2172 , v_jobmon 2173 FROM @extschema@.part_config 2174 WHERE parent_table = p_parent_table 2175 AND partition_type = 'id' 2176 AND retention IS NOT NULL; 2177 2178 IF v_partition_interval IS NULL THEN 2179 RAISE EXCEPTION 'Configuration for given parent table with a retention period not found: %', p_parent_table; 2180 END IF; 2181ELSE 2182 SELECT 2183 partition_interval::bigint 2184 , control 2185 , retention_keep_table 2186 , retention_keep_index 2187 , retention_schema 2188 , jobmon 2189 INTO 2190 v_partition_interval 2191 , v_control 2192 , v_retention_keep_table 2193 , v_retention_keep_index 2194 , v_retention_schema 2195 , v_jobmon 2196 FROM @extschema@.part_config 2197 WHERE parent_table = p_parent_table 2198 AND partition_type = 'id'; 2199 v_retention := p_retention; 2200 2201 IF v_partition_interval IS NULL THEN 2202 RAISE EXCEPTION 'Configuration for given parent table not found: %', p_parent_table; 2203 END IF; 2204END IF; 2205 2206IF v_jobmon THEN 2207 SELECT nspname INTO v_jobmon_schema FROM pg_namespace n, pg_extension e WHERE e.extname = 'pg_jobmon' AND e.extnamespace = n.oid; 2208 IF v_jobmon_schema IS NOT NULL THEN 2209 SELECT current_setting('search_path') INTO v_old_search_path; 2210 EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', '@extschema@,'||v_jobmon_schema, 'false'); 2211 END IF; 2212END IF; 2213 2214IF p_keep_table IS NOT NULL THEN 2215 v_retention_keep_table = p_keep_table; 2216END IF; 2217IF p_keep_index IS NOT NULL THEN 2218 v_retention_keep_index = p_keep_index; 2219END IF; 2220IF p_retention_schema IS NOT NULL THEN 2221 v_retention_schema = p_retention_schema; 2222END IF; 2223 2224SELECT schemaname, tablename INTO v_parent_schema, v_parent_tablename 2225FROM pg_catalog.pg_tables 2226WHERE schemaname = split_part(p_parent_table, '.', 1) 2227AND tablename = split_part(p_parent_table, '.', 2); 2228 2229-- Loop through child tables starting from highest to get current max value in partition set 2230-- Avoids doing a scan on entire partition set and/or getting any values accidentally in parent. 2231FOR v_row_max_id IN 2232 SELECT partition_schemaname, partition_tablename FROM @extschema@.show_partitions(p_parent_table, 'DESC') 2233LOOP 2234 EXECUTE format('SELECT max(%I) FROM %I.%I', v_control, v_row_max_id.partition_schemaname, v_row_max_id.partition_tablename) INTO v_max; 2235 IF v_max IS NOT NULL THEN 2236 EXIT; 2237 END IF; 2238END LOOP; 2239 2240-- Loop through child tables of the given parent 2241FOR v_row IN 2242 SELECT partition_schemaname, partition_tablename FROM @extschema@.show_partitions(p_parent_table, 'ASC') 2243LOOP 2244 SELECT child_start_id INTO v_partition_id FROM @extschema@.show_partition_info(v_row.partition_schemaname||'.'||v_row.partition_tablename 2245 , v_partition_interval::text 2246 , p_parent_table); 2247 2248 -- Add one interval since partition names contain the start of the constraint period 2249 IF v_retention <= (v_max - (v_partition_id + v_partition_interval)) THEN 2250 -- Only create a jobmon entry if there's actual retention work done 2251 IF v_jobmon_schema IS NOT NULL AND v_job_id IS NULL THEN 2252 v_job_id := add_job(format('PARTMAN DROP ID PARTITION: %s', p_parent_table)); 2253 END IF; 2254 2255 IF v_jobmon_schema IS NOT NULL THEN 2256 v_step_id := add_step(v_job_id, format('Uninherit table %s.%s from %s', v_row.partition_schemaname, v_row.partition_tablename, p_parent_table)); 2257 END IF; 2258 EXECUTE format('ALTER TABLE %I.%I NO INHERIT %I.%I' 2259 , v_row.partition_schemaname 2260 , v_row.partition_tablename 2261 , v_parent_schema 2262 , v_parent_tablename); 2263 IF v_jobmon_schema IS NOT NULL THEN 2264 PERFORM update_step(v_step_id, 'OK', 'Done'); 2265 END IF; 2266 IF v_retention_schema IS NULL THEN 2267 IF v_retention_keep_table = false THEN 2268 IF v_jobmon_schema IS NOT NULL THEN 2269 v_step_id := add_step(v_job_id, format('Drop table %s.%s', v_row.partition_schemaname, v_row.partition_tablename)); 2270 END IF; 2271 EXECUTE format('DROP TABLE %I.%I CASCADE', v_row.partition_schemaname, v_row.partition_tablename); 2272 IF v_jobmon_schema IS NOT NULL THEN 2273 PERFORM update_step(v_step_id, 'OK', 'Done'); 2274 END IF; 2275 ELSIF v_retention_keep_index = false THEN 2276 FOR v_index IN 2277 WITH child_info AS ( 2278 SELECT c1.oid 2279 FROM pg_catalog.pg_class c1 2280 JOIN pg_catalog.pg_namespace n1 ON c1.relnamespace = n1.oid 2281 WHERE c1.relname = v_row.partition_tablename 2282 AND n1.nspname = v_row.partition_schema 2283 ) 2284 SELECT c.relname as name 2285 , con.conname 2286 FROM pg_catalog.pg_index i 2287 JOIN pg_catalog.pg_class c ON i.indexrelid = c.oid 2288 LEFT JOIN pg_catalog.pg_constraint con ON i.indexrelid = con.conindid 2289 JOIN child_info ON i.indrelid = child_info.oid 2290 LOOP 2291 IF v_jobmon_schema IS NOT NULL THEN 2292 v_step_id := add_step(v_job_id, format('Drop index %s from %s.%s' 2293 , v_index.name 2294 , v_row.partition_schemaname 2295 , v_row.partition_tablename)); 2296 END IF; 2297 IF v_index.conname IS NOT NULL THEN 2298 EXECUTE format('ALTER TABLE %I.%I DROP CONSTRAINT %I', v_row.partition_schemaname, v_row.partition_tablename, v_index.conname); 2299 ELSE 2300 EXECUTE format('DROP INDEX %I.%I', v_row.partition_schemaname, v_index.name); 2301 END IF; 2302 IF v_jobmon_schema IS NOT NULL THEN 2303 PERFORM update_step(v_step_id, 'OK', 'Done'); 2304 END IF; 2305 END LOOP; 2306 END IF; 2307 ELSE -- Move to new schema 2308 IF v_jobmon_schema IS NOT NULL THEN 2309 v_step_id := add_step(v_job_id, format('Moving table %s.%s to schema %s' 2310 , v_row.partition_schemaname 2311 , v_row.partition_tablename 2312 , v_retention_schema)); 2313 END IF; 2314 2315 EXECUTE format('ALTER TABLE %I.%I SET SCHEMA %I' 2316 , v_row.partition_schemaname 2317 , v_row.partition_tablename 2318 , v_retention_schema); 2319 2320 IF v_jobmon_schema IS NOT NULL THEN 2321 PERFORM update_step(v_step_id, 'OK', 'Done'); 2322 END IF; 2323 END IF; -- End retention schema if 2324 2325 -- If child table is a subpartition, remove it from part_config & part_config_sub (should cascade due to FK) 2326 DELETE FROM @extschema@.part_config WHERE parent_table = v_row.partition_schemaname ||'.'||v_row.partition_tablename; 2327 2328 v_drop_count := v_drop_count + 1; 2329 END IF; -- End retention check IF 2330 2331END LOOP; -- End child table loop 2332 2333IF v_jobmon_schema IS NOT NULL THEN 2334 IF v_job_id IS NOT NULL THEN 2335 v_step_id := add_step(v_job_id, 'Finished partition drop maintenance'); 2336 PERFORM update_step(v_step_id, 'OK', format('%s partitions dropped.', v_drop_count)); 2337 PERFORM close_job(v_job_id); 2338 END IF; 2339 EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false'); 2340END IF; 2341 2342RETURN v_drop_count; 2343 2344EXCEPTION 2345 WHEN OTHERS THEN 2346 GET STACKED DIAGNOSTICS ex_message = MESSAGE_TEXT, 2347 ex_context = PG_EXCEPTION_CONTEXT, 2348 ex_detail = PG_EXCEPTION_DETAIL, 2349 ex_hint = PG_EXCEPTION_HINT; 2350 IF v_jobmon_schema IS NOT NULL THEN 2351 IF v_job_id IS NULL THEN 2352 EXECUTE format('SELECT %I.add_job(''PARTMAN DROP ID PARTITION: %s'')', v_jobmon_schema, p_parent_table) INTO v_job_id; 2353 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; 2354 ELSIF v_step_id IS NULL THEN 2355 EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before first step logged'')', v_jobmon_schema, v_job_id) INTO v_step_id; 2356 END IF; 2357 EXECUTE format('SELECT %I.update_step(%s, ''CRITICAL'', %L)', v_jobmon_schema, v_step_id, 'ERROR: '||coalesce(SQLERRM,'unknown')); 2358 EXECUTE format('SELECT %I.fail_job(%s)', v_jobmon_schema, v_job_id); 2359 END IF; 2360 RAISE EXCEPTION '% 2361CONTEXT: % 2362DETAIL: % 2363HINT: %', ex_message, ex_context, ex_detail, ex_hint; 2364END 2365$$; 2366 2367 2368/* 2369 * Function to drop child tables from a time-based partition set. 2370 * Options to move table to different schema, drop only indexes or actually drop the table from the database. 2371 */ 2372CREATE OR REPLACE 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, p_retention_schema text DEFAULT NULL) RETURNS int 2373 LANGUAGE plpgsql SECURITY DEFINER 2374 AS $$ 2375DECLARE 2376 2377ex_context text; 2378ex_detail text; 2379ex_hint text; 2380ex_message text; 2381v_adv_lock boolean; 2382v_datetime_string text; 2383v_drop_count int := 0; 2384v_index record; 2385v_job_id bigint; 2386v_jobmon boolean; 2387v_jobmon_schema text; 2388v_old_search_path text; 2389v_parent_schema text; 2390v_parent_tablename text; 2391v_partition_interval interval; 2392v_partition_timestamp timestamp; 2393v_retention interval; 2394v_retention_keep_index boolean; 2395v_retention_keep_table boolean; 2396v_retention_schema text; 2397v_row record; 2398v_step_id bigint; 2399v_type text; 2400 2401BEGIN 2402 2403v_adv_lock := pg_try_advisory_xact_lock(hashtext('pg_partman drop_partition_time')); 2404IF v_adv_lock = 'false' THEN 2405 RAISE NOTICE 'drop_partition_time already running.'; 2406 RETURN 0; 2407END IF; 2408 2409-- Allow override of configuration options 2410IF p_retention IS NULL THEN 2411 SELECT 2412 partition_type 2413 , partition_interval::interval 2414 , retention::interval 2415 , retention_keep_table 2416 , retention_keep_index 2417 , datetime_string 2418 , retention_schema 2419 , jobmon 2420 INTO 2421 v_type 2422 , v_partition_interval 2423 , v_retention 2424 , v_retention_keep_table 2425 , v_retention_keep_index 2426 , v_datetime_string 2427 , v_retention_schema 2428 , v_jobmon 2429 FROM @extschema@.part_config 2430 WHERE parent_table = p_parent_table 2431 AND (partition_type = 'time' OR partition_type = 'time-custom') 2432 AND retention IS NOT NULL; 2433 2434 IF v_partition_interval IS NULL THEN 2435 RAISE EXCEPTION 'Configuration for given parent table with a retention period not found: %', p_parent_table; 2436 END IF; 2437ELSE 2438 SELECT 2439 partition_type 2440 , partition_interval::interval 2441 , retention_keep_table 2442 , retention_keep_index 2443 , datetime_string 2444 , retention_schema 2445 , jobmon 2446 INTO 2447 v_type 2448 , v_partition_interval 2449 , v_retention_keep_table 2450 , v_retention_keep_index 2451 , v_datetime_string 2452 , v_retention_schema 2453 , v_jobmon 2454 FROM @extschema@.part_config 2455 WHERE parent_table = p_parent_table 2456 AND (partition_type = 'time' OR partition_type = 'time-custom'); 2457 v_retention := p_retention; 2458 2459 IF v_partition_interval IS NULL THEN 2460 RAISE EXCEPTION 'Configuration for given parent table not found: %', p_parent_table; 2461 END IF; 2462END IF; 2463 2464IF v_jobmon THEN 2465 SELECT nspname INTO v_jobmon_schema FROM pg_namespace n, pg_extension e WHERE e.extname = 'pg_jobmon' AND e.extnamespace = n.oid; 2466 IF v_jobmon_schema IS NOT NULL THEN 2467 SELECT current_setting('search_path') INTO v_old_search_path; 2468 EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', '@extschema@,'||v_jobmon_schema, 'false'); 2469 END IF; 2470END IF; 2471 2472IF p_keep_table IS NOT NULL THEN 2473 v_retention_keep_table = p_keep_table; 2474END IF; 2475IF p_keep_index IS NOT NULL THEN 2476 v_retention_keep_index = p_keep_index; 2477END IF; 2478IF p_retention_schema IS NOT NULL THEN 2479 v_retention_schema = p_retention_schema; 2480END IF; 2481 2482SELECT schemaname, tablename INTO v_parent_schema, v_parent_tablename 2483FROM pg_catalog.pg_tables 2484WHERE schemaname = split_part(p_parent_table, '.', 1) 2485AND tablename = split_part(p_parent_table, '.', 2); 2486 2487-- Loop through child tables of the given parent 2488FOR v_row IN 2489 SELECT partition_schemaname, partition_tablename FROM @extschema@.show_partitions(p_parent_table, 'DESC') 2490LOOP 2491 -- pull out datetime portion of partition's tablename to make the next one 2492 SELECT child_start_time INTO v_partition_timestamp FROM @extschema@.show_partition_info(v_row.partition_schemaname||'.'||v_row.partition_tablename 2493 , v_partition_interval::text 2494 , p_parent_table); 2495 2496 -- Add one interval since partition names contain the start of the constraint period 2497 IF v_retention < (CURRENT_TIMESTAMP - (v_partition_timestamp + v_partition_interval)) THEN 2498 -- Only create a jobmon entry if there's actual retention work done 2499 IF v_jobmon_schema IS NOT NULL AND v_job_id IS NULL THEN 2500 v_job_id := add_job(format('PARTMAN DROP TIME PARTITION: %s', p_parent_table)); 2501 END IF; 2502 2503 IF v_jobmon_schema IS NOT NULL THEN 2504 v_step_id := add_step(v_job_id, format('Uninherit table %s.%s from %s' 2505 , v_row.partition_schemaname 2506 , v_row.partition_tablename 2507 , p_parent_table)); 2508 END IF; 2509 EXECUTE format('ALTER TABLE %I.%I NO INHERIT %I.%I' 2510 , v_row.partition_schemaname 2511 , v_row.partition_tablename 2512 , v_parent_schema 2513 , v_parent_tablename); 2514 IF v_type = 'time-custom' THEN 2515 DELETE FROM @extschema@.custom_time_partitions WHERE parent_table = p_parent_table AND child_table = v_row.partition_schemaname||'.'||v_row.partition_tablename; 2516 END IF; 2517 IF v_jobmon_schema IS NOT NULL THEN 2518 PERFORM update_step(v_step_id, 'OK', 'Done'); 2519 END IF; 2520 IF v_retention_schema IS NULL THEN 2521 IF v_retention_keep_table = false THEN 2522 IF v_jobmon_schema IS NOT NULL THEN 2523 v_step_id := add_step(v_job_id, format('Drop table %s.%s', v_row.partition_schemaname, v_row.partition_tablename)); 2524 END IF; 2525 EXECUTE format('DROP TABLE %I.%I CASCADE', v_row.partition_schemaname, v_row.partition_tablename); 2526 IF v_jobmon_schema IS NOT NULL THEN 2527 PERFORM update_step(v_step_id, 'OK', 'Done'); 2528 END IF; 2529 ELSIF v_retention_keep_index = false THEN 2530 FOR v_index IN 2531 WITH child_info AS ( 2532 SELECT c1.oid 2533 FROM pg_catalog.pg_class c1 2534 JOIN pg_catalog.pg_namespace n1 ON c1.relnamespace = n1.oid 2535 WHERE c1.relname = v_row.partition_tablename 2536 AND n1.nspname = v_row.partition_schemaname 2537 ) 2538 SELECT c.relname as name 2539 , con.conname 2540 FROM pg_catalog.pg_index i 2541 JOIN pg_catalog.pg_class c ON i.indexrelid = c.oid 2542 LEFT JOIN pg_catalog.pg_constraint con ON i.indexrelid = con.conindid 2543 JOIN child_info ON i.indrelid = child_info.oid 2544 LOOP 2545 IF v_jobmon_schema IS NOT NULL THEN 2546 v_step_id := add_step(v_job_id, format('Drop index %s from %s.%s' 2547 , v_index.name 2548 , v_row.partition_schemaname 2549 , v_row.partition_tablename)); 2550 END IF; 2551 IF v_index.conname IS NOT NULL THEN 2552 EXECUTE format('ALTER TABLE %I.%I DROP CONSTRAINT %I' 2553 , v_row.partition_schemaname 2554 , v_row.partition_tablename 2555 , v_index.conname); 2556 ELSE 2557 EXECUTE format('DROP INDEX %I.%I', v_parent_schema, v_index.name); 2558 END IF; 2559 IF v_jobmon_schema IS NOT NULL THEN 2560 PERFORM update_step(v_step_id, 'OK', 'Done'); 2561 END IF; 2562 END LOOP; 2563 END IF; 2564 ELSE -- Move to new schema 2565 IF v_jobmon_schema IS NOT NULL THEN 2566 v_step_id := add_step(v_job_id, format('Moving table %s.%s to schema %s' 2567 , v_row.partition_schemaname 2568 , v_row.partition_tablename 2569 , v_retention_schema)); 2570 END IF; 2571 2572 EXECUTE format('ALTER TABLE %I.%I SET SCHEMA %I', v_row.partition_schemaname, v_row.partition_tablename, v_retention_schema); 2573 2574 2575 IF v_jobmon_schema IS NOT NULL THEN 2576 PERFORM update_step(v_step_id, 'OK', 'Done'); 2577 END IF; 2578 END IF; -- End retention schema if 2579 2580 -- If child table is a subpartition, remove it from part_config & part_config_sub (should cascade due to FK) 2581 DELETE FROM @extschema@.part_config WHERE parent_table = v_row.partition_schemaname||'.'||v_row.partition_tablename; 2582 2583 v_drop_count := v_drop_count + 1; 2584 END IF; -- End retention check IF 2585 2586END LOOP; -- End child table loop 2587 2588IF v_jobmon_schema IS NOT NULL THEN 2589 IF v_job_id IS NOT NULL THEN 2590 v_step_id := add_step(v_job_id, 'Finished partition drop maintenance'); 2591 PERFORM update_step(v_step_id, 'OK', format('%s partitions dropped.', v_drop_count)); 2592 PERFORM close_job(v_job_id); 2593 END IF; 2594 EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false'); 2595END IF; 2596 2597RETURN v_drop_count; 2598 2599EXCEPTION 2600 WHEN OTHERS THEN 2601 GET STACKED DIAGNOSTICS ex_message = MESSAGE_TEXT, 2602 ex_context = PG_EXCEPTION_CONTEXT, 2603 ex_detail = PG_EXCEPTION_DETAIL, 2604 ex_hint = PG_EXCEPTION_HINT; 2605 IF v_jobmon_schema IS NOT NULL THEN 2606 IF v_job_id IS NULL THEN 2607 EXECUTE format('SELECT %I.add_job(''PARTMAN DROP TIME PARTITION: %s'')', v_jobmon_schema, p_parent_table) INTO v_job_id; 2608 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; 2609 ELSIF v_step_id IS NULL THEN 2610 EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before first step logged'')', v_jobmon_schema, v_job_id) INTO v_step_id; 2611 END IF; 2612 EXECUTE format('SELECT %I.update_step(%s, ''CRITICAL'', %L)', v_jobmon_schema, v_step_id, 'ERROR: '||coalesce(SQLERRM,'unknown')); 2613 EXECUTE format('SELECT %I.fail_job(%s)', v_jobmon_schema, v_job_id); 2614 END IF; 2615 RAISE EXCEPTION '% 2616CONTEXT: % 2617DETAIL: % 2618HINT: %', ex_message, ex_context, ex_detail, ex_hint; 2619END 2620$$; 2621 2622 2623/* 2624 * Populate the child table(s) of a time-based partition set with old data from the original parent 2625 */ 2626CREATE 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 2627 LANGUAGE plpgsql SECURITY DEFINER 2628 AS $$ 2629DECLARE 2630 2631v_control text; 2632v_datetime_string text; 2633v_current_partition_name text; 2634v_epoch boolean; 2635v_last_partition text; 2636v_lock_iter int := 1; 2637v_lock_obtained boolean := FALSE; 2638v_max_partition_timestamp timestamp; 2639v_min_partition_timestamp timestamp; 2640v_parent_schema text; 2641v_parent_tablename text; 2642v_partition_interval interval; 2643v_partition_suffix text; 2644v_partition_timestamp timestamp[]; 2645v_quarter text; 2646v_rowcount bigint; 2647v_sql text; 2648v_start_control timestamp; 2649v_time_position int; 2650v_total_rows bigint := 0; 2651v_type text; 2652v_year text; 2653 2654BEGIN 2655 2656SELECT partition_type 2657 , partition_interval::interval 2658 , control 2659 , datetime_string 2660 , epoch 2661INTO v_type 2662 , v_partition_interval 2663 , v_control 2664 , v_datetime_string 2665 , v_epoch 2666FROM @extschema@.part_config 2667WHERE parent_table = p_parent_table 2668AND (partition_type = 'time' OR partition_type = 'time-custom'); 2669IF NOT FOUND THEN 2670 RAISE EXCEPTION 'ERROR: no config found for %', p_parent_table; 2671END IF; 2672 2673IF p_batch_interval IS NULL OR p_batch_interval > v_partition_interval THEN 2674 p_batch_interval := v_partition_interval; 2675END IF; 2676 2677SELECT schemaname, tablename INTO v_parent_schema, v_parent_tablename 2678FROM pg_catalog.pg_tables 2679WHERE schemaname = split_part(p_parent_table, '.', 1) 2680AND tablename = split_part(p_parent_table, '.', 2); 2681 2682SELECT partition_tablename INTO v_last_partition FROM @extschema@.show_partitions(p_parent_table, 'DESC') LIMIT 1; 2683 2684FOR i IN 1..p_batch_count LOOP 2685 2686 IF v_epoch = false THEN 2687 IF p_order = 'ASC' THEN 2688 EXECUTE format('SELECT min(%I) FROM ONLY %I.%I', v_control, v_parent_schema, v_parent_tablename) INTO v_start_control; 2689 ELSIF p_order = 'DESC' THEN 2690 EXECUTE format('SELECT max(%I) FROM ONLY %I.%I', v_control, v_parent_schema, v_parent_tablename) INTO v_start_control; 2691 ELSE 2692 RAISE EXCEPTION 'Invalid value for p_order. Must be ASC or DESC'; 2693 END IF; 2694 ELSE 2695 IF p_order = 'ASC' THEN 2696 EXECUTE format('SELECT to_timestamp(min(%I)) FROM ONLY %I.%I', v_control, v_parent_schema, v_parent_tablename) INTO v_start_control; 2697 ELSIF p_order = 'DESC' THEN 2698 EXECUTE format('SELECT to_timestamp(max(%I)) FROM ONLY %I.%I', v_control, v_parent_schema, v_parent_tablename) INTO v_start_control; 2699 ELSE 2700 RAISE EXCEPTION 'Invalid value for p_order. Must be ASC or DESC'; 2701 END IF; 2702 END IF; 2703 2704 IF v_start_control IS NULL THEN 2705 EXIT; 2706 END IF; 2707 2708 IF v_type = 'time' THEN 2709 CASE 2710 WHEN v_partition_interval = '15 mins' THEN 2711 v_min_partition_timestamp := date_trunc('hour', v_start_control) + 2712 '15min'::interval * floor(date_part('minute', v_start_control) / 15.0); 2713 WHEN v_partition_interval = '30 mins' THEN 2714 v_min_partition_timestamp := date_trunc('hour', v_start_control) + 2715 '30min'::interval * floor(date_part('minute', v_start_control) / 30.0); 2716 WHEN v_partition_interval = '1 hour' THEN 2717 v_min_partition_timestamp := date_trunc('hour', v_start_control); 2718 WHEN v_partition_interval = '1 day' THEN 2719 v_min_partition_timestamp := date_trunc('day', v_start_control); 2720 WHEN v_partition_interval = '1 week' THEN 2721 v_min_partition_timestamp := date_trunc('week', v_start_control); 2722 WHEN v_partition_interval = '1 month' THEN 2723 v_min_partition_timestamp := date_trunc('month', v_start_control); 2724 WHEN v_partition_interval = '3 months' THEN 2725 v_min_partition_timestamp := date_trunc('quarter', v_start_control); 2726 WHEN v_partition_interval = '1 year' THEN 2727 v_min_partition_timestamp := date_trunc('year', v_start_control); 2728 END CASE; 2729 ELSIF v_type = 'time-custom' THEN 2730 SELECT child_start_time INTO v_min_partition_timestamp FROM @extschema@.show_partition_info(v_parent_schema||'.'||v_last_partition 2731 , v_partition_interval 2732 , p_parent_table); 2733 v_max_partition_timestamp := v_min_partition_timestamp + v_partition_interval; 2734 LOOP 2735 IF v_start_control >= v_min_partition_timestamp AND v_start_control < v_max_partition_timestamp THEN 2736 EXIT; 2737 ELSE 2738 BEGIN 2739 IF v_start_control > v_max_partition_timestamp THEN 2740 -- Keep going forward in time, checking if child partition time interval encompasses the current v_start_control value 2741 v_min_partition_timestamp := v_max_partition_timestamp; 2742 v_max_partition_timestamp := v_max_partition_timestamp + v_partition_interval; 2743 2744 ELSE 2745 -- Keep going backwards in time, checking if child partition time interval encompasses the current v_start_control value 2746 v_max_partition_timestamp := v_min_partition_timestamp; 2747 v_min_partition_timestamp := v_min_partition_timestamp - v_partition_interval; 2748 END IF; 2749 EXCEPTION WHEN datetime_field_overflow THEN 2750 RAISE EXCEPTION 'Attempted partition time interval is outside PostgreSQL''s supported time range. 2751 Unable to create partition with interval before timestamp % ', v_min_partition_interval; 2752 END; 2753 END IF; 2754 END LOOP; 2755 2756 END IF; 2757 2758 v_partition_timestamp := ARRAY[v_min_partition_timestamp]; 2759 IF p_order = 'ASC' THEN 2760 -- Ensure batch interval given as parameter doesn't cause maximum to overflow the current partition maximum 2761 IF (v_start_control + p_batch_interval) >= (v_min_partition_timestamp + v_partition_interval) THEN 2762 v_max_partition_timestamp := v_min_partition_timestamp + v_partition_interval; 2763 ELSE 2764 v_max_partition_timestamp := v_start_control + p_batch_interval; 2765 END IF; 2766 ELSIF p_order = 'DESC' THEN 2767 -- Must be greater than max value still in parent table since query below grabs < max 2768 v_max_partition_timestamp := v_min_partition_timestamp + v_partition_interval; 2769 -- Ensure batch interval given as parameter doesn't cause minimum to underflow current partition minimum 2770 IF (v_start_control - p_batch_interval) >= v_min_partition_timestamp THEN 2771 v_min_partition_timestamp = v_start_control - p_batch_interval; 2772 END IF; 2773 ELSE 2774 RAISE EXCEPTION 'Invalid value for p_order. Must be ASC or DESC'; 2775 END IF; 2776 2777-- do some locking with timeout, if required 2778 IF p_lock_wait > 0 THEN 2779 v_lock_iter := 0; 2780 WHILE v_lock_iter <= 5 LOOP 2781 v_lock_iter := v_lock_iter + 1; 2782 BEGIN 2783 IF v_epoch = false THEN 2784 v_sql := format('SELECT * FROM ONLY %I.%I WHERE %I >= %L AND %I < %L FOR UPDATE NOWAIT' 2785 , v_parent_schema 2786 , v_parent_tablename 2787 , v_control 2788 , v_min_partition_timestamp 2789 , v_control 2790 , v_max_partition_timestamp); 2791 ELSE 2792 v_sql := format('SELECT * FROM ONLY %I.%I WHERE to_timestamp(%I) >= %L AND to_timestamp(%I) < %L FOR UPDATE NOWAIT' 2793 , v_parent_schema 2794 , v_parent_tablename 2795 , v_control 2796 , v_min_partition_timestamp 2797 , v_control 2798 , v_max_partition_timestamp); 2799 END IF; 2800 EXECUTE v_sql; 2801 v_lock_obtained := TRUE; 2802 EXCEPTION 2803 WHEN lock_not_available THEN 2804 PERFORM pg_sleep( p_lock_wait / 5.0 ); 2805 CONTINUE; 2806 END; 2807 EXIT WHEN v_lock_obtained; 2808 END LOOP; 2809 IF NOT v_lock_obtained THEN 2810 RETURN -1; 2811 END IF; 2812 END IF; 2813 2814 PERFORM @extschema@.create_partition_time(p_parent_table, v_partition_timestamp); 2815 -- This suffix generation code is in create_partition_time() as well 2816 v_partition_suffix := to_char(v_min_partition_timestamp, v_datetime_string); 2817 v_current_partition_name := @extschema@.check_name_length(v_parent_tablename, v_partition_suffix, TRUE); 2818 2819 IF v_epoch = false THEN 2820 v_sql := format('WITH partition_data AS ( 2821 DELETE FROM ONLY %I.%I WHERE %I >= %L AND %I < %L RETURNING *) 2822 INSERT INTO %I.%I SELECT * FROM partition_data' 2823 , v_parent_schema 2824 , v_parent_tablename 2825 , v_control 2826 , v_min_partition_timestamp 2827 , v_control 2828 , v_max_partition_timestamp 2829 , v_parent_schema 2830 , v_current_partition_name); 2831 ELSE 2832 v_sql := format('WITH partition_data AS ( 2833 DELETE FROM ONLY %I.%I WHERE to_timestamp(%I) >= %L AND to_timestamp(%I) < %L RETURNING *) 2834 INSERT INTO %I.%I SELECT * FROM partition_data' 2835 , v_parent_schema 2836 , v_parent_tablename 2837 , v_control 2838 , v_min_partition_timestamp 2839 , v_control 2840 , v_max_partition_timestamp 2841 , v_parent_schema 2842 , v_current_partition_name); 2843 END IF; 2844 EXECUTE v_sql; 2845 GET DIAGNOSTICS v_rowcount = ROW_COUNT; 2846 v_total_rows := v_total_rows + v_rowcount; 2847 IF v_rowcount = 0 THEN 2848 EXIT; 2849 END IF; 2850 2851END LOOP; 2852 2853PERFORM @extschema@.create_function_time(p_parent_table); 2854 2855RETURN v_total_rows; 2856 2857END 2858$$; 2859 2860 2861/* 2862 * Given a parent table and partition value, return the name of the child partition it would go in. 2863 * If using epoch time partitioning, give the text representation of the timestamp NOT the epoch integer value (use to_timestamp() to convert epoch values). 2864 * Also returns just the suffix value and true if the child table exists or false if it does not 2865 */ 2866CREATE OR REPLACE FUNCTION show_partition_name(p_parent_table text, p_value text, OUT partition_table text, OUT suffix_timestamp timestamp, OUT suffix_id bigint, OUT table_exists boolean) RETURNS record 2867 LANGUAGE plpgsql STABLE 2868 AS $$ 2869DECLARE 2870 2871v_child_exists text; 2872v_datetime_string text; 2873v_max_range timestamptz; 2874v_min_range timestamptz; 2875v_parent_schema text; 2876v_parent_tablename text; 2877v_partition_interval text; 2878v_type text; 2879 2880BEGIN 2881 2882SELECT partition_type 2883 , partition_interval 2884 , datetime_string 2885INTO v_type 2886 , v_partition_interval 2887 , v_datetime_string 2888FROM @extschema@.part_config 2889WHERE parent_table = p_parent_table; 2890 2891IF v_type IS NULL THEN 2892 RAISE EXCEPTION 'Parent table given is not managed by pg_partman (%)', p_parent_table; 2893END IF; 2894 2895SELECT schemaname, tablename INTO v_parent_schema, v_parent_tablename 2896FROM pg_catalog.pg_tables 2897WHERE schemaname = split_part(p_parent_table, '.', 1) 2898AND tablename = split_part(p_parent_table, '.', 2); 2899IF v_parent_tablename IS NULL THEN 2900 RAISE EXCEPTION 'Parent table given does not exist (%)', p_parent_table; 2901END IF; 2902 2903IF v_type = 'time' THEN 2904 CASE 2905 WHEN v_partition_interval::interval = '15 mins' THEN 2906 suffix_timestamp := date_trunc('hour', p_value::timestamp) + 2907 '15min'::interval * floor(date_part('minute', p_value::timestamp) / 15.0); 2908 WHEN v_partition_interval::interval = '30 mins' THEN 2909 suffix_timestamp := date_trunc('hour', p_value::timestamp) + 2910 '30min'::interval * floor(date_part('minute', p_value::timestamp) / 30.0); 2911 WHEN v_partition_interval::interval = '1 hour' THEN 2912 suffix_timestamp := date_trunc('hour', p_value::timestamp); 2913 WHEN v_partition_interval::interval = '1 day' THEN 2914 suffix_timestamp := date_trunc('day', p_value::timestamp); 2915 WHEN v_partition_interval::interval = '1 week' THEN 2916 suffix_timestamp := date_trunc('week', p_value::timestamp); 2917 WHEN v_partition_interval::interval = '1 month' THEN 2918 suffix_timestamp := date_trunc('month', p_value::timestamp); 2919 WHEN v_partition_interval::interval = '3 months' THEN 2920 suffix_timestamp := date_trunc('quarter', p_value::timestamp); 2921 WHEN v_partition_interval::interval = '1 year' THEN 2922 suffix_timestamp := date_trunc('year', p_value::timestamp); 2923 END CASE; 2924 partition_table := v_parent_schema||'.'||@extschema@.check_name_length(v_parent_tablename, to_char(suffix_timestamp, v_datetime_string), TRUE); 2925ELSIF v_type = 'id' THEN 2926 suffix_id := (p_value::bigint - (p_value::bigint % v_partition_interval::bigint)); 2927 partition_table := v_parent_schema||'.'||@extschema@.check_name_length(v_parent_tablename, suffix_id::text, TRUE); 2928ELSIF v_type = 'time-custom' THEN 2929 2930 SELECT child_table, lower(partition_range) INTO partition_table, suffix_timestamp FROM @extschema@.custom_time_partitions 2931 WHERE parent_table = p_parent_table AND partition_range @> p_value::timestamptz; 2932 2933 IF partition_table IS NULL THEN 2934 SELECT max(upper(partition_range)) INTO v_max_range FROM @extschema@.custom_time_partitions WHERE parent_table = p_parent_table; 2935 SELECT min(lower(partition_range)) INTO v_min_range FROM @extschema@.custom_time_partitions WHERE parent_table = p_parent_table; 2936 IF p_value::timestamp >= v_max_range THEN 2937 suffix_timestamp := v_max_range; 2938 LOOP 2939 -- Keep incrementing higher until given value is below the upper range 2940 suffix_timestamp := suffix_timestamp + v_partition_interval::interval; 2941 IF p_value::timestamp < suffix_timestamp THEN 2942 -- Have to subtract one interval because the value would actually be in the partition previous 2943 -- to this partition timestamp since the partition names contain the lower boundary 2944 suffix_timestamp := suffix_timestamp - v_partition_interval::interval; 2945 EXIT; 2946 END IF; 2947 END LOOP; 2948 ELSIF p_value::timestamp < v_min_range THEN 2949 suffix_timestamp := v_min_range; 2950 LOOP 2951 -- Keep decrementing lower until given value is below or equal to the lower range 2952 suffix_timestamp := suffix_timestamp - v_partition_interval::interval; 2953 IF p_value::timestamp >= suffix_timestamp THEN 2954 EXIT; 2955 END IF; 2956 END LOOP; 2957 ELSE 2958 RAISE EXCEPTION 'Unable to determine a valid child table for the given parent table and value'; 2959 END IF; 2960 2961 partition_table := v_parent_schema||'.'||@extschema@.check_name_length(v_parent_tablename, to_char(suffix_timestamp, v_datetime_string), TRUE); 2962 END IF; 2963END IF; 2964 2965SELECT tablename INTO v_child_exists 2966FROM pg_catalog.pg_tables 2967WHERE schemaname = split_part(partition_table, '.', 1) 2968AND tablename = split_part(partition_table, '.', 2); 2969 2970IF v_child_exists IS NOT NULL THEN 2971 table_exists := true; 2972ELSE 2973 table_exists := false; 2974END IF; 2975 2976RETURN; 2977 2978END 2979$$; 2980 2981-- Restore dropped object privileges 2982DO $$ 2983DECLARE 2984v_row record; 2985BEGIN 2986 FOR v_row IN SELECT statement FROM partman_preserve_privs_temp LOOP 2987 IF v_row.statement IS NOT NULL THEN 2988 EXECUTE v_row.statement; 2989 END IF; 2990 END LOOP; 2991END 2992$$; 2993 2994DROP TABLE IF EXISTS partman_preserve_privs_temp; 2995