1-- Preliminary compatability with PostgreSQL 12. 2 3-- IMPORTANT SUPPORT CHANGE: As of PostgreSQL 12's stable release, version 9.4 of PostgreSQL will no longer officially be supported by pg_partman. It will likely continue to function without any issue, but any bugs tied specifically to that version and lower will no longer be fixed and new features that are not compatible with 9.4 may be introduced. 4 5-- IMPORTANT CHANGE: The UNLOGGED status of a partition set in native partitioning has changed to be managed via the template table, no longer the parent table. 6 -- Currently setting a natively partitioned parent table as UNLOGGED/LOGGED is not a property that can be changed with an ALTER TABLE. So if the state is later changed to LOGGED (or vice versa), this property will not change for future child tables. Current child tables were never handled via this manner before and always had to be fixed manually. 7 -- If any of your native partition sets are UNLOGGED, be sure to set this property on the template table before upgrading to this version. Otherwise new child tables may be created as LOGGED. 8 -- pg_partman will maintain this behavior until core PostgreSQL determines a definative way that UNLOGGED changes will be handled. 9 -- See reported bug at https://www.postgresql.org/message-id/flat/15954-b61523bed4b110c4%40postgresql.org 10 11-- As of PostgreSQL 12, pg_partman will no longer support inheriting OIDs since the special column behavior has been removed. See the release notes for PG12 for more information on this change. The feature has not been deprecated from pg_partman for older versions of PostgreSQL, but all tests involving the feature have been removed and any bugs involving it will likely not be fixed unless it is a compatability issue with 12+. It is recommended to remove any dependencies on special OID behavior that you previously had in your partition sets at this time. 12 -- Fixed bug that prevented pg_partman from creating child tables in PG12+ due to useage of pg_class.relhasoids column. (Github Issue #256) 13 14-- A new configuration option, "constraint_valid", has been added to the part_config(_sub) table to control whether the additonal constraints that pg_partman can manage (see "Constraint Exclusion" section in pg_partman.md) are added in a NOT VALID state. By default the constraints are added as VALID as they were before. Note that if this config is changed to "false", constraint exclusion will not work until the constraints are validated. 15 16-- Fixed bug where primary key/unique indexes applied to pre-existing template tables were not being applied to the DEFAULT partition created in PG11+ native partitioning. Thanks to @dtseiler on Github for testing and reporting this. (Github Issue #266) 17 18-- Fixed bug that was causing error in the retention management of time-based partitioning sets. If retention would attempt to remove the last child table, maintenance would throw an error about a missing table instead of just a warning about the last table attempting to be dropped. Thanks to @LaVoCG on Github for the examples to reproduce this scenario. (Github Issues #227 & #237) 19 20-- Changed default option to false on PG11+ for whether the maintenance background worker will run an ANALYZE whenever a child table is created. If this is desired, please set the option pg_partman_bgw.analyze to true in your postgresql.conf. (Github Issue #262) 21 22-- Properly throw an error with create_parent() when the data type of the partition column is numeric. Previously would not report any errors but would also not add the proper data to the part_config table so future maintenance would not work. Currently testing to see if numeric partitioning can be properly supported in pg_partman. (Github Issue #265) 23 24 25ALTER TABLE @extschema@.part_config ADD COLUMN constraint_valid boolean DEFAULT true NOT NULL; 26ALTER TABLE @extschema@.part_config_sub ADD COLUMN sub_constraint_valid boolean DEFAULT true NOT NULL; 27 28CREATE OR REPLACE FUNCTION @extschema@.create_parent( 29 p_parent_table text 30 , p_control text 31 , p_type text 32 , p_interval text 33 , p_constraint_cols text[] DEFAULT NULL 34 , p_premake int DEFAULT 4 35 , p_automatic_maintenance text DEFAULT 'on' 36 , p_start_partition text DEFAULT NULL 37 , p_inherit_fk boolean DEFAULT true 38 , p_epoch text DEFAULT 'none' 39 , p_upsert text DEFAULT '' 40 , p_publications text[] DEFAULT NULL 41 , p_trigger_return_null boolean DEFAULT true 42 , p_template_table text DEFAULT NULL 43 , p_jobmon boolean DEFAULT true 44 , p_debug boolean DEFAULT false) 45RETURNS boolean 46 LANGUAGE plpgsql 47 AS $$ 48DECLARE 49 50ex_context text; 51ex_detail text; 52ex_hint text; 53ex_message text; 54v_partattrs smallint[]; 55v_base_timestamp timestamptz; 56v_count int := 1; 57v_control_type text; 58v_control_exact_type text; 59v_datetime_string text; 60v_default_partition text; 61v_higher_control_type text; 62v_higher_parent_control text; 63v_higher_parent_schema text := split_part(p_parent_table, '.', 1); 64v_higher_parent_table text := split_part(p_parent_table, '.', 2); 65v_id_interval bigint; 66v_inherit_privileges boolean := false; 67v_job_id bigint; 68v_jobmon_schema text; 69v_last_partition_created boolean; 70v_max bigint; 71v_native_sub_control text; 72v_notnull boolean; 73v_new_search_path text := '@extschema@,pg_temp'; 74v_old_search_path text; 75v_parent_owner text; 76v_parent_partition_id bigint; 77v_parent_partition_timestamp timestamptz; 78v_parent_schema text; 79v_parent_tablename text; 80v_parent_tablespace text; 81v_part_col text; 82v_part_type text; 83v_partition_time timestamptz; 84v_partition_time_array timestamptz[]; 85v_partition_id_array bigint[]; 86v_partstrat char; 87v_publication_exists text; 88v_row record; 89v_sql text; 90v_start_time timestamptz; 91v_starting_partition_id bigint; 92v_step_id bigint; 93v_step_overflow_id bigint; 94v_sub_parent text; 95v_success boolean := false; 96v_template_schema text; 97v_template_tablename text; 98v_time_interval interval; 99v_top_datetime_string text; 100v_top_parent_schema text := split_part(p_parent_table, '.', 1); 101v_top_parent_table text := split_part(p_parent_table, '.', 2); 102v_unlogged char; 103 104BEGIN 105/* 106 * Function to turn a table into the parent of a partition set 107 */ 108 109IF position('.' in p_parent_table) = 0 THEN 110 RAISE EXCEPTION 'Parent table must be schema qualified'; 111END IF; 112 113IF p_upsert <> '' THEN 114 IF current_setting('server_version_num')::int < 90500 THEN 115 RAISE EXCEPTION 'INSERT ... ON CONFLICT (UPSERT) feature is only supported in PostgreSQL 9.5 and later'; 116 END IF; 117 IF p_type = 'native' THEN 118 RAISE EXCEPTION 'Native partitioning does not currently support upsert. Use pg_partman''s partitioning methods instead if this is required'; 119 END IF; 120END IF; 121 122SELECT n.nspname, c.relname, t.spcname, c.relpersistence 123INTO v_parent_schema, v_parent_tablename, v_parent_tablespace, v_unlogged 124FROM pg_catalog.pg_class c 125JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 126LEFT OUTER JOIN pg_catalog.pg_tablespace t ON c.reltablespace = t.oid 127WHERE n.nspname = split_part(p_parent_table, '.', 1)::name 128AND c.relname = split_part(p_parent_table, '.', 2)::name; 129 IF v_parent_tablename IS NULL THEN 130 RAISE EXCEPTION 'Unable to find given parent table in system catalogs. Please create parent table first: %', p_parent_table; 131 END IF; 132 133SELECT attnotnull INTO v_notnull 134FROM pg_catalog.pg_attribute a 135JOIN pg_catalog.pg_class c ON a.attrelid = c.oid 136JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 137WHERE c.relname = v_parent_tablename::name 138AND n.nspname = v_parent_schema::name 139AND a.attname = p_control::name; 140 IF p_type <> 'native' AND (v_notnull = false OR v_notnull IS NULL) THEN 141 RAISE EXCEPTION 'Control column given (%) for parent table (%) does not exist or must be set to NOT NULL', p_control, p_parent_table; 142 END IF; 143 144SELECT general_type, exact_type INTO v_control_type, v_control_exact_type 145FROM @extschema@.check_control_type(v_parent_schema, v_parent_tablename, p_control); 146 147IF v_control_type IS NULL THEN 148 RAISE EXCEPTION 'pg_partman only supports partitioning of data types that are integer or date/timestamp. Supplied column is of type %', v_control_exact_type; 149END IF; 150 151IF (p_epoch <> 'none' AND v_control_type <> 'id') THEN 152 RAISE EXCEPTION 'p_epoch can only be used with an integer based control column and does not work for native partitioning'; 153END IF; 154 155 156IF NOT @extschema@.check_partition_type(p_type) THEN 157 RAISE EXCEPTION '% is not a valid partitioning type for pg_partman', p_type; 158END IF; 159 160IF p_type = 'native' THEN 161 162 IF current_setting('server_version_num')::int < 100000 THEN 163 RAISE EXCEPTION 'Native partitioning only available in PostgreSQL versions 10.0+'; 164 END IF; 165 -- Check if given parent table has been already set up as a partitioned table and is ranged 166 SELECT p.partstrat, partattrs INTO v_partstrat, v_partattrs 167 FROM pg_catalog.pg_partitioned_table p 168 JOIN pg_catalog.pg_class c ON p.partrelid = c.oid 169 JOIN pg_namespace n ON c.relnamespace = n.oid 170 WHERE n.nspname = v_parent_schema::name 171 AND c.relname = v_parent_tablename::name; 172 173 IF v_partstrat <> 'r' OR v_partstrat IS NULL THEN 174 RAISE EXCEPTION 'When using native partitioning, you must have created the given parent table as ranged (not list) partitioned already. Ex: CREATE TABLE ... PARITIONED BY RANGE ...)'; 175 END IF; 176 177 IF array_length(v_partattrs, 1) > 1 THEN 178 RAISE NOTICE 'pg_partman only supports single column native partitioning at this time. Found % columns in given parent definition.', array_length(v_partattrs, 1); 179 END IF; 180 181 SELECT a.attname, t.typname 182 INTO v_part_col, v_part_type 183 FROM pg_attribute a 184 JOIN pg_class c ON a.attrelid = c.oid 185 JOIN pg_namespace n ON c.relnamespace = n.oid 186 JOIN pg_type t ON a.atttypid = t.oid 187 WHERE n.nspname = v_parent_schema::name 188 AND c.relname = v_parent_tablename::name 189 AND attnum IN (SELECT unnest(partattrs) FROM pg_partitioned_table p WHERE a.attrelid = p.partrelid); 190 191 IF p_control <> v_part_col OR v_control_exact_type <> v_part_type THEN 192 RAISE EXCEPTION 'Control column and type given in arguments (%, %) does not match the control column and type of the given native partition set (%, %)', p_control, v_control_exact_type, v_part_col, v_part_type; 193 END IF; 194 195 -- Check that control column is a usable type for pg_partman. 196 IF v_control_type NOT IN ('time', 'id') THEN 197 RAISE EXCEPTION 'Only date/time or integer types are allowed for the control column with native partitioning.'; 198 END IF; 199 200 -- Table to handle properties not natively inherited yet (indexes, fks, etc) 201 IF p_template_table IS NULL THEN 202 v_template_schema := '@extschema@'; 203 v_template_tablename := @extschema@.check_name_length('template_'||v_parent_schema||'_'||v_parent_tablename); 204 EXECUTE format('CREATE TABLE IF NOT EXISTS %I.%I (LIKE %I.%I)', '@extschema@', v_template_tablename, v_parent_schema, v_parent_tablename); 205 206 SELECT pg_get_userbyid(c.relowner) INTO v_parent_owner 207 FROM pg_catalog.pg_class c 208 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 209 WHERE n.nspname = v_parent_schema::name 210 AND c.relname = v_parent_tablename::name; 211 212 EXECUTE format('ALTER TABLE %I.%I OWNER TO %I' 213 , '@extschema@' 214 , v_template_tablename 215 , v_parent_owner); 216 ELSE 217 SELECT n.nspname, c.relname INTO v_template_schema, v_template_tablename 218 FROM pg_catalog.pg_class c 219 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 220 WHERE n.nspname = split_part(p_template_table, '.', 1)::name 221 AND c.relname = split_part(p_template_table, '.', 2)::name; 222 IF v_template_tablename IS NULL THEN 223 RAISE EXCEPTION 'Unable to find given template table in system catalogs (%). Please create template table first or leave parameter NULL to have a default one created for you.', p_parent_table; 224 END IF; 225 END IF; 226 227ELSE -- if not native 228 229 IF current_setting('server_version_num')::int >= 100000 THEN 230 SELECT p.partstrat INTO v_partstrat 231 FROM pg_catalog.pg_partitioned_table p 232 JOIN pg_catalog.pg_class c ON p.partrelid = c.oid 233 JOIN pg_namespace n ON c.relnamespace = n.oid 234 WHERE n.nspname = v_parent_schema::name 235 AND c.relname = v_parent_tablename::name; 236 END IF; 237 238 IF v_partstrat IS NOT NULL THEN 239 RAISE EXCEPTION 'Given parent table has been set up with native partitioning therefore cannot be used with pg_partman''s other partitioning types. Either recreate table non-native or set the type argument to ''native'''; 240 END IF; 241 242END IF; -- end if "native" check 243 244 245IF p_publications IS NOT NULL THEN 246 IF current_setting('server_version_num')::int < 100000 THEN 247 RAISE EXCEPTION 'p_publications argument not null but CREATE PUBLICATION is only available in PostgreSQL versions 10.0+'; 248 END IF; 249 IF p_publications = '{}' THEN 250 RAISE EXCEPTION 'p_publications cannot be an empty set'; 251 END IF; 252 FOR v_row IN 253 SELECT unnest(p_publications) AS pubname 254 LOOP 255 SELECT pubname INTO v_publication_exists FROM pg_catalog.pg_publication where pubname = v_row.pubname::name; 256 IF v_publication_exists IS NULL THEN 257 RAISE EXCEPTION 'Given publication name (%) does not exist in system catalog. Ensure it is created first.', v_row.pubname; 258 END IF; 259 END LOOP; 260END IF; 261 262-- Only inherit parent ownership/privileges on non-native sets by default 263-- This is false by default so initial partition set creation doesn't require superuser. 264IF p_type = 'native' THEN 265 v_inherit_privileges = false; 266ELSE 267 v_inherit_privileges = true; 268END IF; 269 270SELECT current_setting('search_path') INTO v_old_search_path; 271IF p_jobmon THEN 272 SELECT nspname INTO v_jobmon_schema FROM pg_catalog.pg_namespace n, pg_catalog.pg_extension e WHERE e.extname = 'pg_jobmon'::name AND e.extnamespace = n.oid; 273 IF v_jobmon_schema IS NOT NULL THEN 274 v_new_search_path := '@extschema@,'||v_jobmon_schema||',pg_temp'; 275 END IF; 276END IF; 277EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_new_search_path, 'false'); 278 279EXECUTE format('LOCK TABLE %I.%I IN ACCESS EXCLUSIVE MODE', v_parent_schema, v_parent_tablename); 280 281IF v_jobmon_schema IS NOT NULL THEN 282 v_job_id := add_job(format('PARTMAN SETUP PARENT: %s', p_parent_table)); 283 v_step_id := add_step(v_job_id, format('Creating initial partitions on new parent table: %s', p_parent_table)); 284END IF; 285 286-- If this parent table has siblings that are also partitioned (subpartitions), ensure this parent gets added to part_config_sub table so future maintenance will subpartition it 287-- 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.) 288FOR v_row IN 289 WITH parent_table AS ( 290 SELECT h.inhparent AS parent_oid 291 FROM pg_catalog.pg_inherits h 292 JOIN pg_catalog.pg_class c ON h.inhrelid = c.oid 293 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 294 WHERE c.relname = v_parent_tablename::name 295 AND n.nspname = v_parent_schema::name 296 ), sibling_children AS ( 297 SELECT i.inhrelid::regclass::text AS tablename 298 FROM pg_inherits i 299 JOIN parent_table p ON i.inhparent = p.parent_oid 300 ) 301 SELECT DISTINCT sub_partition_type 302 , sub_control 303 , sub_partition_interval 304 , sub_constraint_cols 305 , sub_premake 306 , sub_inherit_fk 307 , sub_retention 308 , sub_retention_schema 309 , sub_retention_keep_table 310 , sub_retention_keep_index 311 , sub_automatic_maintenance 312 , sub_epoch 313 , sub_optimize_trigger 314 , sub_optimize_constraint 315 , sub_infinite_time_partitions 316 , sub_jobmon 317 , sub_trigger_exception_handling 318 , sub_upsert 319 , sub_trigger_return_null 320 , sub_template_table 321 , sub_inherit_privileges 322 , sub_constraint_valid 323 FROM @extschema@.part_config_sub a 324 JOIN sibling_children b on a.sub_parent = b.tablename LIMIT 1 325LOOP 326 INSERT INTO @extschema@.part_config_sub ( 327 sub_parent 328 , sub_partition_type 329 , sub_control 330 , sub_partition_interval 331 , sub_constraint_cols 332 , sub_premake 333 , sub_inherit_fk 334 , sub_retention 335 , sub_retention_schema 336 , sub_retention_keep_table 337 , sub_retention_keep_index 338 , sub_automatic_maintenance 339 , sub_epoch 340 , sub_optimize_trigger 341 , sub_optimize_constraint 342 , sub_infinite_time_partitions 343 , sub_jobmon 344 , sub_trigger_exception_handling 345 , sub_upsert 346 , sub_trigger_return_null 347 , sub_template_table 348 , sub_inherit_privileges 349 , sub_constraint_valid) 350 VALUES ( 351 p_parent_table 352 , v_row.sub_partition_type 353 , v_row.sub_control 354 , v_row.sub_partition_interval 355 , v_row.sub_constraint_cols 356 , v_row.sub_premake 357 , v_row.sub_inherit_fk 358 , v_row.sub_retention 359 , v_row.sub_retention_schema 360 , v_row.sub_retention_keep_table 361 , v_row.sub_retention_keep_index 362 , v_row.sub_automatic_maintenance 363 , v_row.sub_epoch 364 , v_row.sub_optimize_trigger 365 , v_row.sub_optimize_constraint 366 , v_row.sub_infinite_time_partitions 367 , v_row.sub_jobmon 368 , v_row.sub_trigger_exception_handling 369 , v_row.sub_upsert 370 , v_row.sub_trigger_return_null 371 , v_row.sub_template_table 372 , v_row.sub_inherit_privileges 373 , v_row.sub_constraint_valid); 374 375 -- Set this equal to sibling configs so that newly created child table 376 -- privileges are set properly below during initial setup. 377 -- This setting is special because it applies immediately to the new child 378 -- tables of a given parent, not just during maintenance like most other settings. 379 v_inherit_privileges = v_row.sub_inherit_privileges; 380END LOOP; 381 382IF v_control_type = 'time' OR (v_control_type = 'id' AND p_epoch <> 'none') THEN 383 384 CASE 385 WHEN p_interval = 'yearly' THEN 386 v_time_interval := '1 year'; 387 WHEN p_interval = 'quarterly' THEN 388 v_time_interval := '3 months'; 389 WHEN p_interval = 'monthly' THEN 390 v_time_interval := '1 month'; 391 WHEN p_interval = 'weekly' THEN 392 v_time_interval := '1 week'; 393 WHEN p_interval = 'daily' THEN 394 v_time_interval := '1 day'; 395 WHEN p_interval = 'hourly' THEN 396 v_time_interval := '1 hour'; 397 WHEN p_interval = 'half-hour' THEN 398 v_time_interval := '30 mins'; 399 WHEN p_interval = 'quarter-hour' THEN 400 v_time_interval := '15 mins'; 401 ELSE 402 IF p_type <> 'native' THEN 403 -- Reset for use as part_config type value below 404 p_type = 'time-custom'; 405 END IF; 406 v_time_interval := p_interval::interval; 407 IF v_time_interval < '1 second'::interval THEN 408 RAISE EXCEPTION 'Partitioning interval must be 1 second or greater'; 409 END IF; 410 END CASE; 411 412 -- First partition is either the min premake or p_start_partition 413 v_start_time := COALESCE(p_start_partition::timestamptz, CURRENT_TIMESTAMP - (v_time_interval * p_premake)); 414 415 IF v_time_interval >= '1 year' THEN 416 v_base_timestamp := date_trunc('year', v_start_time); 417 IF v_time_interval >= '10 years' THEN 418 v_base_timestamp := date_trunc('decade', v_start_time); 419 IF v_time_interval >= '100 years' THEN 420 v_base_timestamp := date_trunc('century', v_start_time); 421 IF v_time_interval >= '1000 years' THEN 422 v_base_timestamp := date_trunc('millennium', v_start_time); 423 END IF; -- 1000 424 END IF; -- 100 425 END IF; -- 10 426 END IF; -- 1 427 428 v_datetime_string := 'YYYY'; 429 IF v_time_interval < '1 year' THEN 430 IF p_interval = 'quarterly' THEN 431 v_base_timestamp := date_trunc('quarter', v_start_time); 432 v_datetime_string = 'YYYY"q"Q'; 433 ELSE 434 v_base_timestamp := date_trunc('month', v_start_time); 435 v_datetime_string := v_datetime_string || '_MM'; 436 END IF; 437 IF v_time_interval < '1 month' THEN 438 IF p_interval = 'weekly' THEN 439 v_base_timestamp := date_trunc('week', v_start_time); 440 v_datetime_string := 'IYYY"w"IW'; 441 ELSE 442 v_base_timestamp := date_trunc('day', v_start_time); 443 v_datetime_string := v_datetime_string || '_DD'; 444 END IF; 445 IF v_time_interval < '1 day' THEN 446 v_base_timestamp := date_trunc('hour', v_start_time); 447 v_datetime_string := v_datetime_string || '_HH24MI'; 448 IF v_time_interval < '1 minute' THEN 449 v_base_timestamp := date_trunc('minute', v_start_time); 450 v_datetime_string := v_datetime_string || 'SS'; 451 END IF; -- minute 452 END IF; -- day 453 END IF; -- month 454 END IF; -- year 455 456 v_partition_time_array := array_append(v_partition_time_array, v_base_timestamp); 457 LOOP 458 -- If current loop value is less than or equal to the value of the max premake, add time to array. 459 IF (v_base_timestamp + (v_time_interval * v_count)) < (CURRENT_TIMESTAMP + (v_time_interval * p_premake)) THEN 460 BEGIN 461 v_partition_time := (v_base_timestamp + (v_time_interval * v_count))::timestamptz; 462 v_partition_time_array := array_append(v_partition_time_array, v_partition_time); 463 EXCEPTION WHEN datetime_field_overflow THEN 464 RAISE WARNING 'Attempted partition time interval is outside PostgreSQL''s supported time range. 465 Child partition creation after time % skipped', v_partition_time; 466 v_step_overflow_id := add_step(v_job_id, 'Attempted partition time interval is outside PostgreSQL''s supported time range.'); 467 PERFORM update_step(v_step_overflow_id, 'CRITICAL', 'Child partition creation after time '||v_partition_time||' skipped'); 468 CONTINUE; 469 END; 470 ELSE 471 EXIT; -- all needed partitions added to array. Exit the loop. 472 END IF; 473 v_count := v_count + 1; 474 END LOOP; 475 476 INSERT INTO @extschema@.part_config ( 477 parent_table 478 , partition_type 479 , partition_interval 480 , epoch 481 , control 482 , premake 483 , constraint_cols 484 , datetime_string 485 , automatic_maintenance 486 , inherit_fk 487 , jobmon 488 , upsert 489 , trigger_return_null 490 , template_table 491 , publications 492 , inherit_privileges) 493 VALUES ( 494 p_parent_table 495 , p_type 496 , v_time_interval 497 , p_epoch 498 , p_control 499 , p_premake 500 , p_constraint_cols 501 , v_datetime_string 502 , p_automatic_maintenance 503 , p_inherit_fk 504 , p_jobmon 505 , p_upsert 506 , p_trigger_return_null 507 , v_template_schema||'.'||v_template_tablename 508 , p_publications 509 , v_inherit_privileges); 510 511 RAISE DEBUG 'create_parent: v_partition_time_array: %', v_partition_time_array; 512 513 v_last_partition_created := @extschema@.create_partition_time(p_parent_table, v_partition_time_array, false); 514 515 IF v_last_partition_created = false THEN 516 -- This can happen with subpartitioning when future or past partitions prevent child creation because they're out of range of the parent 517 -- First see if this parent is a subpartition managed by pg_partman 518 WITH top_oid AS ( 519 SELECT i.inhparent AS top_parent_oid 520 FROM pg_catalog.pg_inherits i 521 JOIN pg_catalog.pg_class c ON c.oid = i.inhrelid 522 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 523 WHERE c.relname = v_parent_tablename::name 524 AND n.nspname = v_parent_schema::name 525 ) SELECT n.nspname, c.relname 526 INTO v_top_parent_schema, v_top_parent_table 527 FROM pg_catalog.pg_class c 528 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 529 JOIN top_oid t ON c.oid = t.top_parent_oid 530 JOIN @extschema@.part_config p ON p.parent_table = n.nspname||'.'||c.relname; 531 532 IF v_top_parent_table IS NOT NULL THEN 533 -- If so create the lowest possible partition that is within the boundary of the parent 534 SELECT child_start_time INTO v_parent_partition_timestamp FROM @extschema@.show_partition_info(p_parent_table, p_parent_table := v_top_parent_schema||'.'||v_top_parent_table); 535 IF v_base_timestamp >= v_parent_partition_timestamp THEN 536 WHILE v_base_timestamp >= v_parent_partition_timestamp LOOP 537 v_base_timestamp := v_base_timestamp - v_time_interval; 538 END LOOP; 539 v_base_timestamp := v_base_timestamp + v_time_interval; -- add one back since while loop set it one lower than is needed 540 ELSIF v_base_timestamp < v_parent_partition_timestamp THEN 541 WHILE v_base_timestamp < v_parent_partition_timestamp LOOP 542 v_base_timestamp := v_base_timestamp + v_time_interval; 543 END LOOP; 544 -- Don't need to remove one since new starting time will fit in top parent interval 545 END IF; 546 v_partition_time_array := NULL; 547 v_partition_time_array := array_append(v_partition_time_array, v_base_timestamp); 548 v_last_partition_created := @extschema@.create_partition_time(p_parent_table, v_partition_time_array, false); 549 ELSE 550 RAISE WARNING 'No child tables created. Check that all child tables did not already exist and may not have been part of partition set. Given parent has still been configured with pg_partman, but may not have expected children. Please review schema and config to confirm things are ok.'; 551 552 IF v_jobmon_schema IS NOT NULL THEN 553 PERFORM update_step(v_step_id, 'OK', 'Done'); 554 IF v_step_overflow_id IS NOT NULL THEN 555 PERFORM fail_job(v_job_id); 556 ELSE 557 PERFORM close_job(v_job_id); 558 END IF; 559 END IF; 560 561 EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false'); 562 563 RETURN v_success; 564 END IF; 565 END IF; -- End v_last_partition IF 566 567 IF v_jobmon_schema IS NOT NULL THEN 568 PERFORM update_step(v_step_id, 'OK', format('Time partitions premade: %s', p_premake)); 569 END IF; 570 571END IF; 572 573IF v_control_type = 'id' AND p_epoch = 'none' THEN 574 v_id_interval := p_interval::bigint; 575 IF p_type <> 'native' AND v_id_interval < 10 THEN 576 RAISE EXCEPTION 'Interval for serial, non-native partitioning must be greater than or equal to 10'; 577 END IF; 578 579 -- Check if parent table is a subpartition of an already existing id partition set managed by pg_partman. 580 WHILE v_higher_parent_table IS NOT NULL LOOP -- initially set in DECLARE 581 WITH top_oid AS ( 582 SELECT i.inhparent AS top_parent_oid 583 FROM pg_catalog.pg_inherits i 584 JOIN pg_catalog.pg_class c ON c.oid = i.inhrelid 585 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 586 WHERE n.nspname = v_higher_parent_schema::name 587 AND c.relname = v_higher_parent_table::name 588 ) SELECT n.nspname, c.relname, p.control 589 INTO v_higher_parent_schema, v_higher_parent_table, v_higher_parent_control 590 FROM pg_catalog.pg_class c 591 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 592 JOIN top_oid t ON c.oid = t.top_parent_oid 593 JOIN @extschema@.part_config p ON p.parent_table = n.nspname||'.'||c.relname; 594 595 IF v_higher_parent_table IS NOT NULL THEN 596 SELECT general_type INTO v_higher_control_type 597 FROM @extschema@.check_control_type(v_higher_parent_schema, v_higher_parent_table, v_higher_parent_control); 598 IF v_higher_control_type <> 'id' THEN 599 -- The parent above the p_parent_table parameter is not partitioned by ID 600 -- so don't check for max values in parents that aren't partitioned by ID. 601 -- This avoids missing child tables in subpartition sets that have differing ID data 602 EXIT; 603 END IF; 604 -- v_top_parent initially set in DECLARE 605 v_top_parent_schema := v_higher_parent_schema; 606 v_top_parent_table := v_higher_parent_table; 607 END IF; 608 END LOOP; 609 610 -- If custom start partition is set, use that. 611 -- 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 612 IF p_start_partition IS NOT NULL THEN 613 v_max := p_start_partition::bigint; 614 ELSE 615 v_sql := format('SELECT COALESCE(max(%I)::bigint, 0) FROM %I.%I LIMIT 1' 616 , p_control 617 , v_top_parent_schema 618 , v_top_parent_table); 619 EXECUTE v_sql INTO v_max; 620 END IF; 621 622 v_starting_partition_id := v_max - (v_max % v_id_interval); 623 FOR i IN 0..p_premake LOOP 624 -- Only make previous partitions if ID value is less than the starting value and positive (and custom start partition wasn't set) 625 IF p_start_partition IS NULL AND 626 (v_starting_partition_id - (v_id_interval*i)) > 0 AND 627 (v_starting_partition_id - (v_id_interval*i)) < v_starting_partition_id 628 THEN 629 v_partition_id_array = array_append(v_partition_id_array, (v_starting_partition_id - v_id_interval*i)); 630 END IF; 631 v_partition_id_array = array_append(v_partition_id_array, (v_id_interval*i) + v_starting_partition_id); 632 END LOOP; 633 634 INSERT INTO @extschema@.part_config ( 635 parent_table 636 , partition_type 637 , partition_interval 638 , control 639 , premake 640 , constraint_cols 641 , automatic_maintenance 642 , inherit_fk 643 , jobmon 644 , upsert 645 , trigger_return_null 646 , template_table 647 , publications 648 , inherit_privileges) 649 VALUES ( 650 p_parent_table 651 , p_type 652 , v_id_interval 653 , p_control 654 , p_premake 655 , p_constraint_cols 656 , p_automatic_maintenance 657 , p_inherit_fk 658 , p_jobmon 659 , p_upsert 660 , p_trigger_return_null 661 , v_template_schema||'.'||v_template_tablename 662 , p_publications 663 , v_inherit_privileges); 664 665 v_last_partition_created := @extschema@.create_partition_id(p_parent_table, v_partition_id_array, false); 666 667 IF v_last_partition_created = false THEN 668 -- This can happen with subpartitioning when future or past partitions prevent child creation because they're out of range of the parent 669 -- See if it's actually a subpartition of a parent id partition 670 WITH top_oid AS ( 671 SELECT i.inhparent AS top_parent_oid 672 FROM pg_catalog.pg_inherits i 673 JOIN pg_catalog.pg_class c ON c.oid = i.inhrelid 674 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 675 WHERE c.relname = v_parent_tablename::name 676 AND n.nspname = v_parent_schema::name 677 ) SELECT n.nspname||'.'||c.relname 678 INTO v_top_parent_table 679 FROM pg_catalog.pg_class c 680 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 681 JOIN top_oid t ON c.oid = t.top_parent_oid 682 JOIN @extschema@.part_config p ON p.parent_table = n.nspname||'.'||c.relname; 683 684 IF v_top_parent_table IS NOT NULL THEN 685 -- Create the lowest possible partition that is within the boundary of the parent 686 SELECT child_start_id INTO v_parent_partition_id FROM @extschema@.show_partition_info(p_parent_table, p_parent_table := v_top_parent_table); 687 IF v_starting_partition_id >= v_parent_partition_id THEN 688 WHILE v_starting_partition_id >= v_parent_partition_id LOOP 689 v_starting_partition_id := v_starting_partition_id - v_id_interval; 690 END LOOP; 691 v_starting_partition_id := v_starting_partition_id + v_id_interval; -- add one back since while loop set it one lower than is needed 692 ELSIF v_starting_partition_id < v_parent_partition_id THEN 693 WHILE v_starting_partition_id < v_parent_partition_id LOOP 694 v_starting_partition_id := v_starting_partition_id + v_id_interval; 695 END LOOP; 696 -- Don't need to remove one since new starting id will fit in top parent interval 697 END IF; 698 v_partition_id_array = NULL; 699 v_partition_id_array = array_append(v_partition_id_array, v_starting_partition_id); 700 v_last_partition_created := @extschema@.create_partition_id(p_parent_table, v_partition_id_array, false); 701 ELSE 702 -- Currently unknown edge case if code gets here 703 RAISE WARNING 'No child tables created. Check that all child tables did not already exist and may not have been part of partition set. Given parent has still been configured with pg_partman, but may not have expected children. Please review schema and config to confirm things are ok.'; 704 IF v_jobmon_schema IS NOT NULL THEN 705 PERFORM update_step(v_step_id, 'OK', 'Done'); 706 IF v_step_overflow_id IS NOT NULL THEN 707 PERFORM fail_job(v_job_id); 708 ELSE 709 PERFORM close_job(v_job_id); 710 END IF; 711 END IF; 712 713 EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false'); 714 715 RETURN v_success; 716 END IF; 717 END IF; -- End v_last_partition_created IF 718 719END IF; -- End IF id 720 721IF p_type = 'native' AND current_setting('server_version_num')::int >= 110000 THEN 722 -- Add default partition to native sets in PG11+ 723 724 v_default_partition := @extschema@.check_name_length(v_parent_tablename, '_default', FALSE); 725 v_sql := 'CREATE'; 726 727 -- Left this here as reminder to revisit once native figures out how it is handling changing unlogged stats 728 -- Currently handed via template table below 729 /* 730 IF v_unlogged = 'u' THEN 731 v_sql := v_sql ||' UNLOGGED'; 732 END IF; 733 */ 734 735 -- Same INCLUDING list is used in create_partition_*() 736 v_sql := v_sql || format(' TABLE %I.%I (LIKE %I.%I INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING STORAGE INCLUDING COMMENTS)' 737 , v_parent_schema, v_default_partition, v_parent_schema, v_parent_tablename); 738 EXECUTE v_sql; 739 v_sql := format('ALTER TABLE %I.%I ATTACH PARTITION %I.%I DEFAULT' 740 , v_parent_schema, v_parent_tablename, v_parent_schema, v_default_partition); 741 EXECUTE v_sql; 742 743 -- Ensure any primary/unique keys on premade template tables are applied 744 PERFORM @extschema@.inherit_template_properties(p_parent_table, v_parent_schema, v_default_partition); 745 746 IF v_parent_tablespace IS NOT NULL THEN 747 EXECUTE format('ALTER TABLE %I.%I SET TABLESPACE %I', v_parent_schema, v_default_partition, v_parent_tablespace); 748 END IF; 749 750END IF; 751 752IF p_type <> 'native' THEN 753 IF v_jobmon_schema IS NOT NULL THEN 754 v_step_id := add_step(v_job_id, 'Creating partition function'); 755 END IF; 756 IF v_control_type = 'time' OR (v_control_type = 'id' AND p_epoch <> 'none') THEN 757 PERFORM @extschema@.create_function_time(p_parent_table, v_job_id); 758 IF v_jobmon_schema IS NOT NULL THEN 759 PERFORM update_step(v_step_id, 'OK', 'Time function created'); 760 END IF; 761 ELSIF v_control_type = 'id' THEN 762 PERFORM @extschema@.create_function_id(p_parent_table, v_job_id); 763 IF v_jobmon_schema IS NOT NULL THEN 764 PERFORM update_step(v_step_id, 'OK', 'ID function created'); 765 END IF; 766 END IF; 767 768 IF v_jobmon_schema IS NOT NULL THEN 769 v_step_id := add_step(v_job_id, 'Creating partition trigger'); 770 END IF; 771 PERFORM @extschema@.create_trigger(p_parent_table); 772END IF; -- end native check 773 774 775IF v_jobmon_schema IS NOT NULL THEN 776 PERFORM update_step(v_step_id, 'OK', 'Done'); 777 IF v_step_overflow_id IS NOT NULL THEN 778 PERFORM fail_job(v_job_id); 779 ELSE 780 PERFORM close_job(v_job_id); 781 END IF; 782END IF; 783 784EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false'); 785 786v_success := true; 787 788RETURN v_success; 789 790EXCEPTION 791 WHEN OTHERS THEN 792 GET STACKED DIAGNOSTICS ex_message = MESSAGE_TEXT, 793 ex_context = PG_EXCEPTION_CONTEXT, 794 ex_detail = PG_EXCEPTION_DETAIL, 795 ex_hint = PG_EXCEPTION_HINT; 796 IF v_jobmon_schema IS NOT NULL THEN 797 IF v_job_id IS NULL THEN 798 EXECUTE format('SELECT %I.add_job(''PARTMAN CREATE PARENT: %s'')', v_jobmon_schema, p_parent_table) INTO v_job_id; 799 EXECUTE format('SELECT %I.add_step(%s, ''Partition creation for table '||p_parent_table||' failed'')', v_jobmon_schema, v_job_id, p_parent_table) INTO v_step_id; 800 ELSIF v_step_id IS NULL THEN 801 EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before first step logged'')', v_jobmon_schema, v_job_id) INTO v_step_id; 802 END IF; 803 EXECUTE format('SELECT %I.update_step(%s, ''CRITICAL'', %L)', v_jobmon_schema, v_step_id, 'ERROR: '||coalesce(SQLERRM,'unknown')); 804 EXECUTE format('SELECT %I.fail_job(%s)', v_jobmon_schema, v_job_id); 805 END IF; 806 RAISE EXCEPTION '% 807CONTEXT: % 808DETAIL: % 809HINT: %', ex_message, ex_context, ex_detail, ex_hint; 810END 811$$; 812 813 814 815CREATE OR REPLACE FUNCTION @extschema@.create_partition_id(p_parent_table text, p_partition_ids bigint[], p_analyze boolean DEFAULT true, p_debug boolean DEFAULT false) RETURNS boolean 816 LANGUAGE plpgsql 817 AS $$ 818DECLARE 819 820ex_context text; 821ex_detail text; 822ex_hint text; 823ex_message text; 824v_all text[] := ARRAY['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER']; 825v_analyze boolean := FALSE; 826v_control text; 827v_control_type text; 828v_exists text; 829v_grantees text[]; 830v_hasoids boolean; 831v_id bigint; 832v_inherit_fk boolean; 833v_inherit_privileges boolean; 834v_job_id bigint; 835v_jobmon boolean; 836v_jobmon_schema text; 837v_new_search_path text := '@extschema@,pg_temp'; 838v_old_search_path text; 839v_parent_grant record; 840v_parent_schema text; 841v_parent_tablename text; 842v_parent_tablespace text; 843v_partition_interval bigint; 844v_partition_created boolean := false; 845v_partition_name text; 846v_partition_type text; 847v_publications text[]; 848v_revoke text; 849v_row record; 850v_sql text; 851v_step_id bigint; 852v_sub_control text; 853v_sub_partition_type text; 854v_sub_id_max bigint; 855v_sub_id_min bigint; 856v_template_table text; 857v_unlogged char; 858 859BEGIN 860/* 861 * Function to create id partitions 862 */ 863 864SELECT control 865 , partition_type 866 , partition_interval 867 , inherit_fk 868 , jobmon 869 , template_table 870 , publications 871 , inherit_privileges 872INTO v_control 873 , v_partition_type 874 , v_partition_interval 875 , v_inherit_fk 876 , v_jobmon 877 , v_template_table 878 , v_publications 879 , v_inherit_privileges 880FROM @extschema@.part_config 881WHERE parent_table = p_parent_table; 882 883IF NOT FOUND THEN 884 RAISE EXCEPTION 'ERROR: no config found for %', p_parent_table; 885END IF; 886 887SELECT n.nspname, c.relname, t.spcname 888INTO v_parent_schema, v_parent_tablename, v_parent_tablespace 889FROM pg_catalog.pg_class c 890JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 891LEFT OUTER JOIN pg_catalog.pg_tablespace t ON c.reltablespace = t.oid 892WHERE n.nspname = split_part(p_parent_table, '.', 1)::name 893 894AND c.relname = split_part(p_parent_table, '.', 2)::name; 895 896SELECT general_type INTO v_control_type FROM @extschema@.check_control_type(v_parent_schema, v_parent_tablename, v_control); 897IF v_control_type <> 'id' THEN 898 RAISE EXCEPTION 'ERROR: Given parent table is not set up for id/serial partitioning'; 899END IF; 900 901SELECT current_setting('search_path') INTO v_old_search_path; 902IF v_jobmon THEN 903 SELECT nspname INTO v_jobmon_schema FROM pg_catalog.pg_namespace n, pg_catalog.pg_extension e WHERE e.extname = 'pg_jobmon'::name AND e.extnamespace = n.oid; 904 IF v_jobmon_schema IS NOT NULL THEN 905 v_new_search_path := '@extschema@,'||v_jobmon_schema||',pg_temp'; 906 END IF; 907END IF; 908EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_new_search_path, 'false'); 909 910-- Determine if this table is a child of a subpartition parent. If so, get limits of what child tables can be created based on parent suffix 911SELECT sub_min::bigint, sub_max::bigint INTO v_sub_id_min, v_sub_id_max FROM @extschema@.check_subpartition_limits(p_parent_table, 'id'); 912 913IF v_jobmon_schema IS NOT NULL THEN 914 v_job_id := add_job(format('PARTMAN CREATE TABLE: %s', p_parent_table)); 915END IF; 916 917FOREACH v_id IN ARRAY p_partition_ids LOOP 918-- Do not create the child table if it's outside the bounds of the top parent. 919 IF v_sub_id_min IS NOT NULL THEN 920 IF v_id < v_sub_id_min OR v_id > v_sub_id_max THEN 921 CONTINUE; 922 END IF; 923 END IF; 924 925 v_partition_name := @extschema@.check_name_length(v_parent_tablename, v_id::text, TRUE); 926 -- If child table already exists, skip creation 927 -- Have to check pg_class because if subpartitioned, table will not be in pg_tables 928 SELECT c.relname INTO v_exists 929 FROM pg_catalog.pg_class c 930 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 931 WHERE n.nspname = v_parent_schema::name AND c.relname = v_partition_name::name; 932 IF v_exists IS NOT NULL THEN 933 CONTINUE; 934 END IF; 935 936 -- Ensure analyze is run if a new partition is created. Otherwise if one isn't, will be false and analyze will be skipped 937 v_analyze := TRUE; 938 939 IF v_jobmon_schema IS NOT NULL THEN 940 v_step_id := add_step(v_job_id, 'Creating new partition '||v_partition_name||' with interval from '||v_id||' to '||(v_id + v_partition_interval)-1); 941 END IF; 942 943 v_sql := 'CREATE'; 944 945 -- As of PG12, the unlogged/logged status of a native parent table cannot be changed via an ALTER TABLE in order to affect its children. 946 -- As of v4.2x, the unlogged state will be managed via the template table 947 SELECT relpersistence INTO v_unlogged 948 FROM pg_catalog.pg_class c 949 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 950 WHERE c.relname = v_parent_tablename::name 951 AND n.nspname = v_parent_schema::name; 952 IF v_unlogged = 'u' and v_partition_type != 'native' THEN 953 v_sql := v_sql || ' UNLOGGED'; 954 END IF; 955 956 -- Close parentheses on LIKE are below due to differing requirements of native subpartitioning 957 -- Same INCLUDING list is used in create_parent() 958 v_sql := v_sql || format(' TABLE %I.%I (LIKE %I.%I INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING STORAGE INCLUDING COMMENTS ' 959 , v_parent_schema 960 , v_partition_name 961 , v_parent_schema 962 , v_parent_tablename); 963 964 SELECT sub_partition_type, sub_control INTO v_sub_partition_type, v_sub_control 965 FROM @extschema@.part_config_sub 966 WHERE sub_parent = p_parent_table; 967 IF v_sub_partition_type = 'native' THEN 968 -- NOTE: Need to handle this differently when index inheritance is supported natively 969 -- Cannot include indexes since they cannot exist on native parents. 970 v_sql := v_sql || format(') PARTITION BY RANGE (%I) ', v_sub_control); 971 ELSE 972 v_sql := v_sql || format(' INCLUDING INDEXES) ', v_sub_control); 973 END IF; 974 975 976 IF current_setting('server_version_num')::int < 120000 THEN 977 -- column removed from pgclass in pg12 978 SELECT relhasoids INTO v_hasoids 979 FROM pg_catalog.pg_class c 980 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 981 WHERE c.relname = v_parent_tablename::name 982 AND n.nspname = v_parent_schema::name; 983 IF v_hasoids IS TRUE THEN 984 v_sql := v_sql || ' WITH (OIDS)'; 985 END IF; 986 END IF; 987 988 IF p_debug THEN 989 RAISE NOTICE 'create_partition_id v_sql: %', v_sql; 990 END IF; 991 EXECUTE v_sql; 992 993 IF v_partition_type = 'native' THEN 994 995 IF v_template_table IS NOT NULL THEN 996 PERFORM @extschema@.inherit_template_properties(p_parent_table, v_parent_schema, v_partition_name); 997 END IF; 998 999 EXECUTE format('ALTER TABLE %I.%I ATTACH PARTITION %I.%I FOR VALUES FROM (%L) TO (%L)' 1000 , v_parent_schema 1001 , v_parent_tablename 1002 , v_parent_schema 1003 , v_partition_name 1004 , v_id 1005 , v_id + v_partition_interval); 1006 1007 ELSE 1008 -- Handled in inherit_template_properties for native because CREATE TABLE ignores TABLESPACE flag for native partition parents 1009 IF v_parent_tablespace IS NOT NULL THEN 1010 EXECUTE format('ALTER TABLE %I.%I SET TABLESPACE %I', v_parent_schema, v_partition_name, v_parent_tablespace); 1011 END IF; 1012 1013 EXECUTE format('ALTER TABLE %I.%I ADD CONSTRAINT %I CHECK (%I >= %s AND %I < %s )' 1014 , v_parent_schema 1015 , v_partition_name 1016 , v_partition_name||'_partition_check' 1017 , v_control 1018 , v_id 1019 , v_control 1020 , v_id + v_partition_interval); 1021 1022 EXECUTE format('ALTER TABLE %I.%I INHERIT %I.%I', v_parent_schema, v_partition_name, v_parent_schema, v_parent_tablename); 1023 1024 -- Indexes cannot be created on the parent, so clustering cannot be used for native yet. 1025 PERFORM @extschema@.apply_cluster(v_parent_schema, v_parent_tablename, v_parent_schema, v_partition_name); 1026 1027 -- Foreign keys to other tables not supported on native parent tables 1028 IF v_inherit_fk THEN 1029 PERFORM @extschema@.apply_foreign_keys(p_parent_table, v_parent_schema||'.'||v_partition_name, v_job_id); 1030 END IF; 1031 1032 END IF; 1033 1034 -- NOTE: Privileges not automatically inherited for native. Only do so if config flag is set 1035 IF v_partition_type != 'native' OR (v_partition_type = 'native' AND v_inherit_privileges = TRUE) THEN 1036 PERFORM @extschema@.apply_privileges(v_parent_schema, v_parent_tablename, v_parent_schema, v_partition_name, v_job_id); 1037 END IF; 1038 1039 IF v_jobmon_schema IS NOT NULL THEN 1040 PERFORM update_step(v_step_id, 'OK', 'Done'); 1041 END IF; 1042 1043 -- Will only loop once and only if sub_partitioning is actually configured 1044 -- This seemed easier than assigning a bunch of variables then doing an IF condition 1045 FOR v_row IN 1046 SELECT sub_parent 1047 , sub_partition_type 1048 , sub_control 1049 , sub_partition_interval 1050 , sub_constraint_cols 1051 , sub_premake 1052 , sub_optimize_trigger 1053 , sub_optimize_constraint 1054 , sub_epoch 1055 , sub_inherit_fk 1056 , sub_retention 1057 , sub_retention_schema 1058 , sub_retention_keep_table 1059 , sub_retention_keep_index 1060 , sub_automatic_maintenance 1061 , sub_infinite_time_partitions 1062 , sub_jobmon 1063 , sub_trigger_exception_handling 1064 , sub_template_table 1065 , sub_inherit_privileges 1066 , sub_constraint_valid 1067 FROM @extschema@.part_config_sub 1068 WHERE sub_parent = p_parent_table 1069 LOOP 1070 IF v_jobmon_schema IS NOT NULL THEN 1071 v_step_id := add_step(v_job_id, 'Subpartitioning '||v_partition_name); 1072 END IF; 1073 v_sql := format('SELECT @extschema@.create_parent( 1074 p_parent_table := %L 1075 , p_control := %L 1076 , p_type := %L 1077 , p_interval := %L 1078 , p_constraint_cols := %L 1079 , p_premake := %L 1080 , p_automatic_maintenance := %L 1081 , p_inherit_fk := %L 1082 , p_epoch := %L 1083 , p_template_table := %L 1084 , p_jobmon := %L )' 1085 , v_parent_schema||'.'||v_partition_name 1086 , v_row.sub_control 1087 , v_row.sub_partition_type 1088 , v_row.sub_partition_interval 1089 , v_row.sub_constraint_cols 1090 , v_row.sub_premake 1091 , v_row.sub_automatic_maintenance 1092 , v_row.sub_inherit_fk 1093 , v_row.sub_epoch 1094 , v_row.sub_template_table 1095 , v_row.sub_jobmon); 1096 EXECUTE v_sql; 1097 1098 UPDATE @extschema@.part_config SET 1099 retention_schema = v_row.sub_retention_schema 1100 , retention_keep_table = v_row.sub_retention_keep_table 1101 , retention_keep_index = v_row.sub_retention_keep_index 1102 , optimize_trigger = v_row.sub_optimize_trigger 1103 , optimize_constraint = v_row.sub_optimize_constraint 1104 , infinite_time_partitions = v_row.sub_infinite_time_partitions 1105 , trigger_exception_handling = v_row.sub_trigger_exception_handling 1106 , inherit_privileges = v_row.sub_inherit_privileges 1107 , constraint_valid = v_row.sub_constraint_valid 1108 WHERE parent_table = v_parent_schema||'.'||v_partition_name; 1109 1110 IF v_jobmon_schema IS NOT NULL THEN 1111 PERFORM update_step(v_step_id, 'OK', 'Done'); 1112 END IF; 1113 1114 END LOOP; -- end sub partitioning LOOP 1115 1116 -- Manage additonal constraints if set 1117 PERFORM @extschema@.apply_constraints(p_parent_table, p_job_id := v_job_id, p_debug := p_debug); 1118 1119 IF v_publications IS NOT NULL THEN 1120 -- NOTE: Publications currently not supported on parent table, but are supported on the table partitions if individually assigned. 1121 PERFORM @extschema@.apply_publications(p_parent_table, v_parent_schema, v_partition_name); 1122 END IF; 1123 1124 v_partition_created := true; 1125 1126END LOOP; 1127 1128-- v_analyze is a local check if a new table is made. 1129-- 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. 1130IF v_analyze AND p_analyze THEN 1131 IF v_jobmon_schema IS NOT NULL THEN 1132 v_step_id := add_step(v_job_id, format('Analyzing partition set: %s', p_parent_table)); 1133 END IF; 1134 1135 EXECUTE format('ANALYZE %I.%I', v_parent_schema, v_parent_tablename); 1136 1137 IF v_jobmon_schema IS NOT NULL THEN 1138 PERFORM update_step(v_step_id, 'OK', 'Done'); 1139 END IF; 1140END IF; 1141 1142IF v_jobmon_schema IS NOT NULL THEN 1143 IF v_partition_created = false THEN 1144 v_step_id := add_step(v_job_id, format('No partitions created for partition set: %s', p_parent_table)); 1145 PERFORM update_step(v_step_id, 'OK', 'Done'); 1146 END IF; 1147 1148 PERFORM close_job(v_job_id); 1149END IF; 1150 1151EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false'); 1152 1153RETURN v_partition_created; 1154 1155EXCEPTION 1156 WHEN OTHERS THEN 1157 GET STACKED DIAGNOSTICS ex_message = MESSAGE_TEXT, 1158 ex_context = PG_EXCEPTION_CONTEXT, 1159 ex_detail = PG_EXCEPTION_DETAIL, 1160 ex_hint = PG_EXCEPTION_HINT; 1161 IF v_jobmon_schema IS NOT NULL THEN 1162 IF v_job_id IS NULL THEN 1163 EXECUTE format('SELECT %I.add_job(''PARTMAN CREATE TABLE: %s'')', v_jobmon_schema, p_parent_table) INTO v_job_id; 1164 EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before job logging started'')', v_jobmon_schema, v_job_id, p_parent_table) INTO v_step_id; 1165 ELSIF v_step_id IS NULL THEN 1166 EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before first step logged'')', v_jobmon_schema, v_job_id) INTO v_step_id; 1167 END IF; 1168 EXECUTE format('SELECT %I.update_step(%s, ''CRITICAL'', %L)', v_jobmon_schema, v_step_id, 'ERROR: '||coalesce(SQLERRM,'unknown')); 1169 EXECUTE format('SELECT %I.fail_job(%s)', v_jobmon_schema, v_job_id); 1170 END IF; 1171 RAISE EXCEPTION '% 1172CONTEXT: % 1173DETAIL: % 1174HINT: %', ex_message, ex_context, ex_detail, ex_hint; 1175END 1176$$; 1177 1178 1179 1180CREATE OR REPLACE FUNCTION @extschema@.create_partition_time(p_parent_table text, p_partition_times timestamptz[], p_analyze boolean DEFAULT true, p_debug boolean DEFAULT false) 1181RETURNS boolean 1182 LANGUAGE plpgsql 1183 AS $$ 1184DECLARE 1185 1186ex_context text; 1187ex_detail text; 1188ex_hint text; 1189ex_message text; 1190v_all text[] := ARRAY['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER']; 1191v_analyze boolean := FALSE; 1192v_control text; 1193v_control_type text; 1194v_datetime_string text; 1195v_epoch text; 1196v_exists smallint; 1197v_grantees text[]; 1198v_hasoids boolean; 1199v_inherit_privileges boolean; 1200v_inherit_fk boolean; 1201v_job_id bigint; 1202v_jobmon boolean; 1203v_jobmon_schema text; 1204v_new_search_path text := '@extschema@,pg_temp'; 1205v_old_search_path text; 1206v_parent_grant record; 1207v_parent_schema text; 1208v_parent_tablename text; 1209v_part_col text; 1210v_partition_created boolean := false; 1211v_partition_name text; 1212v_partition_suffix text; 1213v_parent_tablespace text; 1214v_partition_expression text; 1215v_partition_interval interval; 1216v_partition_timestamp_end timestamptz; 1217v_partition_timestamp_start timestamptz; 1218v_publications text[]; 1219v_quarter text; 1220v_revoke text; 1221v_row record; 1222v_sql text; 1223v_step_id bigint; 1224v_step_overflow_id bigint; 1225v_sub_control text; 1226v_sub_parent text; 1227v_sub_partition_type text; 1228v_sub_timestamp_max timestamptz; 1229v_sub_timestamp_min timestamptz; 1230v_template_table text; 1231v_trunc_value text; 1232v_time timestamptz; 1233v_partition_type text; 1234v_unlogged char; 1235v_year text; 1236 1237BEGIN 1238/* 1239 * Function to create a child table in a time-based partition set 1240 */ 1241 1242SELECT partition_type 1243 , control 1244 , partition_interval 1245 , epoch 1246 , inherit_fk 1247 , jobmon 1248 , datetime_string 1249 , template_table 1250 , publications 1251 , inherit_privileges 1252INTO v_partition_type 1253 , v_control 1254 , v_partition_interval 1255 , v_epoch 1256 , v_inherit_fk 1257 , v_jobmon 1258 , v_datetime_string 1259 , v_template_table 1260 , v_publications 1261 , v_inherit_privileges 1262FROM @extschema@.part_config 1263WHERE parent_table = p_parent_table; 1264 1265IF NOT FOUND THEN 1266 RAISE EXCEPTION 'ERROR: no config found for %', p_parent_table; 1267END IF; 1268 1269SELECT n.nspname, c.relname, t.spcname 1270INTO v_parent_schema, v_parent_tablename, v_parent_tablespace 1271FROM pg_catalog.pg_class c 1272JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 1273LEFT OUTER JOIN pg_catalog.pg_tablespace t ON c.reltablespace = t.oid 1274WHERE n.nspname = split_part(p_parent_table, '.', 1)::name 1275AND c.relname = split_part(p_parent_table, '.', 2)::name; 1276 1277SELECT general_type INTO v_control_type FROM @extschema@.check_control_type(v_parent_schema, v_parent_tablename, v_control); 1278IF v_control_type <> 'time' THEN 1279 IF (v_control_type = 'id' AND v_epoch = 'none') OR v_control_type <> 'id' THEN 1280 RAISE EXCEPTION 'Cannot run on partition set without time based control column or epoch flag set with an id column. Found control: %, epoch: %', v_control_type, v_epoch; 1281 END IF; 1282END IF; 1283 1284SELECT current_setting('search_path') INTO v_old_search_path; 1285IF v_jobmon THEN 1286 SELECT nspname INTO v_jobmon_schema FROM pg_catalog.pg_namespace n, pg_catalog.pg_extension e WHERE e.extname = 'pg_jobmon'::name AND e.extnamespace = n.oid; 1287 IF v_jobmon_schema IS NOT NULL THEN 1288 v_new_search_path := '@extschema@,'||v_jobmon_schema||',pg_temp'; 1289 END IF; 1290END IF; 1291EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_new_search_path, 'false'); 1292 1293-- Determine if this table is a child of a subpartition parent. If so, get limits of what child tables can be created based on parent suffix 1294SELECT sub_min::timestamptz, sub_max::timestamptz INTO v_sub_timestamp_min, v_sub_timestamp_max FROM @extschema@.check_subpartition_limits(p_parent_table, 'time'); 1295 1296IF v_jobmon_schema IS NOT NULL THEN 1297 v_job_id := add_job(format('PARTMAN CREATE TABLE: %s', p_parent_table)); 1298END IF; 1299 1300v_partition_expression := CASE 1301 WHEN v_epoch = 'seconds' THEN format('to_timestamp(%I)', v_control) 1302 WHEN v_epoch = 'milliseconds' THEN format('to_timestamp((%I/1000)::float)', v_control) 1303 ELSE format('%I', v_control) 1304END; 1305IF p_debug THEN 1306 RAISE NOTICE 'create_partition_time: v_partition_expression: %', v_partition_expression; 1307END IF; 1308 1309FOREACH v_time IN ARRAY p_partition_times LOOP 1310 v_partition_timestamp_start := v_time; 1311 BEGIN 1312 v_partition_timestamp_end := v_time + v_partition_interval; 1313 EXCEPTION WHEN datetime_field_overflow THEN 1314 RAISE WARNING 'Attempted partition time interval is outside PostgreSQL''s supported time range. 1315 Child partition creation after time % skipped', v_time; 1316 v_step_overflow_id := add_step(v_job_id, 'Attempted partition time interval is outside PostgreSQL''s supported time range.'); 1317 PERFORM update_step(v_step_overflow_id, 'CRITICAL', 'Child partition creation after time '||v_time||' skipped'); 1318 1319 CONTINUE; 1320 END; 1321 1322 -- Do not create the child table if it's outside the bounds of the top parent. 1323 IF v_sub_timestamp_min IS NOT NULL THEN 1324 IF v_time < v_sub_timestamp_min OR v_time > v_sub_timestamp_max THEN 1325 CONTINUE; 1326 END IF; 1327 END IF; 1328 1329 -- This suffix generation code is in partition_data_time() as well 1330 v_partition_suffix := to_char(v_time, v_datetime_string); 1331 v_partition_name := @extschema@.check_name_length(v_parent_tablename, v_partition_suffix, TRUE); 1332 -- Check if child exists. 1333 SELECT count(*) INTO v_exists 1334 FROM pg_catalog.pg_class c 1335 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 1336 WHERE n.nspname = v_parent_schema::name 1337 AND c.relname = v_partition_name::name; 1338 1339 IF v_exists > 0 THEN 1340 CONTINUE; 1341 END IF; 1342 1343 -- Ensure analyze is run if a new partition is created. Otherwise if one isn't, will be false and analyze will be skipped 1344 v_analyze := TRUE; 1345 1346 IF v_jobmon_schema IS NOT NULL THEN 1347 v_step_id := add_step(v_job_id, format('Creating new partition %s.%s with interval from %s to %s' 1348 , v_parent_schema 1349 , v_partition_name 1350 , v_partition_timestamp_start 1351 , v_partition_timestamp_end-'1sec'::interval)); 1352 END IF; 1353 1354 v_sql := 'CREATE'; 1355 1356 -- As of PG12, the unlogged/logged status of a native parent table cannot be changed via an ALTER TABLE in order to affect its children. 1357 -- As of v4.2x, the unlogged state will be managed via the template table 1358 SELECT relpersistence INTO v_unlogged 1359 FROM pg_catalog.pg_class c 1360 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 1361 WHERE c.relname = v_parent_tablename::name 1362 AND n.nspname = v_parent_schema::name; 1363 IF v_unlogged = 'u' and v_partition_type != 'native' THEN 1364 v_sql := v_sql || ' UNLOGGED'; 1365 END IF; 1366 1367 -- Close parentheses on LIKE are below due to differing requirements of native subpartitioning 1368 -- Same INCLUDING list is used in create_parent() 1369 v_sql := v_sql || format(' TABLE %I.%I (LIKE %I.%I INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING STORAGE INCLUDING COMMENTS ' 1370 , v_parent_schema 1371 , v_partition_name 1372 , v_parent_schema 1373 , v_parent_tablename); 1374 1375 SELECT sub_partition_type, sub_control INTO v_sub_partition_type, v_sub_control 1376 FROM @extschema@.part_config_sub 1377 WHERE sub_parent = p_parent_table; 1378 IF v_sub_partition_type = 'native' THEN 1379 -- NOTE: Need to handle this differently when index inheritance is supported natively 1380 -- Cannot include indexes since they cannot exist on native parents 1381 v_sql := v_sql || format(') PARTITION BY RANGE (%I) ', v_sub_control); 1382 ELSE 1383 v_sql := v_sql || format(' INCLUDING INDEXES) ', v_sub_control); 1384 END IF; 1385 1386 IF current_setting('server_version_num')::int < 120000 THEN 1387 -- column removed from pgclass in pg12 1388 SELECT relhasoids INTO v_hasoids 1389 FROM pg_catalog.pg_class c 1390 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 1391 WHERE c.relname = v_parent_tablename::name 1392 AND n.nspname = v_parent_schema::name; 1393 IF v_hasoids IS TRUE THEN 1394 v_sql := v_sql || ' WITH (OIDS)'; 1395 END IF; 1396 END IF; 1397 1398 IF p_debug THEN 1399 RAISE NOTICE 'create_partition_time v_sql: %', v_sql; 1400 END IF; 1401 EXECUTE v_sql; 1402 1403 IF v_parent_tablespace IS NOT NULL THEN 1404 EXECUTE format('ALTER TABLE %I.%I SET TABLESPACE %I', v_parent_schema, v_partition_name, v_parent_tablespace); 1405 END IF; 1406 1407 IF v_partition_type = 'native' THEN 1408 1409 IF v_template_table IS NOT NULL THEN 1410 PERFORM @extschema@.inherit_template_properties(p_parent_table, v_parent_schema, v_partition_name); 1411 END IF; 1412 1413 IF v_epoch = 'none' THEN 1414 -- Attach with normal, time-based values for native constraint 1415 EXECUTE format('ALTER TABLE %I.%I ATTACH PARTITION %I.%I FOR VALUES FROM (%L) TO (%L)' 1416 , v_parent_schema 1417 , v_parent_tablename 1418 , v_parent_schema 1419 , v_partition_name 1420 , v_partition_timestamp_start 1421 , v_partition_timestamp_end); 1422 ELSE 1423 -- Must attach with integer based values for native constraint and epoch 1424 IF v_epoch = 'seconds' THEN 1425 EXECUTE format('ALTER TABLE %I.%I ATTACH PARTITION %I.%I FOR VALUES FROM (%L) TO (%L)' 1426 , v_parent_schema 1427 , v_parent_tablename 1428 , v_parent_schema 1429 , v_partition_name 1430 , EXTRACT('epoch' FROM v_partition_timestamp_start) 1431 , EXTRACT('epoch' FROM v_partition_timestamp_end)); 1432 ELSIF v_epoch = 'milliseconds' THEN 1433 EXECUTE format('ALTER TABLE %I.%I ATTACH PARTITION %I.%I FOR VALUES FROM (%L) TO (%L)' 1434 , v_parent_schema 1435 , v_parent_tablename 1436 , v_parent_schema 1437 , v_partition_name 1438 , EXTRACT('epoch' FROM v_partition_timestamp_start) * 1000 1439 , EXTRACT('epoch' FROM v_partition_timestamp_end) * 1000); 1440 END IF; 1441 -- Create secondary, time-based constraint since native's constraint is already integer based 1442 EXECUTE format('ALTER TABLE %I.%I ADD CONSTRAINT %I CHECK (%s >= %L AND %4$s < %6$L)' 1443 , v_parent_schema 1444 , v_partition_name 1445 , v_partition_name||'_partition_check' 1446 , v_partition_expression 1447 , v_partition_timestamp_start 1448 , v_partition_timestamp_end); 1449 END IF; 1450 ELSE 1451 -- Non-native always gets time-based constraint 1452 EXECUTE format('ALTER TABLE %I.%I ADD CONSTRAINT %I CHECK (%s >= %L AND %4$s < %6$L)' 1453 , v_parent_schema 1454 , v_partition_name 1455 , v_partition_name||'_partition_check' 1456 , v_partition_expression 1457 , v_partition_timestamp_start 1458 , v_partition_timestamp_end); 1459 IF v_epoch = 'seconds' THEN 1460 -- Non-native needs secondary, integer based constraint for epoch 1461 EXECUTE format('ALTER TABLE %I.%I ADD CONSTRAINT %I CHECK (%I >= %L AND %I < %L)' 1462 , v_parent_schema 1463 , v_partition_name 1464 , v_partition_name||'_partition_int_check' 1465 , v_control 1466 , EXTRACT('epoch' from v_partition_timestamp_start) 1467 , v_control 1468 , EXTRACT('epoch' from v_partition_timestamp_end) ); 1469 ELSIF v_epoch = 'milliseconds' THEN 1470 EXECUTE format('ALTER TABLE %I.%I ADD CONSTRAINT %I CHECK (%I >= %L AND %I < %L)' 1471 , v_parent_schema 1472 , v_partition_name 1473 , v_partition_name||'_partition_int_check' 1474 , v_control 1475 , EXTRACT('epoch' from v_partition_timestamp_start) * 1000 1476 , v_control 1477 , EXTRACT('epoch' from v_partition_timestamp_end) * 1000); 1478 END IF; 1479 1480 EXECUTE format('ALTER TABLE %I.%I INHERIT %I.%I' 1481 , v_parent_schema 1482 , v_partition_name 1483 , v_parent_schema 1484 , v_parent_tablename); 1485 1486 -- If custom time, set extra config options. 1487 IF v_partition_type = 'time-custom' THEN 1488 INSERT INTO @extschema@.custom_time_partitions (parent_table, child_table, partition_range) 1489 VALUES ( p_parent_table, v_parent_schema||'.'||v_partition_name, tstzrange(v_partition_timestamp_start, v_partition_timestamp_end, '[)') ); 1490 END IF; 1491 1492 -- Indexes cannot be created on the parent, so clustering cannot be used for native yet. 1493 PERFORM @extschema@.apply_cluster(v_parent_schema, v_parent_tablename, v_parent_schema, v_partition_name); 1494 1495 -- Foreign keys to other tables not supported in native 1496 IF v_inherit_fk THEN 1497 PERFORM @extschema@.apply_foreign_keys(p_parent_table, v_parent_schema||'.'||v_partition_name, v_job_id); 1498 END IF; 1499 1500 END IF; -- end native check 1501 1502 -- NOTE: Privileges not automatically inherited for native. Only do so if config flag is set 1503 IF v_partition_type != 'native' OR (v_partition_type = 'native' AND v_inherit_privileges = TRUE) THEN 1504 PERFORM @extschema@.apply_privileges(v_parent_schema, v_parent_tablename, v_parent_schema, v_partition_name, v_job_id); 1505 END IF; 1506 1507 IF v_jobmon_schema IS NOT NULL THEN 1508 PERFORM update_step(v_step_id, 'OK', 'Done'); 1509 END IF; 1510 1511 -- Will only loop once and only if sub_partitioning is actually configured 1512 -- This seemed easier than assigning a bunch of variables then doing an IF condition 1513 FOR v_row IN 1514 SELECT sub_parent 1515 , sub_partition_type 1516 , sub_control 1517 , sub_partition_interval 1518 , sub_constraint_cols 1519 , sub_premake 1520 , sub_optimize_trigger 1521 , sub_optimize_constraint 1522 , sub_epoch 1523 , sub_inherit_fk 1524 , sub_retention 1525 , sub_retention_schema 1526 , sub_retention_keep_table 1527 , sub_retention_keep_index 1528 , sub_automatic_maintenance 1529 , sub_infinite_time_partitions 1530 , sub_jobmon 1531 , sub_trigger_exception_handling 1532 , sub_template_table 1533 , sub_inherit_privileges 1534 , sub_constraint_valid 1535 FROM @extschema@.part_config_sub 1536 WHERE sub_parent = p_parent_table 1537 LOOP 1538 IF v_jobmon_schema IS NOT NULL THEN 1539 v_step_id := add_step(v_job_id, format('Subpartitioning %s.%s', v_parent_schema, v_partition_name)); 1540 END IF; 1541 v_sql := format('SELECT @extschema@.create_parent( 1542 p_parent_table := %L 1543 , p_control := %L 1544 , p_type := %L 1545 , p_interval := %L 1546 , p_constraint_cols := %L 1547 , p_premake := %L 1548 , p_automatic_maintenance := %L 1549 , p_inherit_fk := %L 1550 , p_epoch := %L 1551 , p_template_table := %L 1552 , p_jobmon := %L )' 1553 , v_parent_schema||'.'||v_partition_name 1554 , v_row.sub_control 1555 , v_row.sub_partition_type 1556 , v_row.sub_partition_interval 1557 , v_row.sub_constraint_cols 1558 , v_row.sub_premake 1559 , v_row.sub_automatic_maintenance 1560 , v_row.sub_inherit_fk 1561 , v_row.sub_epoch 1562 , v_row.sub_template_table 1563 , v_row.sub_jobmon); 1564 IF p_debug THEN 1565 RAISE NOTICE 'create_partition_time (create_parent loop): %', v_sql; 1566 END IF; 1567 EXECUTE v_sql; 1568 1569 UPDATE @extschema@.part_config SET 1570 retention_schema = v_row.sub_retention_schema 1571 , retention_keep_table = v_row.sub_retention_keep_table 1572 , retention_keep_index = v_row.sub_retention_keep_index 1573 , optimize_trigger = v_row.sub_optimize_trigger 1574 , optimize_constraint = v_row.sub_optimize_constraint 1575 , infinite_time_partitions = v_row.sub_infinite_time_partitions 1576 , trigger_exception_handling = v_row.sub_trigger_exception_handling 1577 , inherit_privileges = v_row.sub_inherit_privileges 1578 , constraint_valid = v_row.sub_constraint_valid 1579 WHERE parent_table = v_parent_schema||'.'||v_partition_name; 1580 1581 END LOOP; -- end sub partitioning LOOP 1582 1583 -- Manage additonal constraints if set 1584 PERFORM @extschema@.apply_constraints(p_parent_table, p_job_id := v_job_id, p_debug := p_debug); 1585 1586 IF v_publications IS NOT NULL THEN 1587 -- NOTE: Publications currently not supported on parent table, but are supported on the table partitions if individually assigned. 1588 PERFORM @extschema@.apply_publications(p_parent_table, v_parent_schema, v_partition_name); 1589 END IF; 1590 1591 v_partition_created := true; 1592 1593END LOOP; 1594-- v_analyze is a local check if a new table is made. 1595-- 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. 1596IF v_analyze AND p_analyze THEN 1597 IF v_jobmon_schema IS NOT NULL THEN 1598 v_step_id := add_step(v_job_id, format('Analyzing partition set: %s', p_parent_table)); 1599 END IF; 1600 1601 EXECUTE format('ANALYZE %I.%I', v_parent_schema, v_parent_tablename); 1602 1603 IF v_jobmon_schema IS NOT NULL THEN 1604 PERFORM update_step(v_step_id, 'OK', 'Done'); 1605 END IF; 1606END IF; 1607 1608IF v_jobmon_schema IS NOT NULL THEN 1609 IF v_partition_created = false THEN 1610 v_step_id := add_step(v_job_id, format('No partitions created for partition set: %s. Attempted intervals: %s', p_parent_table, p_partition_times)); 1611 PERFORM update_step(v_step_id, 'OK', 'Done'); 1612 END IF; 1613 1614 IF v_step_overflow_id IS NOT NULL THEN 1615 PERFORM fail_job(v_job_id); 1616 ELSE 1617 PERFORM close_job(v_job_id); 1618 END IF; 1619END IF; 1620 1621EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false'); 1622 1623RETURN v_partition_created; 1624 1625EXCEPTION 1626 WHEN OTHERS THEN 1627 GET STACKED DIAGNOSTICS ex_message = MESSAGE_TEXT, 1628 ex_context = PG_EXCEPTION_CONTEXT, 1629 ex_detail = PG_EXCEPTION_DETAIL, 1630 ex_hint = PG_EXCEPTION_HINT; 1631 IF v_jobmon_schema IS NOT NULL THEN 1632 IF v_job_id IS NULL THEN 1633 EXECUTE format('SELECT %I.add_job(''PARTMAN CREATE TABLE: %s'')', v_jobmon_schema, p_parent_table) INTO v_job_id; 1634 EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before job logging started'')', v_jobmon_schema, v_job_id, p_parent_table) INTO v_step_id; 1635 ELSIF v_step_id IS NULL THEN 1636 EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before first step logged'')', v_jobmon_schema, v_job_id) INTO v_step_id; 1637 END IF; 1638 EXECUTE format('SELECT %I.update_step(%s, ''CRITICAL'', %L)', v_jobmon_schema, v_step_id, 'ERROR: '||coalesce(SQLERRM,'unknown')); 1639 EXECUTE format('SELECT %I.fail_job(%s)', v_jobmon_schema, v_job_id); 1640 END IF; 1641 RAISE EXCEPTION '% 1642CONTEXT: % 1643DETAIL: % 1644HINT: %', ex_message, ex_context, ex_detail, ex_hint; 1645END 1646$$; 1647 1648 1649CREATE OR REPLACE FUNCTION @extschema@.drop_partition_time(p_parent_table text, p_retention interval DEFAULT NULL, p_keep_table boolean DEFAULT NULL, p_keep_index boolean DEFAULT NULL, p_retention_schema text DEFAULT NULL) RETURNS int 1650 LANGUAGE plpgsql 1651 AS $$ 1652DECLARE 1653 1654ex_context text; 1655ex_detail text; 1656ex_hint text; 1657ex_message text; 1658v_adv_lock boolean; 1659v_control text; 1660v_control_type text; 1661v_count int; 1662v_datetime_string text; 1663v_drop_count int := 0; 1664v_epoch text; 1665v_index record; 1666v_job_id bigint; 1667v_jobmon boolean; 1668v_jobmon_schema text; 1669v_new_search_path text := '@extschema@,pg_temp'; 1670v_old_search_path text; 1671v_parent_schema text; 1672v_parent_tablename text; 1673v_partition_interval interval; 1674v_partition_timestamp timestamptz; 1675v_partition_type text; 1676v_retention interval; 1677v_retention_keep_index boolean; 1678v_retention_keep_table boolean; 1679v_retention_schema text; 1680v_row record; 1681v_step_id bigint; 1682 1683BEGIN 1684/* 1685 * Function to drop child tables from a time-based partition set. 1686 * Options to move table to different schema, drop only indexes or actually drop the table from the database. 1687 */ 1688 1689v_adv_lock := pg_try_advisory_xact_lock(hashtext('pg_partman drop_partition_time')); 1690IF v_adv_lock = 'false' THEN 1691 RAISE NOTICE 'drop_partition_time already running.'; 1692 RETURN 0; 1693END IF; 1694 1695-- Allow override of configuration options 1696IF p_retention IS NULL THEN 1697 SELECT 1698 partition_type 1699 , control 1700 , partition_interval::interval 1701 , epoch 1702 , retention::interval 1703 , retention_keep_table 1704 , retention_keep_index 1705 , datetime_string 1706 , retention_schema 1707 , jobmon 1708 INTO 1709 v_partition_type 1710 , v_control 1711 , v_partition_interval 1712 , v_epoch 1713 , v_retention 1714 , v_retention_keep_table 1715 , v_retention_keep_index 1716 , v_datetime_string 1717 , v_retention_schema 1718 , v_jobmon 1719 FROM @extschema@.part_config 1720 WHERE parent_table = p_parent_table 1721 AND retention IS NOT NULL; 1722 1723 IF v_partition_interval IS NULL THEN 1724 RAISE EXCEPTION 'Configuration for given parent table with a retention period not found: %', p_parent_table; 1725 END IF; 1726ELSE 1727 SELECT 1728 partition_type 1729 , partition_interval::interval 1730 , epoch 1731 , retention_keep_table 1732 , retention_keep_index 1733 , datetime_string 1734 , retention_schema 1735 , jobmon 1736 INTO 1737 v_partition_type 1738 , v_partition_interval 1739 , v_epoch 1740 , v_retention_keep_table 1741 , v_retention_keep_index 1742 , v_datetime_string 1743 , v_retention_schema 1744 , v_jobmon 1745 FROM @extschema@.part_config 1746 WHERE parent_table = p_parent_table; 1747 v_retention := p_retention; 1748 1749 IF v_partition_interval IS NULL THEN 1750 RAISE EXCEPTION 'Configuration for given parent table not found: %', p_parent_table; 1751 END IF; 1752END IF; 1753 1754SELECT general_type INTO v_control_type FROM @extschema@.check_control_type(v_parent_schema, v_parent_tablename, v_control); 1755IF v_control_type <> 'time' THEN 1756 IF (v_control_type = 'id' AND v_epoch = 'none') OR v_control_type <> 'id' THEN 1757 RAISE EXCEPTION 'Cannot run on partition set without time based control column or epoch flag set with an id column. Found control: %, epoch: %', v_control_type, v_epoch; 1758 END IF; 1759END IF; 1760 1761SELECT current_setting('search_path') INTO v_old_search_path; 1762IF v_jobmon THEN 1763 SELECT nspname INTO v_jobmon_schema FROM pg_catalog.pg_namespace n, pg_catalog.pg_extension e WHERE e.extname = 'pg_jobmon'::name AND e.extnamespace = n.oid; 1764 IF v_jobmon_schema IS NOT NULL THEN 1765 v_new_search_path := '@extschema@,'||v_jobmon_schema||',pg_temp'; 1766 END IF; 1767END IF; 1768EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_new_search_path, 'false'); 1769 1770IF p_keep_table IS NOT NULL THEN 1771 v_retention_keep_table = p_keep_table; 1772END IF; 1773IF p_keep_index IS NOT NULL THEN 1774 v_retention_keep_index = p_keep_index; 1775END IF; 1776IF p_retention_schema IS NOT NULL THEN 1777 v_retention_schema = p_retention_schema; 1778END IF; 1779 1780SELECT schemaname, tablename INTO v_parent_schema, v_parent_tablename 1781FROM pg_catalog.pg_tables 1782WHERE schemaname = split_part(p_parent_table, '.', 1)::name 1783AND tablename = split_part(p_parent_table, '.', 2)::name; 1784 1785-- Loop through child tables of the given parent 1786-- Must go in ascending order to avoid dropping what may be the "last" partition in the set after dropping tables that match retention period 1787FOR v_row IN 1788 SELECT partition_schemaname, partition_tablename FROM @extschema@.show_partitions(p_parent_table, 'ASC') 1789LOOP 1790 -- pull out datetime portion of partition's tablename to make the next one 1791 SELECT child_start_time INTO v_partition_timestamp FROM @extschema@.show_partition_info(v_row.partition_schemaname||'.'||v_row.partition_tablename 1792 , v_partition_interval::text 1793 , p_parent_table); 1794 -- Add one interval since partition names contain the start of the constraint period 1795 IF v_retention < (CURRENT_TIMESTAMP - (v_partition_timestamp + v_partition_interval)) THEN 1796 1797 -- Do not allow final partition to be dropped 1798 SELECT count(*) INTO v_count FROM @extschema@.show_partitions(p_parent_table); 1799 IF v_count = 1 THEN 1800 RAISE WARNING 'Attempt to drop final partition in partition set % as part of retention policy. If you see this message multiple times for the same table, advise reviewing retention policy and/or data entry into the partition set. Also consider setting "infinite_time_partitions = true" if there are large gaps in data insertion.).', p_parent_table; 1801 CONTINUE; 1802 END IF; 1803 1804 -- Only create a jobmon entry if there's actual retention work done 1805 IF v_jobmon_schema IS NOT NULL AND v_job_id IS NULL THEN 1806 v_job_id := add_job(format('PARTMAN DROP TIME PARTITION: %s', p_parent_table)); 1807 END IF; 1808 1809 IF v_jobmon_schema IS NOT NULL THEN 1810 v_step_id := add_step(v_job_id, format('Detach/Uninherit table %s.%s from %s' 1811 , v_row.partition_schemaname 1812 , v_row.partition_tablename 1813 , p_parent_table)); 1814 END IF; 1815 IF v_partition_type = 'native' THEN 1816 EXECUTE format('ALTER TABLE %I.%I DETACH PARTITION %I.%I' 1817 , v_parent_schema 1818 , v_parent_tablename 1819 , v_row.partition_schemaname 1820 , v_row.partition_tablename); 1821 ELSE 1822 EXECUTE format('ALTER TABLE %I.%I NO INHERIT %I.%I' 1823 , v_row.partition_schemaname 1824 , v_row.partition_tablename 1825 , v_parent_schema 1826 , v_parent_tablename); 1827 END IF; 1828 IF v_partition_type = 'time-custom' THEN 1829 DELETE FROM @extschema@.custom_time_partitions WHERE parent_table = p_parent_table AND child_table = v_row.partition_schemaname||'.'||v_row.partition_tablename; 1830 END IF; 1831 IF v_jobmon_schema IS NOT NULL THEN 1832 PERFORM update_step(v_step_id, 'OK', 'Done'); 1833 END IF; 1834 IF v_retention_schema IS NULL THEN 1835 IF v_retention_keep_table = false THEN 1836 IF v_jobmon_schema IS NOT NULL THEN 1837 v_step_id := add_step(v_job_id, format('Drop table %s.%s', v_row.partition_schemaname, v_row.partition_tablename)); 1838 END IF; 1839 EXECUTE format('DROP TABLE %I.%I CASCADE', v_row.partition_schemaname, v_row.partition_tablename); 1840 IF v_jobmon_schema IS NOT NULL THEN 1841 PERFORM update_step(v_step_id, 'OK', 'Done'); 1842 END IF; 1843 ELSIF v_retention_keep_index = false THEN 1844 IF v_partition_type = 'partman' OR 1845 ( v_partition_type = 'native' AND current_setting('server_version_num')::int < 110000) THEN 1846 -- Cannot drop child indexes on native partition sets in PG11+ 1847 FOR v_index IN 1848 WITH child_info AS ( 1849 SELECT c1.oid 1850 FROM pg_catalog.pg_class c1 1851 JOIN pg_catalog.pg_namespace n1 ON c1.relnamespace = n1.oid 1852 WHERE c1.relname = v_row.partition_tablename::name 1853 AND n1.nspname = v_row.partition_schemaname::name 1854 ) 1855 SELECT c.relname as name 1856 , con.conname 1857 FROM pg_catalog.pg_index i 1858 JOIN pg_catalog.pg_class c ON i.indexrelid = c.oid 1859 LEFT JOIN pg_catalog.pg_constraint con ON i.indexrelid = con.conindid 1860 JOIN child_info ON i.indrelid = child_info.oid 1861 LOOP 1862 IF v_jobmon_schema IS NOT NULL THEN 1863 v_step_id := add_step(v_job_id, format('Drop index %s from %s.%s' 1864 , v_index.name 1865 , v_row.partition_schemaname 1866 , v_row.partition_tablename)); 1867 END IF; 1868 IF v_index.conname IS NOT NULL THEN 1869 EXECUTE format('ALTER TABLE %I.%I DROP CONSTRAINT %I' 1870 , v_row.partition_schemaname 1871 , v_row.partition_tablename 1872 , v_index.conname); 1873 ELSE 1874 EXECUTE format('DROP INDEX %I.%I', v_parent_schema, v_index.name); 1875 END IF; 1876 IF v_jobmon_schema IS NOT NULL THEN 1877 PERFORM update_step(v_step_id, 'OK', 'Done'); 1878 END IF; 1879 END LOOP; 1880 END IF; -- end native/11 check 1881 END IF; -- end v_retention_keep_index IF 1882 ELSE -- Move to new schema 1883 IF v_jobmon_schema IS NOT NULL THEN 1884 v_step_id := add_step(v_job_id, format('Moving table %s.%s to schema %s' 1885 , v_row.partition_schemaname 1886 , v_row.partition_tablename 1887 , v_retention_schema)); 1888 END IF; 1889 1890 EXECUTE format('ALTER TABLE %I.%I SET SCHEMA %I', v_row.partition_schemaname, v_row.partition_tablename, v_retention_schema); 1891 1892 1893 IF v_jobmon_schema IS NOT NULL THEN 1894 PERFORM update_step(v_step_id, 'OK', 'Done'); 1895 END IF; 1896 END IF; -- End retention schema if 1897 1898 -- If child table is a subpartition, remove it from part_config & part_config_sub (should cascade due to FK) 1899 DELETE FROM @extschema@.part_config WHERE parent_table = v_row.partition_schemaname||'.'||v_row.partition_tablename; 1900 1901 v_drop_count := v_drop_count + 1; 1902 END IF; -- End retention check IF 1903 1904END LOOP; -- End child table loop 1905 1906IF v_jobmon_schema IS NOT NULL THEN 1907 IF v_job_id IS NOT NULL THEN 1908 v_step_id := add_step(v_job_id, 'Finished partition drop maintenance'); 1909 PERFORM update_step(v_step_id, 'OK', format('%s partitions dropped.', v_drop_count)); 1910 PERFORM close_job(v_job_id); 1911 END IF; 1912END IF; 1913 1914EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false'); 1915 1916RETURN v_drop_count; 1917 1918EXCEPTION 1919 WHEN OTHERS THEN 1920 GET STACKED DIAGNOSTICS ex_message = MESSAGE_TEXT, 1921 ex_context = PG_EXCEPTION_CONTEXT, 1922 ex_detail = PG_EXCEPTION_DETAIL, 1923 ex_hint = PG_EXCEPTION_HINT; 1924 IF v_jobmon_schema IS NOT NULL THEN 1925 IF v_job_id IS NULL THEN 1926 EXECUTE format('SELECT %I.add_job(''PARTMAN DROP TIME PARTITION: %s'')', v_jobmon_schema, p_parent_table) INTO v_job_id; 1927 EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before job logging started'')', v_jobmon_schema, v_job_id, p_parent_table) INTO v_step_id; 1928 ELSIF v_step_id IS NULL THEN 1929 EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before first step logged'')', v_jobmon_schema, v_job_id) INTO v_step_id; 1930 END IF; 1931 EXECUTE format('SELECT %I.update_step(%s, ''CRITICAL'', %L)', v_jobmon_schema, v_step_id, 'ERROR: '||coalesce(SQLERRM,'unknown')); 1932 EXECUTE format('SELECT %I.fail_job(%s)', v_jobmon_schema, v_job_id); 1933 END IF; 1934 RAISE EXCEPTION '% 1935CONTEXT: % 1936DETAIL: % 1937HINT: %', ex_message, ex_context, ex_detail, ex_hint; 1938END 1939$$; 1940 1941 1942CREATE OR REPLACE FUNCTION @extschema@.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 1943 LANGUAGE plpgsql 1944 AS $$ 1945DECLARE 1946 1947ex_context text; 1948ex_detail text; 1949ex_hint text; 1950ex_message text; 1951v_adv_lock boolean; 1952v_control text; 1953v_control_type text; 1954v_count int; 1955v_drop_count int := 0; 1956v_index record; 1957v_job_id bigint; 1958v_jobmon boolean; 1959v_jobmon_schema text; 1960v_max bigint; 1961v_new_search_path text := '@extschema@,pg_temp'; 1962v_old_search_path text; 1963v_parent_schema text; 1964v_parent_tablename text; 1965v_partition_interval bigint; 1966v_partition_id bigint; 1967v_partition_type text; 1968v_retention bigint; 1969v_retention_keep_index boolean; 1970v_retention_keep_table boolean; 1971v_retention_schema text; 1972v_row record; 1973v_row_max_id record; 1974v_step_id bigint; 1975 1976BEGIN 1977/* 1978 * Function to drop child tables from an id-based partition set. 1979 * Options to move table to different schema, drop only indexes or actually drop the table from the database. 1980 */ 1981 1982v_adv_lock := pg_try_advisory_xact_lock(hashtext('pg_partman drop_partition_id')); 1983IF v_adv_lock = 'false' THEN 1984 RAISE NOTICE 'drop_partition_id already running.'; 1985 RETURN 0; 1986END IF; 1987 1988IF p_retention IS NULL THEN 1989 SELECT 1990 partition_interval::bigint 1991 , partition_type 1992 , control 1993 , retention::bigint 1994 , retention_keep_table 1995 , retention_keep_index 1996 , retention_schema 1997 , jobmon 1998 INTO 1999 v_partition_interval 2000 , v_partition_type 2001 , v_control 2002 , v_retention 2003 , v_retention_keep_table 2004 , v_retention_keep_index 2005 , v_retention_schema 2006 , v_jobmon 2007 FROM @extschema@.part_config 2008 WHERE parent_table = p_parent_table 2009 AND retention IS NOT NULL; 2010 2011 IF v_partition_interval IS NULL THEN 2012 RAISE EXCEPTION 'Configuration for given parent table with a retention period not found: %', p_parent_table; 2013 END IF; 2014ELSE -- Allow override of configuration options 2015 SELECT 2016 partition_interval::bigint 2017 , partition_type 2018 , control 2019 , retention_keep_table 2020 , retention_keep_index 2021 , retention_schema 2022 , jobmon 2023 INTO 2024 v_partition_interval 2025 , v_partition_type 2026 , v_control 2027 , v_retention_keep_table 2028 , v_retention_keep_index 2029 , v_retention_schema 2030 , v_jobmon 2031 FROM @extschema@.part_config 2032 WHERE parent_table = p_parent_table; 2033 v_retention := p_retention; 2034 2035 IF v_partition_interval IS NULL THEN 2036 RAISE EXCEPTION 'Configuration for given parent table not found: %', p_parent_table; 2037 END IF; 2038END IF; 2039 2040SELECT general_type INTO v_control_type FROM @extschema@.check_control_type(v_parent_schema, v_parent_tablename, v_control); 2041IF v_control_type <> 'id' THEN 2042 RAISE EXCEPTION 'Data type of control column in given partition set is not an integer type'; 2043END IF; 2044 2045SELECT current_setting('search_path') INTO v_old_search_path; 2046IF v_jobmon THEN 2047 SELECT nspname INTO v_jobmon_schema FROM pg_catalog.pg_namespace n, pg_catalog.pg_extension e WHERE e.extname = 'pg_jobmon'::name AND e.extnamespace = n.oid; 2048 IF v_jobmon_schema IS NOT NULL THEN 2049 v_new_search_path := '@extschema@,'||v_jobmon_schema||',pg_temp'; 2050 END IF; 2051END IF; 2052EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_new_search_path, 'false'); 2053 2054IF p_keep_table IS NOT NULL THEN 2055 v_retention_keep_table = p_keep_table; 2056END IF; 2057IF p_keep_index IS NOT NULL THEN 2058 v_retention_keep_index = p_keep_index; 2059END IF; 2060IF p_retention_schema IS NOT NULL THEN 2061 v_retention_schema = p_retention_schema; 2062END IF; 2063 2064SELECT schemaname, tablename INTO v_parent_schema, v_parent_tablename 2065FROM pg_catalog.pg_tables 2066WHERE schemaname = split_part(p_parent_table, '.', 1)::name 2067AND tablename = split_part(p_parent_table, '.', 2)::name; 2068 2069-- Loop through child tables starting from highest to get current max value in partition set 2070-- Avoids doing a scan on entire partition set and/or getting any values accidentally in parent. 2071FOR v_row_max_id IN 2072 SELECT partition_schemaname, partition_tablename FROM @extschema@.show_partitions(p_parent_table, 'DESC') 2073LOOP 2074 EXECUTE format('SELECT max(%I) FROM %I.%I', v_control, v_row_max_id.partition_schemaname, v_row_max_id.partition_tablename) INTO v_max; 2075 IF v_max IS NOT NULL THEN 2076 EXIT; 2077 END IF; 2078END LOOP; 2079 2080-- Loop through child tables of the given parent 2081-- Must go in ascending order to avoid dropping what may be the "last" partition in the set after dropping tables that match retention period 2082FOR v_row IN 2083 SELECT partition_schemaname, partition_tablename FROM @extschema@.show_partitions(p_parent_table, 'ASC') 2084LOOP 2085 SELECT child_start_id INTO v_partition_id FROM @extschema@.show_partition_info(v_row.partition_schemaname||'.'||v_row.partition_tablename 2086 , v_partition_interval::text 2087 , p_parent_table); 2088 2089 -- Add one interval since partition names contain the start of the constraint period 2090 IF v_retention <= (v_max - (v_partition_id + v_partition_interval)) THEN 2091 2092 -- Do not allow final partition to be dropped 2093 SELECT count(*) INTO v_count FROM @extschema@.show_partitions(p_parent_table); 2094 IF v_count = 1 THEN 2095 RAISE WARNING 'Attempt to drop final partition in partition set % as part of retention policy. Advise reviewing retention policy and/or data entry into the partition set.', p_parent_table; 2096 CONTINUE; 2097 END IF; 2098 2099 -- Only create a jobmon entry if there's actual retention work done 2100 IF v_jobmon_schema IS NOT NULL AND v_job_id IS NULL THEN 2101 v_job_id := add_job(format('PARTMAN DROP ID PARTITION: %s', p_parent_table)); 2102 END IF; 2103 2104 IF v_jobmon_schema IS NOT NULL THEN 2105 v_step_id := add_step(v_job_id, format('Detach/Uninherit table %s.%s from %s', v_row.partition_schemaname, v_row.partition_tablename, p_parent_table)); 2106 END IF; 2107 IF v_partition_type = 'native' THEN 2108 EXECUTE format('ALTER TABLE %I.%I DETACH PARTITION %I.%I' 2109 , v_parent_schema 2110 , v_parent_tablename 2111 , v_row.partition_schemaname 2112 , v_row.partition_tablename); 2113 ELSE 2114 EXECUTE format('ALTER TABLE %I.%I NO INHERIT %I.%I' 2115 , v_row.partition_schemaname 2116 , v_row.partition_tablename 2117 , v_parent_schema 2118 , v_parent_tablename); 2119 IF v_jobmon_schema IS NOT NULL THEN 2120 PERFORM update_step(v_step_id, 'OK', 'Done'); 2121 END IF; 2122 END IF; 2123 IF v_retention_schema IS NULL THEN 2124 IF v_retention_keep_table = false THEN 2125 IF v_jobmon_schema IS NOT NULL THEN 2126 v_step_id := add_step(v_job_id, format('Drop table %s.%s', v_row.partition_schemaname, v_row.partition_tablename)); 2127 END IF; 2128 EXECUTE format('DROP TABLE %I.%I CASCADE', v_row.partition_schemaname, v_row.partition_tablename); 2129 IF v_jobmon_schema IS NOT NULL THEN 2130 PERFORM update_step(v_step_id, 'OK', 'Done'); 2131 END IF; 2132 ELSIF v_retention_keep_index = false THEN 2133 IF v_partition_type = 'partman' OR 2134 ( v_partition_type = 'native' AND current_setting('server_version_num')::int < 110000) THEN 2135 -- Cannot drop child indexes on native partition sets in PG11+ 2136 FOR v_index IN 2137 WITH child_info AS ( 2138 SELECT c1.oid 2139 FROM pg_catalog.pg_class c1 2140 JOIN pg_catalog.pg_namespace n1 ON c1.relnamespace = n1.oid 2141 WHERE c1.relname = v_row.partition_tablename::name 2142 AND n1.nspname = v_row.partition_schema::name 2143 ) 2144 SELECT c.relname as name 2145 , con.conname 2146 FROM pg_catalog.pg_index i 2147 JOIN pg_catalog.pg_class c ON i.indexrelid = c.oid 2148 LEFT JOIN pg_catalog.pg_constraint con ON i.indexrelid = con.conindid 2149 JOIN child_info ON i.indrelid = child_info.oid 2150 LOOP 2151 IF v_jobmon_schema IS NOT NULL THEN 2152 v_step_id := add_step(v_job_id, format('Drop index %s from %s.%s' 2153 , v_index.name 2154 , v_row.partition_schemaname 2155 , v_row.partition_tablename)); 2156 END IF; 2157 IF v_index.conname IS NOT NULL THEN 2158 EXECUTE format('ALTER TABLE %I.%I DROP CONSTRAINT %I', v_row.partition_schemaname, v_row.partition_tablename, v_index.conname); 2159 ELSE 2160 EXECUTE format('DROP INDEX %I.%I', v_row.partition_schemaname, v_index.name); 2161 END IF; 2162 IF v_jobmon_schema IS NOT NULL THEN 2163 PERFORM update_step(v_step_id, 'OK', 'Done'); 2164 END IF; 2165 END LOOP; 2166 END IF; -- end native/11 check 2167 END IF; -- end v_retention_keep_index IF 2168 ELSE -- Move to new schema 2169 IF v_jobmon_schema IS NOT NULL THEN 2170 v_step_id := add_step(v_job_id, format('Moving table %s.%s to schema %s' 2171 , v_row.partition_schemaname 2172 , v_row.partition_tablename 2173 , v_retention_schema)); 2174 END IF; 2175 2176 EXECUTE format('ALTER TABLE %I.%I SET SCHEMA %I' 2177 , v_row.partition_schemaname 2178 , v_row.partition_tablename 2179 , v_retention_schema); 2180 2181 IF v_jobmon_schema IS NOT NULL THEN 2182 PERFORM update_step(v_step_id, 'OK', 'Done'); 2183 END IF; 2184 END IF; -- End retention schema if 2185 2186 -- If child table is a subpartition, remove it from part_config & part_config_sub (should cascade due to FK) 2187 DELETE FROM @extschema@.part_config WHERE parent_table = v_row.partition_schemaname ||'.'||v_row.partition_tablename; 2188 2189 v_drop_count := v_drop_count + 1; 2190 END IF; -- End retention check IF 2191 2192END LOOP; -- End child table loop 2193 2194IF v_jobmon_schema IS NOT NULL THEN 2195 IF v_job_id IS NOT NULL THEN 2196 v_step_id := add_step(v_job_id, 'Finished partition drop maintenance'); 2197 PERFORM update_step(v_step_id, 'OK', format('%s partitions dropped.', v_drop_count)); 2198 PERFORM close_job(v_job_id); 2199 END IF; 2200END IF; 2201 2202EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false'); 2203 2204RETURN v_drop_count; 2205 2206EXCEPTION 2207 WHEN OTHERS THEN 2208 GET STACKED DIAGNOSTICS ex_message = MESSAGE_TEXT, 2209 ex_context = PG_EXCEPTION_CONTEXT, 2210 ex_detail = PG_EXCEPTION_DETAIL, 2211 ex_hint = PG_EXCEPTION_HINT; 2212 IF v_jobmon_schema IS NOT NULL THEN 2213 IF v_job_id IS NULL THEN 2214 EXECUTE format('SELECT %I.add_job(''PARTMAN DROP ID PARTITION: %s'')', v_jobmon_schema, p_parent_table) INTO v_job_id; 2215 EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before job logging started'')', v_jobmon_schema, v_job_id, p_parent_table) INTO v_step_id; 2216 ELSIF v_step_id IS NULL THEN 2217 EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before first step logged'')', v_jobmon_schema, v_job_id) INTO v_step_id; 2218 END IF; 2219 EXECUTE format('SELECT %I.update_step(%s, ''CRITICAL'', %L)', v_jobmon_schema, v_step_id, 'ERROR: '||coalesce(SQLERRM,'unknown')); 2220 EXECUTE format('SELECT %I.fail_job(%s)', v_jobmon_schema, v_job_id); 2221 END IF; 2222 RAISE EXCEPTION '% 2223CONTEXT: % 2224DETAIL: % 2225HINT: %', ex_message, ex_context, ex_detail, ex_hint; 2226END 2227$$; 2228 2229 2230 2231CREATE OR REPLACE FUNCTION @extschema@.apply_constraints(p_parent_table text, p_child_table text DEFAULT NULL, p_analyze boolean DEFAULT FALSE, p_job_id bigint DEFAULT NULL, p_debug boolean DEFAULT FALSE) RETURNS void 2232 LANGUAGE plpgsql 2233 AS $$ 2234DECLARE 2235 2236ex_context text; 2237ex_detail text; 2238ex_hint text; 2239ex_message text; 2240v_child_exists text; 2241v_child_tablename text; 2242v_col text; 2243v_constraint_cols text[]; 2244v_constraint_col_type text; 2245v_constraint_name text; 2246v_constraint_valid boolean; 2247v_constraint_values record; 2248v_control text; 2249v_control_type text; 2250v_datetime_string text; 2251v_epoch text; 2252v_existing_constraint_name text; 2253v_job_id bigint; 2254v_jobmon boolean; 2255v_jobmon_schema text; 2256v_last_partition text; 2257v_last_partition_id bigint; 2258v_last_partition_timestamp timestamptz; 2259v_max_id bigint; 2260v_max_timestamp timestamptz; 2261v_new_search_path text := '@extschema@,pg_temp'; 2262v_old_search_path text; 2263v_optimize_constraint int; 2264v_parent_schema text; 2265v_parent_table text; 2266v_parent_tablename text; 2267v_partition_interval text; 2268v_partition_suffix text; 2269v_premake int; 2270v_sql text; 2271v_step_id bigint; 2272v_suffix_position int; 2273v_type text; 2274 2275BEGIN 2276/* 2277 * Apply constraints managed by partman extension 2278 */ 2279 2280SELECT parent_table 2281 , partition_type 2282 , control 2283 , premake 2284 , partition_interval 2285 , optimize_constraint 2286 , epoch 2287 , datetime_string 2288 , constraint_cols 2289 , jobmon 2290 , constraint_valid 2291INTO v_parent_table 2292 , v_type 2293 , v_control 2294 , v_premake 2295 , v_partition_interval 2296 , v_optimize_constraint 2297 , v_epoch 2298 , v_datetime_string 2299 , v_constraint_cols 2300 , v_jobmon 2301 , v_constraint_valid 2302FROM @extschema@.part_config 2303WHERE parent_table = p_parent_table 2304AND constraint_cols IS NOT NULL; 2305 2306IF v_constraint_cols IS NULL THEN 2307 IF p_debug THEN 2308 RAISE NOTICE 'Given parent table (%) not set up for constraint management (constraint_cols is NULL)', p_parent_table; 2309 END IF; 2310 -- Returns silently to allow this function to be simply called by maintenance processes without having to check if config options are set. 2311 RETURN; 2312END IF; 2313 2314SELECT schemaname, tablename 2315INTO v_parent_schema, v_parent_tablename 2316FROM pg_catalog.pg_tables 2317WHERE schemaname = split_part(v_parent_table, '.', 1)::name 2318AND tablename = split_part(v_parent_table, '.', 2)::name; 2319 2320SELECT general_type INTO v_control_type FROM @extschema@.check_control_type(v_parent_schema, v_parent_tablename, v_control); 2321 2322SELECT current_setting('search_path') INTO v_old_search_path; 2323IF v_jobmon THEN 2324 SELECT nspname INTO v_jobmon_schema FROM pg_catalog.pg_namespace n, pg_catalog.pg_extension e WHERE e.extname = 'pg_jobmon'::name AND e.extnamespace = n.oid; 2325 IF v_jobmon_schema IS NOT NULL THEN 2326 v_new_search_path := '@extschema@,'||v_jobmon_schema||',pg_temp'; 2327 END IF; 2328END IF; 2329EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_new_search_path, 'false'); 2330 2331IF v_jobmon_schema IS NOT NULL THEN 2332 IF p_job_id IS NULL THEN 2333 v_job_id := add_job(format('PARTMAN CREATE CONSTRAINT: %s', v_parent_table)); 2334 ELSE 2335 v_job_id = p_job_id; 2336 END IF; 2337END IF; 2338 2339-- If p_child_table is null, figure out the partition that is the one right before the optimize_constraint value backwards. 2340IF p_child_table IS NULL THEN 2341 IF v_jobmon_schema IS NOT NULL THEN 2342 v_step_id := add_step(v_job_id, 'Applying additional constraints: Automatically determining most recent child on which to apply constraints'); 2343 END IF; 2344 2345 SELECT partition_tablename INTO v_last_partition FROM @extschema@.show_partitions(v_parent_table, 'DESC') LIMIT 1; 2346 2347 IF v_control_type = 'time' OR (v_control_type = 'id' AND v_epoch <> 'none') THEN 2348 SELECT child_start_time INTO v_last_partition_timestamp FROM @extschema@.show_partition_info(v_parent_schema||'.'||v_last_partition, v_partition_interval, v_parent_table); 2349 v_partition_suffix := to_char(v_last_partition_timestamp - (v_partition_interval::interval * (v_optimize_constraint + v_premake + 1) ), v_datetime_string); 2350 ELSIF v_control_type = 'id' THEN 2351 SELECT child_start_id INTO v_last_partition_id FROM @extschema@.show_partition_info(v_parent_schema||'.'||v_last_partition, v_partition_interval, v_parent_table); 2352 v_partition_suffix := (v_last_partition_id - (v_partition_interval::int * (v_optimize_constraint + v_premake + 1) ))::text; 2353 END IF; 2354 2355 v_child_tablename := @extschema@.check_name_length(v_parent_tablename, v_partition_suffix, TRUE); 2356 2357 IF p_debug THEN 2358 RAISE NOTICE 'apply_constraint: v_parent_tablename: % , v_partition_suffix: %', v_parent_tablename, v_partition_suffix; 2359 END IF; 2360 2361 IF v_jobmon_schema IS NOT NULL THEN 2362 PERFORM update_step(v_step_id, 'OK', format('Target child table: %s.%s', v_parent_schema, v_child_tablename)); 2363 END IF; 2364ELSE 2365 v_child_tablename = split_part(p_child_table, '.', 2); 2366END IF; 2367 2368IF v_jobmon_schema IS NOT NULL THEN 2369 v_step_id := add_step(v_job_id, 'Applying additional constraints: Checking if target child table exists'); 2370END IF; 2371 2372SELECT tablename FROM pg_catalog.pg_tables INTO v_child_exists WHERE schemaname = v_parent_schema::name AND tablename = v_child_tablename::name; 2373IF v_child_exists IS NULL THEN 2374 IF v_jobmon_schema IS NOT NULL THEN 2375 PERFORM update_step(v_step_id, 'NOTICE', format('Target child table (%s) does not exist. Skipping constraint creation.', v_child_tablename)); 2376 IF p_job_id IS NULL THEN 2377 PERFORM close_job(v_job_id); 2378 END IF; 2379 END IF; 2380 IF p_debug THEN 2381 RAISE NOTICE 'Target child table (%) does not exist. Skipping constraint creation.', v_child_tablename; 2382 END IF; 2383 EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false'); 2384 RETURN; 2385ELSE 2386 IF v_jobmon_schema IS NOT NULL THEN 2387 PERFORM update_step(v_step_id, 'OK', 'Done'); 2388 END IF; 2389END IF; 2390 2391FOREACH v_col IN ARRAY v_constraint_cols 2392LOOP 2393 SELECT con.conname 2394 INTO v_existing_constraint_name 2395 FROM pg_catalog.pg_constraint con 2396 JOIN pg_class c ON c.oid = con.conrelid 2397 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 2398 JOIN pg_catalog.pg_attribute a ON con.conrelid = a.attrelid 2399 WHERE c.relname = v_child_tablename::name 2400 AND n.nspname = v_parent_schema::name 2401 AND con.conname LIKE 'partmanconstr_%' 2402 AND con.contype = 'c' 2403 AND a.attname = v_col::name 2404 AND ARRAY[a.attnum] OPERATOR(pg_catalog.<@) con.conkey 2405 AND a.attisdropped = false; 2406 2407 IF v_jobmon_schema IS NOT NULL THEN 2408 v_step_id := add_step(v_job_id, format('Applying additional constraints: Applying new constraint on column: %s', v_col)); 2409 END IF; 2410 2411 IF v_existing_constraint_name IS NOT NULL THEN 2412 IF v_jobmon_schema IS NOT NULL THEN 2413 PERFORM update_step(v_step_id, 'NOTICE', format('Partman managed constraint already exists on this table (%s) and column (%s). Skipping creation.', v_child_tablename, v_col)); 2414 END IF; 2415 IF p_debug THEN 2416 RAISE NOTICE 'Partman managed constraint already exists on this table (%) and column (%). Skipping creation.', v_child_tablename, v_col ; 2417 END IF; 2418 CONTINUE; 2419 END IF; 2420 2421 -- Ensure column name gets put on end of constraint name to help avoid naming conflicts 2422 v_constraint_name := @extschema@.check_name_length('partmanconstr_'||v_child_tablename, p_suffix := '_'||v_col); 2423 2424 EXECUTE format('SELECT min(%I)::text AS min, max(%I)::text AS max FROM %I.%I', v_col, v_col, v_parent_schema, v_child_tablename) INTO v_constraint_values; 2425 2426 IF v_constraint_values IS NOT NULL THEN 2427 v_sql := format('ALTER TABLE %I.%I ADD CONSTRAINT %I CHECK (%I >= %L AND %I <= %L)' 2428 , v_parent_schema 2429 , v_child_tablename 2430 , v_constraint_name 2431 , v_col 2432 , v_constraint_values.min 2433 , v_col 2434 , v_constraint_values.max); 2435 2436 IF v_constraint_valid = false THEN 2437 v_sql := format('%s NOT VALID', v_sql); 2438 END IF; 2439 2440 IF p_debug THEN 2441 RAISE NOTICE 'Constraint creation query: %', v_sql; 2442 END IF; 2443 EXECUTE v_sql; 2444 2445 IF v_jobmon_schema IS NOT NULL THEN 2446 PERFORM update_step(v_step_id, 'OK', format('New constraint created: %s', v_sql)); 2447 END IF; 2448 ELSE 2449 IF p_debug THEN 2450 RAISE NOTICE 'Given column (%) contains all NULLs. No constraint created', v_col; 2451 END IF; 2452 IF v_jobmon_schema IS NOT NULL THEN 2453 PERFORM update_step(v_step_id, 'NOTICE', format('Given column (%s) contains all NULLs. No constraint created', v_col)); 2454 END IF; 2455 END IF; 2456 2457END LOOP; 2458 2459IF p_analyze THEN 2460 IF v_jobmon_schema IS NOT NULL THEN 2461 v_step_id := add_step(v_job_id, format('Applying additional constraints: Running analyze on partition set: %s', v_parent_table)); 2462 END IF; 2463 IF p_debug THEN 2464 RAISE NOTICE 'Running analyze on partition set: %', v_parent_table; 2465 END IF; 2466 2467 EXECUTE format('ANALYZE %I.%I', v_parent_schema, v_parent_tablename); 2468 2469 IF v_jobmon_schema IS NOT NULL THEN 2470 PERFORM update_step(v_step_id, 'OK', 'Done'); 2471 END IF; 2472END IF; 2473 2474IF v_jobmon_schema IS NOT NULL THEN 2475 PERFORM close_job(v_job_id); 2476END IF; 2477 2478EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false'); 2479 2480EXCEPTION 2481 WHEN OTHERS THEN 2482 GET STACKED DIAGNOSTICS ex_message = MESSAGE_TEXT, 2483 ex_context = PG_EXCEPTION_CONTEXT, 2484 ex_detail = PG_EXCEPTION_DETAIL, 2485 ex_hint = PG_EXCEPTION_HINT; 2486 IF v_jobmon_schema IS NOT NULL THEN 2487 IF v_job_id IS NULL THEN 2488 EXECUTE format('SELECT %I.add_job(''PARTMAN CREATE CONSTRAINT: %s'')', v_jobmon_schema, p_parent_table) INTO v_job_id; 2489 EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before job logging started'')', v_jobmon_schema, v_job_id, p_parent_table) INTO v_step_id; 2490 ELSIF v_step_id IS NULL THEN 2491 EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before first step logged'')', v_jobmon_schema, v_job_id) INTO v_step_id; 2492 END IF; 2493 EXECUTE format('SELECT %I.update_step(%s, ''CRITICAL'', %L)', v_jobmon_schema, v_step_id, 'ERROR: '||coalesce(SQLERRM,'unknown')); 2494 EXECUTE format('SELECT %I.fail_job(%s)', v_jobmon_schema, v_job_id); 2495 END IF; 2496 RAISE EXCEPTION '% 2497CONTEXT: % 2498DETAIL: % 2499HINT: %', ex_message, ex_context, ex_detail, ex_hint; 2500END 2501$$; 2502 2503 2504CREATE OR REPLACE FUNCTION @extschema@.inherit_template_properties (p_parent_table text, p_child_schema text, p_child_tablename text) RETURNS boolean 2505 LANGUAGE plpgsql 2506 AS $$ 2507DECLARE 2508 2509v_child_relkind char; 2510v_child_schema text; 2511v_child_tablename text; 2512v_child_unlogged char; 2513v_dupe_found boolean := false; 2514v_fk_list record; 2515v_index_list record; 2516v_inherit_fk boolean; 2517v_parent_index_list record; 2518v_parent_oid oid; 2519v_parent_table text; 2520v_sql text; 2521v_template_oid oid; 2522v_template_schemaname text; 2523v_template_table text; 2524v_template_tablename name; 2525v_template_tablespace name; 2526v_template_unlogged char; 2527 2528BEGIN 2529/* 2530 * Function to inherit the properties of the template table to newly created child tables. 2531 * Currently used for PostgreSQL 10 to inherit indexes and FKs since that is not natively available 2532 * For PG11, used to inherit non-partition-key unique indexes & primary keys 2533 */ 2534 2535SELECT parent_table, template_table, inherit_fk 2536INTO v_parent_table, v_template_table, v_inherit_fk 2537FROM @extschema@.part_config 2538WHERE parent_table = p_parent_table; 2539IF v_parent_table IS NULL THEN 2540 RAISE EXCEPTION 'Given parent table has no configuration in pg_partman: %', p_parent_table; 2541ELSIF v_template_table IS NULL THEN 2542 RAISE EXCEPTION 'No template table set in configuration for given parent table: %', p_parent_table; 2543END IF; 2544 2545SELECT c.oid INTO v_parent_oid 2546FROM pg_catalog.pg_class c 2547JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 2548WHERE n.nspname = split_part(p_parent_table, '.', 1)::name 2549AND c.relname = split_part(p_parent_table, '.', 2)::name; 2550 IF v_parent_oid IS NULL THEN 2551 RAISE EXCEPTION 'Unable to find given parent table in system catalogs: %', p_parent_table; 2552 END IF; 2553 2554SELECT n.nspname, c.relname, c.relkind INTO v_child_schema, v_child_tablename, v_child_relkind 2555FROM pg_catalog.pg_class c 2556JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 2557WHERE n.nspname = p_child_schema::name 2558AND c.relname = p_child_tablename::name; 2559 IF v_child_tablename IS NULL THEN 2560 RAISE EXCEPTION 'Unable to find given child table in system catalogs: %.%', v_child_schema, v_child_tablename; 2561 END IF; 2562 2563IF v_child_relkind = 'p' THEN 2564 -- Subpartitioned parent, do not apply properties 2565 RAISE DEBUG 'inherit_template_properties: found given child is subpartition parent, so properties not inherited'; 2566 RETURN false; 2567END IF; 2568 2569v_template_schemaname := split_part(v_template_table, '.', 1)::name; 2570v_template_tablename := split_part(v_template_table, '.', 2)::name; 2571 2572 SELECT c.oid, ts.spcname INTO v_template_oid, v_template_tablespace 2573FROM pg_catalog.pg_class c 2574JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 2575LEFT OUTER JOIN pg_catalog.pg_tablespace ts ON c.reltablespace = ts.oid 2576WHERE n.nspname = v_template_schemaname 2577AND c.relname = v_template_tablename; 2578 IF v_template_oid IS NULL THEN 2579 RAISE EXCEPTION 'Unable to find configured template table in system catalogs: %', v_template_table; 2580 END IF; 2581 2582-- Index creation (Required for all indexes in PG10. Only for non-unique, non-partition key indexes in PG11) 2583IF current_setting('server_version_num')::int >= 100000 THEN 2584 FOR v_index_list IN 2585 SELECT 2586 array_to_string(regexp_matches(pg_get_indexdef(indexrelid), ' USING .*'),',') AS statement 2587 , i.indisprimary 2588 , i.indisunique 2589 , ( SELECT array_agg( a.attname ORDER by x.r ) 2590 FROM pg_catalog.pg_attribute a 2591 JOIN ( SELECT k, row_number() over () as r 2592 FROM unnest(i.indkey) k ) as x 2593 ON a.attnum = x.k AND a.attrelid = i.indrelid 2594 ) AS indkey_names 2595 , c.relname AS index_name 2596 , ts.spcname AS tablespace_name 2597 FROM pg_catalog.pg_index i 2598 JOIN pg_catalog.pg_class c ON i.indexrelid = c.oid 2599 LEFT OUTER JOIN pg_catalog.pg_tablespace ts ON c.reltablespace = ts.oid 2600 WHERE i.indrelid = v_template_oid 2601 AND i.indisvalid 2602 ORDER BY 1 2603 LOOP 2604 IF current_setting('server_version_num')::int >= 110000 THEN 2605 FOR v_parent_index_list IN 2606 SELECT 2607 array_to_string(regexp_matches(pg_get_indexdef(indexrelid), ' USING .*'),',') AS statement 2608 , i.indisprimary 2609 , ( SELECT array_agg( a.attname ORDER by x.r ) 2610 FROM pg_catalog.pg_attribute a 2611 JOIN ( SELECT k, row_number() over () as r 2612 FROM unnest(i.indkey) k ) as x 2613 ON a.attnum = x.k AND a.attrelid = i.indrelid 2614 ) AS indkey_names 2615 FROM pg_catalog.pg_index i 2616 WHERE i.indrelid = v_parent_oid 2617 AND i.indisvalid 2618 ORDER BY 1 2619 LOOP 2620 2621 IF v_parent_index_list.indisprimary AND v_index_list.indisprimary THEN 2622 IF v_parent_index_list.indkey_names = v_index_list.indkey_names THEN 2623 RAISE DEBUG 'Ignoring duplicate primary key on template table: % ', v_index_list.indkey_names; 2624 v_dupe_found := true; 2625 CONTINUE; -- only continue within this nested loop 2626 END IF; 2627 END IF; 2628 2629 IF v_parent_index_list.statement = v_index_list.statement THEN 2630 RAISE DEBUG 'Ignoring duplicate index on template table: %', v_index_list.statement; 2631 v_dupe_found := true; 2632 CONTINUE; -- only continue within this nested loop 2633 END IF; 2634 2635 END LOOP; -- end parent index loop 2636 END IF; -- End PG11 check 2637 2638 IF v_dupe_found = true THEN 2639 -- Only used in PG11 and should skip trying to create indexes that already existed on the parent 2640 CONTINUE; 2641 END IF; 2642 2643 IF v_index_list.indisprimary THEN 2644 v_sql := format('ALTER TABLE %I.%I ADD PRIMARY KEY (%s)' 2645 , v_child_schema 2646 , v_child_tablename 2647 , '"' || array_to_string(v_index_list.indkey_names, '","') || '"'); 2648 IF v_index_list.tablespace_name IS NOT NULL THEN 2649 v_sql := v_sql || format(' USING INDEX TABLESPACE %I', v_index_list.tablespace_name); 2650 END IF; 2651 RAISE DEBUG 'Create pk: %', v_sql; 2652 EXECUTE v_sql; 2653 ELSE 2654 -- statement column should be just the portion of the index definition that defines what it actually is 2655 v_sql := format('CREATE %s INDEX ON %I.%I %s', CASE WHEN v_index_list.indisunique = TRUE THEN 'UNIQUE' ELSE '' END, v_child_schema, v_child_tablename, v_index_list.statement); 2656 IF v_index_list.tablespace_name IS NOT NULL THEN 2657 v_sql := v_sql || format(' TABLESPACE %I', v_index_list.tablespace_name); 2658 END IF; 2659 2660 RAISE DEBUG 'Create index: %', v_sql; 2661 EXECUTE v_sql; 2662 2663 END IF; 2664 2665 END LOOP; 2666END IF; 2667-- End index creation 2668 2669-- Foreign key creation (PG10 only) 2670IF current_setting('server_version_num')::int >= 100000 AND current_setting('server_version_num')::int < 110000 THEN 2671 IF v_inherit_fk THEN 2672 FOR v_fk_list IN 2673 SELECT pg_get_constraintdef(con.oid) AS constraint_def 2674 FROM pg_catalog.pg_constraint con 2675 JOIN pg_catalog.pg_class c ON con.conrelid = c.oid 2676 WHERE c.oid = v_template_oid 2677 AND contype = 'f' 2678 LOOP 2679 v_sql := format('ALTER TABLE %I.%I ADD %s', v_child_schema, v_child_tablename, v_fk_list.constraint_def); 2680 RAISE DEBUG 'Create FK: %', v_sql; 2681 EXECUTE v_sql; 2682 END LOOP; 2683 END IF; 2684END IF; 2685-- End foreign key creation 2686 2687-- Tablespace inheritance 2688IF v_template_tablespace IS NOT NULL THEN 2689 v_sql := format('ALTER TABLE %I.%I SET TABLESPACE %I', v_child_schema, v_child_tablename, v_template_tablespace); 2690 RAISE DEBUG 'Alter tablespace: %', v_sql; 2691 EXECUTE v_sql; 2692END IF; 2693 2694-- UNLOGGED status. Currently waiting on final stance of how native will handle this property being changed for its children. 2695-- See release notes for v4.2.0 2696SELECT relpersistence INTO v_template_unlogged 2697FROM pg_catalog.pg_class c 2698JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 2699WHERE n.nspname = v_template_schemaname 2700AND c.relname = v_template_tablename; 2701 2702SELECT relpersistence INTO v_child_unlogged 2703FROM pg_catalog.pg_class c 2704JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 2705WHERE n.nspname = v_child_schema::name 2706AND c.relname = v_child_tablename::name; 2707 2708IF v_template_unlogged = 'u' AND v_child_unlogged = 'p' THEN 2709 v_sql := format ('ALTER TABLE %I.%I SET UNLOGGED', v_child_schema, v_child_tablename); 2710 RAISE DEBUG 'Alter UNLOGGED: %', v_sql; 2711 EXECUTE v_sql; 2712ELSIF v_template_unlogged = 'p' AND v_child_unlogged = 'u' THEN 2713 v_sql := format ('ALTER TABLE %I.%I SET LOGGED', v_child_schema, v_child_tablename); 2714 RAISE DEBUG 'Alter UNLOGGED: %', v_sql; 2715 EXECUTE v_sql; 2716END IF; 2717 2718RETURN true; 2719 2720END 2721$$; 2722 2723