1-- PG Partman now supports sub-partitioning. This allows automatic configuration to turn the child tables of an existing partition set into parent tables of their own partition sets. (Github Issue #26) 2 -- New function "create_sub_parent()" works exactly like "create_parent()", even taking similar parameters. Instead, the parent_table you're giving it as a parameter is telling it which parent's child tables to partition and how to partition them. 3 -- This can be chained down as many levels as desired. Just recall the 63 character limit on object names since this will be adding a new partition suffix every level down. The final suffix is always guarenteed to be added on in full, but the parent suffix name may get truncated off. 4 -- Due to logical complexity (and possible contention issues at larger data sizes), when using subpartitioning, all parent tables at all partition levels are set to use run_maintenance() by default. This includes serial partitioning which normally by default can use a trigger based method to create future partitions. You can still set it to false so you can force maintenance to run at specific times (see new run_maintenance() feature below), but you MUST force it to run at some point otherwise new partitions will never be made. 5 -- Note that there will ALWAYS be at least one child partition created, even for subpartition parents that are outside the current trigger range. Data outside the currently covered trigger range will still be inserted the the relevant parent. 6 -- Note that for retention policies, whatever retention period is set on the highest level will be honored and ALL child tables will be dropped, cascading all the way down to the bottom. Use this option even more carefully! 7 8-- New parent table name parameter to run_maintenance(). If set, skips all other tables for that maintenance run and only does the one given. (Github Issue #32) 9 -- This is an optional parameter, so should not affect any existing use of the function. When not given, maintenance is run for all partition sets set to use it in the part_config table. 10 -- The already existing configuration option in part_config (use_run_maintenance) can be used to tell run_maintenance() to skip any partition sets for which you do not want it to run when no table name parameter is given. You can then schedule partition maintenance for specific tables to run at specific times using the new argument to run_maintenance(). Note that if a parent table argument is explicitely given to run_maintenance, it will always run the maintenance for it no matter what the configuration table has set. 11 -- Note that when a table argument is given to run_maintenance(), retention settings will only be run for that one specific table given (if configured). 12 -- Be aware that the "use_run_maintenance" configuration option is always set to true for time-based partitioning & subpartition sets and set false for serial based partitioning (when not subpartitioned) when calling create_parent() or create_sub_parent(). Adjust this configuration setting accordingly so run_maintenance() does what you require after you create your partition sets. 13 -- The trigger constraint on the **part_config** table that would not allow "use_run_maintenance" to be set to false for time based partitioning has been removed. 14 15-- New analyze parameter to run_maintenance(). 16 -- Defaults to true so that if any partition set has a new child table created, an analyze is run on that whole partition set. This is to ensure constraint exclusion works properly. 17 -- Large partition sets were causing run_maintenance() to take a long time to run since the analyze would hold it up. This could cause some contention. 18 -- Setting p_analyze to false will cause the analyze to not run for ALL partition sets that are eligible for new partition creation or retention management at the time it is called. 19 -- If you set this to false, it is advised that you have some other means to ensure a regular analyze is being run on your partition sets. 20 -- NOTE this parameter is set as the second argument since it's likely to be more commonly used, so make sure to check any current run_maintenence() calls to account for this (previously p_jobmon was the second parameter). 21 22-- Analyze is no longer automatically run on the parent table after create_parent() is run. Since create_parent() takes an exclusive lock on the parent table during setup, tables that already had a lot of existing data where being locked for the length of the analyze run, which could be quite long. When data is partitioned out later, analyze is automatically run. Also, whenever new partitions are created in the future, an analyze will be run as well (if the p_analyze argument to run_maintenance() is true which it is by default). Both those cases should take care of updating the planner statistics when it begins to matter. Run an analyze on the parent table after setup if you want to be sure. 23-- Fixed bug in show_partitions() that caused an error when the values in the control column of a serial partition set were larger than the max int value. This would also cause errors when partitioning existing data with values that high since the partitioning functions use show_partitions() internally. (Reported by S. Kristensen) 24-- create_parent() and new create_sub_parent() now return a boolean value to determine whether they succeeded. 25-- For all pythons scripts, changed the --connection default to "host=" instead of "host=localhost". This makes the default connection to the database use the local socket instead of TCP. Makes it act more predictibly like all other postgres executables (psql, pg_dump, etc). Please check any that you many have scheduled to run to ensure they are still working properly. 26-- Added a --version argument to all python scripts. This tells you the minimum version of pg_partman this script is meant to work with. 27-- Made sure all scripts in bin folder are added to Makefile for installation. 28-- Make sure autovacuum is reset if SIGINT (Ctrl+C) is fired when using partition_data.py or undo_partition.py. 29-- Added howto.md file to doc folder with some more extensive examples. 30-- last_partition column in part_config table no longer in use. Dropped it. 31-- Renamed internal functions create_id_partition(), create_id_function(), create_time_partition() & create_time_function() to create_partition_id(), create_function_id(), create_partition_time() & create_function_time() respectively. This gives all functions a consistent naming pattern. 32 33CREATE TEMP TABLE partman_preserve_privs_temp (statement text); 34 35INSERT INTO partman_preserve_privs_temp 36SELECT 'GRANT EXECUTE ON FUNCTION @extschema@.create_parent(text, text, text, text, text[], int, boolean, text, boolean, boolean, boolean) TO '||array_to_string(array_agg(grantee::text), ',')||';' 37FROM information_schema.routine_privileges 38WHERE routine_schema = '@extschema@' 39AND routine_name = 'create_parent'; 40 41INSERT INTO partman_preserve_privs_temp 42SELECT 'GRANT EXECUTE ON FUNCTION @extschema@.create_partition_id(text, bigint[], boolean) TO '||array_to_string(array_agg(grantee::text), ',')||';' 43FROM information_schema.routine_privileges 44WHERE routine_schema = '@extschema@' 45AND routine_name = 'create_id_partition'; 46 47INSERT INTO partman_preserve_privs_temp 48SELECT 'GRANT EXECUTE ON FUNCTION @extschema@.create_partition_time(text, timestamp[], boolean) TO '||array_to_string(array_agg(grantee::text), ',')||';' 49FROM information_schema.routine_privileges 50WHERE routine_schema = '@extschema@' 51AND routine_name = 'create_time_partition'; 52 53INSERT INTO partman_preserve_privs_temp 54SELECT 'GRANT EXECUTE ON FUNCTION @extschema@.create_function_time(text) TO '||array_to_string(array_agg(grantee::text), ',')||';' 55FROM information_schema.routine_privileges 56WHERE routine_schema = '@extschema@' 57AND routine_name = 'create_time_function'; 58 59INSERT INTO partman_preserve_privs_temp 60SELECT 'GRANT EXECUTE ON FUNCTION @extschema@.create_function_id(text) TO '||array_to_string(array_agg(grantee::text), ',')||';' 61FROM information_schema.routine_privileges 62WHERE routine_schema = '@extschema@' 63AND routine_name = 'create_id_function'; 64 65INSERT INTO partman_preserve_privs_temp 66SELECT 'GRANT EXECUTE ON FUNCTION @extschema@.run_maintenance(text, boolean, boolean) TO '||array_to_string(array_agg(grantee::text), ',')||';' 67FROM information_schema.routine_privileges 68WHERE routine_schema = '@extschema@' 69AND routine_name = 'run_maintenance'; 70 71DROP TRIGGER time_partition_maintenance_true_trig ON @extschema@.part_config; 72DROP FUNCTION @extschema@.time_partition_maintenance_true_trig(); 73DROP FUNCTION @extschema@.create_parent(text, text, text, text, text[], int, boolean, text, boolean, boolean, boolean); 74DROP FUNCTION @extschema@.create_id_partition (text, bigint[]); 75DROP FUNCTION @extschema@.create_time_partition (text, timestamp[]); 76DROP FUNCTION @extschema@.create_time_function(text); 77DROP FUNCTION @extschema@.create_id_function(text); 78DROP FUNCTION @extschema@.run_maintenance(boolean); 79 80-- FK set deferrable because create_parent() inserts to this table before part_config 81CREATE TABLE @extschema@.part_config_sub ( 82 sub_parent text PRIMARY KEY REFERENCES @extschema@.part_config (parent_table) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED 83 , sub_type text NOT NULL 84 , sub_control text NOT NULL 85 , sub_part_interval text NOT NULL 86 , sub_constraint_cols text[] 87 , sub_premake int NOT NULL DEFAULT 4 88 , sub_inherit_fk boolean NOT NULL DEFAULT true 89 , sub_retention text 90 , sub_retention_schema text 91 , sub_retention_keep_table boolean NOT NULL DEFAULT true 92 , sub_retention_keep_index boolean NOT NULL DEFAULT true 93 , sub_use_run_maintenance BOOLEAN NOT NULL DEFAULT true 94 , sub_jobmon boolean NOT NULL DEFAULT true 95); 96 97ALTER TABLE @extschema@.part_config DROP COLUMN last_partition; 98 99/* 100 * Ensure that sub-partitioned tables that are themselves sub-partitions have the same configuration options set when they are part of the same inheritance tree 101 */ 102CREATE FUNCTION check_subpart_sameconfig(text) RETURNS boolean 103 LANGUAGE sql STABLE 104 AS $$ 105 WITH child_tables AS ( 106 SELECT n.nspname||'.'||c.relname AS tablename 107 FROM pg_catalog.pg_inherits h 108 JOIN pg_catalog.pg_class c ON c.oid = h.inhrelid 109 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 110 WHERE h.inhparent::regclass = $1::regclass 111 ) 112 SELECT CASE 113 WHEN count(*) <= 1 THEN 114 true 115 WHEN count(*) > 1 THEN 116 false 117 END 118 FROM ( 119 SELECT DISTINCT sub_type 120 , sub_control 121 , sub_part_interval 122 , sub_constraint_cols 123 , sub_premake 124 , sub_inherit_fk 125 , sub_retention 126 , sub_retention_schema 127 , sub_retention_keep_table 128 , sub_retention_keep_index 129 , sub_use_run_maintenance 130 , sub_jobmon 131 FROM @extschema@.part_config_sub a 132 JOIN child_tables b on a.sub_parent = b.tablename) x; 133$$; 134 135ALTER TABLE @extschema@.part_config_sub 136ADD CONSTRAINT subpart_sameconfig_chk 137CHECK (check_subpart_sameconfig(sub_parent)); 138 139/* 140 * Create a partition set that is a subpartition of an already existing partition set. 141 * Given the parent table of any current partition set, it will turn all existing children into parent tables of their own partition sets 142 * using the configuration options given as parameters to this function. 143 * Uses another config table that allows for turning all future child partitions into a new parent automatically. 144 * To avoid logical complications and contention issues, ALL subpartitions must be maintained using run_maintenance(). 145 * This means the automatic, trigger based partition creation for serial partitioning will not work if it is a subpartition. 146 */ 147CREATE FUNCTION create_sub_parent( 148 p_top_parent text 149 , p_control text 150 , p_type text 151 , p_interval text 152 , p_constraint_cols text[] DEFAULT NULL 153 , p_premake int DEFAULT 4 154 , p_start_partition text DEFAULT NULL 155 , p_inherit_fk boolean DEFAULT true 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_row record; 165v_row_last_part record; 166v_run_maint boolean; 167v_sql text; 168v_success boolean := false; 169v_top_type text; 170 171BEGIN 172 173SELECT use_run_maintenance INTO v_run_maint FROM @extschema@.part_config WHERE parent_table = p_top_parent; 174IF v_run_maint IS NULL THEN 175 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; 176ELSIF v_run_maint = false THEN 177 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.'; 178END IF; 179 180FOR v_row IN 181 -- Loop through all current children to turn them into partitioned tables 182 SELECT show_partitions AS child_table FROM @extschema@.show_partitions(p_top_parent) 183LOOP 184 -- Just call existing create_parent() function but add the given parameters to the part_config_sub table as well 185 v_sql := format('SELECT @extschema@.create_parent( 186 p_parent_table := %L 187 , p_control := %L 188 , p_type := %L 189 , p_interval := %L 190 , p_constraint_cols := %L 191 , p_premake := %L 192 , p_use_run_maintenance := %L 193 , p_start_partition := %L 194 , p_inherit_fk := %L 195 , p_jobmon := %L 196 , p_debug := %L )' 197 , v_row.child_table 198 , p_control 199 , p_type 200 , p_interval 201 , p_constraint_cols 202 , p_premake 203 , true 204 , p_start_partition 205 , p_inherit_fk 206 , p_jobmon 207 , p_debug); 208 EXECUTE v_sql; 209 210END LOOP; 211 212INSERT INTO @extschema@.part_config_sub ( 213 sub_parent 214 , sub_control 215 , sub_type 216 , sub_part_interval 217 , sub_constraint_cols 218 , sub_premake 219 , sub_inherit_fk 220 , sub_use_run_maintenance 221 , sub_jobmon) 222VALUES ( 223 p_top_parent 224 , p_control 225 , p_type 226 , p_interval 227 , p_constraint_cols 228 , p_premake 229 , p_inherit_fk 230 , true 231 , p_jobmon); 232 233v_success := true; 234 235RETURN v_success; 236 237END 238$$; 239 240 241/* 242 * Function to create id partitions 243 */ 244CREATE FUNCTION create_partition_id(p_parent_table text, p_partition_ids bigint[], p_analyze boolean DEFAULT true) RETURNS boolean 245 LANGUAGE plpgsql SECURITY DEFINER 246 AS $$ 247DECLARE 248 249v_all text[] := ARRAY['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER']; 250v_analyze boolean := FALSE; 251v_control text; 252v_grantees text[]; 253v_hasoids boolean; 254v_id bigint; 255v_id_position int; 256v_inherit_fk boolean; 257v_job_id bigint; 258v_jobmon boolean; 259v_jobmon_schema text; 260v_old_search_path text; 261v_parent_grant record; 262v_parent_owner text; 263v_parent_schema text; 264v_parent_tablename text; 265v_parent_tablespace text; 266v_part_interval bigint; 267v_partition_created boolean := false; 268v_partition_name text; 269v_revoke text[]; 270v_row record; 271v_sql text; 272v_step_id bigint; 273v_sub_id_max bigint; 274v_sub_id_min bigint; 275v_tablename text; 276v_top_interval bigint; 277v_top_parent text; 278v_unlogged char; 279 280BEGIN 281 282SELECT control 283 , part_interval 284 , inherit_fk 285 , jobmon 286INTO v_control 287 , v_part_interval 288 , v_inherit_fk 289 , v_jobmon 290FROM @extschema@.part_config 291WHERE parent_table = p_parent_table 292AND (type = 'id-static' OR type = 'id-dynamic'); 293 294IF NOT FOUND THEN 295 RAISE EXCEPTION 'ERROR: no config found for %', p_parent_table; 296END IF; 297 298IF v_jobmon THEN 299 SELECT nspname INTO v_jobmon_schema FROM pg_namespace n, pg_extension e WHERE e.extname = 'pg_jobmon' AND e.extnamespace = n.oid; 300 IF v_jobmon_schema IS NOT NULL THEN 301 SELECT current_setting('search_path') INTO v_old_search_path; 302 EXECUTE 'SELECT set_config(''search_path'',''@extschema@,'||v_jobmon_schema||''',''false'')'; 303 END IF; 304END IF; 305 306-- Check if parent table is a subpartition of an already existing id based partition set managed by pg_partman 307-- If so, limit what child tables can be created based on parent suffix 308WITH top_oid AS ( 309 SELECT i.inhparent AS top_parent_oid 310 FROM pg_catalog.pg_class c 311 JOIN pg_catalog.pg_inherits i ON c.oid = i.inhrelid 312 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 313 WHERE n.nspname||'.'||c.relname = p_parent_table 314) SELECT n.nspname||'.'||c.relname 315 INTO v_top_parent 316 FROM pg_catalog.pg_class c 317 JOIN top_oid t ON c.oid = t.top_parent_oid 318 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 319 JOIN @extschema@.part_config p ON p.parent_table = n.nspname||'.'||c.relname 320 WHERE c.oid = t.top_parent_oid 321 AND p.type = 'id-static' OR p.type = 'id-dynamic'; 322 323IF v_top_parent IS NOT NULL THEN 324 SELECT part_interval::bigint INTO v_top_interval FROM @extschema@.part_config WHERE parent_table = v_top_parent; 325 v_id_position := (length(p_parent_table) - position('p_' in reverse(p_parent_table))) + 2; 326 v_sub_id_min = substring(p_parent_table from v_id_position)::bigint; 327 v_sub_id_max = (v_sub_id_min + v_top_interval) - 1; 328END IF; 329 330SELECT tableowner, schemaname, tablename, tablespace INTO v_parent_owner, v_parent_schema, v_parent_tablename, v_parent_tablespace FROM pg_tables WHERE schemaname ||'.'|| tablename = p_parent_table; 331 332FOREACH v_id IN ARRAY p_partition_ids LOOP 333-- Do not create the child table if it's outside the bounds of the top parent. 334 IF v_sub_id_min IS NOT NULL THEN 335 IF v_id < v_sub_id_min OR v_id > v_sub_id_max THEN 336 CONTINUE; 337 END IF; 338 END IF; 339 340 v_partition_name := @extschema@.check_name_length(v_parent_tablename, v_parent_schema, v_id::text, TRUE); 341 -- If child table already exists, skip creation 342 SELECT tablename INTO v_tablename FROM pg_catalog.pg_tables WHERE schemaname ||'.'|| tablename = v_partition_name; 343 IF v_tablename IS NOT NULL THEN 344 CONTINUE; 345 END IF; 346 347 -- Ensure analyze is run if a new partition is created. Otherwise if one isn't, will be false and analyze will be skipped 348 v_analyze := TRUE; 349 350 IF v_jobmon_schema IS NOT NULL THEN 351 v_job_id := add_job('PARTMAN CREATE TABLE: '||p_parent_table); 352 v_step_id := add_step(v_job_id, 'Creating new partition '||v_partition_name||' with interval from '||v_id||' to '||(v_id + v_part_interval)-1); 353 END IF; 354 355 SELECT relpersistence INTO v_unlogged FROM pg_catalog.pg_class WHERE oid::regclass = p_parent_table::regclass; 356 v_sql := 'CREATE'; 357 IF v_unlogged = 'u' THEN 358 v_sql := v_sql || ' UNLOGGED'; 359 END IF; 360 v_sql := v_sql || ' TABLE '||v_partition_name||' (LIKE '||p_parent_table||' INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING STORAGE INCLUDING COMMENTS)'; 361 SELECT relhasoids INTO v_hasoids FROM pg_catalog.pg_class WHERE oid::regclass = p_parent_table::regclass; 362 IF v_hasoids IS TRUE THEN 363 v_sql := v_sql || ' WITH (OIDS)'; 364 END IF; 365 EXECUTE v_sql; 366 SELECT tablename INTO v_tablename FROM pg_catalog.pg_tables WHERE schemaname ||'.'|| tablename = v_partition_name; 367 IF v_parent_tablespace IS NOT NULL THEN 368 EXECUTE 'ALTER TABLE '||v_partition_name||' SET TABLESPACE '||v_parent_tablespace; 369 END IF; 370 EXECUTE 'ALTER TABLE '||v_partition_name||' ADD CONSTRAINT '||v_tablename||'_partition_check 371 CHECK ('||v_control||'>='||quote_literal(v_id)||' AND '||v_control||'<'||quote_literal(v_id + v_part_interval)||')'; 372 EXECUTE 'ALTER TABLE '||v_partition_name||' INHERIT '||p_parent_table; 373 374 FOR v_parent_grant IN 375 SELECT array_agg(DISTINCT privilege_type::text ORDER BY privilege_type::text) AS types, grantee 376 FROM information_schema.table_privileges 377 WHERE table_schema ||'.'|| table_name = p_parent_table 378 GROUP BY grantee 379 LOOP 380 EXECUTE 'GRANT '||array_to_string(v_parent_grant.types, ',')||' ON '||v_partition_name||' TO '||v_parent_grant.grantee; 381 SELECT array_agg(r) INTO v_revoke FROM (SELECT unnest(v_all) AS r EXCEPT SELECT unnest(v_parent_grant.types)) x; 382 IF v_revoke IS NOT NULL THEN 383 EXECUTE 'REVOKE '||array_to_string(v_revoke, ',')||' ON '||v_partition_name||' FROM '||v_parent_grant.grantee||' CASCADE'; 384 END IF; 385 v_grantees := array_append(v_grantees, v_parent_grant.grantee::text); 386 END LOOP; 387 -- Revoke all privileges from roles that have none on the parent 388 IF v_grantees IS NOT NULL THEN 389 SELECT array_agg(r) INTO v_revoke FROM ( 390 SELECT DISTINCT grantee::text AS r FROM information_schema.table_privileges WHERE table_schema ||'.'|| table_name = v_partition_name 391 EXCEPT 392 SELECT unnest(v_grantees)) x; 393 IF v_revoke IS NOT NULL THEN 394 EXECUTE 'REVOKE ALL ON '||v_partition_name||' FROM '||array_to_string(v_revoke, ','); 395 END IF; 396 END IF; 397 398 EXECUTE 'ALTER TABLE '||v_partition_name||' OWNER TO '||v_parent_owner; 399 400 IF v_inherit_fk THEN 401 PERFORM @extschema@.apply_foreign_keys(quote_ident(v_parent_schema)||'.'||quote_ident(v_parent_tablename), v_partition_name); 402 END IF; 403 404 IF v_jobmon_schema IS NOT NULL THEN 405 PERFORM update_step(v_step_id, 'OK', 'Done'); 406 END IF; 407 408 -- Will only loop once and only if sub_partitioning is actually configured 409 -- This seemed easier than assigning a bunch of variables then doing an IF condition 410 FOR v_row IN 411 SELECT sub_parent 412 , sub_control 413 , sub_type 414 , sub_part_interval 415 , sub_constraint_cols 416 , sub_premake 417 , sub_inherit_fk 418 , sub_retention 419 , sub_retention_schema 420 , sub_retention_keep_table 421 , sub_retention_keep_index 422 , sub_use_run_maintenance 423 , sub_jobmon 424 FROM @extschema@.part_config_sub 425 WHERE sub_parent = p_parent_table 426 LOOP 427 IF v_jobmon_schema IS NOT NULL THEN 428 v_step_id := add_step(v_job_id, 'Subpartitioning '||v_partition_name); 429 END IF; 430 v_sql := format('SELECT @extschema@.create_parent( 431 p_parent_table := %L 432 , p_control := %L 433 , p_type := %L 434 , p_interval := %L 435 , p_constraint_cols := %L 436 , p_premake := %L 437 , p_use_run_maintenance := %L 438 , p_inherit_fk := %L 439 , p_jobmon := %L )' 440 , v_partition_name 441 , v_row.sub_control 442 , v_row.sub_type 443 , v_row.sub_part_interval 444 , v_row.sub_constraint_cols 445 , v_row.sub_premake 446 , v_row.sub_inherit_fk 447 , v_row.sub_use_run_maintenance 448 , v_row.sub_jobmon); 449 EXECUTE v_sql; 450 451 UPDATE @extschema@.part_config SET 452 retention_schema = v_row.sub_retention_schema 453 , retention_keep_table = v_row.sub_retention_keep_table 454 , retention_keep_index = v_row.sub_retention_keep_index 455 WHERE parent_table = v_partition_name; 456 457 IF v_jobmon_schema IS NOT NULL THEN 458 PERFORM update_step(v_step_id, 'OK', 'Done'); 459 END IF; 460 461 END LOOP; -- end sub partitioning LOOP 462 463 IF v_jobmon_schema IS NOT NULL THEN 464 PERFORM close_job(v_job_id); 465 END IF; 466 467 v_partition_created := true; 468 469END LOOP; 470 471-- v_analyze is a local check if a new table is made. 472-- 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. 473IF v_analyze AND p_analyze THEN 474 EXECUTE 'ANALYZE '||p_parent_table; 475END IF; 476 477IF v_jobmon_schema IS NOT NULL THEN 478 EXECUTE 'SELECT set_config(''search_path'','''||v_old_search_path||''',''false'')'; 479END IF; 480 481RETURN v_partition_created; 482 483EXCEPTION 484 WHEN OTHERS THEN 485 IF v_jobmon_schema IS NOT NULL THEN 486 IF v_job_id IS NULL THEN 487 EXECUTE 'SELECT '||v_jobmon_schema||'.add_job(''PARTMAN CREATE TABLE: '||p_parent_table||''')' INTO v_job_id; 488 EXECUTE 'SELECT '||v_jobmon_schema||'.add_step('||v_job_id||', ''EXCEPTION before job logging started'')' INTO v_step_id; 489 ELSIF v_step_id IS NULL THEN 490 EXECUTE 'SELECT '||v_jobmon_schema||'.add_step('||v_job_id||', ''EXCEPTION before first step logged'')' INTO v_step_id; 491 END IF; 492 EXECUTE 'SELECT '||v_jobmon_schema||'.update_step('||v_step_id||', ''CRITICAL'', ''ERROR: '||coalesce(SQLERRM,'unknown')||''')'; 493 EXECUTE 'SELECT '||v_jobmon_schema||'.fail_job('||v_job_id||')'; 494 END IF; 495 RAISE EXCEPTION '%', SQLERRM; 496END 497$$; 498 499 500/* 501 * Function to create a child table in a time-based partition set 502 */ 503CREATE FUNCTION create_partition_time (p_parent_table text, p_partition_times timestamp[], p_analyze boolean DEFAULT true) 504RETURNS boolean 505 LANGUAGE plpgsql SECURITY DEFINER 506 AS $$ 507DECLARE 508 509v_all text[] := ARRAY['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER']; 510v_analyze boolean := FALSE; 511v_control text; 512v_datetime_string text; 513v_grantees text[]; 514v_hasoids boolean; 515v_inherit_fk boolean; 516v_job_id bigint; 517v_jobmon boolean; 518v_jobmon_schema text; 519v_old_search_path text; 520v_parent_grant record; 521v_parent_owner text; 522v_parent_schema text; 523v_parent_tablename text; 524v_partition_created boolean := false; 525v_partition_name text; 526v_partition_suffix text; 527v_parent_tablespace text; 528v_part_interval interval; 529v_partition_timestamp_end timestamp; 530v_partition_timestamp_start timestamp; 531v_quarter text; 532v_revoke text[]; 533v_row record; 534v_sql text; 535v_step_id bigint; 536v_step_overflow_id bigint; 537v_sub_timestamp_max timestamp; 538v_sub_timestamp_min timestamp; 539v_tablename text; 540v_time_position int; 541v_top_interval interval; 542v_top_parent text; 543v_trunc_value text; 544v_time timestamp; 545v_type text; 546v_unlogged char; 547v_year text; 548 549BEGIN 550 551SELECT type 552 , control 553 , part_interval 554 , inherit_fk 555 , jobmon 556 , datetime_string 557INTO v_type 558 , v_control 559 , v_part_interval 560 , v_inherit_fk 561 , v_jobmon 562 , v_datetime_string 563FROM @extschema@.part_config 564WHERE parent_table = p_parent_table 565AND (type = 'time-static' OR type = 'time-dynamic' OR type = 'time-custom'); 566 567IF NOT FOUND THEN 568 RAISE EXCEPTION 'ERROR: no config found for %', p_parent_table; 569END IF; 570 571IF v_jobmon THEN 572 SELECT nspname INTO v_jobmon_schema FROM pg_namespace n, pg_extension e WHERE e.extname = 'pg_jobmon' AND e.extnamespace = n.oid; 573 IF v_jobmon_schema IS NOT NULL THEN 574 SELECT current_setting('search_path') INTO v_old_search_path; 575 EXECUTE 'SELECT set_config(''search_path'',''@extschema@,'||v_jobmon_schema||''',''false'')'; 576 END IF; 577END IF; 578 579-- Check if parent table is a subpartition of an already existing time-based partition set managed by pg_partman 580-- If so, limit what child tables can be created based on parent suffix 581WITH top_oid AS ( 582 SELECT i.inhparent AS top_parent_oid 583 FROM pg_catalog.pg_class c 584 JOIN pg_catalog.pg_inherits i ON c.oid = i.inhrelid 585 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 586 WHERE n.nspname||'.'||c.relname = p_parent_table 587) SELECT n.nspname||'.'||c.relname 588 INTO v_top_parent 589 FROM pg_catalog.pg_class c 590 JOIN top_oid t ON c.oid = t.top_parent_oid 591 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 592 JOIN @extschema@.part_config p ON p.parent_table = n.nspname||'.'||c.relname 593 WHERE c.oid = t.top_parent_oid 594 AND p.type = 'time-static' OR p.type = 'time-dynamic'; 595 596IF v_top_parent IS NOT NULL THEN 597 598 SELECT part_interval::interval INTO v_top_interval FROM @extschema@.part_config WHERE parent_table = v_top_parent; 599 600 v_time_position := (length(p_parent_table) - position('p_' in reverse(p_parent_table))) + 2; 601 IF v_part_interval::interval <> '3 months' OR (v_part_interval::interval = '3 months' AND v_type = 'time-custom') THEN 602 v_sub_timestamp_min := to_timestamp(substring(p_parent_table from v_time_position), v_datetime_string); 603 ELSE 604 -- to_timestamp doesn't recognize 'Q' date string formater. Handle it 605 v_year := split_part(substring(p_parent_table from v_time_position), 'q', 1); 606 v_quarter := split_part(substring(p_parent_table from v_time_position), 'q', 2); 607 CASE 608 WHEN v_quarter = '1' THEN 609 v_sub_timestamp_min := to_timestamp(v_year || '-01-01', 'YYYY-MM-DD'); 610 WHEN v_quarter = '2' THEN 611 v_sub_timestamp_min := to_timestamp(v_year || '-04-01', 'YYYY-MM-DD'); 612 WHEN v_quarter = '3' THEN 613 v_sub_timestamp_min := to_timestamp(v_year || '-07-01', 'YYYY-MM-DD'); 614 WHEN v_quarter = '4' THEN 615 v_sub_timestamp_min := to_timestamp(v_year || '-10-01', 'YYYY-MM-DD'); 616 END CASE; 617 END IF; 618 v_sub_timestamp_max = (v_sub_timestamp_min + v_top_interval::interval) - '1 sec'::interval; 619 620END IF; 621 622SELECT tableowner, schemaname, tablename, tablespace INTO v_parent_owner, v_parent_schema, v_parent_tablename, v_parent_tablespace FROM pg_tables WHERE schemaname ||'.'|| tablename = p_parent_table; 623 624FOREACH v_time IN ARRAY p_partition_times LOOP 625 v_partition_timestamp_start := v_time; 626 BEGIN 627 v_partition_timestamp_end := v_time + v_part_interval; 628 EXCEPTION WHEN datetime_field_overflow THEN 629 RAISE WARNING 'Attempted partition time interval is outside PostgreSQL''s supported time range. 630 Child partition creation after time % skipped', v_time; 631 v_step_overflow_id := add_step(v_job_id, 'Attempted partition time interval is outside PostgreSQL''s supported time range.'); 632 PERFORM update_step(v_step_overflow_id, 'CRITICAL', 'Child partition creation after time '||v_time||' skipped'); 633 CONTINUE; 634 END; 635 636 -- This suffix generation code is in partition_data_time() as well 637 v_partition_suffix := to_char(v_time, 'YYYY'); 638 IF v_part_interval < '1 year' AND v_part_interval <> '1 week' THEN 639 v_partition_suffix := v_partition_suffix ||'_'|| to_char(v_time, 'MM'); 640 IF v_part_interval < '1 month' AND v_part_interval <> '1 week' THEN 641 v_partition_suffix := v_partition_suffix ||'_'|| to_char(v_time, 'DD'); 642 IF v_part_interval < '1 day' THEN 643 v_partition_suffix := v_partition_suffix || '_' || to_char(v_time, 'HH24MI'); 644 IF v_part_interval < '1 minute' THEN 645 v_partition_suffix := v_partition_suffix || to_char(v_time, 'SS'); 646 END IF; -- end < minute IF 647 END IF; -- end < day IF 648 END IF; -- end < month IF 649 END IF; -- end < year IF 650 651 IF v_part_interval = '1 week' THEN 652 v_partition_suffix := to_char(v_time, 'IYYY') || 'w' || to_char(v_time, 'IW'); 653 END IF; 654 655 -- "Q" is ignored in to_timestamp, so handle special case 656 IF v_part_interval = '3 months' AND (v_type = 'time-static' OR v_type = 'time-dynamic') THEN 657 v_year := to_char(v_time, 'YYYY'); 658 v_quarter := to_char(v_time, 'Q'); 659 v_partition_suffix := v_year || 'q' || v_quarter; 660 END IF; 661 662 663-- Do not create the child table if it's outside the bounds of the top parent. 664 IF v_sub_timestamp_min IS NOT NULL THEN 665 IF v_time < v_sub_timestamp_min OR v_time > v_sub_timestamp_max THEN 666 CONTINUE; 667 END IF; 668 END IF; 669 670 v_partition_name := @extschema@.check_name_length(v_parent_tablename, v_parent_schema, v_partition_suffix, TRUE); 671 SELECT tablename INTO v_tablename FROM pg_catalog.pg_tables WHERE schemaname ||'.'|| tablename = v_partition_name; 672 IF v_tablename IS NOT NULL THEN 673 CONTINUE; 674 END IF; 675 676 -- Ensure analyze is run if a new partition is created. Otherwise if one isn't, will be false and analyze will be skipped 677 v_analyze := TRUE; 678 679 IF v_jobmon_schema IS NOT NULL THEN 680 v_job_id := add_job('PARTMAN CREATE TABLE: '||p_parent_table); 681 v_step_id := add_step(v_job_id, 'Creating new partition '||v_partition_name||' with interval from '||v_partition_timestamp_start||' to '||(v_partition_timestamp_end-'1sec'::interval)); 682 END IF; 683 684 SELECT relpersistence INTO v_unlogged FROM pg_catalog.pg_class WHERE oid::regclass = p_parent_table::regclass; 685 v_sql := 'CREATE'; 686 IF v_unlogged = 'u' THEN 687 v_sql := v_sql || ' UNLOGGED'; 688 END IF; 689 v_sql := v_sql || ' TABLE '||v_partition_name||' (LIKE '||p_parent_table||' INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING STORAGE INCLUDING COMMENTS)'; 690 SELECT relhasoids INTO v_hasoids FROM pg_catalog.pg_class WHERE oid::regclass = p_parent_table::regclass; 691 IF v_hasoids IS TRUE THEN 692 v_sql := v_sql || ' WITH (OIDS)'; 693 END IF; 694 EXECUTE v_sql; 695 SELECT tablename INTO v_tablename FROM pg_catalog.pg_tables WHERE schemaname ||'.'|| tablename = v_partition_name; 696 IF v_parent_tablespace IS NOT NULL THEN 697 EXECUTE 'ALTER TABLE '||v_partition_name||' SET TABLESPACE '||v_parent_tablespace; 698 END IF; 699 EXECUTE 'ALTER TABLE '||v_partition_name||' ADD CONSTRAINT '||v_tablename||'_partition_check 700 CHECK ('||v_control||'>='||quote_literal(v_partition_timestamp_start)||' AND '||v_control||'<'||quote_literal(v_partition_timestamp_end)||')'; 701 EXECUTE 'ALTER TABLE '||v_partition_name||' INHERIT '||p_parent_table; 702 703 -- If custom time, set extra config options. 704 IF v_type = 'time-custom' THEN 705 INSERT INTO @extschema@.custom_time_partitions (parent_table, child_table, partition_range) 706 VALUES ( p_parent_table, v_partition_name, tstzrange(v_partition_timestamp_start, v_partition_timestamp_end, '[)') ); 707 END IF; 708 709 FOR v_parent_grant IN 710 SELECT array_agg(DISTINCT privilege_type::text ORDER BY privilege_type::text) AS types, grantee 711 FROM information_schema.table_privileges 712 WHERE table_schema ||'.'|| table_name = p_parent_table 713 GROUP BY grantee 714 LOOP 715 EXECUTE 'GRANT '||array_to_string(v_parent_grant.types, ',')||' ON '||v_partition_name||' TO '||v_parent_grant.grantee; 716 SELECT array_agg(r) INTO v_revoke FROM (SELECT unnest(v_all) AS r EXCEPT SELECT unnest(v_parent_grant.types)) x; 717 IF v_revoke IS NOT NULL THEN 718 EXECUTE 'REVOKE '||array_to_string(v_revoke, ',')||' ON '||v_partition_name||' FROM '||v_parent_grant.grantee||' CASCADE'; 719 END IF; 720 v_grantees := array_append(v_grantees, v_parent_grant.grantee::text); 721 END LOOP; 722 -- Revoke all privileges from roles that have none on the parent 723 IF v_grantees IS NOT NULL THEN 724 SELECT array_agg(r) INTO v_revoke FROM ( 725 SELECT DISTINCT grantee::text AS r FROM information_schema.table_privileges WHERE table_schema ||'.'|| table_name = v_partition_name 726 EXCEPT 727 SELECT unnest(v_grantees)) x; 728 IF v_revoke IS NOT NULL THEN 729 EXECUTE 'REVOKE ALL ON '||v_partition_name||' FROM '||array_to_string(v_revoke, ','); 730 END IF; 731 END IF; 732 733 EXECUTE 'ALTER TABLE '||v_partition_name||' OWNER TO '||v_parent_owner; 734 735 IF v_inherit_fk THEN 736 PERFORM @extschema@.apply_foreign_keys(quote_ident(v_parent_schema)||'.'||quote_ident(v_parent_tablename), v_partition_name); 737 END IF; 738 739 IF v_jobmon_schema IS NOT NULL THEN 740 PERFORM update_step(v_step_id, 'OK', 'Done'); 741 END IF; 742 743 -- Will only loop once and only if sub_partitioning is actually configured 744 -- This seemed easier than assigning a bunch of variables then doing an IF condition 745 FOR v_row IN 746 SELECT sub_parent 747 , sub_control 748 , sub_type 749 , sub_part_interval 750 , sub_constraint_cols 751 , sub_premake 752 , sub_inherit_fk 753 , sub_retention 754 , sub_retention_schema 755 , sub_retention_keep_table 756 , sub_retention_keep_index 757 , sub_use_run_maintenance 758 , sub_jobmon 759 FROM @extschema@.part_config_sub 760 WHERE sub_parent = p_parent_table 761 LOOP 762 IF v_jobmon_schema IS NOT NULL THEN 763 v_step_id := add_step(v_job_id, 'Subpartitioning '||v_partition_name); 764 END IF; 765 v_sql := format('SELECT @extschema@.create_parent( 766 p_parent_table := %L 767 , p_control := %L 768 , p_type := %L 769 , p_interval := %L 770 , p_constraint_cols := %L 771 , p_premake := %L 772 , p_use_run_maintenance := %L 773 , p_inherit_fk := %L 774 , p_jobmon := %L )' 775 , v_partition_name 776 , v_row.sub_control 777 , v_row.sub_type 778 , v_row.sub_part_interval 779 , v_row.sub_constraint_cols 780 , v_row.sub_premake 781 , v_row.sub_inherit_fk 782 , v_row.sub_use_run_maintenance 783 , v_row.sub_jobmon); 784 EXECUTE v_sql; 785 786 UPDATE @extschema@.part_config SET 787 retention_schema = v_row.sub_retention_schema 788 , retention_keep_table = v_row.sub_retention_keep_table 789 , retention_keep_index = v_row.sub_retention_keep_index 790 WHERE parent_table = v_partition_name; 791 792 END LOOP; -- end sub partitioning LOOP 793 794 IF v_jobmon_schema IS NOT NULL THEN 795 IF v_step_overflow_id IS NOT NULL THEN 796 PERFORM fail_job(v_job_id); 797 ELSE 798 PERFORM close_job(v_job_id); 799 END IF; 800 END IF; 801 802 v_partition_created := true; 803 804END LOOP; 805 806-- v_analyze is a local check if a new table is made. 807-- 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. 808IF v_analyze AND p_analyze THEN 809 EXECUTE 'ANALYZE '||p_parent_table; 810END IF; 811 812IF v_jobmon_schema IS NOT NULL THEN 813 EXECUTE 'SELECT set_config(''search_path'','''||v_old_search_path||''',''false'')'; 814END IF; 815 816RETURN v_partition_created; 817 818EXCEPTION 819 WHEN OTHERS THEN 820 IF v_jobmon_schema IS NOT NULL THEN 821 IF v_job_id IS NULL THEN 822 EXECUTE 'SELECT '||v_jobmon_schema||'.add_job(''PARTMAN CREATE TABLE: '||p_parent_table||''')' INTO v_job_id; 823 EXECUTE 'SELECT '||v_jobmon_schema||'.add_step('||v_job_id||', ''EXCEPTION before job logging started'')' INTO v_step_id; 824 ELSIF v_step_id IS NULL THEN 825 EXECUTE 'SELECT '||v_jobmon_schema||'.add_step('||v_job_id||', ''EXCEPTION before first step logged'')' INTO v_step_id; 826 END IF; 827 EXECUTE 'SELECT '||v_jobmon_schema||'.update_step('||v_step_id||', ''CRITICAL'', ''ERROR: '||coalesce(SQLERRM,'unknown')||''')'; 828 EXECUTE 'SELECT '||v_jobmon_schema||'.fail_job('||v_job_id||')'; 829 END IF; 830 RAISE EXCEPTION '%', SQLERRM; 831END 832$$; 833 834 835/* 836 * Function to turn a table into the parent of a partition set 837 */ 838CREATE FUNCTION create_parent( 839 p_parent_table text 840 , p_control text 841 , p_type text 842 , p_interval text 843 , p_constraint_cols text[] DEFAULT NULL 844 , p_premake int DEFAULT 4 845 , p_use_run_maintenance boolean DEFAULT NULL 846 , p_start_partition text DEFAULT NULL 847 , p_inherit_fk boolean DEFAULT true 848 , p_jobmon boolean DEFAULT true 849 , p_debug boolean DEFAULT false) 850RETURNS boolean 851 LANGUAGE plpgsql SECURITY DEFINER 852 AS $$ 853DECLARE 854 855v_base_timestamp timestamp; 856v_count int := 1; 857v_datetime_string text; 858v_higher_parent text := p_parent_table; 859v_id_interval bigint; 860v_id_position int; 861v_job_id bigint; 862v_jobmon_schema text; 863v_last_partition_created boolean; 864v_max bigint; 865v_notnull boolean; 866v_old_search_path text; 867v_parent_partition_id bigint; 868v_parent_partition_timestamp timestamp; 869v_partition_time timestamp; 870v_partition_time_array timestamp[]; 871v_partition_id_array bigint[]; 872v_row record; 873v_run_maint boolean; 874v_sql text; 875v_start_time timestamp; 876v_starting_partition_id bigint; 877v_step_id bigint; 878v_step_overflow_id bigint; 879v_sub_parent text; 880v_success boolean := false; 881v_tablename text; 882v_time_interval interval; 883v_time_position int; 884v_top_parent text := p_parent_table; 885 886BEGIN 887 888IF position('.' in p_parent_table) = 0 THEN 889 RAISE EXCEPTION 'Parent table must be schema qualified'; 890END IF; 891 892SELECT tablename INTO v_tablename FROM pg_tables WHERE schemaname || '.' || tablename = p_parent_table; 893 IF v_tablename IS NULL THEN 894 RAISE EXCEPTION 'Please create given parent table first: %', p_parent_table; 895 END IF; 896 897SELECT attnotnull INTO v_notnull FROM pg_attribute WHERE attrelid = p_parent_table::regclass AND attname = p_control; 898 IF v_notnull = false OR v_notnull IS NULL THEN 899 RAISE EXCEPTION 'Control column (%) for parent table (%) must be NOT NULL', p_control, p_parent_table; 900 END IF; 901 902IF NOT @extschema@.check_partition_type(p_type) THEN 903 RAISE EXCEPTION '% is not a valid partitioning type', p_type; 904END IF; 905 906IF p_type = 'time-custom' AND @extschema@.check_version('9.2.0') IS FALSE THEN 907 RAISE EXCEPTION 'The "time-custom" type requires a minimum PostgreSQL version of 9.2.0'; 908END IF; 909 910EXECUTE 'LOCK TABLE '||p_parent_table||' IN ACCESS EXCLUSIVE MODE'; 911 912IF p_jobmon THEN 913 SELECT nspname INTO v_jobmon_schema FROM pg_namespace n, pg_extension e WHERE e.extname = 'pg_jobmon' AND e.extnamespace = n.oid; 914 IF v_jobmon_schema IS NOT NULL THEN 915 SELECT current_setting('search_path') INTO v_old_search_path; 916 EXECUTE 'SELECT set_config(''search_path'',''@extschema@,'||v_jobmon_schema||''',''false'')'; 917 END IF; 918END IF; 919 920IF p_use_run_maintenance IS NOT NULL THEN 921 IF p_use_run_maintenance IS FALSE AND (p_type = 'time-static' OR p_type = 'time-dynamic' OR p_type = 'time-custom') THEN 922 RAISE EXCEPTION 'p_run_maintenance cannot be set to false for time based partitioning'; 923 END IF; 924 v_run_maint := p_use_run_maintenance; 925ELSIF p_type = 'time-static' OR p_type = 'time-dynamic' OR p_type = 'time-custom' THEN 926 v_run_maint := TRUE; 927ELSIF p_type = 'id-static' OR p_type ='id-dynamic' THEN 928 v_run_maint := FALSE; 929ELSE 930 RAISE EXCEPTION 'use_run_maintenance value cannot be set NULL'; 931END IF; 932 933IF v_jobmon_schema IS NOT NULL THEN 934 v_job_id := add_job('PARTMAN SETUP PARENT: '||p_parent_table); 935 v_step_id := add_step(v_job_id, 'Creating initial partitions on new parent table: '||p_parent_table); 936END IF; 937 938-- If this parent table has siblings that are also partitioned (subpartitions), ensure it gets added to part_config_sub table so future maintenance will subpartition it 939-- 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.) 940FOR v_row IN 941 WITH parent_table AS ( 942 SELECT h.inhparent as parent_oid 943 from pg_inherits h 944 where h.inhrelid::regclass = p_parent_table::regclass 945 ), sibling_children as ( 946 select i.inhrelid::regclass::text as tablename 947 from pg_inherits i 948 join parent_table p on i.inhparent = p.parent_oid 949 ) 950 SELECT DISTINCT sub_type 951 , sub_control 952 , sub_part_interval 953 , sub_constraint_cols 954 , sub_premake 955 , sub_inherit_fk 956 , sub_retention 957 , sub_retention_schema 958 , sub_retention_keep_table 959 , sub_retention_keep_index 960 , sub_use_run_maintenance 961 , sub_jobmon 962 FROM @extschema@.part_config_sub a 963 JOIN sibling_children b on a.sub_parent = b.tablename LIMIT 1 964LOOP 965 INSERT INTO @extschema@.part_config_sub ( 966 sub_parent 967 , sub_type 968 , sub_control 969 , sub_part_interval 970 , sub_constraint_cols 971 , sub_premake 972 , sub_inherit_fk 973 , sub_retention 974 , sub_retention_schema 975 , sub_retention_keep_table 976 , sub_retention_keep_index 977 , sub_use_run_maintenance 978 , sub_jobmon) 979 VALUES ( 980 p_parent_table 981 , v_row.sub_type 982 , v_row.sub_control 983 , v_row.sub_part_interval 984 , v_row.sub_constraint_cols 985 , v_row.sub_premake 986 , v_row.sub_inherit_fk 987 , v_row.sub_retention 988 , v_row.sub_retention_schema 989 , v_row.sub_retention_keep_table 990 , v_row.sub_retention_keep_index 991 , v_row.sub_use_run_maintenance 992 , v_row.sub_jobmon); 993END LOOP; 994 995IF p_type = 'time-static' OR p_type = 'time-dynamic' OR p_type = 'time-custom' THEN 996 997 CASE 998 WHEN p_interval = 'yearly' THEN 999 v_time_interval := '1 year'; 1000 WHEN p_interval = 'quarterly' THEN 1001 v_time_interval := '3 months'; 1002 WHEN p_interval = 'monthly' THEN 1003 v_time_interval := '1 month'; 1004 WHEN p_interval = 'weekly' THEN 1005 v_time_interval := '1 week'; 1006 WHEN p_interval = 'daily' THEN 1007 v_time_interval := '1 day'; 1008 WHEN p_interval = 'hourly' THEN 1009 v_time_interval := '1 hour'; 1010 WHEN p_interval = 'half-hour' THEN 1011 v_time_interval := '30 mins'; 1012 WHEN p_interval = 'quarter-hour' THEN 1013 v_time_interval := '15 mins'; 1014 ELSE 1015 IF p_type <> 'time-custom' THEN 1016 RAISE EXCEPTION 'Must use a predefined time interval if not using type "time-custom". See documentation.'; 1017 END IF; 1018 v_time_interval := p_interval::interval; 1019 IF v_time_interval < '1 second'::interval THEN 1020 RAISE EXCEPTION 'Partitioning interval must be 1 second or greater'; 1021 END IF; 1022 END CASE; 1023 1024 -- First partition is either the min premake or p_start_partition 1025 v_start_time := COALESCE(p_start_partition::timestamp, CURRENT_TIMESTAMP - (v_time_interval * p_premake)); 1026 1027 IF v_time_interval >= '1 year' THEN 1028 v_base_timestamp := date_trunc('year', v_start_time); 1029 IF v_time_interval >= '10 years' THEN 1030 v_base_timestamp := date_trunc('decade', v_start_time); 1031 IF v_time_interval >= '100 years' THEN 1032 v_base_timestamp := date_trunc('century', v_start_time); 1033 IF v_time_interval >= '1000 years' THEN 1034 v_base_timestamp := date_trunc('millennium', v_start_time); 1035 END IF; -- 1000 1036 END IF; -- 100 1037 END IF; -- 10 1038 END IF; -- 1 1039 1040 v_datetime_string := 'YYYY'; 1041 IF v_time_interval < '1 year' THEN 1042 IF p_interval = 'quarterly' THEN 1043 v_base_timestamp := date_trunc('quarter', v_start_time); 1044 v_datetime_string = 'YYYY"q"Q'; 1045 ELSE 1046 v_base_timestamp := date_trunc('month', v_start_time); 1047 v_datetime_string := v_datetime_string || '_MM'; 1048 END IF; 1049 IF v_time_interval < '1 month' THEN 1050 IF p_interval = 'weekly' THEN 1051 v_base_timestamp := date_trunc('week', v_start_time); 1052 v_datetime_string := 'IYYY"w"IW'; 1053 ELSE 1054 v_base_timestamp := date_trunc('day', v_start_time); 1055 v_datetime_string := v_datetime_string || '_DD'; 1056 END IF; 1057 IF v_time_interval < '1 day' THEN 1058 v_base_timestamp := date_trunc('hour', v_start_time); 1059 v_datetime_string := v_datetime_string || '_HH24MI'; 1060 IF v_time_interval < '1 minute' THEN 1061 v_base_timestamp := date_trunc('minute', v_start_time); 1062 v_datetime_string := v_datetime_string || 'SS'; 1063 END IF; -- minute 1064 END IF; -- day 1065 END IF; -- month 1066 END IF; -- year 1067 1068 v_partition_time_array := array_append(v_partition_time_array, v_base_timestamp); 1069 LOOP 1070 -- If current loop value is less than or equal to the value of the max premake, add time to array. 1071 IF (v_base_timestamp + (v_time_interval * v_count)) < (CURRENT_TIMESTAMP + (v_time_interval * p_premake)) THEN 1072 BEGIN 1073 v_partition_time := (v_base_timestamp + (v_time_interval * v_count))::timestamp; 1074 v_partition_time_array := array_append(v_partition_time_array, v_partition_time); 1075 EXCEPTION WHEN datetime_field_overflow THEN 1076 RAISE WARNING 'Attempted partition time interval is outside PostgreSQL''s supported time range. 1077 Child partition creation after time % skipped', v_partition_time; 1078 v_step_overflow_id := add_step(v_job_id, 'Attempted partition time interval is outside PostgreSQL''s supported time range.'); 1079 PERFORM update_step(v_step_overflow_id, 'CRITICAL', 'Child partition creation after time '||v_partition_time||' skipped'); 1080 CONTINUE; 1081 END; 1082 ELSE 1083 EXIT; -- all needed partitions added to array. Exit the loop. 1084 END IF; 1085 v_count := v_count + 1; 1086 END LOOP; 1087 1088 INSERT INTO @extschema@.part_config ( 1089 parent_table 1090 , type 1091 , part_interval 1092 , control 1093 , premake 1094 , constraint_cols 1095 , datetime_string 1096 , use_run_maintenance 1097 , inherit_fk 1098 , jobmon) 1099 VALUES ( 1100 p_parent_table 1101 , p_type 1102 , v_time_interval 1103 , p_control 1104 , p_premake 1105 , p_constraint_cols 1106 , v_datetime_string 1107 , v_run_maint 1108 , p_inherit_fk 1109 , p_jobmon); 1110 v_last_partition_created := @extschema@.create_partition_time(p_parent_table, v_partition_time_array, false); 1111 1112 IF v_last_partition_created = false THEN 1113 -- This can happen with subpartitioning when future or past partitions prevent child creation because they're out of range of the parent 1114 -- First see if this parent is a subpartition managed by pg_partman 1115 WITH top_oid AS ( 1116 SELECT i.inhparent AS top_parent_oid 1117 FROM pg_catalog.pg_inherits i 1118 JOIN pg_catalog.pg_class c ON c.oid = i.inhrelid 1119 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 1120 WHERE n.nspname||'.'||c.relname = p_parent_table 1121 ) SELECT n.nspname||'.'||c.relname 1122 INTO v_top_parent 1123 FROM pg_catalog.pg_class c 1124 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 1125 JOIN top_oid t ON c.oid = t.top_parent_oid 1126 JOIN @extschema@.part_config p ON p.parent_table = n.nspname||'.'||c.relname; 1127 IF v_top_parent IS NOT NULL THEN 1128 -- If so create the lowest possible partition that is within the boundary of the parent 1129 v_time_position := (length(p_parent_table) - position('p_' in reverse(p_parent_table))) + 2; 1130 v_parent_partition_timestamp := to_timestamp(substring(p_parent_table from v_time_position), v_datetime_string); 1131 IF v_base_timestamp >= v_parent_partition_timestamp THEN 1132 WHILE v_base_timestamp >= v_parent_partition_timestamp LOOP 1133 v_base_timestamp := v_base_timestamp - v_time_interval; 1134 END LOOP; 1135 v_base_timestamp := v_base_timestamp + v_time_interval; -- add one back since while loop set it one lower than is needed 1136 ELSIF v_base_timestamp < v_parent_partition_timestamp THEN 1137 WHILE v_base_timestamp < v_parent_partition_timestamp LOOP 1138 v_base_timestamp := v_base_timestamp + v_time_interval; 1139 END LOOP; 1140 -- Don't need to remove one since new starting time will fit in top parent interval 1141 END IF; 1142 v_partition_time_array := NULL; 1143 v_partition_time_array := array_append(v_partition_time_array, v_base_timestamp); 1144 v_last_partition_created := @extschema@.create_partition_time(p_parent_table, v_partition_time_array, false); 1145 ELSE 1146 -- Currently unknown edge case if code gets here 1147 RAISE EXCEPTION 'No child tables created. Unexpected edge case encountered. Please report this error to author with conditions that led to it.'; 1148 END IF; 1149 END IF; 1150 1151 IF v_jobmon_schema IS NOT NULL THEN 1152 PERFORM update_step(v_step_id, 'OK', 'Time partitions premade: '||p_premake); 1153 END IF; 1154END IF; 1155 1156IF p_type = 'id-static' OR p_type = 'id-dynamic' THEN 1157 v_id_interval := p_interval::bigint; 1158 IF v_id_interval <= 1 THEN 1159 RAISE EXCEPTION 'Interval for serial partitioning must be greater than 1'; 1160 END IF; 1161 1162 -- Check if parent table is a subpartition of an already existing id partition set managed by pg_partman. 1163 WHILE v_higher_parent IS NOT NULL LOOP -- initially set in DECLARE 1164 WITH top_oid AS ( 1165 SELECT i.inhparent AS top_parent_oid 1166 FROM pg_catalog.pg_inherits i 1167 JOIN pg_catalog.pg_class c ON c.oid = i.inhrelid 1168 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 1169 WHERE n.nspname||'.'||c.relname = v_higher_parent 1170 ) SELECT n.nspname||'.'||c.relname 1171 INTO v_higher_parent 1172 FROM pg_catalog.pg_class c 1173 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 1174 JOIN top_oid t ON c.oid = t.top_parent_oid 1175 JOIN @extschema@.part_config p ON p.parent_table = n.nspname||'.'||c.relname 1176 WHERE p.type = 'id-static' OR p.type = 'id-dynamic'; 1177 1178 IF v_higher_parent IS NOT NULL THEN 1179 -- v_top_parent initially set in DECLARE 1180 v_top_parent := v_higher_parent; 1181 END IF; 1182 END LOOP; 1183 1184 -- If custom start partition is set, use that. 1185 -- 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 1186 v_sql := 'SELECT COALESCE('||quote_nullable(p_start_partition::bigint)||', max('||p_control||')::bigint, 0) FROM '||v_top_parent||' LIMIT 1'; 1187 EXECUTE v_sql INTO v_max; 1188 v_starting_partition_id := v_max - (v_max % v_id_interval); 1189 FOR i IN 0..p_premake LOOP 1190 -- Only make previous partitions if ID value is less than the starting value and positive (and custom start partition wasn't set) 1191 IF p_start_partition IS NULL AND 1192 (v_starting_partition_id - (v_id_interval*i)) > 0 AND 1193 (v_starting_partition_id - (v_id_interval*i)) < v_starting_partition_id 1194 THEN 1195 v_partition_id_array = array_append(v_partition_id_array, (v_starting_partition_id - v_id_interval*i)); 1196 END IF; 1197 v_partition_id_array = array_append(v_partition_id_array, (v_id_interval*i) + v_starting_partition_id); 1198 END LOOP; 1199 1200 INSERT INTO @extschema@.part_config ( 1201 parent_table 1202 , type 1203 , part_interval 1204 , control 1205 , premake 1206 , constraint_cols 1207 , use_run_maintenance 1208 , inherit_fk 1209 , jobmon) 1210 VALUES ( 1211 p_parent_table 1212 , p_type 1213 , v_id_interval 1214 , p_control 1215 , p_premake 1216 , p_constraint_cols 1217 , v_run_maint 1218 , p_inherit_fk 1219 , p_jobmon); 1220 v_last_partition_created := @extschema@.create_partition_id(p_parent_table, v_partition_id_array, false); 1221 IF v_last_partition_created = false THEN 1222 -- This can happen with subpartitioning when future or past partitions prevent child creation because they're out of range of the parent 1223 -- See if it's actually a subpartition of a parent id partition 1224 WITH top_oid AS ( 1225 SELECT i.inhparent AS top_parent_oid 1226 FROM pg_catalog.pg_inherits i 1227 JOIN pg_catalog.pg_class c ON c.oid = i.inhrelid 1228 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 1229 WHERE n.nspname||'.'||c.relname = p_parent_table 1230 ) SELECT n.nspname||'.'||c.relname 1231 INTO v_top_parent 1232 FROM pg_catalog.pg_class c 1233 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 1234 JOIN top_oid t ON c.oid = t.top_parent_oid 1235 JOIN @extschema@.part_config p ON p.parent_table = n.nspname||'.'||c.relname 1236 WHERE p.type = 'id-static' OR p.type = 'id-dynamic'; 1237 IF v_top_parent IS NOT NULL THEN 1238 -- Create the lowest possible partition that is within the boundary of the parent 1239 v_id_position := (length(p_parent_table) - position('p_' in reverse(p_parent_table))) + 2; 1240 v_parent_partition_id = substring(p_parent_table from v_id_position)::bigint; 1241 IF v_starting_partition_id >= v_parent_partition_id THEN 1242 WHILE v_starting_partition_id >= v_parent_partition_id LOOP 1243 v_starting_partition_id := v_starting_partition_id - v_id_interval; 1244 END LOOP; 1245 v_starting_partition_id := v_starting_partition_id + v_id_interval; -- add one back since while loop set it one lower than is needed 1246 ELSIF v_starting_partition_id < v_parent_partition_id THEN 1247 WHILE v_starting_partition_id < v_parent_partition_id LOOP 1248 v_starting_partition_id := v_starting_partition_id + v_id_interval; 1249 END LOOP; 1250 -- Don't need to remove one since new starting id will fit in top parent interval 1251 END IF; 1252 v_partition_id_array = NULL; 1253 v_partition_id_array = array_append(v_partition_id_array, v_starting_partition_id); 1254 v_last_partition_created := @extschema@.create_partition_id(p_parent_table, v_partition_id_array, false); 1255 ELSE 1256 -- Currently unknown edge case if code gets here 1257 RAISE EXCEPTION 'No child tables created. Unexpected edge case encountered. Please report this error to author with conditions that led to it.'; 1258 END IF; 1259 END IF; 1260END IF; 1261 1262IF v_jobmon_schema IS NOT NULL THEN 1263 v_step_id := add_step(v_job_id, 'Creating partition function'); 1264END IF; 1265IF p_type = 'time-static' OR p_type = 'time-dynamic' OR p_type = 'time-custom' THEN 1266 PERFORM @extschema@.create_function_time(p_parent_table); 1267 IF v_jobmon_schema IS NOT NULL THEN 1268 PERFORM update_step(v_step_id, 'OK', 'Time function created'); 1269 END IF; 1270ELSIF p_type = 'id-static' OR p_type = 'id-dynamic' THEN 1271 PERFORM @extschema@.create_function_id(p_parent_table); 1272 IF v_jobmon_schema IS NOT NULL THEN 1273 PERFORM update_step(v_step_id, 'OK', 'ID function created'); 1274 END IF; 1275END IF; 1276 1277IF v_jobmon_schema IS NOT NULL THEN 1278 v_step_id := add_step(v_job_id, 'Creating partition trigger'); 1279END IF; 1280PERFORM @extschema@.create_trigger(p_parent_table); 1281 1282IF v_jobmon_schema IS NOT NULL THEN 1283 PERFORM update_step(v_step_id, 'OK', 'Done'); 1284 IF v_step_overflow_id IS NOT NULL THEN 1285 PERFORM fail_job(v_job_id); 1286 ELSE 1287 PERFORM close_job(v_job_id); 1288 END IF; 1289 EXECUTE 'SELECT set_config(''search_path'','''||v_old_search_path||''',''false'')'; 1290END IF; 1291 1292v_success := true; 1293 1294RETURN v_success; 1295 1296EXCEPTION 1297 WHEN OTHERS THEN 1298 IF v_jobmon_schema IS NOT NULL THEN 1299 IF v_job_id IS NULL THEN 1300 EXECUTE 'SELECT '||v_jobmon_schema||'.add_job(''PARTMAN CREATE PARENT: '||p_parent_table||''')' INTO v_job_id; 1301 EXECUTE 'SELECT '||v_jobmon_schema||'.add_step('||v_job_id||', ''Partition creation for table '||p_parent_table||' failed'')' INTO v_step_id; 1302 ELSIF v_step_id IS NULL THEN 1303 EXECUTE 'SELECT '||v_jobmon_schema||'.add_step('||v_job_id||', ''EXCEPTION before first step logged'')' INTO v_step_id; 1304 END IF; 1305 EXECUTE 'SELECT '||v_jobmon_schema||'.update_step('||v_step_id||', ''CRITICAL'', ''ERROR: '||coalesce(SQLERRM,'unknown')||''')'; 1306 EXECUTE 'SELECT '||v_jobmon_schema||'.fail_job('||v_job_id||')'; 1307 END IF; 1308 RAISE EXCEPTION '%', SQLERRM; 1309END 1310$$; 1311 1312 1313/* 1314 * Create the trigger function for the parent table of a time-based partition set 1315 */ 1316CREATE FUNCTION create_function_time(p_parent_table text) RETURNS void 1317 LANGUAGE plpgsql SECURITY DEFINER 1318 AS $$ 1319DECLARE 1320 1321v_control text; 1322v_count int; 1323v_current_partition_name text; 1324v_current_partition_timestamp timestamptz; 1325v_datetime_string text; 1326v_final_partition_timestamp timestamptz; 1327v_function_name text; 1328v_job_id bigint; 1329v_jobmon boolean; 1330v_jobmon_schema text; 1331v_old_search_path text; 1332v_new_length int; 1333v_next_partition_name text; 1334v_next_partition_timestamp timestamptz; 1335v_parent_schema text; 1336v_parent_tablename text; 1337v_part_interval interval; 1338v_premake int; 1339v_prev_partition_name text; 1340v_prev_partition_timestamp timestamptz; 1341v_step_id bigint; 1342v_trig_func text; 1343v_type text; 1344 1345BEGIN 1346 1347SELECT type 1348 , part_interval::interval 1349 , control 1350 , premake 1351 , datetime_string 1352 , jobmon 1353INTO v_type 1354 , v_part_interval 1355 , v_control 1356 , v_premake 1357 , v_datetime_string 1358 , v_jobmon 1359FROM @extschema@.part_config 1360WHERE parent_table = p_parent_table 1361AND (type = 'time-static' OR type = 'time-dynamic' OR type = 'time-custom'); 1362 1363IF NOT FOUND THEN 1364 RAISE EXCEPTION 'ERROR: no config found for %', p_parent_table; 1365END IF; 1366 1367IF v_jobmon THEN 1368 SELECT nspname INTO v_jobmon_schema FROM pg_namespace n, pg_extension e WHERE e.extname = 'pg_jobmon' AND e.extnamespace = n.oid; 1369 IF v_jobmon_schema IS NOT NULL THEN 1370 SELECT current_setting('search_path') INTO v_old_search_path; 1371 EXECUTE 'SELECT set_config(''search_path'',''@extschema@,'||v_jobmon_schema||''',''false'')'; 1372 END IF; 1373END IF; 1374 1375IF v_jobmon_schema IS NOT NULL THEN 1376 v_job_id := add_job('PARTMAN CREATE FUNCTION: '||p_parent_table); 1377 v_step_id := add_step(v_job_id, 'Creating partition function for table '||p_parent_table); 1378END IF; 1379 1380SELECT schemaname, tablename INTO v_parent_schema, v_parent_tablename FROM pg_tables WHERE schemaname ||'.'|| tablename = p_parent_table; 1381 1382v_function_name := @extschema@.check_name_length(v_parent_tablename, v_parent_schema, '_part_trig_func', FALSE); 1383 1384IF v_type = 'time-static' THEN 1385 CASE 1386 WHEN v_part_interval = '15 mins' THEN 1387 v_current_partition_timestamp := date_trunc('hour', CURRENT_TIMESTAMP) + 1388 '15min'::interval * floor(date_part('minute', CURRENT_TIMESTAMP) / 15.0); 1389 WHEN v_part_interval = '30 mins' THEN 1390 v_current_partition_timestamp := date_trunc('hour', CURRENT_TIMESTAMP) + 1391 '30min'::interval * floor(date_part('minute', CURRENT_TIMESTAMP) / 30.0); 1392 WHEN v_part_interval = '1 hour' THEN 1393 v_current_partition_timestamp := date_trunc('hour', CURRENT_TIMESTAMP); 1394 WHEN v_part_interval = '1 day' THEN 1395 v_current_partition_timestamp := date_trunc('day', CURRENT_TIMESTAMP); 1396 WHEN v_part_interval = '1 week' THEN 1397 v_current_partition_timestamp := date_trunc('week', CURRENT_TIMESTAMP); 1398 WHEN v_part_interval = '1 month' THEN 1399 v_current_partition_timestamp := date_trunc('month', CURRENT_TIMESTAMP); 1400 -- Type time-static plus this interval is the special quarterly interval 1401 WHEN v_part_interval = '3 months' THEN 1402 v_current_partition_timestamp := date_trunc('quarter', CURRENT_TIMESTAMP); 1403 WHEN v_part_interval = '1 year' THEN 1404 v_current_partition_timestamp := date_trunc('year', CURRENT_TIMESTAMP); 1405 END CASE; 1406 1407 v_current_partition_name := @extschema@.check_name_length(v_parent_tablename, v_parent_schema, to_char(v_current_partition_timestamp, v_datetime_string), TRUE); 1408 v_next_partition_timestamp := v_current_partition_timestamp + v_part_interval::interval; 1409 1410 v_trig_func := 'CREATE OR REPLACE FUNCTION '||v_function_name||'() RETURNS trigger LANGUAGE plpgsql AS $t$ 1411 BEGIN 1412 IF TG_OP = ''INSERT'' THEN 1413 IF NEW.'||v_control||' >= '||quote_literal(v_current_partition_timestamp)||' AND NEW.'||v_control||' < '||quote_literal(v_next_partition_timestamp)|| ' THEN '; 1414 SELECT count(*) INTO v_count FROM pg_catalog.pg_tables WHERE schemaname ||'.'||tablename = v_current_partition_name; 1415 IF v_count > 0 THEN 1416 v_trig_func := v_trig_func || ' 1417 INSERT INTO '||v_current_partition_name||' VALUES (NEW.*); '; 1418 ELSE 1419 v_trig_func := v_trig_func || ' 1420 -- Child table for current values does not exist in this partition set, so write to parent 1421 RETURN NEW;'; 1422 END IF; 1423 FOR i IN 1..v_premake LOOP 1424 v_prev_partition_timestamp := v_current_partition_timestamp - (v_part_interval::interval * i); 1425 v_next_partition_timestamp := v_current_partition_timestamp + (v_part_interval::interval * i); 1426 v_final_partition_timestamp := v_next_partition_timestamp + (v_part_interval::interval); 1427 v_prev_partition_name := @extschema@.check_name_length(v_parent_tablename, v_parent_schema, to_char(v_prev_partition_timestamp, v_datetime_string), TRUE); 1428 v_next_partition_name := @extschema@.check_name_length(v_parent_tablename, v_parent_schema, to_char(v_next_partition_timestamp, v_datetime_string), TRUE); 1429 1430 -- Check that child table exist before making a rule to insert to them. 1431 -- Handles edge case of changing premake immediately after running create_parent(). 1432 SELECT count(*) INTO v_count FROM pg_catalog.pg_tables WHERE schemaname ||'.'||tablename = v_prev_partition_name; 1433 IF v_count > 0 THEN 1434 v_trig_func := v_trig_func ||' 1435 ELSIF NEW.'||v_control||' >= '||quote_literal(v_prev_partition_timestamp)||' AND NEW.'||v_control||' < '|| 1436 quote_literal(v_prev_partition_timestamp + v_part_interval::interval)|| ' THEN 1437 INSERT INTO '||v_prev_partition_name||' VALUES (NEW.*);'; 1438 END IF; 1439 SELECT count(*) INTO v_count FROM pg_catalog.pg_tables WHERE schemaname ||'.'||tablename = v_next_partition_name; 1440 IF v_count > 0 THEN 1441 v_trig_func := v_trig_func ||' 1442 ELSIF NEW.'||v_control||' >= '||quote_literal(v_next_partition_timestamp)||' AND NEW.'||v_control||' < '|| 1443 quote_literal(v_final_partition_timestamp)|| ' THEN 1444 INSERT INTO '||v_next_partition_name||' VALUES (NEW.*);'; 1445 END IF; 1446 1447 END LOOP; 1448 v_trig_func := v_trig_func ||' 1449 ELSE 1450 RETURN NEW; 1451 END IF; 1452 END IF; 1453 RETURN NULL; 1454 END $t$;'; 1455 1456 EXECUTE v_trig_func; 1457 1458 IF v_jobmon_schema IS NOT NULL THEN 1459 PERFORM update_step(v_step_id, 'OK', 'Added function for current time interval: '|| 1460 v_current_partition_timestamp||' to '||(v_final_partition_timestamp-'1sec'::interval)); 1461 END IF; 1462 1463ELSIF v_type = 'time-dynamic' THEN 1464 1465 v_trig_func := 'CREATE OR REPLACE FUNCTION '||v_function_name||'() RETURNS trigger LANGUAGE plpgsql AS $t$ 1466 DECLARE 1467 v_count int; 1468 v_partition_name text; 1469 v_partition_timestamp timestamptz; 1470 BEGIN 1471 IF TG_OP = ''INSERT'' THEN 1472 '; 1473 CASE 1474 WHEN v_part_interval = '15 mins' THEN 1475 v_trig_func := v_trig_func||'v_partition_timestamp := date_trunc(''hour'', NEW.'||v_control||') + 1476 ''15min''::interval * floor(date_part(''minute'', NEW.'||v_control||') / 15.0);'; 1477 WHEN v_part_interval = '30 mins' THEN 1478 v_trig_func := v_trig_func||'v_partition_timestamp := date_trunc(''hour'', NEW.'||v_control||') + 1479 ''30min''::interval * floor(date_part(''minute'', NEW.'||v_control||') / 30.0);'; 1480 WHEN v_part_interval = '1 hour' THEN 1481 v_trig_func := v_trig_func||'v_partition_timestamp := date_trunc(''hour'', NEW.'||v_control||');'; 1482 WHEN v_part_interval = '1 day' THEN 1483 v_trig_func := v_trig_func||'v_partition_timestamp := date_trunc(''day'', NEW.'||v_control||');'; 1484 WHEN v_part_interval = '1 week' THEN 1485 v_trig_func := v_trig_func||'v_partition_timestamp := date_trunc(''week'', NEW.'||v_control||');'; 1486 WHEN v_part_interval = '1 month' THEN 1487 v_trig_func := v_trig_func||'v_partition_timestamp := date_trunc(''month'', NEW.'||v_control||');'; 1488 WHEN v_part_interval = '3 months' THEN 1489 v_trig_func := v_trig_func||'v_partition_timestamp := date_trunc(''quarter'', NEW.'||v_control||');'; 1490 WHEN v_part_interval = '1 year' THEN 1491 v_trig_func := v_trig_func||'v_partition_timestamp := date_trunc(''year'', NEW.'||v_control||');'; 1492 END CASE; 1493 v_trig_func := v_trig_func||' 1494 v_partition_name := @extschema@.check_name_length('''||v_parent_tablename||''', '''||v_parent_schema||''', to_char(v_partition_timestamp, '||quote_literal(v_datetime_string)||'), TRUE); 1495 SELECT count(*) INTO v_count FROM pg_tables WHERE schemaname ||''.''|| tablename = v_partition_name; 1496 IF v_count > 0 THEN 1497 EXECUTE ''INSERT INTO ''||v_partition_name||'' VALUES($1.*)'' USING NEW; 1498 ELSE 1499 RETURN NEW; 1500 END IF; 1501 END IF; 1502 1503 RETURN NULL; 1504 END $t$;'; 1505 1506 EXECUTE v_trig_func; 1507 1508 IF v_jobmon_schema IS NOT NULL THEN 1509 PERFORM update_step(v_step_id, 'OK', 'Added function for dynamic time table: '||p_parent_table); 1510 END IF; 1511 1512ELSIF v_type = 'time-custom' THEN 1513 1514 v_trig_func := 'CREATE OR REPLACE FUNCTION '||v_function_name||'() RETURNS trigger LANGUAGE plpgsql AS $t$ 1515 DECLARE 1516 v_child_table text; 1517 v_count int; 1518 BEGIN 1519 1520 SELECT child_table INTO v_child_table 1521 FROM @extschema@.custom_time_partitions 1522 WHERE partition_range @> NEW.'||v_control||' 1523 AND parent_table = '||quote_literal(p_parent_table)||'; 1524 1525 SELECT count(*) INTO v_count FROM pg_tables WHERE schemaname ||''.''|| tablename = v_child_table; 1526 IF v_count > 0 THEN 1527 EXECUTE ''INSERT INTO ''||v_child_table||'' VALUES ($1.*)'' USING NEW; 1528 ELSE 1529 RETURN NEW; 1530 END IF; 1531 1532 RETURN NULL; 1533 END $t$;'; 1534 1535 EXECUTE v_trig_func; 1536 1537 IF v_jobmon_schema IS NOT NULL THEN 1538 PERFORM update_step(v_step_id, 'OK', 'Added function for custom time table: '||p_parent_table); 1539 END IF; 1540 1541ELSE 1542 RAISE EXCEPTION 'ERROR: Invalid time partitioning type given: %', v_type; 1543END IF; 1544 1545IF v_jobmon_schema IS NOT NULL THEN 1546 PERFORM close_job(v_job_id); 1547 EXECUTE 'SELECT set_config(''search_path'','''||v_old_search_path||''',''false'')'; 1548END IF; 1549 1550EXCEPTION 1551 WHEN OTHERS THEN 1552 IF v_jobmon_schema IS NOT NULL THEN 1553 IF v_job_id IS NULL THEN 1554 EXECUTE 'SELECT '||v_jobmon_schema||'.add_job(''PARTMAN CREATE FUNCTION: '||p_parent_table||''')' INTO v_job_id; 1555 EXECUTE 'SELECT '||v_jobmon_schema||'.add_step('||v_job_id||', ''EXCEPTION before job logging started'')' INTO v_step_id; 1556 ELSIF v_step_id IS NULL THEN 1557 EXECUTE 'SELECT '||v_jobmon_schema||'.add_step('||v_job_id||', ''EXCEPTION before first step logged'')' INTO v_step_id; 1558 END IF; 1559 EXECUTE 'SELECT '||v_jobmon_schema||'.update_step('||v_step_id||', ''CRITICAL'', ''ERROR: '||coalesce(SQLERRM,'unknown')||''')'; 1560 EXECUTE 'SELECT '||v_jobmon_schema||'.fail_job('||v_job_id||')'; 1561 END IF; 1562 RAISE EXCEPTION '%', SQLERRM; 1563END 1564$$; 1565 1566 1567/* 1568 * Create the trigger function for the parent table of an id-based partition set 1569 */ 1570CREATE FUNCTION create_function_id(p_parent_table text) RETURNS void 1571 LANGUAGE plpgsql SECURITY DEFINER 1572 AS $$ 1573DECLARE 1574 1575v_control text; 1576v_count int; 1577v_current_partition_name text; 1578v_current_partition_id bigint; 1579v_datetime_string text; 1580v_final_partition_id bigint; 1581v_function_name text; 1582v_higher_parent text := p_parent_table; 1583v_id_position int; 1584v_job_id bigint; 1585v_jobmon text; 1586v_jobmon_schema text; 1587v_last_partition text; 1588v_max bigint; 1589v_next_partition_id bigint; 1590v_next_partition_name text; 1591v_old_search_path text; 1592v_parent_schema text; 1593v_parent_tablename text; 1594v_part_interval bigint; 1595v_premake int; 1596v_prev_partition_id bigint; 1597v_prev_partition_name text; 1598v_run_maint boolean; 1599v_step_id bigint; 1600v_top_parent text := p_parent_table; 1601v_trig_func text; 1602v_type text; 1603 1604BEGIN 1605 1606SELECT type 1607 , part_interval::bigint 1608 , control 1609 , premake 1610 , use_run_maintenance 1611 , jobmon 1612INTO v_type 1613 , v_part_interval 1614 , v_control 1615 , v_premake 1616 , v_run_maint 1617 , v_jobmon 1618FROM @extschema@.part_config 1619WHERE parent_table = p_parent_table 1620AND (type = 'id-static' OR type = 'id-dynamic'); 1621 1622IF NOT FOUND THEN 1623 RAISE EXCEPTION 'ERROR: no config found for %', p_parent_table; 1624END IF; 1625 1626SELECT show_partitions INTO v_last_partition FROM @extschema@.show_partitions(p_parent_table, 'DESC') LIMIT 1; 1627 1628IF v_jobmon THEN 1629 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; 1630 IF v_jobmon_schema IS NOT NULL THEN 1631 SELECT current_setting('search_path') INTO v_old_search_path; 1632 EXECUTE 'SELECT set_config(''search_path'',''@extschema@,'||v_jobmon_schema||''',''false'')'; 1633 END IF; 1634END IF; 1635 1636IF v_jobmon_schema IS NOT NULL THEN 1637 v_job_id := add_job('PARTMAN CREATE FUNCTION: '||p_parent_table); 1638 v_step_id := add_step(v_job_id, 'Creating partition function for table '||p_parent_table); 1639END IF; 1640 1641SELECT schemaname, tablename INTO v_parent_schema, v_parent_tablename FROM pg_catalog.pg_tables WHERE schemaname ||'.'|| tablename = p_parent_table; 1642v_function_name := @extschema@.check_name_length(v_parent_tablename, v_parent_schema, '_part_trig_func', FALSE); 1643 1644IF v_type = 'id-static' THEN 1645 -- Get the highest level top parent if multi-level partitioned in order to get proper max() value below 1646 WHILE v_higher_parent IS NOT NULL LOOP -- initially set in DECLARE 1647 WITH top_oid AS ( 1648 SELECT i.inhparent AS top_parent_oid 1649 FROM pg_catalog.pg_inherits i 1650 JOIN pg_catalog.pg_class c ON c.oid = i.inhrelid 1651 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 1652 WHERE n.nspname||'.'||c.relname = v_higher_parent 1653 ) SELECT n.nspname||'.'||c.relname 1654 INTO v_higher_parent 1655 FROM pg_catalog.pg_class c 1656 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 1657 JOIN top_oid t ON c.oid = t.top_parent_oid 1658 JOIN @extschema@.part_config p ON p.parent_table = n.nspname||'.'||c.relname 1659 WHERE p.type = 'id-static' OR p.type = 'id-dynamic'; 1660 1661 IF v_higher_parent IS NOT NULL THEN 1662 -- initially set in DECLARE 1663 v_top_parent := v_higher_parent; 1664 END IF; 1665 1666 END LOOP; 1667 1668 EXECUTE 'SELECT COALESCE(max('||v_control||'), 0) FROM '||v_top_parent INTO v_max; 1669 v_current_partition_id = v_max - (v_max % v_part_interval); 1670 v_next_partition_id := v_current_partition_id + v_part_interval; 1671 v_current_partition_name := @extschema@.check_name_length(v_parent_tablename, v_parent_schema, v_current_partition_id::text, TRUE); 1672 1673 v_trig_func := 'CREATE OR REPLACE FUNCTION '||v_function_name||'() RETURNS trigger LANGUAGE plpgsql AS $t$ 1674 DECLARE 1675 v_current_partition_id bigint; 1676 v_last_partition text := '||quote_literal(v_last_partition)||'; 1677 v_id_position int; 1678 v_next_partition_id bigint; 1679 v_next_partition_name text; 1680 v_partition_created boolean; 1681 BEGIN 1682 IF TG_OP = ''INSERT'' THEN 1683 IF NEW.'||v_control||' >= '||v_current_partition_id||' AND NEW.'||v_control||' < '||v_next_partition_id|| ' THEN '; 1684 SELECT count(*) INTO v_count FROM pg_catalog.pg_tables WHERE schemaname ||'.'||tablename = v_current_partition_name; 1685 IF v_count > 0 THEN 1686 v_trig_func := v_trig_func || ' 1687 INSERT INTO '||v_current_partition_name||' VALUES (NEW.*); '; 1688 ELSE 1689 v_trig_func := v_trig_func || ' 1690 -- Child table for current values does not exist in this partition set, so write to parent 1691 RETURN NEW;'; 1692 END IF; 1693 1694 FOR i IN 1..v_premake LOOP 1695 v_prev_partition_id := v_current_partition_id - (v_part_interval * i); 1696 v_next_partition_id := v_current_partition_id + (v_part_interval * i); 1697 v_final_partition_id := v_next_partition_id + v_part_interval; 1698 v_prev_partition_name := @extschema@.check_name_length(v_parent_tablename, v_parent_schema, v_prev_partition_id::text, TRUE); 1699 v_next_partition_name := @extschema@.check_name_length(v_parent_tablename, v_parent_schema, v_next_partition_id::text, TRUE); 1700 1701 -- Check that child table exist before making a rule to insert to them. 1702 -- Handles edge case of changing premake immediately after running create_parent(). 1703 SELECT count(*) INTO v_count FROM pg_catalog.pg_tables WHERE schemaname ||'.'||tablename = v_prev_partition_name; 1704 IF v_count > 0 THEN 1705 -- Only handle previous partitions if they're starting above zero 1706 IF v_prev_partition_id >= 0 THEN 1707 v_trig_func := v_trig_func ||' 1708 ELSIF NEW.'||v_control||' >= '||v_prev_partition_id||' AND NEW.'||v_control||' < '||v_prev_partition_id + v_part_interval|| ' THEN 1709 INSERT INTO '||v_prev_partition_name||' VALUES (NEW.*); '; 1710 END IF; 1711 END IF; 1712 1713 SELECT count(*) INTO v_count FROM pg_catalog.pg_tables WHERE schemaname ||'.'||tablename = v_next_partition_name; 1714 IF v_count > 0 THEN 1715 v_trig_func := v_trig_func ||' 1716 ELSIF NEW.'||v_control||' >= '||v_next_partition_id||' AND NEW.'||v_control||' < '||v_final_partition_id|| ' THEN 1717 INSERT INTO '||v_next_partition_name||' VALUES (NEW.*);'; 1718 END IF; 1719 END LOOP; 1720 v_trig_func := v_trig_func ||' 1721 ELSE 1722 RETURN NEW; 1723 END IF;'; 1724 1725 IF v_run_maint IS FALSE THEN 1726 v_trig_func := v_trig_func ||' 1727 v_current_partition_id := NEW.'||v_control||' - (NEW.'||v_control||' % '||v_part_interval||'); 1728 IF (NEW.'||v_control||' % '||v_part_interval||') > ('||v_part_interval||' / 2) THEN 1729 v_id_position := (length(v_last_partition) - position(''p_'' in reverse(v_last_partition))) + 2; 1730 v_next_partition_id := (substring(v_last_partition from v_id_position)::bigint) + '||v_part_interval||'; 1731 WHILE ((v_next_partition_id - v_current_partition_id) / '||v_part_interval||') <= '||v_premake||' LOOP 1732 v_partition_created := @extschema@.create_partition_id('||quote_literal(p_parent_table)||', ARRAY[v_next_partition_id]); 1733 IF v_partition_created THEN 1734 PERFORM @extschema@.create_function_id('||quote_literal(p_parent_table)||'); 1735 PERFORM @extschema@.apply_constraints('||quote_literal(p_parent_table)||'); 1736 END IF; 1737 v_next_partition_id := v_next_partition_id + '||v_part_interval||'; 1738 END LOOP; 1739 END IF;'; 1740 END IF; 1741 1742 v_trig_func := v_trig_func ||' 1743 END IF; 1744 RETURN NULL; 1745 END $t$;'; 1746 1747 EXECUTE v_trig_func; 1748 1749 IF v_jobmon_schema IS NOT NULL THEN 1750 PERFORM update_step(v_step_id, 'OK', 'Added function for current id interval: '||v_current_partition_id||' to '||v_final_partition_id-1); 1751 END IF; 1752 1753ELSIF v_type = 'id-dynamic' THEN 1754 -- The return inside the partition creation check is there to keep really high ID values from creating new partitions. 1755 v_trig_func := 'CREATE OR REPLACE FUNCTION '||v_function_name||'() RETURNS trigger LANGUAGE plpgsql AS $t$ 1756 DECLARE 1757 v_count int; 1758 v_current_partition_id bigint; 1759 v_current_partition_name text; 1760 v_id_position int; 1761 v_last_partition text := '||quote_literal(v_last_partition)||'; 1762 v_last_partition_id bigint; 1763 v_next_partition_id bigint; 1764 v_next_partition_name text; 1765 v_partition_created boolean; 1766 BEGIN 1767 IF TG_OP = ''INSERT'' THEN 1768 v_current_partition_id := NEW.'||v_control||' - (NEW.'||v_control||' % '||v_part_interval||'); 1769 v_current_partition_name := @extschema@.check_name_length('''||v_parent_tablename||''', '''||v_parent_schema||''', v_current_partition_id::text, TRUE); 1770 SELECT count(*) INTO v_count FROM pg_tables WHERE schemaname ||''.''|| tablename = v_current_partition_name; 1771 IF v_count > 0 THEN 1772 EXECUTE ''INSERT INTO ''||v_current_partition_name||'' VALUES($1.*)'' USING NEW; 1773 ELSE 1774 RETURN NEW; 1775 END IF;'; 1776 1777 IF v_run_maint IS FALSE THEN 1778 v_trig_func := v_trig_func ||' 1779 IF (NEW.'||v_control||' % '||v_part_interval||') > ('||v_part_interval||' / 2) THEN 1780 v_id_position := (length(v_last_partition) - position(''p_'' in reverse(v_last_partition))) + 2; 1781 v_last_partition_id = substring(v_last_partition from v_id_position)::bigint; 1782 v_next_partition_id := v_last_partition_id + '||v_part_interval||'; 1783 IF NEW.'||v_control||' >= v_next_partition_id THEN 1784 RETURN NEW; 1785 END IF; 1786 WHILE ((v_next_partition_id - v_current_partition_id) / '||v_part_interval||') <= '||v_premake||' LOOP 1787 v_partition_created := @extschema@.create_partition_id('||quote_literal(p_parent_table)||', ARRAY[v_next_partition_id]); 1788 IF v_partition_created THEN 1789 PERFORM @extschema@.create_function_id('||quote_literal(p_parent_table)||'); 1790 PERFORM @extschema@.apply_constraints('||quote_literal(p_parent_table)||'); 1791 END IF; 1792 v_next_partition_id := v_next_partition_id + '||v_part_interval||'; 1793 END LOOP; 1794 END IF;'; 1795 END IF; 1796 1797 v_trig_func := v_trig_func ||' 1798 END IF; 1799 RETURN NULL; 1800 END $t$;'; 1801 1802 EXECUTE v_trig_func; 1803 1804 IF v_jobmon_schema IS NOT NULL THEN 1805 PERFORM update_step(v_step_id, 'OK', 'Added function for dynamic id table: '||p_parent_table); 1806 END IF; 1807 1808ELSE 1809 RAISE EXCEPTION 'ERROR: Invalid id partitioning type given: %', v_type; 1810END IF; 1811 1812IF v_jobmon_schema IS NOT NULL THEN 1813 PERFORM close_job(v_job_id); 1814 EXECUTE 'SELECT set_config(''search_path'','''||v_old_search_path||''',''false'')'; 1815END IF; 1816 1817EXCEPTION 1818 WHEN OTHERS THEN 1819 IF v_jobmon_schema IS NOT NULL THEN 1820 IF v_job_id IS NULL THEN 1821 EXECUTE 'SELECT '||v_jobmon_schema||'.add_job(''PARTMAN CREATE FUNCTION: '||p_parent_table||''')' INTO v_job_id; 1822 EXECUTE 'SELECT '||v_jobmon_schema||'.add_step('||v_job_id||', ''Partition function maintenance for table '||p_parent_table||' failed'')' INTO v_step_id; 1823 ELSIF v_step_id IS NULL THEN 1824 EXECUTE 'SELECT '||v_jobmon_schema||'.add_step('||v_job_id||', ''EXCEPTION before first step logged'')' INTO v_step_id; 1825 END IF; 1826 EXECUTE 'SELECT '||v_jobmon_schema||'.update_step('||v_step_id||', ''CRITICAL'', ''ERROR: '||coalesce(SQLERRM,'unknown')||''')'; 1827 EXECUTE 'SELECT '||v_jobmon_schema||'.fail_job('||v_job_id||')'; 1828 END IF; 1829 RAISE EXCEPTION '%', SQLERRM; 1830END 1831$$; 1832 1833 1834/* 1835 * Populate the child table(s) of a time-based partition set with old data from the original parent 1836 */ 1837CREATE 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 1838 LANGUAGE plpgsql SECURITY DEFINER 1839 AS $$ 1840DECLARE 1841 1842v_control text; 1843v_datetime_string text; 1844v_current_partition_name text; 1845v_max_partition_timestamp timestamp; 1846v_last_partition text; 1847v_lock_iter int := 1; 1848v_lock_obtained boolean := FALSE; 1849v_min_partition_timestamp timestamp; 1850v_parent_schema text; 1851v_parent_tablename text; 1852v_part_interval interval; 1853v_partition_suffix text; 1854v_partition_timestamp timestamp[]; 1855v_quarter text; 1856v_rowcount bigint; 1857v_sql text; 1858v_start_control timestamp; 1859v_time_position int; 1860v_total_rows bigint := 0; 1861v_type text; 1862v_year text; 1863 1864BEGIN 1865 1866SELECT type 1867 , part_interval::interval 1868 , control 1869 , datetime_string 1870INTO v_type 1871 , v_part_interval 1872 , v_control 1873 , v_datetime_string 1874FROM @extschema@.part_config 1875WHERE parent_table = p_parent_table 1876AND (type = 'time-static' OR type = 'time-dynamic' OR type = 'time-custom'); 1877IF NOT FOUND THEN 1878 RAISE EXCEPTION 'ERROR: no config found for %', p_parent_table; 1879END IF; 1880 1881IF p_batch_interval IS NULL OR p_batch_interval > v_part_interval THEN 1882 p_batch_interval := v_part_interval; 1883END IF; 1884 1885SELECT show_partitions INTO v_last_partition FROM @extschema@.show_partitions(p_parent_table, 'DESC') LIMIT 1; 1886 1887FOR i IN 1..p_batch_count LOOP 1888 1889 IF p_order = 'ASC' THEN 1890 EXECUTE 'SELECT min('||v_control||') FROM ONLY '||p_parent_table INTO v_start_control; 1891 ELSIF p_order = 'DESC' THEN 1892 EXECUTE 'SELECT max('||v_control||') FROM ONLY '||p_parent_table INTO v_start_control; 1893 ELSE 1894 RAISE EXCEPTION 'Invalid value for p_order. Must be ASC or DESC'; 1895 END IF; 1896 1897 IF v_start_control IS NULL THEN 1898 EXIT; 1899 END IF; 1900 1901 IF v_type = 'time-static' OR v_type = 'time-dynamic' THEN 1902 CASE 1903 WHEN v_part_interval = '15 mins' THEN 1904 v_min_partition_timestamp := date_trunc('hour', v_start_control) + 1905 '15min'::interval * floor(date_part('minute', v_start_control) / 15.0); 1906 WHEN v_part_interval = '30 mins' THEN 1907 v_min_partition_timestamp := date_trunc('hour', v_start_control) + 1908 '30min'::interval * floor(date_part('minute', v_start_control) / 30.0); 1909 WHEN v_part_interval = '1 hour' THEN 1910 v_min_partition_timestamp := date_trunc('hour', v_start_control); 1911 WHEN v_part_interval = '1 day' THEN 1912 v_min_partition_timestamp := date_trunc('day', v_start_control); 1913 WHEN v_part_interval = '1 week' THEN 1914 v_min_partition_timestamp := date_trunc('week', v_start_control); 1915 WHEN v_part_interval = '1 month' THEN 1916 v_min_partition_timestamp := date_trunc('month', v_start_control); 1917 WHEN v_part_interval = '3 months' THEN 1918 v_min_partition_timestamp := date_trunc('quarter', v_start_control); 1919 WHEN v_part_interval = '1 year' THEN 1920 v_min_partition_timestamp := date_trunc('year', v_start_control); 1921 END CASE; 1922 ELSIF v_type = 'time-custom' THEN 1923 -- Keep going backwards, checking if the time interval encompases the current v_start_control value 1924 v_time_position := (length(v_last_partition) - position('p_' in reverse(v_last_partition))) + 2; 1925 v_min_partition_timestamp := to_timestamp(substring(v_last_partition from v_time_position), v_datetime_string); 1926 v_max_partition_timestamp := v_min_partition_timestamp + v_part_interval; 1927 LOOP 1928 IF v_start_control >= v_min_partition_timestamp AND v_start_control < v_max_partition_timestamp THEN 1929 EXIT; 1930 ELSE 1931 v_max_partition_timestamp := v_min_partition_timestamp; 1932 BEGIN 1933 v_min_partition_timestamp := v_min_partition_timestamp - v_part_interval; 1934 EXCEPTION WHEN datetime_field_overflow THEN 1935 RAISE EXCEPTION 'Attempted partition time interval is outside PostgreSQL''s supported time range. 1936 Unable to create partition with interval before timestamp % ', v_min_partition_interval; 1937 END; 1938 END IF; 1939 END LOOP; 1940 1941 END IF; 1942 1943 v_partition_timestamp := ARRAY[v_min_partition_timestamp]; 1944 IF p_order = 'ASC' THEN 1945 IF (v_start_control + p_batch_interval) >= (v_min_partition_timestamp + v_part_interval) THEN 1946 v_max_partition_timestamp := v_min_partition_timestamp + v_part_interval; 1947 ELSE 1948 v_max_partition_timestamp := v_start_control + p_batch_interval; 1949 END IF; 1950 ELSIF p_order = 'DESC' THEN 1951 -- Must be greater than max value still in parent table since query below grabs < max 1952 v_max_partition_timestamp := v_min_partition_timestamp + v_part_interval; 1953 -- Make sure minimum doesn't underflow current partition minimum 1954 IF (v_start_control - p_batch_interval) >= v_min_partition_timestamp THEN 1955 v_min_partition_timestamp = v_start_control - p_batch_interval; 1956 END IF; 1957 ELSE 1958 RAISE EXCEPTION 'Invalid value for p_order. Must be ASC or DESC'; 1959 END IF; 1960 1961-- do some locking with timeout, if required 1962 IF p_lock_wait > 0 THEN 1963 v_lock_iter := 0; 1964 WHILE v_lock_iter <= 5 LOOP 1965 v_lock_iter := v_lock_iter + 1; 1966 BEGIN 1967 v_sql := 'SELECT * FROM ONLY ' || p_parent_table || 1968 ' WHERE '||v_control||' >= '||quote_literal(v_min_partition_timestamp)|| 1969 ' AND '||v_control||' < '||quote_literal(v_max_partition_timestamp) 1970 ||' FOR UPDATE NOWAIT'; 1971 EXECUTE v_sql; 1972 v_lock_obtained := TRUE; 1973 EXCEPTION 1974 WHEN lock_not_available THEN 1975 PERFORM pg_sleep( p_lock_wait / 5.0 ); 1976 CONTINUE; 1977 END; 1978 EXIT WHEN v_lock_obtained; 1979 END LOOP; 1980 IF NOT v_lock_obtained THEN 1981 RETURN -1; 1982 END IF; 1983 END IF; 1984 1985 PERFORM @extschema@.create_partition_time(p_parent_table, v_partition_timestamp); 1986 -- This suffix generation code is in create_partition_time() as well 1987 v_partition_suffix := to_char(v_min_partition_timestamp, 'YYYY'); 1988 IF v_part_interval < '1 year' AND v_part_interval <> '1 week' THEN 1989 v_partition_suffix := v_partition_suffix ||'_'|| to_char(v_min_partition_timestamp, 'MM'); 1990 IF v_part_interval < '1 month' AND v_part_interval <> '1 week' THEN 1991 v_partition_suffix := v_partition_suffix ||'_'|| to_char(v_min_partition_timestamp, 'DD'); 1992 IF v_part_interval < '1 day' THEN 1993 v_partition_suffix := v_partition_suffix || '_' || to_char(v_min_partition_timestamp, 'HH24MI'); 1994 IF v_part_interval < '1 minute' THEN 1995 v_partition_suffix := v_partition_suffix || to_char(v_min_partition_timestamp, 'SS'); 1996 END IF; -- end < minute IF 1997 END IF; -- end < day IF 1998 END IF; -- end < month IF 1999 END IF; -- end < year IF 2000 IF v_part_interval = '1 week' THEN 2001 v_partition_suffix := to_char(v_min_partition_timestamp, 'IYYY') || 'w' || to_char(v_min_partition_timestamp, 'IW'); 2002 END IF; 2003 -- "Q" is ignored in to_timestamp, so handle special case 2004 IF v_part_interval = '3 months' AND (v_type = 'time-static' OR v_type = 'time-dynamic') THEN 2005 v_year := to_char(v_min_partition_timestamp, 'YYYY'); 2006 v_quarter := to_char(v_min_partition_timestamp, 'Q'); 2007 v_partition_suffix := v_year || 'q' || v_quarter; 2008 END IF; 2009 2010 SELECT schemaname, tablename INTO v_parent_schema, v_parent_tablename FROM pg_catalog.pg_tables WHERE schemaname||'.'||tablename = p_parent_table; 2011 v_current_partition_name := @extschema@.check_name_length(v_parent_tablename, v_parent_schema, v_partition_suffix, TRUE); 2012 2013 EXECUTE 'WITH partition_data AS ( 2014 DELETE FROM ONLY '||p_parent_table||' WHERE '||v_control||' >= '||quote_literal(v_min_partition_timestamp)|| 2015 ' AND '||v_control||' < '||quote_literal(v_max_partition_timestamp)||' RETURNING *) 2016 INSERT INTO '||v_current_partition_name||' SELECT * FROM partition_data'; 2017 2018 GET DIAGNOSTICS v_rowcount = ROW_COUNT; 2019 v_total_rows := v_total_rows + v_rowcount; 2020 IF v_rowcount = 0 THEN 2021 EXIT; 2022 END IF; 2023 2024END LOOP; 2025 2026IF v_type = 'time-static' THEN 2027 PERFORM @extschema@.create_function_time(p_parent_table); 2028END IF; 2029 2030RETURN v_total_rows; 2031 2032END 2033$$; 2034 2035 2036/* 2037 * Populate the child table(s) of an id-based partition set with old data from the original parent 2038 */ 2039CREATE OR REPLACE FUNCTION partition_data_id(p_parent_table text, p_batch_count int DEFAULT 1, p_batch_interval int DEFAULT NULL, p_lock_wait numeric DEFAULT 0, p_order text DEFAULT 'ASC') RETURNS bigint 2040 LANGUAGE plpgsql SECURITY DEFINER 2041 AS $$ 2042DECLARE 2043 2044v_control text; 2045v_current_partition_name text; 2046v_lock_iter int := 1; 2047v_lock_obtained boolean := FALSE; 2048v_max_partition_id bigint; 2049v_min_partition_id bigint; 2050v_parent_schema text; 2051v_parent_tablename text; 2052v_part_interval bigint; 2053v_partition_id bigint[]; 2054v_rowcount bigint; 2055v_sql text; 2056v_start_control bigint; 2057v_total_rows bigint := 0; 2058v_type text; 2059 2060BEGIN 2061 2062SELECT type 2063 , part_interval::bigint 2064 , control 2065INTO v_type 2066 , v_part_interval 2067 , v_control 2068FROM @extschema@.part_config 2069WHERE parent_table = p_parent_table 2070AND (type = 'id-static' OR type = 'id-dynamic'); 2071IF NOT FOUND THEN 2072 RAISE EXCEPTION 'ERROR: no config found for %', p_parent_table; 2073END IF; 2074 2075IF p_batch_interval IS NULL OR p_batch_interval > v_part_interval THEN 2076 p_batch_interval := v_part_interval; 2077END IF; 2078 2079FOR i IN 1..p_batch_count LOOP 2080 2081 IF p_order = 'ASC' THEN 2082 EXECUTE 'SELECT min('||v_control||') FROM ONLY '||p_parent_table INTO v_start_control; 2083 IF v_start_control IS NULL THEN 2084 EXIT; 2085 END IF; 2086 v_min_partition_id = v_start_control - (v_start_control % v_part_interval); 2087 v_partition_id := ARRAY[v_min_partition_id]; 2088 -- Check if custom batch interval overflows current partition maximum 2089 IF (v_start_control + p_batch_interval) >= (v_min_partition_id + v_part_interval) THEN 2090 v_max_partition_id := v_min_partition_id + v_part_interval; 2091 ELSE 2092 v_max_partition_id := v_start_control + p_batch_interval; 2093 END IF; 2094 2095 ELSIF p_order = 'DESC' THEN 2096 EXECUTE 'SELECT max('||v_control||') FROM ONLY '||p_parent_table INTO v_start_control; 2097 IF v_start_control IS NULL THEN 2098 EXIT; 2099 END IF; 2100 v_min_partition_id = v_start_control - (v_start_control % v_part_interval); 2101 -- Must be greater than max value still in parent table since query below grabs < max 2102 v_max_partition_id := v_min_partition_id + v_part_interval; 2103 v_partition_id := ARRAY[v_min_partition_id]; 2104 -- Make sure minimum doesn't underflow current partition minimum 2105 IF (v_start_control - p_batch_interval) >= v_min_partition_id THEN 2106 v_min_partition_id = v_start_control - p_batch_interval; 2107 END IF; 2108 ELSE 2109 RAISE EXCEPTION 'Invalid value for p_order. Must be ASC or DESC'; 2110 END IF; 2111 2112 -- do some locking with timeout, if required 2113 IF p_lock_wait > 0 THEN 2114 v_lock_iter := 0; 2115 WHILE v_lock_iter <= 5 LOOP 2116 v_lock_iter := v_lock_iter + 1; 2117 BEGIN 2118 v_sql := 'SELECT * FROM ONLY ' || p_parent_table || 2119 ' WHERE '||v_control||' >= '||quote_literal(v_min_partition_id)|| 2120 ' AND '||v_control||' < '||quote_literal(v_max_partition_id) 2121 ||' FOR UPDATE NOWAIT'; 2122 EXECUTE v_sql; 2123 v_lock_obtained := TRUE; 2124 EXCEPTION 2125 WHEN lock_not_available THEN 2126 PERFORM pg_sleep( p_lock_wait / 5.0 ); 2127 CONTINUE; 2128 END; 2129 EXIT WHEN v_lock_obtained; 2130 END LOOP; 2131 IF NOT v_lock_obtained THEN 2132 RETURN -1; 2133 END IF; 2134 END IF; 2135 2136 PERFORM @extschema@.create_partition_id(p_parent_table, v_partition_id); 2137 SELECT schemaname, tablename INTO v_parent_schema, v_parent_tablename FROM pg_catalog.pg_tables WHERE schemaname||'.'||tablename = p_parent_table; 2138 v_current_partition_name := @extschema@.check_name_length(v_parent_tablename, v_parent_schema, v_min_partition_id::text, TRUE); 2139 2140 EXECUTE 'WITH partition_data AS ( 2141 DELETE FROM ONLY '||p_parent_table||' WHERE '||v_control||' >= '||v_min_partition_id|| 2142 ' AND '||v_control||' < '||v_max_partition_id||' RETURNING *) 2143 INSERT INTO '||v_current_partition_name||' SELECT * FROM partition_data'; 2144 2145 GET DIAGNOSTICS v_rowcount = ROW_COUNT; 2146 v_total_rows := v_total_rows + v_rowcount; 2147 IF v_rowcount = 0 THEN 2148 EXIT; 2149 END IF; 2150 2151END LOOP; 2152 2153IF v_type = 'id-static' THEN 2154 PERFORM @extschema@.create_function_id(p_parent_table); 2155END IF; 2156 2157RETURN v_total_rows; 2158 2159END 2160$$; 2161 2162 2163/* 2164 * Function to manage pre-creation of the next partitions in a set. 2165 * Also manages dropping old partitions if the retention option is set. 2166 * 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) 2167 * Otherwise, will run on all tables in the config table with p_run_maintenance() set to true. 2168 * 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. 2169 * Be aware that constraint exclusion may not work properly until an analyze on the partition set is run. 2170 */ 2171CREATE FUNCTION run_maintenance(p_parent_table text DEFAULT NULL, p_analyze boolean DEFAULT true, p_jobmon boolean DEFAULT true) RETURNS void 2172 LANGUAGE plpgsql SECURITY DEFINER 2173 AS $$ 2174DECLARE 2175 2176v_adv_lock boolean; 2177v_create_count int := 0; 2178v_current_partition text; 2179v_current_partition_id bigint; 2180v_current_partition_timestamp timestamp; 2181v_datetime_string text; 2182v_drop_count int := 0; 2183v_id_position int; 2184v_job_id bigint; 2185v_jobmon boolean; 2186v_jobmon_schema text; 2187v_last_partition text; 2188v_last_partition_created boolean; 2189v_last_partition_id bigint; 2190v_last_partition_timestamp timestamp; 2191v_next_partition_id bigint; 2192v_next_partition_timestamp timestamp; 2193v_old_search_path text; 2194v_premade_count int; 2195v_quarter text; 2196v_step_id bigint; 2197v_step_overflow_id bigint; 2198v_step_serial_id bigint; 2199v_sub_parent text; 2200v_row record; 2201v_row_sub record; 2202v_tablename text; 2203v_tables_list_sql text; 2204v_time_position int; 2205v_year text; 2206 2207BEGIN 2208 2209v_adv_lock := pg_try_advisory_xact_lock(hashtext('pg_partman run_maintenance')); 2210IF v_adv_lock = 'false' THEN 2211 RAISE NOTICE 'Partman maintenance already running.'; 2212 RETURN; 2213END IF; 2214 2215IF p_jobmon THEN 2216 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; 2217 IF v_jobmon_schema IS NOT NULL THEN 2218 SELECT current_setting('search_path') INTO v_old_search_path; 2219 EXECUTE 'SELECT set_config(''search_path'',''@extschema@,'||v_jobmon_schema||''',''false'')'; 2220 END IF; 2221END IF; 2222 2223IF v_jobmon_schema IS NOT NULL THEN 2224 v_job_id := add_job('PARTMAN RUN MAINTENANCE'); 2225 v_step_id := add_step(v_job_id, 'Running maintenance loop'); 2226END IF; 2227 2228v_tables_list_sql := 'SELECT parent_table 2229 , type 2230 , part_interval 2231 , control 2232 , premake 2233 , datetime_string 2234 , undo_in_progress 2235 FROM @extschema@.part_config'; 2236 2237IF p_parent_table IS NULL THEN 2238 v_tables_list_sql := v_tables_list_sql || ' WHERE use_run_maintenance = true'; 2239ELSE 2240 v_tables_list_sql := v_tables_list_sql || format(' WHERE parent_table = %L', p_parent_table); 2241END IF; 2242 2243FOR v_row IN EXECUTE v_tables_list_sql 2244LOOP 2245 2246 CONTINUE WHEN v_row.undo_in_progress; 2247 2248 SELECT show_partitions INTO v_last_partition FROM @extschema@.show_partitions(v_row.parent_table, 'DESC') LIMIT 1; 2249 2250 IF v_row.type = 'time-static' OR v_row.type = 'time-dynamic' OR v_row.type = 'time-custom' THEN 2251 2252 IF v_row.type = 'time-static' OR v_row.type = 'time-dynamic' THEN 2253 CASE 2254 WHEN v_row.part_interval::interval = '15 mins' THEN 2255 v_current_partition_timestamp := date_trunc('hour', CURRENT_TIMESTAMP) + 2256 '15min'::interval * floor(date_part('minute', CURRENT_TIMESTAMP) / 15.0); 2257 WHEN v_row.part_interval::interval = '30 mins' THEN 2258 v_current_partition_timestamp := date_trunc('hour', CURRENT_TIMESTAMP) + 2259 '30min'::interval * floor(date_part('minute', CURRENT_TIMESTAMP) / 30.0); 2260 WHEN v_row.part_interval::interval = '1 hour' THEN 2261 v_current_partition_timestamp := date_trunc('hour', CURRENT_TIMESTAMP); 2262 WHEN v_row.part_interval::interval = '1 day' THEN 2263 v_current_partition_timestamp := date_trunc('day', CURRENT_TIMESTAMP); 2264 WHEN v_row.part_interval::interval = '1 week' THEN 2265 v_current_partition_timestamp := date_trunc('week', CURRENT_TIMESTAMP); 2266 WHEN v_row.part_interval::interval = '1 month' THEN 2267 v_current_partition_timestamp := date_trunc('month', CURRENT_TIMESTAMP); 2268 WHEN v_row.part_interval::interval = '3 months' THEN 2269 v_current_partition_timestamp := date_trunc('quarter', CURRENT_TIMESTAMP); 2270 WHEN v_row.part_interval::interval = '1 year' THEN 2271 v_current_partition_timestamp := date_trunc('year', CURRENT_TIMESTAMP); 2272 END CASE; 2273 ELSIF v_row.type = 'time-custom' THEN 2274 SELECT child_table INTO v_current_partition FROM @extschema@.custom_time_partitions 2275 WHERE parent_table = v_row.parent_table AND partition_range @> CURRENT_TIMESTAMP; 2276 IF v_current_partition IS NULL THEN 2277 RAISE EXCEPTION 'Current time partition missing from custom_time_partitions config table for table % and timestamp %', 2278 CURRENT_TIMESTAMP, v_row.parent_table; 2279 END IF; 2280 v_time_position := (length(v_current_partition) - position('p_' in reverse(v_current_partition))) + 2; 2281 v_current_partition_timestamp := to_timestamp(substring(v_current_partition from v_time_position), v_row.datetime_string); 2282 END IF; 2283 2284 v_time_position := (length(v_last_partition) - position('p_' in reverse(v_last_partition))) + 2; 2285 IF v_row.part_interval::interval <> '3 months' OR (v_row.part_interval::interval = '3 months' AND v_row.type = 'time-custom') THEN 2286 v_last_partition_timestamp := to_timestamp(substring(v_last_partition from v_time_position), v_row.datetime_string); 2287 ELSE 2288 -- to_timestamp doesn't recognize 'Q' date string formater. Handle it 2289 v_year := split_part(substring(v_last_partition from v_time_position), 'q', 1); 2290 v_quarter := split_part(substring(v_last_partition from v_time_position), 'q', 2); 2291 CASE 2292 WHEN v_quarter = '1' THEN 2293 v_last_partition_timestamp := to_timestamp(v_year || '-01-01', 'YYYY-MM-DD'); 2294 WHEN v_quarter = '2' THEN 2295 v_last_partition_timestamp := to_timestamp(v_year || '-04-01', 'YYYY-MM-DD'); 2296 WHEN v_quarter = '3' THEN 2297 v_last_partition_timestamp := to_timestamp(v_year || '-07-01', 'YYYY-MM-DD'); 2298 WHEN v_quarter = '4' THEN 2299 v_last_partition_timestamp := to_timestamp(v_year || '-10-01', 'YYYY-MM-DD'); 2300 END CASE; 2301 END IF; 2302 2303 -- Check and see how many premade partitions there are. 2304 -- Can be negative when subpartitioning and there are parent partitions in the past compared to current timestamp value. 2305 -- abs() prevents run_maintenence from running on those old parent tables 2306 v_premade_count = abs(round(EXTRACT('epoch' FROM age(v_last_partition_timestamp, v_current_partition_timestamp)) / EXTRACT('epoch' FROM v_row.part_interval::interval))); 2307 v_next_partition_timestamp := v_last_partition_timestamp; 2308 -- Loop premaking until config setting is met. Allows it to catch up if it fell behind or if premake changed. 2309 WHILE v_premade_count < v_row.premake LOOP 2310 BEGIN 2311 v_next_partition_timestamp := v_next_partition_timestamp + v_row.part_interval::interval; 2312 EXCEPTION WHEN datetime_field_overflow THEN 2313 v_premade_count := v_row.premake; -- do this so it can exit the premake check loop and continue in the outer for loop 2314 IF v_jobmon_schema IS NOT NULL THEN 2315 v_step_overflow_id := add_step(v_job_id, 'Attempted partition time interval is outside PostgreSQL''s supported time range.'); 2316 PERFORM update_step(v_step_overflow_id, 'CRITICAL', 'Child partition creation skippd for parent table '||v_partition_time); 2317 END IF; 2318 RAISE WARNING 'Attempted partition time interval is outside PostgreSQL''s supported time range. Child partition creation skipped for parent table %', v_row.parent_table; 2319 CONTINUE; 2320 END; 2321 2322 v_last_partition_created := @extschema@.create_partition_time(v_row.parent_table, ARRAY[v_next_partition_timestamp], p_analyze); 2323 v_create_count := v_create_count + 1; 2324 IF v_row.type = 'time-static' AND v_last_partition_created THEN 2325 PERFORM @extschema@.create_function_time(v_row.parent_table); 2326 END IF; 2327 2328 -- Manage additonal constraints if set 2329 PERFORM @extschema@.apply_constraints(v_row.parent_table); 2330 -- Can be negative when subpartitioning and there are parent partitions in the past compared to current timestamp value. 2331 -- abs() prevents run_maintenence from running on those old parent tables 2332 v_premade_count = abs(round(EXTRACT('epoch' FROM age(v_next_partition_timestamp, v_current_partition_timestamp)) / EXTRACT('epoch' FROM v_row.part_interval::interval))); 2333 END LOOP; 2334 ELSIF v_row.type = 'id-static' OR v_row.type ='id-dynamic' THEN 2335 -- This doesn't need the overall max of a full subpartition set, just the max of the current partition set 2336 EXECUTE 'SELECT '||v_row.control||' - ('||v_row.control||' % '||v_row.part_interval::int||') FROM '||v_row.parent_table||' 2337 WHERE '||v_row.control||' = (SELECT max('||v_row.control||') FROM '||v_row.parent_table||')' 2338 INTO v_current_partition_id; 2339 v_id_position := (length(v_last_partition) - position('p_' in reverse(v_last_partition))) + 2; 2340 v_last_partition_id = substring(v_last_partition from v_id_position)::bigint; 2341 -- This catches if there's invalid data in a parent table set that's outside all child table ranges. 2342 IF v_last_partition_id < v_current_partition_id THEN 2343 IF v_jobmon_schema IS NOT NULL THEN 2344 v_step_serial_id := add_step(v_job_id, 'Found inconsistent data in serial partition set.'); 2345 PERFORM update_step(v_step_serial_id, 'CRITICAL', 'Child partition creation skipped for parent table '||v_row.parent_table||'. Current max serial id value ('||v_current_partition_id||') is greater than the id range covered by the last partition created ('||v_last_partition||'). Run check_parent() to find possible cause.'); 2346 END IF; 2347 RAISE WARNING 'Child partition creation skipped for parent table %. Found inconsistent data in serial partition set. Current max serial id value (%) is greater than the id range covered by the last partition created (%). Run check_parent() to find possible cause.', v_row.parent_table, v_current_partition_id, v_last_partition; 2348 CONTINUE; 2349 END IF; 2350 v_next_partition_id := v_last_partition_id + v_row.part_interval::bigint; 2351 -- Can be negative when subpartitioning and there are parent partitions with lower values compared to current id value. 2352 -- abs() prevents run_maintenence from running on those old parent tables 2353 WHILE (abs((v_next_partition_id - v_current_partition_id) / v_row.part_interval::bigint)) <= v_row.premake 2354 LOOP 2355 v_last_partition_created := @extschema@.create_partition_id(v_row.parent_table, ARRAY[v_next_partition_id], p_analyze); 2356 IF v_last_partition_created THEN 2357 PERFORM @extschema@.create_function_id(v_row.parent_table); 2358 PERFORM @extschema@.apply_constraints(v_row.parent_table); 2359 END IF; 2360 v_next_partition_id := v_next_partition_id + v_row.part_interval::bigint; 2361 END LOOP; 2362 2363 END IF; -- end main IF check for time or id 2364 2365END LOOP; -- end of creation loop 2366 2367-- Manage dropping old partitions if retention option is set 2368FOR v_row IN 2369 SELECT parent_table FROM @extschema@.part_config WHERE retention IS NOT NULL AND undo_in_progress = false AND 2370 (type = 'time-static' OR type = 'time-dynamic' OR type = 'time-custom') 2371LOOP 2372 IF p_parent_table IS NULL THEN 2373 v_drop_count := v_drop_count + @extschema@.drop_partition_time(v_row.parent_table); 2374 ELSE -- Only run retention on table given in parameter 2375 IF p_parent_table <> v_row.parent_table THEN 2376 CONTINUE; 2377 ELSE 2378 v_drop_count := v_drop_count + @extschema@.drop_partition_time(v_row.parent_table); 2379 END IF; 2380 END IF; 2381END LOOP; 2382FOR v_row IN 2383 SELECT parent_table FROM @extschema@.part_config WHERE retention IS NOT NULL AND undo_in_progress = false AND (type = 'id-static' OR type = 'id-dynamic') 2384LOOP 2385 IF p_parent_table IS NULL THEN 2386 v_drop_count := v_drop_count + @extschema@.drop_partition_id(v_row.parent_table); 2387 ELSE -- Only run retention on table given in parameter 2388 IF p_parent_table <> v_row.parent_table THEN 2389 CONTINUE; 2390 ELSE 2391 v_drop_count := v_drop_count + @extschema@.drop_partition_id(v_row.parent_table); 2392 END IF; 2393 END IF; 2394END LOOP; 2395 2396IF v_jobmon_schema IS NOT NULL THEN 2397 PERFORM update_step(v_step_id, 'OK', 'Partition maintenance finished. '||v_create_count||' partitions made. '||v_drop_count||' partitions dropped.'); 2398 IF v_step_overflow_id IS NOT NULL OR v_step_serial_id IS NOT NULL THEN 2399 PERFORM fail_job(v_job_id); 2400 ELSE 2401 PERFORM close_job(v_job_id); 2402 END IF; 2403 EXECUTE 'SELECT set_config(''search_path'','''||v_old_search_path||''',''false'')'; 2404END IF; 2405 2406EXCEPTION 2407 WHEN OTHERS THEN 2408 IF v_jobmon_schema IS NOT NULL THEN 2409 IF v_job_id IS NULL THEN 2410 EXECUTE 'SELECT '||v_jobmon_schema||'.add_job(''PARTMAN RUN MAINTENANCE'')' INTO v_job_id; 2411 EXECUTE 'SELECT '||v_jobmon_schema||'.add_step('||v_job_id||', ''EXCEPTION before job logging started'')' INTO v_step_id; 2412 ELSIF v_step_id IS NULL THEN 2413 EXECUTE 'SELECT '||v_jobmon_schema||'.add_step('||v_job_id||', ''EXCEPTION before first step logged'')' INTO v_step_id; 2414 END IF; 2415 EXECUTE 'SELECT '||v_jobmon_schema||'.update_step('||v_step_id||', ''CRITICAL'', ''ERROR: '||coalesce(SQLERRM,'unknown')||''')'; 2416 EXECUTE 'SELECT '||v_jobmon_schema||'.fail_job('||v_job_id||')'; 2417 END IF; 2418 RAISE EXCEPTION '%', SQLERRM; 2419END 2420$$; 2421 2422 2423/* 2424 * Function to drop child tables from an id-based partition set. 2425 * Options to move table to different schema, drop only indexes or actually drop the table from the database. 2426 */ 2427CREATE 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 2428 LANGUAGE plpgsql SECURITY DEFINER 2429 AS $$ 2430DECLARE 2431 2432v_adv_lock boolean; 2433v_child_table text; 2434v_control text; 2435v_drop_count int := 0; 2436v_id_position int; 2437v_index record; 2438v_job_id bigint; 2439v_jobmon boolean; 2440v_jobmon_schema text; 2441v_max bigint; 2442v_old_search_path text; 2443v_part_interval bigint; 2444v_partition_id bigint; 2445v_retention bigint; 2446v_retention_keep_index boolean; 2447v_retention_keep_table boolean; 2448v_retention_schema text; 2449v_step_id bigint; 2450 2451BEGIN 2452 2453v_adv_lock := pg_try_advisory_xact_lock(hashtext('pg_partman drop_partition_id')); 2454IF v_adv_lock = 'false' THEN 2455 RAISE NOTICE 'drop_partition_id already running.'; 2456 RETURN 0; 2457END IF; 2458 2459-- Allow override of configuration options 2460IF p_retention IS NULL THEN 2461 SELECT 2462 part_interval::bigint 2463 , control 2464 , retention::bigint 2465 , retention_keep_table 2466 , retention_keep_index 2467 , retention_schema 2468 , jobmon 2469 INTO 2470 v_part_interval 2471 , v_control 2472 , v_retention 2473 , v_retention_keep_table 2474 , v_retention_keep_index 2475 , v_retention_schema 2476 , v_jobmon 2477 FROM @extschema@.part_config 2478 WHERE parent_table = p_parent_table 2479 AND (type = 'id-static' OR type = 'id-dynamic') 2480 AND retention IS NOT NULL; 2481 2482 IF v_part_interval IS NULL THEN 2483 RAISE EXCEPTION 'Configuration for given parent table with a retention period not found: %', p_parent_table; 2484 END IF; 2485ELSE 2486 SELECT 2487 part_interval::bigint 2488 , control 2489 , retention_keep_table 2490 , retention_keep_index 2491 , retention_schema 2492 , jobmon 2493 INTO 2494 v_part_interval 2495 , v_control 2496 , v_retention_keep_table 2497 , v_retention_keep_index 2498 , v_retention_schema 2499 , v_jobmon 2500 FROM @extschema@.part_config 2501 WHERE parent_table = p_parent_table 2502 AND (type = 'id-static' OR type = 'id-dynamic'); 2503 v_retention := p_retention; 2504 2505 IF v_part_interval IS NULL THEN 2506 RAISE EXCEPTION 'Configuration for given parent table not found: %', p_parent_table; 2507 END IF; 2508END IF; 2509 2510IF v_jobmon THEN 2511 SELECT nspname INTO v_jobmon_schema FROM pg_namespace n, pg_extension e WHERE e.extname = 'pg_jobmon' AND e.extnamespace = n.oid; 2512 IF v_jobmon_schema IS NOT NULL THEN 2513 SELECT current_setting('search_path') INTO v_old_search_path; 2514 EXECUTE 'SELECT set_config(''search_path'',''@extschema@,'||v_jobmon_schema||''',''false'')'; 2515 END IF; 2516END IF; 2517 2518IF p_keep_table IS NOT NULL THEN 2519 v_retention_keep_table = p_keep_table; 2520END IF; 2521IF p_keep_index IS NOT NULL THEN 2522 v_retention_keep_index = p_keep_index; 2523END IF; 2524IF p_retention_schema IS NOT NULL THEN 2525 v_retention_schema = p_retention_schema; 2526END IF; 2527 2528IF v_jobmon_schema IS NOT NULL THEN 2529 v_job_id := add_job('PARTMAN DROP ID PARTITION: '|| p_parent_table); 2530END IF; 2531 2532EXECUTE 'SELECT max('||v_control||') FROM '||p_parent_table INTO v_max; 2533 2534-- Loop through child tables of the given parent 2535FOR v_child_table IN 2536 SELECT n.nspname||'.'||c.relname FROM pg_inherits i join pg_class c ON i.inhrelid = c.oid join pg_namespace n ON c.relnamespace = n.oid WHERE i.inhparent::regclass = p_parent_table::regclass ORDER BY i.inhrelid ASC 2537LOOP 2538 v_id_position := (length(v_child_table) - position('p_' in reverse(v_child_table))) + 2; 2539 v_partition_id := substring(v_child_table from v_id_position)::bigint; 2540 2541 -- Add one interval since partition names contain the start of the constraint period 2542 IF v_retention <= (v_max - (v_partition_id + v_part_interval)) THEN 2543 IF v_jobmon_schema IS NOT NULL THEN 2544 v_step_id := add_step(v_job_id, 'Uninherit table '||v_child_table||' from '||p_parent_table); 2545 END IF; 2546 EXECUTE 'ALTER TABLE '||v_child_table||' NO INHERIT ' || p_parent_table; 2547 IF v_jobmon_schema IS NOT NULL THEN 2548 PERFORM update_step(v_step_id, 'OK', 'Done'); 2549 END IF; 2550 IF v_retention_schema IS NULL THEN 2551 IF v_retention_keep_table = false THEN 2552 IF v_jobmon_schema IS NOT NULL THEN 2553 v_step_id := add_step(v_job_id, 'Drop table '||v_child_table); 2554 END IF; 2555 EXECUTE 'DROP TABLE '||v_child_table||' CASCADE'; 2556 IF v_jobmon_schema IS NOT NULL THEN 2557 PERFORM update_step(v_step_id, 'OK', 'Done'); 2558 END IF; 2559 ELSIF v_retention_keep_index = false THEN 2560 FOR v_index IN 2561 SELECT i.indexrelid::regclass AS name 2562 , c.conname 2563 FROM pg_catalog.pg_index i 2564 LEFT JOIN pg_catalog.pg_constraint c ON i.indexrelid = c.conindid 2565 WHERE i.indrelid = v_child_table::regclass 2566 LOOP 2567 IF v_jobmon_schema IS NOT NULL THEN 2568 v_step_id := add_step(v_job_id, 'Drop index '||v_index.name||' from '||v_child_table); 2569 END IF; 2570 IF v_index.conname IS NOT NULL THEN 2571 EXECUTE 'ALTER TABLE '||v_child_table||' DROP CONSTRAINT '||v_index.conname; 2572 ELSE 2573 EXECUTE 'DROP INDEX '||v_index.name; 2574 END IF; 2575 IF v_jobmon_schema IS NOT NULL THEN 2576 PERFORM update_step(v_step_id, 'OK', 'Done'); 2577 END IF; 2578 END LOOP; 2579 END IF; 2580 ELSE -- Move to new schema 2581 IF v_jobmon_schema IS NOT NULL THEN 2582 v_step_id := add_step(v_job_id, 'Moving table '||v_child_table||' to schema '||v_retention_schema); 2583 END IF; 2584 2585 EXECUTE 'ALTER TABLE '||v_child_table||' SET SCHEMA '||v_retention_schema; 2586 2587 IF v_jobmon_schema IS NOT NULL THEN 2588 PERFORM update_step(v_step_id, 'OK', 'Done'); 2589 END IF; 2590 END IF; -- End retention schema if 2591 2592 -- If child table is a subpartition, remove it from part_config & part_config_sub (should cascade due to FK) 2593 DELETE FROM @extschema@.part_config WHERE parent_table = v_child_table; 2594 2595 v_drop_count := v_drop_count + 1; 2596 END IF; -- End retention check IF 2597 2598END LOOP; -- End child table loop 2599 2600IF v_jobmon_schema IS NOT NULL THEN 2601 v_step_id := add_step(v_job_id, 'Finished partition drop maintenance'); 2602 PERFORM update_step(v_step_id, 'OK', v_drop_count||' partitions dropped.'); 2603 PERFORM close_job(v_job_id); 2604 EXECUTE 'SELECT set_config(''search_path'','''||v_old_search_path||''',''false'')'; 2605END IF; 2606 2607RETURN v_drop_count; 2608 2609EXCEPTION 2610 WHEN OTHERS THEN 2611 IF v_jobmon_schema IS NOT NULL THEN 2612 IF v_job_id IS NULL THEN 2613 EXECUTE 'SELECT '||v_jobmon_schema||'.add_job(''PARTMAN DROP ID PARTITION: '||p_parent_table||''')' INTO v_job_id; 2614 EXECUTE 'SELECT '||v_jobmon_schema||'.add_step('||v_job_id||', ''EXCEPTION before job logging started'')' INTO v_step_id; 2615 ELSIF v_step_id IS NULL THEN 2616 EXECUTE 'SELECT '||v_jobmon_schema||'.add_step('||v_job_id||', ''EXCEPTION before first step logged'')' INTO v_step_id; 2617 END IF; 2618 EXECUTE 'SELECT '||v_jobmon_schema||'.update_step('||v_step_id||', ''CRITICAL'', ''ERROR: '||coalesce(SQLERRM,'unknown')||''')'; 2619 EXECUTE 'SELECT '||v_jobmon_schema||'.fail_job('||v_job_id||')'; 2620 END IF; 2621 RAISE EXCEPTION '%', SQLERRM; 2622END 2623$$; 2624 2625 2626/* 2627 * Function to drop child tables from a time-based partition set. 2628 * Options to move table to different schema, drop only indexes or actually drop the table from the database. 2629 */ 2630CREATE 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 2631 LANGUAGE plpgsql SECURITY DEFINER 2632 AS $$ 2633DECLARE 2634 2635v_adv_lock boolean; 2636v_child_table text; 2637v_datetime_string text; 2638v_drop_count int := 0; 2639v_index record; 2640v_job_id bigint; 2641v_jobmon boolean; 2642v_jobmon_schema text; 2643v_old_search_path text; 2644v_part_interval interval; 2645v_partition_timestamp timestamp; 2646v_quarter text; 2647v_retention interval; 2648v_retention_keep_index boolean; 2649v_retention_keep_table boolean; 2650v_retention_schema text; 2651v_step_id bigint; 2652v_time_position int; 2653v_type text; 2654v_year text; 2655 2656BEGIN 2657 2658v_adv_lock := pg_try_advisory_xact_lock(hashtext('pg_partman drop_partition_time')); 2659IF v_adv_lock = 'false' THEN 2660 RAISE NOTICE 'drop_partition_time already running.'; 2661 RETURN 0; 2662END IF; 2663 2664-- Allow override of configuration options 2665IF p_retention IS NULL THEN 2666 SELECT 2667 type 2668 , part_interval::interval 2669 , retention::interval 2670 , retention_keep_table 2671 , retention_keep_index 2672 , datetime_string 2673 , retention_schema 2674 , jobmon 2675 INTO 2676 v_type 2677 , v_part_interval 2678 , v_retention 2679 , v_retention_keep_table 2680 , v_retention_keep_index 2681 , v_datetime_string 2682 , v_retention_schema 2683 , v_jobmon 2684 FROM @extschema@.part_config 2685 WHERE parent_table = p_parent_table 2686 AND (type = 'time-static' OR type = 'time-dynamic' OR type = 'time-custom') 2687 AND retention IS NOT NULL; 2688 2689 IF v_part_interval IS NULL THEN 2690 RAISE EXCEPTION 'Configuration for given parent table with a retention period not found: %', p_parent_table; 2691 END IF; 2692ELSE 2693 SELECT 2694 type 2695 , part_interval::interval 2696 , retention_keep_table 2697 , retention_keep_index 2698 , datetime_string 2699 , retention_schema 2700 , jobmon 2701 INTO 2702 v_type 2703 , v_part_interval 2704 , v_retention_keep_table 2705 , v_retention_keep_index 2706 , v_datetime_string 2707 , v_retention_schema 2708 , v_jobmon 2709 FROM @extschema@.part_config 2710 WHERE parent_table = p_parent_table 2711 AND (type = 'time-static' OR type = 'time-dynamic' OR type = 'time-custom'); 2712 v_retention := p_retention; 2713 2714 IF v_part_interval IS NULL THEN 2715 RAISE EXCEPTION 'Configuration for given parent table not found: %', p_parent_table; 2716 END IF; 2717END IF; 2718 2719IF v_jobmon THEN 2720 SELECT nspname INTO v_jobmon_schema FROM pg_namespace n, pg_extension e WHERE e.extname = 'pg_jobmon' AND e.extnamespace = n.oid; 2721 IF v_jobmon_schema IS NOT NULL THEN 2722 SELECT current_setting('search_path') INTO v_old_search_path; 2723 EXECUTE 'SELECT set_config(''search_path'',''@extschema@,'||v_jobmon_schema||''',''false'')'; 2724 END IF; 2725END IF; 2726 2727IF p_keep_table IS NOT NULL THEN 2728 v_retention_keep_table = p_keep_table; 2729END IF; 2730IF p_keep_index IS NOT NULL THEN 2731 v_retention_keep_index = p_keep_index; 2732END IF; 2733IF p_retention_schema IS NOT NULL THEN 2734 v_retention_schema = p_retention_schema; 2735END IF; 2736 2737IF v_jobmon_schema IS NOT NULL THEN 2738 v_job_id := add_job('PARTMAN DROP TIME PARTITION: '|| p_parent_table); 2739END IF; 2740 2741-- Loop through child tables of the given parent 2742FOR v_child_table IN 2743 SELECT n.nspname||'.'||c.relname FROM pg_inherits i join pg_class c ON i.inhrelid = c.oid join pg_namespace n ON c.relnamespace = n.oid WHERE i.inhparent::regclass = p_parent_table::regclass ORDER BY i.inhrelid ASC 2744LOOP 2745 -- pull out datetime portion of partition's tablename to make the next one 2746 v_time_position := (length(v_child_table) - position('p_' in reverse(v_child_table))) + 2; 2747 IF v_part_interval <> '3 months' OR (v_part_interval = '3 months' AND v_type = 'time-custom') THEN 2748 v_partition_timestamp := to_timestamp(substring(v_child_table from v_time_position), v_datetime_string); 2749 ELSE 2750 -- to_timestamp doesn't recognize 'Q' date string formater. Handle it 2751 v_year := split_part(substring(v_child_table from v_time_position), 'q', 1); 2752 v_quarter := split_part(substring(v_child_table from v_time_position), 'q', 2); 2753 CASE 2754 WHEN v_quarter = '1' THEN 2755 v_partition_timestamp := to_timestamp(v_year || '-01-01', 'YYYY-MM-DD'); 2756 WHEN v_quarter = '2' THEN 2757 v_partition_timestamp := to_timestamp(v_year || '-04-01', 'YYYY-MM-DD'); 2758 WHEN v_quarter = '3' THEN 2759 v_partition_timestamp := to_timestamp(v_year || '-07-01', 'YYYY-MM-DD'); 2760 WHEN v_quarter = '4' THEN 2761 v_partition_timestamp := to_timestamp(v_year || '-10-01', 'YYYY-MM-DD'); 2762 END CASE; 2763 END IF; 2764 2765 -- Add one interval since partition names contain the start of the constraint period 2766 IF v_retention < (CURRENT_TIMESTAMP - (v_partition_timestamp + v_part_interval)) THEN 2767 IF v_jobmon_schema IS NOT NULL THEN 2768 v_step_id := add_step(v_job_id, 'Uninherit table '||v_child_table||' from '||p_parent_table); 2769 END IF; 2770 EXECUTE 'ALTER TABLE '||v_child_table||' NO INHERIT ' || p_parent_table; 2771 IF v_jobmon_schema IS NOT NULL THEN 2772 PERFORM update_step(v_step_id, 'OK', 'Done'); 2773 END IF; 2774 IF v_retention_schema IS NULL THEN 2775 IF v_retention_keep_table = false THEN 2776 IF v_jobmon_schema IS NOT NULL THEN 2777 v_step_id := add_step(v_job_id, 'Drop table '||v_child_table); 2778 END IF; 2779 EXECUTE 'DROP TABLE '||v_child_table||' CASCADE'; 2780 IF v_jobmon_schema IS NOT NULL THEN 2781 PERFORM update_step(v_step_id, 'OK', 'Done'); 2782 END IF; 2783 ELSIF v_retention_keep_index = false THEN 2784 FOR v_index IN 2785 SELECT i.indexrelid::regclass AS name 2786 , c.conname 2787 FROM pg_catalog.pg_index i 2788 LEFT JOIN pg_catalog.pg_constraint c ON i.indexrelid = c.conindid 2789 WHERE i.indrelid = v_child_table::regclass 2790 LOOP 2791 IF v_jobmon_schema IS NOT NULL THEN 2792 v_step_id := add_step(v_job_id, 'Drop index '||v_index.name||' from '||v_child_table); 2793 END IF; 2794 IF v_index.conname IS NOT NULL THEN 2795 EXECUTE 'ALTER TABLE '||v_child_table||' DROP CONSTRAINT '||v_index.conname; 2796 ELSE 2797 EXECUTE 'DROP INDEX '||v_index.name; 2798 END IF; 2799 IF v_jobmon_schema IS NOT NULL THEN 2800 PERFORM update_step(v_step_id, 'OK', 'Done'); 2801 END IF; 2802 END LOOP; 2803 END IF; 2804 ELSE -- Move to new schema 2805 IF v_jobmon_schema IS NOT NULL THEN 2806 v_step_id := add_step(v_job_id, 'Moving table '||v_child_table||' to schema '||v_retention_schema); 2807 END IF; 2808 2809 EXECUTE 'ALTER TABLE '||v_child_table||' SET SCHEMA '||v_retention_schema; 2810 2811 IF v_jobmon_schema IS NOT NULL THEN 2812 PERFORM update_step(v_step_id, 'OK', 'Done'); 2813 END IF; 2814 END IF; -- End retention schema if 2815 2816 -- If child table is a subpartition, remove it from part_config & part_config_sub (should cascade due to FK) 2817 DELETE FROM @extschema@.part_config WHERE parent_table = v_child_table; 2818 2819 v_drop_count := v_drop_count + 1; 2820 END IF; -- End retention check IF 2821 2822END LOOP; -- End child table loop 2823 2824IF v_jobmon_schema IS NOT NULL THEN 2825 v_step_id := add_step(v_job_id, 'Finished partition drop maintenance'); 2826 PERFORM update_step(v_step_id, 'OK', v_drop_count||' partitions dropped.'); 2827 PERFORM close_job(v_job_id); 2828 EXECUTE 'SELECT set_config(''search_path'','''||v_old_search_path||''',''false'')'; 2829END IF; 2830 2831RETURN v_drop_count; 2832 2833EXCEPTION 2834 WHEN OTHERS THEN 2835 IF v_jobmon_schema IS NOT NULL THEN 2836 IF v_job_id IS NULL THEN 2837 EXECUTE 'SELECT '||v_jobmon_schema||'.add_job(''PARTMAN DROP TIME PARTITION: '||p_parent_table||''')' INTO v_job_id; 2838 EXECUTE 'SELECT '||v_jobmon_schema||'.add_step('||v_job_id||', ''EXCEPTION before job logging started'')' INTO v_step_id; 2839 ELSIF v_step_id IS NULL THEN 2840 EXECUTE 'SELECT '||v_jobmon_schema||'.add_step('||v_job_id||', ''EXCEPTION before first step logged'')' INTO v_step_id; 2841 END IF; 2842 EXECUTE 'SELECT '||v_jobmon_schema||'.update_step('||v_step_id||', ''CRITICAL'', ''ERROR: '||coalesce(SQLERRM,'unknown')||''')'; 2843 EXECUTE 'SELECT '||v_jobmon_schema||'.fail_job('||v_job_id||')'; 2844 END IF; 2845 RAISE EXCEPTION '%', SQLERRM; 2846END 2847$$; 2848 2849 2850/* 2851 * Function to list all child partitions in a set. 2852 */ 2853CREATE OR REPLACE FUNCTION show_partitions (p_parent_table text, p_order text DEFAULT 'ASC') RETURNS SETOF text 2854 LANGUAGE plpgsql STABLE SECURITY DEFINER 2855 AS $$ 2856DECLARE 2857 2858v_datetime_string text; 2859v_part_interval text; 2860v_type text; 2861 2862BEGIN 2863 2864IF p_order NOT IN ('ASC', 'DESC') THEN 2865 RAISE EXCEPTION 'p_order paramter must be one of the following values: ASC, DESC'; 2866END IF; 2867 2868SELECT type 2869 , part_interval 2870 , datetime_string 2871INTO v_type 2872 , v_part_interval 2873 , v_datetime_string 2874FROM @extschema@.part_config 2875WHERE parent_table = p_parent_table; 2876 2877IF v_type IN ('time-static', 'time-dynamic', 'time-custom') THEN 2878 2879 RETURN QUERY EXECUTE ' 2880 SELECT n.nspname::text ||''.''|| c.relname::text AS partition_name FROM 2881 pg_catalog.pg_inherits h 2882 JOIN pg_catalog.pg_class c ON c.oid = h.inhrelid 2883 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 2884 WHERE h.inhparent = '||quote_literal(p_parent_table)||'::regclass 2885 ORDER BY to_timestamp(substring(c.relname from ((length(c.relname) - position(''p_'' in reverse(c.relname))) + 2) ), '||quote_literal(v_datetime_string)||') ' || p_order; 2886 2887ELSIF v_type IN ('id-static', 'id-dynamic') THEN 2888 2889 RETURN QUERY EXECUTE ' 2890 SELECT n.nspname::text ||''.''|| c.relname::text AS partition_name FROM 2891 pg_catalog.pg_inherits h 2892 JOIN pg_catalog.pg_class c ON c.oid = h.inhrelid 2893 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 2894 WHERE h.inhparent = '||quote_literal(p_parent_table)||'::regclass 2895 ORDER BY substring(c.relname from ((length(c.relname) - position(''p_'' in reverse(c.relname))) + 2) )::bigint ' || p_order; 2896 2897END IF; 2898 2899END 2900$$; 2901 2902 2903/* 2904 * Apply constraints managed by partman extension 2905 */ 2906CREATE OR REPLACE FUNCTION apply_constraints(p_parent_table text, p_child_table text DEFAULT NULL, p_analyze boolean DEFAULT TRUE, p_debug boolean DEFAULT FALSE) RETURNS void 2907 LANGUAGE plpgsql 2908 AS $$ 2909DECLARE 2910 2911v_child_table text; 2912v_child_tablename text; 2913v_col text; 2914v_constraint_cols text[]; 2915v_constraint_col_type text; 2916v_constraint_name text; 2917v_datetime_string text; 2918v_existing_constraint_name text; 2919v_job_id bigint; 2920v_jobmon boolean; 2921v_jobmon_schema text; 2922v_last_partition text; 2923v_last_partition_id int; 2924v_last_partition_timestamp timestamp; 2925v_constraint_values record; 2926v_old_search_path text; 2927v_parent_schema text; 2928v_parent_tablename text; 2929v_part_interval text; 2930v_partition_suffix text; 2931v_premake int; 2932v_sql text; 2933v_step_id bigint; 2934v_suffix_position int; 2935v_type text; 2936 2937BEGIN 2938 2939SELECT type 2940 , part_interval 2941 , premake 2942 , datetime_string 2943 , constraint_cols 2944 , jobmon 2945INTO v_type 2946 , v_part_interval 2947 , v_premake 2948 , v_datetime_string 2949 , v_constraint_cols 2950 , v_jobmon 2951FROM @extschema@.part_config 2952WHERE parent_table = p_parent_table; 2953 2954IF v_constraint_cols IS NULL THEN 2955 IF p_debug THEN 2956 RAISE NOTICE 'Given parent table (%) not set up for constraint management (constraint_cols is NULL)', p_parent_table; 2957 END IF; 2958 -- Returns silently to allow this function to be simply called by maintenance processes without having to check if config options are set. 2959 RETURN; 2960END IF; 2961 2962SELECT show_partitions INTO v_last_partition FROM @extschema@.show_partitions(p_parent_table, 'DESC') LIMIT 1; 2963 2964IF v_jobmon THEN 2965 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; 2966 IF v_jobmon_schema IS NOT NULL THEN 2967 SELECT current_setting('search_path') INTO v_old_search_path; 2968 EXECUTE 'SELECT set_config(''search_path'',''@extschema@,'||v_jobmon_schema||''',''false'')'; 2969 END IF; 2970END IF; 2971 2972IF v_jobmon_schema IS NOT NULL THEN 2973 v_job_id := add_job('PARTMAN CREATE CONSTRAINT: '||p_parent_table); 2974END IF; 2975 2976SELECT schemaname, tablename INTO v_parent_schema, v_parent_tablename FROM pg_tables WHERE schemaname ||'.'|| tablename = p_parent_table; 2977 2978-- If p_child_table is null, figure out the partition that is the one right before the premake value backwards. 2979IF p_child_table IS NULL THEN 2980 2981 IF v_jobmon_schema IS NOT NULL THEN 2982 v_step_id := add_step(v_job_id, 'Automatically determining most recent child on which to apply constraints'); 2983 END IF; 2984 2985 v_suffix_position := (length(v_last_partition) - position('p_' in reverse(v_last_partition))) + 2; 2986 2987 IF v_type IN ('time-static', 'time-dynamic') THEN 2988 v_last_partition_timestamp := to_timestamp(substring(v_last_partition from v_suffix_position), v_datetime_string); 2989 v_partition_suffix := to_char(v_last_partition_timestamp - (v_part_interval::interval * ((v_premake * 2)+1) ), v_datetime_string); 2990 ELSIF v_type IN ('id-static', 'id-dynamic') THEN 2991 v_last_partition_id := substring(v_last_partition from v_suffix_position)::int; 2992 v_partition_suffix := (v_last_partition_id - (v_part_interval::int * ((v_premake * 2)+1) ))::text; 2993 END IF; 2994 2995 v_child_table := @extschema@.check_name_length(v_parent_tablename, v_parent_schema, v_partition_suffix, TRUE); 2996 2997 IF v_jobmon_schema IS NOT NULL THEN 2998 PERFORM update_step(v_step_id, 'OK', 'Target child table: '||v_child_table); 2999 END IF; 3000ELSE 3001 v_child_table := p_child_table; 3002END IF; 3003 3004IF v_jobmon_schema IS NOT NULL THEN 3005 v_step_id := add_step(v_job_id, 'Checking if target child table exists'); 3006END IF; 3007 3008SELECT tablename INTO v_child_tablename FROM pg_catalog.pg_tables WHERE schemaname ||'.'|| tablename = v_child_table; 3009IF v_child_tablename IS NULL THEN 3010 IF v_jobmon_schema IS NOT NULL THEN 3011 PERFORM update_step(v_step_id, 'NOTICE', 'Target child table ('||v_child_table||') does not exist. Skipping constraint creation.'); 3012 PERFORM close_job(v_job_id); 3013 EXECUTE 'SELECT set_config(''search_path'','''||v_old_search_path||''',''false'')'; 3014 END IF; 3015 IF p_debug THEN 3016 RAISE NOTICE 'Target child table (%) does not exist. Skipping constraint creation.', v_child_table; 3017 END IF; 3018 RETURN; 3019ELSE 3020 IF v_jobmon_schema IS NOT NULL THEN 3021 PERFORM update_step(v_step_id, 'OK', 'Done'); 3022 END IF; 3023END IF; 3024 3025FOREACH v_col IN ARRAY v_constraint_cols 3026LOOP 3027 SELECT c.conname 3028 INTO v_existing_constraint_name 3029 FROM pg_catalog.pg_constraint c 3030 JOIN pg_catalog.pg_attribute a ON c.conrelid = a.attrelid 3031 WHERE conrelid = v_child_table::regclass 3032 AND c.conname LIKE 'partmanconstr_%' 3033 AND c.contype = 'c' 3034 AND a.attname = v_col 3035 AND ARRAY[a.attnum] <@ c.conkey 3036 AND a.attisdropped = false; 3037 3038 IF v_jobmon_schema IS NOT NULL THEN 3039 v_step_id := add_step(v_job_id, 'Applying new constraint on column: '||v_col); 3040 END IF; 3041 3042 IF v_existing_constraint_name IS NOT NULL THEN 3043 IF v_jobmon_schema IS NOT NULL THEN 3044 PERFORM update_step(v_step_id, 'NOTICE', 'Partman managed constraint already exists on this table ('||v_child_table||') and column ('||v_col||'). Skipping creation.'); 3045 END IF; 3046 RAISE WARNING 'Partman managed constraint already exists on this table (%) and column (%). Skipping creation.', v_child_table, v_col ; 3047 CONTINUE; 3048 END IF; 3049 3050 -- Ensure column name gets put on end of constraint name to help avoid naming conflicts 3051 v_constraint_name := @extschema@.check_name_length('partmanconstr_'||v_child_tablename, p_suffix := '_'||v_col); 3052 3053 EXECUTE 'SELECT min('||v_col||')::text AS min, max('||v_col||')::text AS max FROM '||v_child_table INTO v_constraint_values; 3054 3055 IF v_constraint_values IS NOT NULL THEN 3056 v_sql := concat('ALTER TABLE ', v_child_table, ' ADD CONSTRAINT ', v_constraint_name 3057 , ' CHECK (', v_col, ' >= ', quote_literal(v_constraint_values.min), ' AND ' 3058 , v_col, ' <= ', quote_literal(v_constraint_values.max), ')' ); 3059 IF p_debug THEN 3060 RAISE NOTICE 'Constraint creation query: %', v_sql; 3061 END IF; 3062 EXECUTE v_sql; 3063 3064 IF v_jobmon_schema IS NOT NULL THEN 3065 PERFORM update_step(v_step_id, 'OK', 'New constraint created: '||v_sql); 3066 END IF; 3067 ELSE 3068 IF p_debug THEN 3069 RAISE NOTICE 'Given column (%) contains all NULLs. No constraint created', v_col; 3070 END IF; 3071 IF v_jobmon_schema IS NOT NULL THEN 3072 PERFORM update_step(v_step_id, 'NOTICE', 'Given column ('||v_col||') contains all NULLs. No constraint created'); 3073 END IF; 3074 END IF; 3075 3076END LOOP; 3077 3078IF p_analyze THEN 3079 EXECUTE 'ANALYZE '||p_parent_table; 3080END IF; 3081 3082IF v_jobmon_schema IS NOT NULL THEN 3083 PERFORM close_job(v_job_id); 3084 EXECUTE 'SELECT set_config(''search_path'','''||v_old_search_path||''',''false'')'; 3085END IF; 3086 3087EXCEPTION 3088 WHEN OTHERS THEN 3089 IF v_jobmon_schema IS NOT NULL THEN 3090 IF v_job_id IS NULL THEN 3091 EXECUTE 'SELECT '||v_jobmon_schema||'.add_job(''PARTMAN CREATE CONSTRAINT: '||p_parent_table||''')' INTO v_job_id; 3092 EXECUTE 'SELECT '||v_jobmon_schema||'.add_step('||v_job_id||', ''EXCEPTION before job logging started'')' INTO v_step_id; 3093 ELSIF v_step_id IS NULL THEN 3094 EXECUTE 'SELECT '||v_jobmon_schema||'.add_step('||v_job_id||', ''EXCEPTION before first step logged'')' INTO v_step_id; 3095 END IF; 3096 EXECUTE 'SELECT '||v_jobmon_schema||'.update_step('||v_step_id||', ''CRITICAL'', ''ERROR: '||coalesce(SQLERRM,'unknown')||''')'; 3097 EXECUTE 'SELECT '||v_jobmon_schema||'.fail_job('||v_job_id||')'; 3098 END IF; 3099 RAISE EXCEPTION '%', SQLERRM; 3100END 3101$$; 3102 3103 3104/* 3105 * Function to undo time-based partitioning created by this extension 3106 */ 3107CREATE OR REPLACE FUNCTION undo_partition_time(p_parent_table text, p_batch_count int DEFAULT 1, p_batch_interval interval DEFAULT NULL, p_keep_table boolean DEFAULT true, p_lock_wait numeric DEFAULT 0) RETURNS bigint 3108 LANGUAGE plpgsql SECURITY DEFINER 3109 AS $$ 3110DECLARE 3111 3112v_adv_lock boolean; 3113v_batch_loop_count int := 0; 3114v_child_min timestamptz; 3115v_child_loop_total bigint := 0; 3116v_child_table text; 3117v_control text; 3118v_function_name text; 3119v_inner_loop_count int; 3120v_lock_iter int := 1; 3121v_lock_obtained boolean := FALSE; 3122v_job_id bigint; 3123v_jobmon boolean; 3124v_jobmon_schema text; 3125v_move_sql text; 3126v_old_search_path text; 3127v_parent_schema text; 3128v_parent_tablename text; 3129v_part_interval interval; 3130v_row record; 3131v_rowcount bigint; 3132v_step_id bigint; 3133v_sub_count int; 3134v_total bigint := 0; 3135v_trig_name text; 3136v_undo_count int := 0; 3137 3138BEGIN 3139 3140v_adv_lock := pg_try_advisory_xact_lock(hashtext('pg_partman undo_time_partition')); 3141IF v_adv_lock = 'false' THEN 3142 RAISE NOTICE 'undo_time_partition already running.'; 3143 RETURN 0; 3144END IF; 3145 3146SELECT part_interval::interval 3147 , control 3148 , jobmon 3149INTO v_part_interval 3150 , v_control 3151 , v_jobmon 3152FROM @extschema@.part_config 3153WHERE parent_table = p_parent_table 3154AND (type = 'time-static' OR type = 'time-dynamic' OR type = 'time-custom'); 3155 3156IF v_part_interval IS NULL THEN 3157 RAISE EXCEPTION 'Configuration for given parent table not found: %', p_parent_table; 3158END IF; 3159 3160-- Check if any child tables are themselves partitioned or part of an inheritance tree. Prevent undo at this level if so. 3161-- Need to either lock child tables at all levels or handle the proper removal of triggers on all child tables first 3162-- before multi-level undo can be performed safely. 3163FOR v_row IN 3164 SELECT show_partitions AS child_table FROM @extschema@.show_partitions(p_parent_table) 3165LOOP 3166 SELECT count(*) INTO v_sub_count 3167 FROM pg_catalog.pg_inherits 3168 WHERE inhparent::regclass = v_row.child_table::regclass; 3169 IF v_sub_count > 0 THEN 3170 RAISE EXCEPTION 'Child table for this parent has child table(s) itself (%). Run undo partitioning on this table or remove inheritance first to ensure all data is properly moved to parent', v_row.child_table; 3171 END IF; 3172END LOOP; 3173 3174IF v_jobmon THEN 3175 SELECT nspname INTO v_jobmon_schema FROM pg_namespace n, pg_extension e WHERE e.extname = 'pg_jobmon' AND e.extnamespace = n.oid; 3176 IF v_jobmon_schema IS NOT NULL THEN 3177 SELECT current_setting('search_path') INTO v_old_search_path; 3178 EXECUTE 'SELECT set_config(''search_path'',''@extschema@,'||v_jobmon_schema||''',''false'')'; 3179 END IF; 3180END IF; 3181 3182IF v_jobmon_schema IS NOT NULL THEN 3183 v_job_id := add_job('PARTMAN UNDO PARTITIONING: '||p_parent_table); 3184 v_step_id := add_step(v_job_id, 'Undoing partitioning for table '||p_parent_table); 3185END IF; 3186 3187IF p_batch_interval IS NULL THEN 3188 p_batch_interval := v_part_interval; 3189END IF; 3190 3191-- Stops new time partitions from being made as well as stopping child tables from being dropped if they were configured with a retention period. 3192UPDATE @extschema@.part_config SET undo_in_progress = true WHERE parent_table = p_parent_table; 3193-- Stop data going into child tables. 3194SELECT schemaname, tablename INTO v_parent_schema, v_parent_tablename FROM pg_tables WHERE schemaname ||'.'|| tablename = p_parent_table; 3195v_trig_name := @extschema@.check_name_length(p_object_name := v_parent_tablename, p_suffix := '_part_trig'); 3196v_function_name := @extschema@.check_name_length(v_parent_tablename, v_parent_schema, '_part_trig_func', FALSE); 3197 3198SELECT tgname INTO v_trig_name FROM pg_catalog.pg_trigger t WHERE tgname = v_trig_name; 3199IF v_trig_name IS NOT NULL THEN 3200 -- lockwait for trigger drop 3201 IF p_lock_wait > 0 THEN 3202 v_lock_iter := 0; 3203 WHILE v_lock_iter <= 5 LOOP 3204 v_lock_iter := v_lock_iter + 1; 3205 BEGIN 3206 EXECUTE 'LOCK TABLE ONLY '||p_parent_table||' IN ACCESS EXCLUSIVE MODE NOWAIT'; 3207 v_lock_obtained := TRUE; 3208 EXCEPTION 3209 WHEN lock_not_available THEN 3210 PERFORM pg_sleep( p_lock_wait / 5.0 ); 3211 CONTINUE; 3212 END; 3213 EXIT WHEN v_lock_obtained; 3214 END LOOP; 3215 IF NOT v_lock_obtained THEN 3216 RAISE NOTICE 'Unable to obtain lock on parent table to remove trigger'; 3217 RETURN -1; 3218 END IF; 3219 END IF; -- END p_lock_wait IF 3220 EXECUTE 'DROP TRIGGER IF EXISTS '||v_trig_name||' ON '||p_parent_table; 3221END IF; -- END trigger IF 3222v_lock_obtained := FALSE; -- reset for reuse later 3223 3224EXECUTE 'DROP FUNCTION IF EXISTS '||v_function_name||'()'; 3225 3226IF v_jobmon_schema IS NOT NULL THEN 3227 PERFORM update_step(v_step_id, 'OK', 'Stopped partition creation process. Removed trigger & trigger function'); 3228END IF; 3229 3230<<outer_child_loop>> 3231WHILE v_batch_loop_count < p_batch_count LOOP 3232 SELECT n.nspname||'.'||c.relname INTO v_child_table 3233 FROM pg_inherits i 3234 JOIN pg_class c ON i.inhrelid = c.oid 3235 JOIN pg_namespace n ON c.relnamespace = n.oid 3236 WHERE i.inhparent::regclass = p_parent_table::regclass 3237 ORDER BY i.inhrelid ASC; 3238 3239 EXIT WHEN v_child_table IS NULL; 3240 3241 IF v_jobmon_schema IS NOT NULL THEN 3242 v_step_id := add_step(v_job_id, 'Removing child partition: '||v_child_table); 3243 END IF; 3244 3245 EXECUTE 'SELECT min('||v_control||') FROM '||v_child_table INTO v_child_min; 3246 IF v_child_min IS NULL THEN 3247 -- No rows left in this child table. Remove from partition set. 3248 3249 -- lockwait timeout for table drop 3250 IF p_lock_wait > 0 THEN 3251 v_lock_iter := 0; 3252 WHILE v_lock_iter <= 5 LOOP 3253 v_lock_iter := v_lock_iter + 1; 3254 BEGIN 3255 EXECUTE 'LOCK TABLE ONLY '||v_child_table||' IN ACCESS EXCLUSIVE MODE NOWAIT'; 3256 v_lock_obtained := TRUE; 3257 EXCEPTION 3258 WHEN lock_not_available THEN 3259 PERFORM pg_sleep( p_lock_wait / 5.0 ); 3260 CONTINUE; 3261 END; 3262 EXIT WHEN v_lock_obtained; 3263 END LOOP; 3264 IF NOT v_lock_obtained THEN 3265 RAISE NOTICE 'Unable to obtain lock on child table for removal from partition set'; 3266 RETURN -1; 3267 END IF; 3268 END IF; -- END p_lock_wait IF 3269 v_lock_obtained := FALSE; -- reset for reuse later 3270 3271 EXECUTE 'ALTER TABLE '||v_child_table||' NO INHERIT ' || p_parent_table; 3272 IF p_keep_table = false THEN 3273 EXECUTE 'DROP TABLE '||v_child_table; 3274 IF v_jobmon_schema IS NOT NULL THEN 3275 PERFORM update_step(v_step_id, 'OK', 'Child table DROPPED. Moved '||v_child_loop_total||' rows to parent'); 3276 END IF; 3277 ELSE 3278 IF v_jobmon_schema IS NOT NULL THEN 3279 PERFORM update_step(v_step_id, 'OK', 'Child table UNINHERITED, not DROPPED. Moved '||v_child_loop_total||' rows to parent'); 3280 END IF; 3281 END IF; 3282 v_undo_count := v_undo_count + 1; 3283 CONTINUE outer_child_loop; 3284 END IF; 3285 v_inner_loop_count := 1; 3286 v_child_loop_total := 0; 3287 <<inner_child_loop>> 3288 LOOP 3289 -- do some locking with timeout, if required 3290 IF p_lock_wait > 0 THEN 3291 v_lock_iter := 0; 3292 WHILE v_lock_iter <= 5 LOOP 3293 v_lock_iter := v_lock_iter + 1; 3294 BEGIN 3295 EXECUTE 'SELECT * FROM ' || v_child_table || 3296 ' WHERE '||v_control||' <= '||quote_literal(v_child_min + (p_batch_interval * v_inner_loop_count)) 3297 ||' FOR UPDATE NOWAIT'; 3298 v_lock_obtained := TRUE; 3299 EXCEPTION 3300 WHEN lock_not_available THEN 3301 PERFORM pg_sleep( p_lock_wait / 5.0 ); 3302 CONTINUE; 3303 END; 3304 EXIT WHEN v_lock_obtained; 3305 END LOOP; 3306 IF NOT v_lock_obtained THEN 3307 RAISE NOTICE 'Unable to obtain lock on batch of rows to move'; 3308 RETURN -1; 3309 END IF; 3310 END IF; 3311 3312 -- Get everything from the current child minimum up to the multiples of the given interval 3313 v_move_sql := 'WITH move_data AS (DELETE FROM '||v_child_table|| 3314 ' WHERE '||v_control||' <= '||quote_literal(v_child_min + (p_batch_interval * v_inner_loop_count))||' RETURNING *) 3315 INSERT INTO '||p_parent_table||' SELECT * FROM move_data'; 3316 EXECUTE v_move_sql; 3317 GET DIAGNOSTICS v_rowcount = ROW_COUNT; 3318 v_total := v_total + v_rowcount; 3319 v_child_loop_total := v_child_loop_total + v_rowcount; 3320 IF v_jobmon_schema IS NOT NULL THEN 3321 PERFORM update_step(v_step_id, 'OK', 'Moved '||v_child_loop_total||' rows to parent.'); 3322 END IF; 3323 EXIT inner_child_loop WHEN v_rowcount = 0; -- exit before loop incr if table is empty 3324 v_inner_loop_count := v_inner_loop_count + 1; 3325 v_batch_loop_count := v_batch_loop_count + 1; 3326 EXIT outer_child_loop WHEN v_batch_loop_count >= p_batch_count; -- Exit outer FOR loop if p_batch_count is reached 3327 END LOOP inner_child_loop; 3328END LOOP outer_child_loop; 3329 3330IF v_batch_loop_count < p_batch_count THEN 3331 -- FOR loop never ran, so there's no child tables left. 3332 DELETE FROM @extschema@.part_config WHERE parent_table = p_parent_table; 3333 IF v_jobmon_schema IS NOT NULL THEN 3334 v_step_id := add_step(v_job_id, 'Removing config from pg_partman'); 3335 PERFORM update_step(v_step_id, 'OK', 'Done'); 3336 END IF; 3337END IF; 3338 3339RAISE NOTICE 'Copied % row(s) to the parent. Removed % partitions.', v_total, v_undo_count; 3340IF v_jobmon_schema IS NOT NULL THEN 3341 v_step_id := add_step(v_job_id, 'Final stats'); 3342 PERFORM update_step(v_step_id, 'OK', 'Copied '||v_total||' row(s) to the parent. Removed '||v_undo_count||' partitions.'); 3343END IF; 3344 3345IF v_jobmon_schema IS NOT NULL THEN 3346 PERFORM close_job(v_job_id); 3347 EXECUTE 'SELECT set_config(''search_path'','''||v_old_search_path||''',''false'')'; 3348END IF; 3349 3350RETURN v_total; 3351 3352EXCEPTION 3353 WHEN OTHERS THEN 3354 IF v_jobmon_schema IS NOT NULL THEN 3355 IF v_job_id IS NULL THEN 3356 EXECUTE 'SELECT '||v_jobmon_schema||'.add_job(''PARTMAN UNDO PARTITIONING: '||p_parent_table||''')' INTO v_job_id; 3357 EXECUTE 'SELECT '||v_jobmon_schema||'.add_step('||v_job_id||', ''EXCEPTION before job logging started'')' INTO v_step_id; 3358 ELSIF v_step_id IS NULL THEN 3359 EXECUTE 'SELECT '||v_jobmon_schema||'.add_step('||v_job_id||', ''EXCEPTION before first step logged'')' INTO v_step_id; 3360 END IF; 3361 EXECUTE 'SELECT '||v_jobmon_schema||'.update_step('||v_step_id||', ''CRITICAL'', ''ERROR: '||coalesce(SQLERRM,'unknown')||''')'; 3362 EXECUTE 'SELECT '||v_jobmon_schema||'.fail_job('||v_job_id||')'; 3363 END IF; 3364 RAISE EXCEPTION '%', SQLERRM; 3365END 3366$$; 3367 3368 3369/* 3370 * Function to undo id-based partitioning created by this extension 3371 */ 3372CREATE OR REPLACE FUNCTION undo_partition_id(p_parent_table text, p_batch_count int DEFAULT 1, p_batch_interval bigint DEFAULT NULL, p_keep_table boolean DEFAULT true, p_lock_wait numeric DEFAULT 0) RETURNS bigint 3373 LANGUAGE plpgsql SECURITY DEFINER 3374 AS $$ 3375DECLARE 3376 3377v_adv_lock boolean; 3378v_batch_loop_count int := 0; 3379v_child_loop_total bigint := 0; 3380v_child_min bigint; 3381v_child_table text; 3382v_control text; 3383v_exists int; 3384v_function_name text; 3385v_inner_loop_count int; 3386v_job_id bigint; 3387v_jobmon boolean; 3388v_jobmon_schema text; 3389v_lock_iter int := 1; 3390v_lock_obtained boolean := FALSE; 3391v_move_sql text; 3392v_old_search_path text; 3393v_parent_schema text; 3394v_parent_tablename text; 3395v_part_interval bigint; 3396v_row record; 3397v_rowcount bigint; 3398v_step_id bigint; 3399v_sub_count int; 3400v_trig_name text; 3401v_total bigint := 0; 3402v_undo_count int := 0; 3403 3404BEGIN 3405 3406v_adv_lock := pg_try_advisory_xact_lock(hashtext('pg_partman undo_id_partition')); 3407IF v_adv_lock = 'false' THEN 3408 RAISE NOTICE 'undo_id_partition already running.'; 3409 RETURN 0; 3410END IF; 3411 3412SELECT part_interval::bigint 3413 , control 3414 , jobmon 3415INTO v_part_interval 3416 , v_control 3417 , v_jobmon 3418FROM @extschema@.part_config 3419WHERE parent_table = p_parent_table 3420AND (type = 'id-static' OR type = 'id-dynamic'); 3421 3422IF v_part_interval IS NULL THEN 3423 RAISE EXCEPTION 'Configuration for given parent table not found: %', p_parent_table; 3424END IF; 3425 3426-- Check if any child tables are themselves partitioned or part of an inheritance tree. Prevent undo at this level if so. 3427-- Need to either lock child tables at all levels or handle the proper removal of triggers on all child tables first 3428-- before multi-level undo can be performed safely. 3429FOR v_row IN 3430 SELECT show_partitions AS child_table FROM @extschema@.show_partitions(p_parent_table) 3431LOOP 3432 SELECT count(*) INTO v_sub_count 3433 FROM pg_catalog.pg_inherits 3434 WHERE inhparent::regclass = v_row.child_table::regclass; 3435 IF v_sub_count > 0 THEN 3436 RAISE EXCEPTION 'Child table for this parent has child table(s) itself (%). Run undo partitioning on this table or remove inheritance first to ensure all data is properly moved to parent', v_row.child_table; 3437 END IF; 3438END LOOP; 3439 3440IF v_jobmon THEN 3441 SELECT nspname INTO v_jobmon_schema FROM pg_namespace n, pg_extension e WHERE e.extname = 'pg_jobmon' AND e.extnamespace = n.oid; 3442 IF v_jobmon_schema IS NOT NULL THEN 3443 SELECT current_setting('search_path') INTO v_old_search_path; 3444 EXECUTE 'SELECT set_config(''search_path'',''@extschema@,'||v_jobmon_schema||''',''false'')'; 3445 END IF; 3446END IF; 3447 3448IF v_jobmon_schema IS NOT NULL THEN 3449 v_job_id := add_job('PARTMAN UNDO PARTITIONING: '||p_parent_table); 3450 v_step_id := add_step(v_job_id, 'Undoing partitioning for table '||p_parent_table); 3451END IF; 3452 3453IF p_batch_interval IS NULL THEN 3454 p_batch_interval := v_part_interval; 3455END IF; 3456 3457-- Stops new time partitions from being made as well as stopping child tables from being dropped if they were configured with a retention period. 3458UPDATE @extschema@.part_config SET undo_in_progress = true WHERE parent_table = p_parent_table; 3459-- Stop data going into child tables and stop new id partitions from being made. 3460SELECT schemaname, tablename INTO v_parent_schema, v_parent_tablename FROM pg_tables WHERE schemaname ||'.'|| tablename = p_parent_table; 3461v_trig_name := @extschema@.check_name_length(p_object_name := v_parent_tablename, p_suffix := '_part_trig'); 3462v_function_name := @extschema@.check_name_length(v_parent_tablename, v_parent_schema, '_part_trig_func', FALSE); 3463 3464SELECT tgname INTO v_trig_name FROM pg_catalog.pg_trigger t WHERE tgname = v_trig_name; 3465IF v_trig_name IS NOT NULL THEN 3466 -- lockwait for trigger drop 3467 IF p_lock_wait > 0 THEN 3468 v_lock_iter := 0; 3469 WHILE v_lock_iter <= 5 LOOP 3470 v_lock_iter := v_lock_iter + 1; 3471 BEGIN 3472 EXECUTE 'LOCK TABLE ONLY '||p_parent_table||' IN ACCESS EXCLUSIVE MODE NOWAIT'; 3473 v_lock_obtained := TRUE; 3474 EXCEPTION 3475 WHEN lock_not_available THEN 3476 PERFORM pg_sleep( p_lock_wait / 5.0 ); 3477 CONTINUE; 3478 END; 3479 EXIT WHEN v_lock_obtained; 3480 END LOOP; 3481 IF NOT v_lock_obtained THEN 3482 RAISE NOTICE 'Unable to obtain lock on parent table to remove trigger'; 3483 RETURN -1; 3484 END IF; 3485 END IF; -- END p_lock_wait IF 3486 EXECUTE 'DROP TRIGGER IF EXISTS '||v_trig_name||' ON '||p_parent_table; 3487END IF; -- END trigger IF 3488v_lock_obtained := FALSE; -- reset for reuse later 3489 3490EXECUTE 'DROP FUNCTION IF EXISTS '||v_function_name||'()'; 3491 3492IF v_jobmon_schema IS NOT NULL THEN 3493 PERFORM update_step(v_step_id, 'OK', 'Stopped partition creation process. Removed trigger & trigger function'); 3494END IF; 3495 3496<<outer_child_loop>> 3497WHILE v_batch_loop_count < p_batch_count LOOP 3498 SELECT n.nspname||'.'||c.relname INTO v_child_table 3499 FROM pg_inherits i 3500 JOIN pg_class c ON i.inhrelid = c.oid 3501 JOIN pg_namespace n ON c.relnamespace = n.oid 3502 WHERE i.inhparent::regclass = p_parent_table::regclass 3503 ORDER BY i.inhrelid ASC; 3504 3505 EXIT WHEN v_child_table IS NULL; 3506 3507 IF v_jobmon_schema IS NOT NULL THEN 3508 v_step_id := add_step(v_job_id, 'Removing child partition: '||v_child_table); 3509 END IF; 3510 3511 EXECUTE 'SELECT min('||v_control||') FROM '||v_child_table INTO v_child_min; 3512 IF v_child_min IS NULL THEN 3513 -- No rows left in this child table. Remove from partition set. 3514 3515 -- lockwait timeout for table drop 3516 IF p_lock_wait > 0 THEN 3517 v_lock_iter := 0; 3518 WHILE v_lock_iter <= 5 LOOP 3519 v_lock_iter := v_lock_iter + 1; 3520 BEGIN 3521 EXECUTE 'LOCK TABLE ONLY '||v_child_table||' IN ACCESS EXCLUSIVE MODE NOWAIT'; 3522 v_lock_obtained := TRUE; 3523 EXCEPTION 3524 WHEN lock_not_available THEN 3525 PERFORM pg_sleep( p_lock_wait / 5.0 ); 3526 CONTINUE; 3527 END; 3528 EXIT WHEN v_lock_obtained; 3529 END LOOP; 3530 IF NOT v_lock_obtained THEN 3531 RAISE NOTICE 'Unable to obtain lock on child table for removal from partition set'; 3532 RETURN -1; 3533 END IF; 3534 END IF; -- END p_lock_wait IF 3535 v_lock_obtained := FALSE; -- reset for reuse later 3536 3537 EXECUTE 'ALTER TABLE '||v_child_table||' NO INHERIT ' || p_parent_table; 3538 IF p_keep_table = false THEN 3539 EXECUTE 'DROP TABLE '||v_child_table; 3540 IF v_jobmon_schema IS NOT NULL THEN 3541 PERFORM update_step(v_step_id, 'OK', 'Child table DROPPED. Moved '||v_child_loop_total||' rows to parent'); 3542 END IF; 3543 ELSE 3544 IF v_jobmon_schema IS NOT NULL THEN 3545 PERFORM update_step(v_step_id, 'OK', 'Child table UNINHERITED, not DROPPED. Moved '||v_child_loop_total||' rows to parent'); 3546 END IF; 3547 END IF; 3548 v_undo_count := v_undo_count + 1; 3549 CONTINUE outer_child_loop; 3550 END IF; 3551 v_inner_loop_count := 1; 3552 v_child_loop_total := 0; 3553 <<inner_child_loop>> 3554 LOOP 3555 -- lockwait timeout for row batches 3556 IF p_lock_wait > 0 THEN 3557 v_lock_iter := 0; 3558 WHILE v_lock_iter <= 5 LOOP 3559 v_lock_iter := v_lock_iter + 1; 3560 BEGIN 3561 EXECUTE 'SELECT * FROM ' || v_child_table || 3562 ' WHERE '||v_control||' <= '||quote_literal(v_child_min + (p_batch_interval * v_inner_loop_count)) 3563 ||' FOR UPDATE NOWAIT'; 3564 v_lock_obtained := TRUE; 3565 EXCEPTION 3566 WHEN lock_not_available THEN 3567 PERFORM pg_sleep( p_lock_wait / 5.0 ); 3568 CONTINUE; 3569 END; 3570 EXIT WHEN v_lock_obtained; 3571 END LOOP; 3572 IF NOT v_lock_obtained THEN 3573 RAISE NOTICE 'Unable to obtain lock on batch of rows to move'; 3574 RETURN -1; 3575 END IF; 3576 END IF; 3577 3578 -- Get everything from the current child minimum up to the multiples of the given interval 3579 v_move_sql := 'WITH move_data AS (DELETE FROM '||v_child_table|| 3580 ' WHERE '||v_control||' <= '||quote_literal(v_child_min + (p_batch_interval * v_inner_loop_count))||' RETURNING *) 3581 INSERT INTO '||p_parent_table||' SELECT * FROM move_data'; 3582 EXECUTE v_move_sql; 3583 GET DIAGNOSTICS v_rowcount = ROW_COUNT; 3584 v_total := v_total + v_rowcount; 3585 v_child_loop_total := v_child_loop_total + v_rowcount; 3586 IF v_jobmon_schema IS NOT NULL THEN 3587 PERFORM update_step(v_step_id, 'OK', 'Moved '||v_child_loop_total||' rows to parent.'); 3588 END IF; 3589 EXIT inner_child_loop WHEN v_rowcount = 0; -- exit before loop incr if table is empty 3590 v_inner_loop_count := v_inner_loop_count + 1; 3591 v_batch_loop_count := v_batch_loop_count + 1; 3592 EXIT outer_child_loop WHEN v_batch_loop_count >= p_batch_count; -- Exit outer FOR loop if p_batch_count is reached 3593 END LOOP inner_child_loop; 3594END LOOP outer_child_loop; 3595 3596IF v_batch_loop_count < p_batch_count THEN 3597 -- FOR loop never ran, so there's no child tables left. 3598 DELETE FROM @extschema@.part_config WHERE parent_table = p_parent_table; 3599 IF v_jobmon_schema IS NOT NULL THEN 3600 v_step_id := add_step(v_job_id, 'Removing config from pg_partman'); 3601 PERFORM update_step(v_step_id, 'OK', 'Done'); 3602 END IF; 3603END IF; 3604 3605RAISE NOTICE 'Copied % row(s) to the parent. Removed % partitions.', v_total, v_undo_count; 3606IF v_jobmon_schema IS NOT NULL THEN 3607 v_step_id := add_step(v_job_id, 'Final stats'); 3608 PERFORM update_step(v_step_id, 'OK', 'Copied '||v_total||' row(s) to the parent. Removed '||v_undo_count||' partitions.'); 3609END IF; 3610 3611IF v_jobmon_schema IS NOT NULL THEN 3612 PERFORM close_job(v_job_id); 3613 EXECUTE 'SELECT set_config(''search_path'','''||v_old_search_path||''',''false'')'; 3614END IF; 3615 3616RETURN v_total; 3617 3618EXCEPTION 3619 WHEN OTHERS THEN 3620 IF v_jobmon_schema IS NOT NULL THEN 3621 IF v_job_id IS NULL THEN 3622 EXECUTE 'SELECT '||v_jobmon_schema||'.add_job(''PARTMAN UNDO PARTITIONING: '||p_parent_table||''')' INTO v_job_id; 3623 EXECUTE 'SELECT '||v_jobmon_schema||'.add_step('||v_job_id||', ''EXCEPTION before job logging started'')' INTO v_step_id; 3624 ELSIF v_step_id IS NULL THEN 3625 EXECUTE 'SELECT '||v_jobmon_schema||'.add_step('||v_job_id||', ''EXCEPTION before first step logged'')' INTO v_step_id; 3626 END IF; 3627 EXECUTE 'SELECT '||v_jobmon_schema||'.update_step('||v_step_id||', ''CRITICAL'', ''ERROR: '||coalesce(SQLERRM,'unknown')||''')'; 3628 EXECUTE 'SELECT '||v_jobmon_schema||'.fail_job('||v_job_id||')'; 3629 END IF; 3630 RAISE EXCEPTION '%', SQLERRM; 3631END 3632$$; 3633 3634 3635-- Restore dropped object privileges 3636DO $$ 3637DECLARE 3638v_row record; 3639BEGIN 3640 FOR v_row IN SELECT statement FROM partman_preserve_privs_temp LOOP 3641 IF v_row.statement IS NOT NULL THEN 3642 EXECUTE v_row.statement; 3643 END IF; 3644 END LOOP; 3645END 3646$$; 3647 3648DROP TABLE IF EXISTS partman_preserve_privs_temp; 3649