-- Allow maintenance background worker to restart itself if it crashes. Previously if the main background worker process exited for any reason, the only way to restart it was to restart the whole postgresql instance. Now it will try to restart itself every 10 minutes. A warning will be issued into the logs if this event occurs. (Github Issue #180). -- NOTE: You will have to restart the PostgreSQL cluster to load the new library with the auto-restart option. -- Remove internal version checking function and use simpler comparison method using integer based version number (Pull Request #183). -- Apply fixes from running plpgsql_check (https://github.com/okbob/plpgsql_check/) DROP FUNCTION IF EXISTS @extschema@.check_version(text); CREATE OR REPLACE FUNCTION create_parent( p_parent_table text , p_control text , p_type text , p_interval text , p_constraint_cols text[] DEFAULT NULL , p_premake int DEFAULT 4 , p_automatic_maintenance text DEFAULT 'on' , p_start_partition text DEFAULT NULL , p_inherit_fk boolean DEFAULT true , p_epoch text DEFAULT 'none' , p_upsert text DEFAULT '' , p_trigger_return_null boolean DEFAULT true , p_jobmon boolean DEFAULT true , p_debug boolean DEFAULT false) RETURNS boolean LANGUAGE plpgsql SECURITY DEFINER AS $$ DECLARE ex_context text; ex_detail text; ex_hint text; ex_message text; v_partattrs smallint[]; v_base_timestamp timestamptz; v_count int := 1; v_control_type text; v_control_exact_type text; v_datetime_string text; v_higher_control_type text; v_higher_parent_control text; v_higher_parent_schema text := split_part(p_parent_table, '.', 1); v_higher_parent_table text := split_part(p_parent_table, '.', 2); v_id_interval bigint; v_job_id bigint; v_jobmon_schema text; v_last_partition_created boolean; v_max bigint; v_native_sub_control text; v_notnull boolean; v_new_search_path text := '@extschema@,pg_temp'; v_old_search_path text; v_parent_partition_id bigint; v_parent_partition_timestamp timestamptz; v_parent_schema text; v_parent_tablename text; v_part_col text; v_part_type text; v_partition_time timestamptz; v_partition_time_array timestamptz[]; v_partition_id_array bigint[]; v_partstrat char; v_row record; v_sql text; v_start_time timestamptz; v_starting_partition_id bigint; v_step_id bigint; v_step_overflow_id bigint; v_sub_parent text; v_success boolean := false; v_time_interval interval; v_top_datetime_string text; v_top_parent_schema text := split_part(p_parent_table, '.', 1); v_top_parent_table text := split_part(p_parent_table, '.', 2); BEGIN /* * Function to turn a table into the parent of a partition set */ IF position('.' in p_parent_table) = 0 THEN RAISE EXCEPTION 'Parent table must be schema qualified'; END IF; IF p_upsert <> '' THEN IF current_setting('server_version_num')::int < 90500 THEN RAISE EXCEPTION 'INSERT ... ON CONFLICT (UPSERT) feature is only supported in PostgreSQL 9.5 and later'; END IF; IF p_type = 'native' THEN RAISE EXCEPTION 'Native partitioning does not currently support upsert. Use pg_partman''s partitioning methods instead if this is required'; END IF; END IF; SELECT n.nspname, c.relname INTO v_parent_schema, v_parent_tablename FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE n.nspname = split_part(p_parent_table, '.', 1)::name AND c.relname = split_part(p_parent_table, '.', 2)::name; IF v_parent_tablename IS NULL THEN RAISE EXCEPTION 'Unable to find given parent table in system catalogs. Please create parent table first: %', p_parent_table; END IF; SELECT attnotnull INTO v_notnull FROM pg_catalog.pg_attribute a JOIN pg_catalog.pg_class c ON a.attrelid = c.oid JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE c.relname = v_parent_tablename::name AND n.nspname = v_parent_schema::name AND a.attname = p_control::name; IF p_type <> 'native' AND (v_notnull = false OR v_notnull IS NULL) THEN RAISE EXCEPTION 'Control column given (%) for parent table (%) does not exist or must be set to NOT NULL', p_control, p_parent_table; END IF; SELECT general_type, exact_type INTO v_control_type, v_control_exact_type FROM @extschema@.check_control_type(v_parent_schema, v_parent_tablename, p_control); IF (p_epoch <> 'none' AND v_control_type <> 'id') THEN RAISE EXCEPTION 'p_epoch can only be used with an integer based control column and does not work for native partitioning'; END IF; IF NOT @extschema@.check_partition_type(p_type) THEN RAISE EXCEPTION '% is not a valid partitioning type for pg_partman', p_type; END IF; IF p_type = 'native' THEN IF current_setting('server_version_num')::int < 100000 THEN RAISE EXCEPTION 'Native partitioning only available in PostgreSQL versions 10.0+'; END IF; -- Check if given parent table has been already set up as a partitioned table and is ranged SELECT p.partstrat, partattrs INTO v_partstrat, v_partattrs FROM pg_catalog.pg_partitioned_table p JOIN pg_catalog.pg_class c ON p.partrelid = c.oid JOIN pg_namespace n ON c.relnamespace = n.oid WHERE n.nspname = v_parent_schema::name AND c.relname = v_parent_tablename::name; IF v_partstrat <> 'r' OR v_partstrat IS NULL THEN 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 ...)'; END IF; IF array_length(v_partattrs, 1) > 1 THEN RAISE NOTICE 'pg_partman only supports single column native partitioning at this time. Found % columns in given parent definition.', array_length(v_partattrs, 1); END IF; SELECT a.attname, t.typname INTO v_part_col, v_part_type FROM pg_attribute a JOIN pg_class c ON a.attrelid = c.oid JOIN pg_namespace n ON c.relnamespace = n.oid JOIN pg_type t ON a.atttypid = t.oid WHERE n.nspname = v_parent_schema::name AND c.relname = v_parent_tablename::name AND attnum IN (SELECT unnest(partattrs) FROM pg_partitioned_table p WHERE a.attrelid = p.partrelid); IF p_control <> v_part_col OR v_control_exact_type <> v_part_type THEN 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; END IF; -- Check that control column is a usable type for pg_partman. IF v_control_type NOT IN ('time', 'id') THEN RAISE EXCEPTION 'Only date/time or integer types are allowed for the control column with native partitioning.'; END IF; ELSE IF current_setting('server_version_num')::int >= 100000 THEN SELECT p.partstrat INTO v_partstrat FROM pg_catalog.pg_partitioned_table p JOIN pg_catalog.pg_class c ON p.partrelid = c.oid JOIN pg_namespace n ON c.relnamespace = n.oid WHERE n.nspname = v_parent_schema::name AND c.relname = v_parent_tablename::name; END IF; IF v_partstrat IS NOT NULL THEN 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'''; END IF; END IF; SELECT current_setting('search_path') INTO v_old_search_path; IF p_jobmon THEN 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; IF v_jobmon_schema IS NOT NULL THEN v_new_search_path := '@extschema@,'||v_jobmon_schema||',pg_temp'; END IF; END IF; EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_new_search_path, 'false'); EXECUTE format('LOCK TABLE %I.%I IN ACCESS EXCLUSIVE MODE', v_parent_schema, v_parent_tablename); IF v_jobmon_schema IS NOT NULL THEN v_job_id := add_job(format('PARTMAN SETUP PARENT: %s', p_parent_table)); v_step_id := add_step(v_job_id, format('Creating initial partitions on new parent table: %s', p_parent_table)); END IF; -- 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 -- 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.) FOR v_row IN WITH parent_table AS ( SELECT h.inhparent AS parent_oid FROM pg_catalog.pg_inherits h JOIN pg_catalog.pg_class c ON h.inhrelid = c.oid JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE c.relname = v_parent_tablename::name AND n.nspname = v_parent_schema::name ), sibling_children AS ( SELECT i.inhrelid::regclass::text AS tablename FROM pg_inherits i JOIN parent_table p ON i.inhparent = p.parent_oid ) SELECT DISTINCT sub_partition_type , sub_control , sub_partition_interval , sub_constraint_cols , sub_premake , sub_inherit_fk , sub_retention , sub_retention_schema , sub_retention_keep_table , sub_retention_keep_index , sub_automatic_maintenance , sub_epoch , sub_optimize_trigger , sub_optimize_constraint , sub_infinite_time_partitions , sub_jobmon , sub_trigger_exception_handling , sub_upsert , sub_trigger_return_null FROM @extschema@.part_config_sub a JOIN sibling_children b on a.sub_parent = b.tablename LIMIT 1 LOOP INSERT INTO @extschema@.part_config_sub ( sub_parent , sub_partition_type , sub_control , sub_partition_interval , sub_constraint_cols , sub_premake , sub_inherit_fk , sub_retention , sub_retention_schema , sub_retention_keep_table , sub_retention_keep_index , sub_automatic_maintenance , sub_epoch , sub_optimize_trigger , sub_optimize_constraint , sub_infinite_time_partitions , sub_jobmon , sub_trigger_exception_handling , sub_upsert , sub_trigger_return_null) VALUES ( p_parent_table , v_row.sub_partition_type , v_row.sub_control , v_row.sub_partition_interval , v_row.sub_constraint_cols , v_row.sub_premake , v_row.sub_inherit_fk , v_row.sub_retention , v_row.sub_retention_schema , v_row.sub_retention_keep_table , v_row.sub_retention_keep_index , v_row.sub_automatic_maintenance , v_row.sub_epoch , v_row.sub_optimize_trigger , v_row.sub_optimize_constraint , v_row.sub_infinite_time_partitions , v_row.sub_jobmon , v_row.sub_trigger_exception_handling , v_row.sub_upsert , v_row.sub_trigger_return_null); END LOOP; IF v_control_type = 'time' OR (v_control_type = 'id' AND p_epoch <> 'none') THEN CASE WHEN p_interval = 'yearly' THEN v_time_interval := '1 year'; WHEN p_interval = 'quarterly' THEN v_time_interval := '3 months'; WHEN p_interval = 'monthly' THEN v_time_interval := '1 month'; WHEN p_interval = 'weekly' THEN v_time_interval := '1 week'; WHEN p_interval = 'daily' THEN v_time_interval := '1 day'; WHEN p_interval = 'hourly' THEN v_time_interval := '1 hour'; WHEN p_interval = 'half-hour' THEN v_time_interval := '30 mins'; WHEN p_interval = 'quarter-hour' THEN v_time_interval := '15 mins'; ELSE IF p_type <> 'native' THEN -- Reset for use as part_config type value below p_type = 'time-custom'; END IF; v_time_interval := p_interval::interval; IF v_time_interval < '1 second'::interval THEN RAISE EXCEPTION 'Partitioning interval must be 1 second or greater'; END IF; END CASE; -- First partition is either the min premake or p_start_partition v_start_time := COALESCE(p_start_partition::timestamptz, CURRENT_TIMESTAMP - (v_time_interval * p_premake)); IF v_time_interval >= '1 year' THEN v_base_timestamp := date_trunc('year', v_start_time); IF v_time_interval >= '10 years' THEN v_base_timestamp := date_trunc('decade', v_start_time); IF v_time_interval >= '100 years' THEN v_base_timestamp := date_trunc('century', v_start_time); IF v_time_interval >= '1000 years' THEN v_base_timestamp := date_trunc('millennium', v_start_time); END IF; -- 1000 END IF; -- 100 END IF; -- 10 END IF; -- 1 v_datetime_string := 'YYYY'; IF v_time_interval < '1 year' THEN IF p_interval = 'quarterly' THEN v_base_timestamp := date_trunc('quarter', v_start_time); v_datetime_string = 'YYYY"q"Q'; ELSE v_base_timestamp := date_trunc('month', v_start_time); v_datetime_string := v_datetime_string || '_MM'; END IF; IF v_time_interval < '1 month' THEN IF p_interval = 'weekly' THEN v_base_timestamp := date_trunc('week', v_start_time); v_datetime_string := 'IYYY"w"IW'; ELSE v_base_timestamp := date_trunc('day', v_start_time); v_datetime_string := v_datetime_string || '_DD'; END IF; IF v_time_interval < '1 day' THEN v_base_timestamp := date_trunc('hour', v_start_time); v_datetime_string := v_datetime_string || '_HH24MI'; IF v_time_interval < '1 minute' THEN v_base_timestamp := date_trunc('minute', v_start_time); v_datetime_string := v_datetime_string || 'SS'; END IF; -- minute END IF; -- day END IF; -- month END IF; -- year v_partition_time_array := array_append(v_partition_time_array, v_base_timestamp); LOOP -- If current loop value is less than or equal to the value of the max premake, add time to array. IF (v_base_timestamp + (v_time_interval * v_count)) < (CURRENT_TIMESTAMP + (v_time_interval * p_premake)) THEN BEGIN v_partition_time := (v_base_timestamp + (v_time_interval * v_count))::timestamptz; v_partition_time_array := array_append(v_partition_time_array, v_partition_time); EXCEPTION WHEN datetime_field_overflow THEN RAISE WARNING 'Attempted partition time interval is outside PostgreSQL''s supported time range. Child partition creation after time % skipped', v_partition_time; v_step_overflow_id := add_step(v_job_id, 'Attempted partition time interval is outside PostgreSQL''s supported time range.'); PERFORM update_step(v_step_overflow_id, 'CRITICAL', 'Child partition creation after time '||v_partition_time||' skipped'); CONTINUE; END; ELSE EXIT; -- all needed partitions added to array. Exit the loop. END IF; v_count := v_count + 1; END LOOP; INSERT INTO @extschema@.part_config ( parent_table , partition_type , partition_interval , epoch , control , premake , constraint_cols , datetime_string , automatic_maintenance , inherit_fk , jobmon , upsert , trigger_return_null) VALUES ( p_parent_table , p_type , v_time_interval , p_epoch , p_control , p_premake , p_constraint_cols , v_datetime_string , p_automatic_maintenance , p_inherit_fk , p_jobmon , p_upsert , p_trigger_return_null); v_last_partition_created := @extschema@.create_partition_time(p_parent_table, v_partition_time_array, false); IF v_last_partition_created = false THEN -- This can happen with subpartitioning when future or past partitions prevent child creation because they're out of range of the parent -- First see if this parent is a subpartition managed by pg_partman WITH top_oid AS ( SELECT i.inhparent AS top_parent_oid FROM pg_catalog.pg_inherits i JOIN pg_catalog.pg_class c ON c.oid = i.inhrelid JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relname = v_parent_tablename::name AND n.nspname = v_parent_schema::name ) SELECT n.nspname, c.relname INTO v_top_parent_schema, v_top_parent_table FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace JOIN top_oid t ON c.oid = t.top_parent_oid JOIN @extschema@.part_config p ON p.parent_table = n.nspname||'.'||c.relname; IF v_top_parent_table IS NOT NULL THEN -- If so create the lowest possible partition that is within the boundary of the parent 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); IF v_base_timestamp >= v_parent_partition_timestamp THEN WHILE v_base_timestamp >= v_parent_partition_timestamp LOOP v_base_timestamp := v_base_timestamp - v_time_interval; END LOOP; v_base_timestamp := v_base_timestamp + v_time_interval; -- add one back since while loop set it one lower than is needed ELSIF v_base_timestamp < v_parent_partition_timestamp THEN WHILE v_base_timestamp < v_parent_partition_timestamp LOOP v_base_timestamp := v_base_timestamp + v_time_interval; END LOOP; -- Don't need to remove one since new starting time will fit in top parent interval END IF; v_partition_time_array := NULL; v_partition_time_array := array_append(v_partition_time_array, v_base_timestamp); v_last_partition_created := @extschema@.create_partition_time(p_parent_table, v_partition_time_array, false); ELSE 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.'; IF v_jobmon_schema IS NOT NULL THEN PERFORM update_step(v_step_id, 'OK', 'Done'); IF v_step_overflow_id IS NOT NULL THEN PERFORM fail_job(v_job_id); ELSE PERFORM close_job(v_job_id); END IF; END IF; EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false'); RETURN v_success; END IF; END IF; -- End v_last_partition IF IF v_jobmon_schema IS NOT NULL THEN PERFORM update_step(v_step_id, 'OK', format('Time partitions premade: %s', p_premake)); END IF; END IF; IF v_control_type = 'id' AND p_epoch = 'none' THEN v_id_interval := p_interval::bigint; IF p_type <> 'native' AND v_id_interval < 10 THEN RAISE EXCEPTION 'Interval for serial, non-native partitioning must be greater than or equal to 10'; END IF; -- Check if parent table is a subpartition of an already existing id partition set managed by pg_partman. WHILE v_higher_parent_table IS NOT NULL LOOP -- initially set in DECLARE WITH top_oid AS ( SELECT i.inhparent AS top_parent_oid FROM pg_catalog.pg_inherits i JOIN pg_catalog.pg_class c ON c.oid = i.inhrelid JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname = v_higher_parent_schema::name AND c.relname = v_higher_parent_table::name ) SELECT n.nspname, c.relname, p.control INTO v_higher_parent_schema, v_higher_parent_table, v_higher_parent_control FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace JOIN top_oid t ON c.oid = t.top_parent_oid JOIN @extschema@.part_config p ON p.parent_table = n.nspname||'.'||c.relname; IF v_higher_parent_table IS NOT NULL THEN SELECT general_type INTO v_higher_control_type FROM @extschema@.check_control_type(v_higher_parent_schema, v_higher_parent_table, v_higher_parent_control); IF v_higher_control_type <> 'id' THEN -- The parent above the p_parent_table parameter is not partitioned by ID -- so don't check for max values in parents that aren't partitioned by ID. -- This avoids missing child tables in subpartition sets that have differing ID data EXIT; END IF; -- v_top_parent initially set in DECLARE v_top_parent_schema := v_higher_parent_schema; v_top_parent_table := v_higher_parent_table; END IF; END LOOP; -- If custom start partition is set, use that. -- 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 IF p_start_partition IS NOT NULL THEN v_max := p_start_partition::bigint; ELSE v_sql := format('SELECT COALESCE(max(%I)::bigint, 0) FROM %I.%I LIMIT 1' , p_control , v_top_parent_schema , v_top_parent_table); EXECUTE v_sql INTO v_max; END IF; v_starting_partition_id := v_max - (v_max % v_id_interval); FOR i IN 0..p_premake LOOP -- Only make previous partitions if ID value is less than the starting value and positive (and custom start partition wasn't set) IF p_start_partition IS NULL AND (v_starting_partition_id - (v_id_interval*i)) > 0 AND (v_starting_partition_id - (v_id_interval*i)) < v_starting_partition_id THEN v_partition_id_array = array_append(v_partition_id_array, (v_starting_partition_id - v_id_interval*i)); END IF; v_partition_id_array = array_append(v_partition_id_array, (v_id_interval*i) + v_starting_partition_id); END LOOP; INSERT INTO @extschema@.part_config ( parent_table , partition_type , partition_interval , control , premake , constraint_cols , automatic_maintenance , inherit_fk , jobmon , upsert , trigger_return_null) VALUES ( p_parent_table , p_type , v_id_interval , p_control , p_premake , p_constraint_cols , p_automatic_maintenance , p_inherit_fk , p_jobmon , p_upsert , p_trigger_return_null); v_last_partition_created := @extschema@.create_partition_id(p_parent_table, v_partition_id_array, false); IF v_last_partition_created = false THEN -- This can happen with subpartitioning when future or past partitions prevent child creation because they're out of range of the parent -- See if it's actually a subpartition of a parent id partition WITH top_oid AS ( SELECT i.inhparent AS top_parent_oid FROM pg_catalog.pg_inherits i JOIN pg_catalog.pg_class c ON c.oid = i.inhrelid JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relname = v_parent_tablename::name AND n.nspname = v_parent_schema::name ) SELECT n.nspname||'.'||c.relname INTO v_top_parent_table FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace JOIN top_oid t ON c.oid = t.top_parent_oid JOIN @extschema@.part_config p ON p.parent_table = n.nspname||'.'||c.relname; IF v_top_parent_table IS NOT NULL THEN -- Create the lowest possible partition that is within the boundary of the parent SELECT child_start_id INTO v_parent_partition_id FROM @extschema@.show_partition_info(p_parent_table, p_parent_table := v_top_parent_table); IF v_starting_partition_id >= v_parent_partition_id THEN WHILE v_starting_partition_id >= v_parent_partition_id LOOP v_starting_partition_id := v_starting_partition_id - v_id_interval; END LOOP; v_starting_partition_id := v_starting_partition_id + v_id_interval; -- add one back since while loop set it one lower than is needed ELSIF v_starting_partition_id < v_parent_partition_id THEN WHILE v_starting_partition_id < v_parent_partition_id LOOP v_starting_partition_id := v_starting_partition_id + v_id_interval; END LOOP; -- Don't need to remove one since new starting id will fit in top parent interval END IF; v_partition_id_array = NULL; v_partition_id_array = array_append(v_partition_id_array, v_starting_partition_id); v_last_partition_created := @extschema@.create_partition_id(p_parent_table, v_partition_id_array, false); ELSE -- Currently unknown edge case if code gets here 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.'; IF v_jobmon_schema IS NOT NULL THEN PERFORM update_step(v_step_id, 'OK', 'Done'); IF v_step_overflow_id IS NOT NULL THEN PERFORM fail_job(v_job_id); ELSE PERFORM close_job(v_job_id); END IF; END IF; EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false'); RETURN v_success; END IF; END IF; -- End v_last_partition_created IF END IF; -- End IF id IF p_type <> 'native' THEN IF v_jobmon_schema IS NOT NULL THEN v_step_id := add_step(v_job_id, 'Creating partition function'); END IF; IF v_control_type = 'time' OR (v_control_type = 'id' AND p_epoch <> 'none') THEN PERFORM @extschema@.create_function_time(p_parent_table, v_job_id); IF v_jobmon_schema IS NOT NULL THEN PERFORM update_step(v_step_id, 'OK', 'Time function created'); END IF; ELSIF v_control_type = 'id' THEN PERFORM @extschema@.create_function_id(p_parent_table, v_job_id); IF v_jobmon_schema IS NOT NULL THEN PERFORM update_step(v_step_id, 'OK', 'ID function created'); END IF; END IF; IF v_jobmon_schema IS NOT NULL THEN v_step_id := add_step(v_job_id, 'Creating partition trigger'); END IF; PERFORM @extschema@.create_trigger(p_parent_table); END IF; -- end native check IF v_jobmon_schema IS NOT NULL THEN PERFORM update_step(v_step_id, 'OK', 'Done'); IF v_step_overflow_id IS NOT NULL THEN PERFORM fail_job(v_job_id); ELSE PERFORM close_job(v_job_id); END IF; END IF; EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false'); v_success := true; RETURN v_success; EXCEPTION WHEN OTHERS THEN GET STACKED DIAGNOSTICS ex_message = MESSAGE_TEXT, ex_context = PG_EXCEPTION_CONTEXT, ex_detail = PG_EXCEPTION_DETAIL, ex_hint = PG_EXCEPTION_HINT; IF v_jobmon_schema IS NOT NULL THEN IF v_job_id IS NULL THEN EXECUTE format('SELECT %I.add_job(''PARTMAN CREATE PARENT: %s'')', v_jobmon_schema, p_parent_table) INTO v_job_id; 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; ELSIF v_step_id IS NULL THEN EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before first step logged'')', v_jobmon_schema, v_job_id) INTO v_step_id; END IF; EXECUTE format('SELECT %I.update_step(%s, ''CRITICAL'', %L)', v_jobmon_schema, v_step_id, 'ERROR: '||coalesce(SQLERRM,'unknown')); EXECUTE format('SELECT %I.fail_job(%s)', v_jobmon_schema, v_job_id); END IF; RAISE EXCEPTION '% CONTEXT: % DETAIL: % HINT: %', ex_message, ex_context, ex_detail, ex_hint; END $$; CREATE OR REPLACE FUNCTION create_sub_parent( p_top_parent text , p_control text , p_type text , p_interval text , p_native_check text DEFAULT NULL , p_constraint_cols text[] DEFAULT NULL , p_premake int DEFAULT 4 , p_start_partition text DEFAULT NULL , p_inherit_fk boolean DEFAULT true , p_epoch text DEFAULT 'none' , p_upsert text DEFAULT '' , p_trigger_return_null boolean DEFAULT true , p_jobmon boolean DEFAULT true , p_debug boolean DEFAULT false) RETURNS boolean LANGUAGE plpgsql SECURITY DEFINER AS $$ DECLARE v_child_interval interval; v_child_start_id bigint; v_child_start_time timestamptz; v_control text; v_control_parent_type text; v_control_sub_type text; v_last_partition text; v_new_search_path text := '@extschema@,pg_temp'; v_old_search_path text; v_parent_epoch text; v_parent_interval text; v_parent_relkind char; v_parent_schema text; v_parent_tablename text; v_parent_type text; v_part_col text; v_partition_id_array bigint[]; v_partition_time_array timestamptz[]; v_relkind char; v_recreate_child boolean := false; v_row record; v_row_last_part record; v_run_maint boolean; v_sql text; v_success boolean := false; v_top_type text; BEGIN /* * Create a partition set that is a subpartition of an already existing partition set. * Given the parent table of any current partition set, it will turn all existing children into parent tables of their own partition sets * using the configuration options given as parameters to this function. * Uses another config table that allows for turning all future child partitions into a new parent automatically. */ SELECT n.nspname, c.relname, c.relkind INTO v_parent_schema, v_parent_tablename, v_parent_relkind FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE n.nspname = split_part(p_top_parent, '.', 1)::name AND c.relname = split_part(p_top_parent, '.', 2)::name; IF v_parent_tablename IS NULL THEN RAISE EXCEPTION 'Unable to find given parent table in system catalogs. Please create parent table first: %', p_top_parent; END IF; IF NOT @extschema@.check_partition_type(p_type) THEN RAISE EXCEPTION '% is not a valid partitioning type', p_type; END IF; IF v_parent_relkind = 'p' AND p_type <> 'native' THEN RAISE EXCEPTION 'Cannot create a non-native sub-partition of a native parent table. All levels of a sub-partition set must be either all native or all non-native'; END IF; SELECT partition_type, partition_interval, control, automatic_maintenance, epoch INTO v_parent_type, v_parent_interval, v_control, v_run_maint, v_parent_epoch FROM @extschema@.part_config WHERE parent_table = p_top_parent; IF v_parent_type IS NULL THEN RAISE EXCEPTION 'Cannot subpartition a table that is not managed by pg_partman already. Given top parent table not found in @extschema@.part_config: %', p_top_parent; END IF; IF p_type = 'native' AND (lower(p_native_check) <> 'yes' OR p_native_check IS NULL) THEN RAISE EXCEPTION 'The sub-partitioning of a natively partitoned table is a DESTRUCTIVE process unless all child tables are already natively subpartitioned. All child tables, and therefore ALL DATA, may be destroyed since the parent table must be declared as partitioned on first creation and cannot be altered later. See docs for more info. Set p_native_check parameter to "yes" if you are sure this is ok.'; END IF; IF p_upsert <> '' THEN IF current_setting('server_version_num')::int < 90500 THEN RAISE EXCEPTION 'INSERT ... ON CONFLICT (UPSERT) feature is only supported in PostgreSQL 9.5 and later'; END IF; IF p_type = 'native' THEN RAISE EXCEPTION 'Native partitioning does not currently support upsert. Use pg_partman''s partitioning methods instead if this is required'; END IF; END IF; SELECT general_type INTO v_control_parent_type FROM @extschema@.check_control_type(v_parent_schema, v_parent_tablename, v_control); SELECT current_setting('search_path') INTO v_old_search_path; EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_new_search_path, 'false'); -- Add the given parameters to the part_config_sub table first in case create_partition_* functions are called below INSERT INTO @extschema@.part_config_sub ( sub_parent , sub_control , sub_partition_type , sub_partition_interval , sub_constraint_cols , sub_premake , sub_inherit_fk , sub_automatic_maintenance , sub_epoch , sub_upsert , sub_jobmon , sub_trigger_return_null) VALUES ( p_top_parent , p_control , p_type , p_interval , p_constraint_cols , p_premake , p_inherit_fk , 'on' , p_epoch , p_upsert , p_jobmon , p_trigger_return_null); FOR v_row IN -- Loop through all current children to turn them into partitioned tables SELECT partition_schemaname AS child_schema, partition_tablename AS child_tablename FROM @extschema@.show_partitions(p_top_parent) LOOP SELECT general_type INTO v_control_sub_type FROM @extschema@.check_control_type(v_row.child_schema, v_row.child_tablename, p_control); SELECT c.relkind INTO v_relkind FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE n.nspname = v_row.child_schema AND c.relname = v_row.child_tablename; -- If both parent and sub-parent are the same partition type (time/id), ensure boundaries of sub-parent are within parent IF (v_control_parent_type = 'time' AND v_control_sub_type = 'time') OR (v_control_parent_type = 'id' AND v_parent_epoch <> 'none' AND v_control_sub_type = 'id' AND p_epoch <> 'none') THEN CASE WHEN p_interval = 'yearly' THEN v_child_interval := '1 year'; WHEN p_interval = 'quarterly' THEN v_child_interval := '3 months'; WHEN p_interval = 'monthly' THEN v_child_interval := '1 month'; WHEN p_interval = 'weekly' THEN v_child_interval := '1 week'; WHEN p_interval = 'daily' THEN v_child_interval := '1 day'; WHEN p_interval = 'hourly' THEN v_child_interval := '1 hour'; WHEN p_interval = 'half-hour' THEN v_child_interval := '30 mins'; WHEN p_interval = 'quarter-hour' THEN v_child_interval := '15 mins'; ELSE v_child_interval := p_interval::interval; IF v_child_interval < '1 second'::interval THEN RAISE EXCEPTION 'Partitioning interval must be 1 second or greater'; END IF; END CASE; IF v_child_interval >= v_parent_interval::interval THEN EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false'); RAISE EXCEPTION 'Sub-partition interval cannot be greater than or equal to the given parent interval'; END IF; IF v_child_interval = '1 week' AND v_parent_interval::interval > '1 week'::interval THEN EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false'); RAISE EXCEPTION 'Due to conflicting data boundaries between ISO weeks and any larger interval of time, pg_partman cannot support a sub-partition interval of weekly'; END IF; ELSIF v_control_parent_type = 'id' AND v_control_sub_type = 'id' THEN IF p_interval::bigint >= v_parent_interval::bigint THEN EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false'); RAISE EXCEPTION 'Sub-partition interval cannot be greater than or equal to the given parent interval'; END IF; END IF; IF p_type = 'native' THEN IF v_relkind <> 'p' THEN -- Not natively partitioned already. Drop it and recreate as such. RAISE WARNING 'Child table % is not natively partitioned. Dropping and recreating with native partitioning' , v_row.child_schema||'.'||v_row.child_tablename; SELECT child_start_time, child_start_id INTO v_child_start_time, v_child_start_id FROM @extschema@.show_partition_info(v_row.child_schema||'.'||v_row.child_tablename , v_parent_interval , p_top_parent); EXECUTE format('DROP TABLE %I.%I', v_row.child_schema, v_row.child_tablename); v_recreate_child := true; IF v_child_start_id IS NOT NULL THEN v_partition_id_array[0] := v_child_start_id; PERFORM @extschema@.create_partition_id(p_top_parent, v_partition_id_array, true); ELSIF v_child_start_time IS NOT NULL THEN v_partition_time_array[0] := v_child_start_time; PERFORM @extschema@.create_partition_time(p_top_parent, v_partition_time_array, true); END IF; ELSE SELECT a.attname INTO v_part_col FROM pg_attribute a JOIN pg_class c ON a.attrelid = c.oid JOIN pg_namespace n ON c.relnamespace = n.oid WHERE n.nspname = v_row.child_schema::name AND c.relname = v_row.child_tablename::name AND attnum IN (SELECT unnest(partattrs) FROM pg_partitioned_table p WHERE a.attrelid = p.partrelid); IF p_control <> v_part_col THEN RAISE EXCEPTION 'Attempted to natively sub-partition an existing table that has the partition column (%) defined differently than the control column given (%)', v_part_col, p_control; ELSE -- Child table is already natively subpartitioned properly. Skip the rest. CONTINUE; END IF; END IF; -- end 'p' relkind check END IF; -- end native check IF v_recreate_child = false THEN -- Always call create_parent() if child table wasn't recreated above. -- If it was, the create_partition_*() functions called above also call create_parent if any of the tables -- it creates are in the part_config_sub table. Since it was inserted there above, -- it should call it appropriately v_sql := format('SELECT @extschema@.create_parent( p_parent_table := %L , p_control := %L , p_type := %L , p_interval := %L , p_constraint_cols := %L , p_premake := %L , p_automatic_maintenance := %L , p_start_partition := %L , p_inherit_fk := %L , p_epoch := %L , p_upsert := %L , p_trigger_return_null := %L , p_jobmon := %L , p_debug := %L )' , v_row.child_schema||'.'||v_row.child_tablename , p_control , p_type , p_interval , p_constraint_cols , p_premake , 'on' , p_start_partition , p_inherit_fk , p_epoch , p_upsert , p_trigger_return_null , p_jobmon , p_debug); EXECUTE v_sql; END IF; -- end recreate check END LOOP; v_success := true; EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false'); RETURN v_success; END $$; CREATE OR REPLACE FUNCTION check_name_length (p_object_name text, p_suffix text DEFAULT NULL, p_table_partition boolean DEFAULT FALSE) RETURNS text LANGUAGE plpgsql IMMUTABLE SECURITY DEFINER AS $$ DECLARE v_new_length int; v_new_name text; BEGIN /* * Truncate the name of the given object if it is greater than the postgres default max (63 characters). * Also appends given suffix and schema if given and truncates the name so that the entire suffix will fit. * Returns original name (with suffix if given) if it doesn't require truncation * Given security definer since it's called by the trigger functions */ IF p_table_partition IS TRUE AND (p_suffix IS NULL) THEN RAISE EXCEPTION 'Table partition name requires a suffix value'; END IF; IF p_table_partition THEN -- 61 characters to account for _p in partition name IF char_length(p_object_name) + char_length(p_suffix) >= 61 THEN v_new_length := 61 - char_length(p_suffix); v_new_name := substring(p_object_name from 1 for v_new_length) || '_p' || p_suffix; ELSE v_new_name := p_object_name||'_p'||p_suffix; END IF; ELSE IF char_length(p_object_name) + char_length(COALESCE(p_suffix, '')) >= 63 THEN v_new_length := 63 - char_length(COALESCE(p_suffix, '')); v_new_name := substring(p_object_name from 1 for v_new_length) || COALESCE(p_suffix, ''); ELSE v_new_name := p_object_name||COALESCE(p_suffix, ''); END IF; END IF; RETURN v_new_name; END $$; CREATE OR REPLACE FUNCTION apply_cluster(p_parent_schema text, p_parent_tablename text, p_child_schema text, p_child_tablename text) RETURNS void LANGUAGE plpgsql SECURITY DEFINER AS $$ DECLARE v_new_search_path text := '@extschema@,pg_temp'; v_old_search_path text; v_parent_indexdef text; v_relkind char; v_row record; v_sql text; BEGIN /* * Function to apply cluster from parent to child table * Adapted from code fork by https://github.com/dturon/pg_partman */ SELECT current_setting('search_path') INTO v_old_search_path; EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_new_search_path, 'false'); SELECT c.relkind INTO v_relkind FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE n.nspname = p_parent_schema AND c.relname = p_parent_tablename; IF v_relkind = 'p' THEN RAISE EXCEPTION 'This function cannot run on natively partitioned tables'; ELSIF v_relkind IS NULL THEN RAISE EXCEPTION 'Unable to find given table in system catalogs: %.%', p_parent_schema, p_parent_tablename; END IF; WITH parent_info AS ( SELECT c.oid AS parent_oid FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE n.nspname = p_parent_schema::name AND c.relname = p_parent_tablename::name ) SELECT substring(pg_get_indexdef(i.indexrelid) from ' USING .*$') AS index_def INTO v_parent_indexdef FROM pg_catalog.pg_index i JOIN pg_catalog.pg_class c ON i.indexrelid = c.oid JOIN parent_info p ON p.parent_oid = indrelid WHERE i.indisclustered = true; -- Loop over all existing indexes in child table to find one with matching definition FOR v_row IN WITH child_info AS ( SELECT c.oid AS child_oid FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE n.nspname = p_child_schema::name AND c.relname = p_child_tablename::name ) SELECT substring(pg_get_indexdef(i.indexrelid) from ' USING .*$') AS child_indexdef , c.relname AS child_indexname FROM pg_catalog.pg_index i JOIN pg_catalog.pg_class c ON i.indexrelid = c.oid JOIN child_info p ON p.child_oid = indrelid LOOP IF v_row.child_indexdef = v_parent_indexdef THEN v_sql = format('ALTER TABLE %I.%I CLUSTER ON %I', p_child_schema, p_child_tablename, v_row.child_indexname); RAISE DEBUG '%', v_sql; EXECUTE v_sql; END IF; END LOOP; EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false'); END; $$; CREATE OR REPLACE FUNCTION apply_privileges(p_parent_schema text, p_parent_tablename text, p_child_schema text, p_child_tablename text, p_job_id bigint DEFAULT NULL) RETURNS void LANGUAGE plpgsql SECURITY DEFINER AS $$ DECLARE ex_context text; ex_detail text; ex_hint text; ex_message text; v_all text[] := ARRAY['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER']; v_child_grant record; v_child_owner text; v_grantees text[]; v_job_id bigint; v_jobmon boolean; v_jobmon_schema text; v_match boolean; v_parent_grant record; v_parent_owner text; v_revoke text; v_row_revoke record; v_sql text; v_step_id bigint; BEGIN /* * Apply privileges and ownership that exist on a given parent to the given child table */ SELECT jobmon INTO v_jobmon FROM @extschema@.part_config WHERE parent_table = p_parent_schema ||'.'|| p_parent_tablename; IF v_jobmon IS NULL THEN RAISE EXCEPTION 'Given table is not managed by this extention: %.%', p_parent_schema, p_parent_tablename; END IF; SELECT pg_get_userbyid(c.relowner) INTO v_parent_owner FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE n.nspname = p_parent_schema::name AND c.relname = p_parent_tablename::name; SELECT pg_get_userbyid(c.relowner) INTO v_child_owner FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE n.nspname = p_child_schema::name AND c.relname = p_child_tablename::name; IF v_parent_owner IS NULL THEN RAISE EXCEPTION 'Given parent table does not exist: %.%', p_parent_schema, p_parent_tablename; END IF; IF v_child_owner IS NULL THEN RAISE EXCEPTION 'Given child table does not exist: %.%', p_child_schema, p_child_tablename; END IF; IF v_jobmon THEN SELECT nspname INTO v_jobmon_schema FROM pg_namespace n, pg_extension e WHERE e.extname = 'pg_jobmon' AND e.extnamespace = n.oid; END IF; IF v_jobmon_schema IS NOT NULL THEN IF p_job_id IS NULL THEN EXECUTE format('SELECT %I.add_job(%L)', v_jobmon_schema, format('PARTMAN APPLYING PRIVILEGES TO CHILD TABLE: %s.%s', p_child_schema, p_child_tablename)) INTO v_job_id; ELSE v_job_id := p_job_id; END IF; EXECUTE format('SELECT %I.add_step(%L, %L)', v_jobmon_schema, v_job_id, format('Setting new child table privileges for %s.%s', p_child_schema, p_child_tablename)) INTO v_step_id; END IF; IF v_jobmon_schema IS NOT NULL THEN EXECUTE format('SELECT %I.update_step(%L, %L, %L)' , v_jobmon_schema , v_step_id , 'PENDING' , format('Applying privileges on child partition: %s.%s' , p_child_schema , p_child_tablename) ); END IF; FOR v_parent_grant IN SELECT array_agg(DISTINCT privilege_type::text ORDER BY privilege_type::text) AS types , grantee FROM @extschema@.table_privs WHERE table_schema = p_parent_schema::name AND table_name = p_parent_tablename::name GROUP BY grantee LOOP -- Compare parent & child grants. Don't re-apply if it already exists v_match := false; v_sql := NULL; FOR v_child_grant IN SELECT array_agg(DISTINCT privilege_type::text ORDER BY privilege_type::text) AS types , grantee FROM @extschema@.table_privs WHERE table_schema = p_child_schema::name AND table_name = p_child_tablename::name GROUP BY grantee LOOP IF v_parent_grant.types = v_child_grant.types AND v_parent_grant.grantee = v_child_grant.grantee THEN v_match := true; END IF; END LOOP; IF v_match = false THEN IF v_parent_grant.grantee = 'PUBLIC' THEN v_sql := 'GRANT %s ON %I.%I TO %s'; ELSE v_sql := 'GRANT %s ON %I.%I TO %I'; END IF; EXECUTE format(v_sql , array_to_string(v_parent_grant.types, ',') , p_child_schema , p_child_tablename , v_parent_grant.grantee); v_sql := NULL; SELECT string_agg(r, ',') INTO v_revoke FROM (SELECT unnest(v_all) AS r EXCEPT SELECT unnest(v_parent_grant.types)) x; IF v_revoke IS NOT NULL THEN IF v_parent_grant.grantee = 'PUBLIC' THEN v_sql := 'REVOKE %s ON %I.%I FROM %s CASCADE'; ELSE v_sql := 'REVOKE %s ON %I.%I FROM %I CASCADE'; END IF; EXECUTE format(v_sql , v_revoke , p_child_schema , p_child_tablename , v_parent_grant.grantee); v_sql := NULL; END IF; END IF; v_grantees := array_append(v_grantees, v_parent_grant.grantee::text); END LOOP; -- Revoke all privileges from roles that have none on the parent IF v_grantees IS NOT NULL THEN FOR v_row_revoke IN SELECT role FROM ( SELECT DISTINCT grantee::text AS role FROM @extschema@.table_privs WHERE table_schema = p_child_schema::name AND table_name = p_child_tablename::name EXCEPT SELECT unnest(v_grantees)) x LOOP IF v_row_revoke.role IS NOT NULL THEN IF v_row_revoke.role = 'PUBLIC' THEN v_sql := 'REVOKE ALL ON %I.%I FROM %s'; ELSE v_sql := 'REVOKE ALL ON %I.%I FROM %I'; END IF; EXECUTE format(v_sql , p_child_schema , p_child_tablename , v_row_revoke.role); END IF; END LOOP; END IF; IF v_parent_owner <> v_child_owner THEN EXECUTE format('ALTER TABLE %I.%I OWNER TO %I' , p_child_schema , p_child_tablename , v_parent_owner); END IF; IF v_jobmon_schema IS NOT NULL THEN EXECUTE format('SELECT %I.update_step(%L, %L, %L)', v_jobmon_schema, v_step_id, 'OK', 'Done'); IF p_job_id IS NULL THEN EXECUTE format('SELECT %I.close_job(%L)', v_jobmon_schema, v_job_id); END IF; END IF; EXCEPTION WHEN OTHERS THEN GET STACKED DIAGNOSTICS ex_message = MESSAGE_TEXT, ex_context = PG_EXCEPTION_CONTEXT, ex_detail = PG_EXCEPTION_DETAIL, ex_hint = PG_EXCEPTION_HINT; IF v_jobmon_schema IS NOT NULL THEN IF v_job_id IS NULL THEN EXECUTE format('SELECT %I.add_job(''PARTMAN RE-APPLYING PRIVILEGES TO ALL CHILD TABLES OF: %s'')', v_jobmon_schema, p_parent_tablename) INTO v_job_id; EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before job logging started'')', v_jobmon_schema, v_job_id, p_parent_tablename) INTO v_step_id; ELSIF v_step_id IS NULL THEN EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before first step logged'')', v_jobmon_schema, v_job_id) INTO v_step_id; END IF; EXECUTE format('SELECT %I.update_step(%s, ''CRITICAL'', %L)', v_jobmon_schema, v_step_id, 'ERROR: '||coalesce(SQLERRM,'unknown')); EXECUTE format('SELECT %I.fail_job(%s)', v_jobmon_schema, v_job_id); END IF; RAISE EXCEPTION '% CONTEXT: % DETAIL: % HINT: %', ex_message, ex_context, ex_detail, ex_hint; END $$; CREATE OR REPLACE FUNCTION create_trigger(p_parent_table text) RETURNS void LANGUAGE plpgsql SECURITY DEFINER AS $$ DECLARE v_function_name text; v_new_search_path text := '@extschema@,pg_temp'; v_old_search_path text; v_parent_schema text; v_parent_tablename text; v_relkind char; v_trig_name text; v_trig_sql text; BEGIN /* * Function to create partitioning trigger on parent table */ SELECT current_setting('search_path') INTO v_old_search_path; EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_new_search_path, 'false'); SELECT n.nspname, c.relname, c.relkind INTO v_parent_schema, v_parent_tablename, v_relkind FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE n.nspname = split_part(p_parent_table, '.', 1)::name AND c.relname = split_part(p_parent_table, '.', 2)::name; IF v_relkind = 'p' THEN RAISE EXCEPTION 'This function cannot run on natively partitioned tables'; ELSIF v_relkind IS NULL THEN RAISE EXCEPTION 'Unable to find given table in system catalogs: %.%', v_parent_schema, v_parent_tablename; END IF; v_trig_name := @extschema@.check_name_length(p_object_name := v_parent_tablename, p_suffix := '_part_trig'); -- Ensure function name matches the naming pattern v_function_name := @extschema@.check_name_length(v_parent_tablename, '_part_trig_func', FALSE); v_trig_sql := format('CREATE TRIGGER %I BEFORE INSERT ON %I.%I FOR EACH ROW EXECUTE PROCEDURE %I.%I()' , v_trig_name , v_parent_schema , v_parent_tablename , v_parent_schema , v_function_name); EXECUTE v_trig_sql; EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false'); END $$; /* * Function to ensure a column is dropped in all child tables, no matter when it was created */ CREATE OR REPLACE FUNCTION drop_partition_column(p_parent_table text, p_column text) RETURNS void LANGUAGE plpgsql SECURITY DEFINER AS $$ DECLARE v_parent_oid oid; v_parent_schema text; v_parent_tablename text; v_row record; BEGIN SELECT c.oid, n.nspname, c.relname INTO v_parent_oid, v_parent_schema, v_parent_tablename FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE n.nspname = split_part(p_parent_table, '.', 1)::name AND c.relname = split_part(p_parent_table, '.', 2)::name; IF v_parent_oid IS NULL THEN RAISE EXCEPTION 'Given parent table does not exist: %', p_parent_table; END IF; EXECUTE format('ALTER TABLE %I.%I DROP COLUMN IF EXISTS %I', v_parent_schema, v_parent_tablename, p_column); FOR v_row IN SELECT n.nspname AS child_schema, c.relname AS child_table FROM pg_catalog.pg_inherits h JOIN pg_catalog.pg_class c ON h.inhrelid = c.oid JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE inhparent = v_parent_oid LOOP EXECUTE format('ALTER TABLE %I.%I DROP COLUMN IF EXISTS %I', v_row.child_schema, v_row.child_table, p_column); END LOOP; END $$; CREATE OR REPLACE FUNCTION partition_data_time( p_parent_table text , p_batch_count int DEFAULT 1 , p_batch_interval interval DEFAULT NULL , p_lock_wait numeric DEFAULT 0 , p_order text DEFAULT 'ASC' , p_analyze boolean DEFAULT true) RETURNS bigint LANGUAGE plpgsql SECURITY DEFINER AS $$ DECLARE v_control text; v_control_type text; v_datetime_string text; v_current_partition_name text; v_epoch text; v_last_partition text; v_lock_iter int := 1; v_lock_obtained boolean := FALSE; v_max_partition_timestamp timestamptz; v_min_partition_timestamp timestamptz; v_new_search_path text := '@extschema@,pg_temp'; v_old_search_path text; v_parent_schema text; v_parent_tablename text; v_partition_expression text; v_partition_interval interval; v_partition_suffix text; v_partition_timestamp timestamptz[]; v_rowcount bigint; v_start_control timestamptz; v_total_rows bigint := 0; v_type text; BEGIN /* * Populate the child table(s) of a time-based partition set with old data from the original parent */ SELECT partition_type , partition_interval::interval , control , datetime_string , epoch INTO v_type , v_partition_interval , v_control , v_datetime_string , v_epoch FROM @extschema@.part_config WHERE parent_table = p_parent_table AND partition_type IN ('partman', 'time-custom'); IF NOT FOUND THEN RAISE EXCEPTION 'ERROR: No entry in part_config found for non-native partitioning for given table: %', p_parent_table; END IF; SELECT schemaname, tablename INTO v_parent_schema, v_parent_tablename FROM pg_catalog.pg_tables WHERE schemaname = split_part(p_parent_table, '.', 1)::name AND tablename = split_part(p_parent_table, '.', 2)::name; SELECT general_type INTO v_control_type FROM @extschema@.check_control_type(v_parent_schema, v_parent_tablename, v_control); IF v_control_type <> 'time' THEN IF (v_control_type = 'id' AND v_epoch = 'none') OR v_control_type <> 'id' THEN 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; END IF; END IF; SELECT current_setting('search_path') INTO v_old_search_path; EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_new_search_path, 'false'); IF p_batch_interval IS NULL OR p_batch_interval > v_partition_interval THEN p_batch_interval := v_partition_interval; END IF; SELECT partition_tablename INTO v_last_partition FROM @extschema@.show_partitions(p_parent_table, 'DESC') LIMIT 1; v_partition_expression := CASE WHEN v_epoch = 'seconds' THEN format('to_timestamp(%I)', v_control) WHEN v_epoch = 'milliseconds' THEN format('to_timestamp((%I/1000)::float)', v_control) ELSE format('%I', v_control) END; FOR i IN 1..p_batch_count LOOP IF p_order = 'ASC' THEN EXECUTE format('SELECT min(%s) FROM ONLY %I.%I', v_partition_expression, v_parent_schema, v_parent_tablename) INTO v_start_control; ELSIF p_order = 'DESC' THEN EXECUTE format('SELECT max(%s) FROM ONLY %I.%I', v_partition_expression, v_parent_schema, v_parent_tablename) INTO v_start_control; ELSE RAISE EXCEPTION 'Invalid value for p_order. Must be ASC or DESC'; END IF; IF v_start_control IS NULL THEN EXIT; END IF; IF v_type = 'partman' THEN CASE WHEN v_partition_interval = '15 mins' THEN v_min_partition_timestamp := date_trunc('hour', v_start_control) + '15min'::interval * floor(date_part('minute', v_start_control) / 15.0); WHEN v_partition_interval = '30 mins' THEN v_min_partition_timestamp := date_trunc('hour', v_start_control) + '30min'::interval * floor(date_part('minute', v_start_control) / 30.0); WHEN v_partition_interval = '1 hour' THEN v_min_partition_timestamp := date_trunc('hour', v_start_control); WHEN v_partition_interval = '1 day' THEN v_min_partition_timestamp := date_trunc('day', v_start_control); WHEN v_partition_interval = '1 week' THEN v_min_partition_timestamp := date_trunc('week', v_start_control); WHEN v_partition_interval = '1 month' THEN v_min_partition_timestamp := date_trunc('month', v_start_control); WHEN v_partition_interval = '3 months' THEN v_min_partition_timestamp := date_trunc('quarter', v_start_control); WHEN v_partition_interval = '1 year' THEN v_min_partition_timestamp := date_trunc('year', v_start_control); END CASE; ELSIF v_type = 'time-custom' THEN SELECT child_start_time INTO v_min_partition_timestamp FROM @extschema@.show_partition_info(v_parent_schema||'.'||v_last_partition , v_partition_interval::text , p_parent_table); v_max_partition_timestamp := v_min_partition_timestamp + v_partition_interval; LOOP IF v_start_control >= v_min_partition_timestamp AND v_start_control < v_max_partition_timestamp THEN EXIT; ELSE BEGIN IF v_start_control > v_max_partition_timestamp THEN -- Keep going forward in time, checking if child partition time interval encompasses the current v_start_control value v_min_partition_timestamp := v_max_partition_timestamp; v_max_partition_timestamp := v_max_partition_timestamp + v_partition_interval; ELSE -- Keep going backwards in time, checking if child partition time interval encompasses the current v_start_control value v_max_partition_timestamp := v_min_partition_timestamp; v_min_partition_timestamp := v_min_partition_timestamp - v_partition_interval; END IF; EXCEPTION WHEN datetime_field_overflow THEN RAISE EXCEPTION 'Attempted partition time interval is outside PostgreSQL''s supported time range. Unable to create partition with interval before timestamp % ', v_min_partition_timestamp; END; END IF; END LOOP; END IF; v_partition_timestamp := ARRAY[v_min_partition_timestamp]; IF p_order = 'ASC' THEN -- Ensure batch interval given as parameter doesn't cause maximum to overflow the current partition maximum IF (v_start_control + p_batch_interval) >= (v_min_partition_timestamp + v_partition_interval) THEN v_max_partition_timestamp := v_min_partition_timestamp + v_partition_interval; ELSE v_max_partition_timestamp := v_start_control + p_batch_interval; END IF; ELSIF p_order = 'DESC' THEN -- Must be greater than max value still in parent table since query below grabs < max v_max_partition_timestamp := v_min_partition_timestamp + v_partition_interval; -- Ensure batch interval given as parameter doesn't cause minimum to underflow current partition minimum IF (v_start_control - p_batch_interval) >= v_min_partition_timestamp THEN v_min_partition_timestamp = v_start_control - p_batch_interval; END IF; ELSE RAISE EXCEPTION 'Invalid value for p_order. Must be ASC or DESC'; END IF; -- do some locking with timeout, if required IF p_lock_wait > 0 THEN v_lock_iter := 0; WHILE v_lock_iter <= 5 LOOP v_lock_iter := v_lock_iter + 1; BEGIN EXECUTE format('SELECT * FROM ONLY %I.%I WHERE %s >= %L AND %3$s < %5$L FOR UPDATE NOWAIT' , v_parent_schema , v_parent_tablename , v_partition_expression , v_min_partition_timestamp , v_max_partition_timestamp); v_lock_obtained := TRUE; EXCEPTION WHEN lock_not_available THEN PERFORM pg_sleep( p_lock_wait / 5.0 ); CONTINUE; END; EXIT WHEN v_lock_obtained; END LOOP; IF NOT v_lock_obtained THEN RETURN -1; END IF; END IF; PERFORM @extschema@.create_partition_time(p_parent_table, v_partition_timestamp, p_analyze); -- This suffix generation code is in create_partition_time() as well v_partition_suffix := to_char(v_min_partition_timestamp, v_datetime_string); v_current_partition_name := @extschema@.check_name_length(v_parent_tablename, v_partition_suffix, TRUE); EXECUTE format('WITH partition_data AS ( DELETE FROM ONLY %I.%I WHERE %s >= %L AND %3$s < %5$L RETURNING *) INSERT INTO %I.%I SELECT * FROM partition_data' , v_parent_schema , v_parent_tablename , v_partition_expression , v_min_partition_timestamp , v_max_partition_timestamp , v_parent_schema , v_current_partition_name); GET DIAGNOSTICS v_rowcount = ROW_COUNT; v_total_rows := v_total_rows + v_rowcount; IF v_rowcount = 0 THEN EXIT; END IF; END LOOP; PERFORM @extschema@.create_function_time(p_parent_table); EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false'); RETURN v_total_rows; END $$; CREATE OR REPLACE FUNCTION create_function_id(p_parent_table text, p_job_id bigint DEFAULT NULL) RETURNS void LANGUAGE plpgsql SECURITY DEFINER AS $$ DECLARE ex_context text; ex_detail text; ex_hint text; ex_message text; v_control text; v_control_type text; v_count int; v_current_partition_name text; v_current_partition_id bigint; v_datetime_string text; v_final_partition_id bigint; v_function_name text; v_higher_parent_schema text := split_part(p_parent_table, '.', 1); v_higher_parent_table text := split_part(p_parent_table, '.', 2); v_id_position int; v_job_id bigint; v_jobmon boolean; v_jobmon_schema text; v_last_partition text; v_max bigint; v_next_partition_id bigint; v_next_partition_name text; v_new_search_path text := '@extschema@,pg_temp'; v_old_search_path text; v_optimize_trigger int; v_parent_schema text; v_parent_tablename text; v_partition_interval bigint; v_premake int; v_prev_partition_id bigint; v_prev_partition_name text; v_relkind char; v_row_max_id record; v_run_maint text; v_step_id bigint; v_top_parent text := p_parent_table; v_trig_func text; v_trigger_exception_handling boolean; v_trigger_return_null boolean; v_upsert text; BEGIN /* * Create the trigger function for the parent table of an id-based partition set */ SELECT partition_interval::bigint , control , premake , optimize_trigger , automatic_maintenance , jobmon , trigger_exception_handling , upsert , trigger_return_null INTO v_partition_interval , v_control , v_premake , v_optimize_trigger , v_run_maint , v_jobmon , v_trigger_exception_handling , v_upsert , v_trigger_return_null FROM @extschema@.part_config WHERE parent_table = p_parent_table AND partition_type = 'partman'; IF NOT FOUND THEN RAISE EXCEPTION 'ERROR: no non-native pg_partman config found for %', p_parent_table; END IF; SELECT n.nspname, c.relname, c.relkind INTO v_parent_schema, v_parent_tablename, v_relkind FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE n.nspname = split_part(p_parent_table, '.', 1)::name AND c.relname = split_part(p_parent_table, '.', 2)::name; IF v_relkind = 'p' THEN RAISE EXCEPTION 'This function cannot run on natively partitioned tables'; ELSIF v_relkind IS NULL THEN RAISE EXCEPTION 'Unable to find given table in system catalogs: %.%', v_parent_schema, v_parent_tablename; END IF; SELECT general_type INTO v_control_type FROM @extschema@.check_control_type(v_parent_schema, v_parent_tablename, v_control); IF v_control_type <> 'id' THEN RAISE EXCEPTION 'Cannot run create_function_id on partition set without id based control column. Found: %', v_control_type; END IF; SELECT current_setting('search_path') INTO v_old_search_path; IF v_jobmon THEN 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; IF v_jobmon_schema IS NOT NULL THEN v_new_search_path := '@extschema@,'||v_jobmon_schema||',pg_temp'; END IF; END IF; EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_new_search_path, 'false'); SELECT partition_tablename INTO v_last_partition FROM @extschema@.show_partitions(p_parent_table, 'DESC') LIMIT 1; v_function_name := @extschema@.check_name_length(v_parent_tablename, '_part_trig_func', FALSE); IF v_jobmon_schema IS NOT NULL THEN IF p_job_id IS NULL THEN v_job_id := add_job(format('PARTMAN CREATE FUNCTION: %s', p_parent_table)); ELSE v_job_id = p_job_id; END IF; v_step_id := add_step(v_job_id, format('Creating partition function for table %s', p_parent_table)); END IF; -- Get the highest level top parent if multi-level partitioned in order to get proper max() value below WHILE v_higher_parent_table IS NOT NULL LOOP -- initially set in DECLARE WITH top_oid AS ( SELECT i.inhparent AS top_parent_oid FROM pg_catalog.pg_inherits i JOIN pg_catalog.pg_class c ON c.oid = i.inhrelid JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname = v_higher_parent_schema::name AND c.relname = v_higher_parent_table::name ) SELECT n.nspname, c.relname INTO v_higher_parent_schema, v_higher_parent_table FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace JOIN top_oid t ON c.oid = t.top_parent_oid JOIN @extschema@.part_config p ON p.parent_table = n.nspname||'.'||c.relname WHERE p.partition_type = 'id'; IF v_higher_parent_table IS NOT NULL THEN -- initially set in DECLARE v_top_parent := v_higher_parent_schema||'.'||v_higher_parent_table; END IF; END LOOP; -- Loop through child tables starting from highest to get current max value in partition set -- Avoids doing a scan on entire partition set and/or getting any values accidentally in parent. FOR v_row_max_id IN SELECT partition_schemaname, partition_tablename FROM @extschema@.show_partitions(v_top_parent, 'DESC') LOOP 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; IF v_max IS NOT NULL THEN EXIT; END IF; END LOOP; IF v_max IS NULL THEN v_max := 0; END IF; v_current_partition_id = v_max - (v_max % v_partition_interval); v_next_partition_id := v_current_partition_id + v_partition_interval; v_current_partition_name := @extschema@.check_name_length(v_parent_tablename, v_current_partition_id::text, TRUE); v_trig_func := format('CREATE OR REPLACE FUNCTION %I.%I() RETURNS trigger LANGUAGE plpgsql AS $t$ DECLARE v_count int; v_current_partition_id bigint; v_current_partition_name text; v_id_position int; v_last_partition text := %L; v_next_partition_id bigint; v_next_partition_name text; v_partition_created boolean; BEGIN IF TG_OP = ''INSERT'' THEN IF NEW.%I >= %s AND NEW.%I < %s THEN ' , v_parent_schema , v_function_name , v_last_partition , v_control , v_current_partition_id , v_control , v_next_partition_id ); SELECT count(*) INTO v_count FROM pg_catalog.pg_tables WHERE schemaname = v_parent_schema::name AND tablename = v_current_partition_name::name; IF v_count > 0 THEN v_trig_func := v_trig_func || format(' INSERT INTO %I.%I VALUES (NEW.*) %s; ', v_parent_schema, v_current_partition_name, v_upsert); ELSE v_trig_func := v_trig_func || ' -- Child table for current values does not exist in this partition set, so write to parent RETURN NEW;'; END IF; FOR i IN 1..v_optimize_trigger LOOP v_prev_partition_id := v_current_partition_id - (v_partition_interval * i); v_next_partition_id := v_current_partition_id + (v_partition_interval * i); v_final_partition_id := v_next_partition_id + v_partition_interval; v_prev_partition_name := @extschema@.check_name_length(v_parent_tablename, v_prev_partition_id::text, TRUE); v_next_partition_name := @extschema@.check_name_length(v_parent_tablename, v_next_partition_id::text, TRUE); -- Check that child table exist before making a rule to insert to them. -- Handles optimize_trigger being larger than premake (to go back in time further) and edge case of changing optimize_trigger immediately after running create_parent(). SELECT count(*) INTO v_count FROM pg_catalog.pg_tables WHERE schemaname = v_parent_schema::name AND tablename = v_prev_partition_name::name; IF v_count > 0 THEN -- Only handle previous partitions if they're starting above zero IF v_prev_partition_id >= 0 THEN v_trig_func := v_trig_func ||format(' ELSIF NEW.%I >= %s AND NEW.%I < %s THEN INSERT INTO %I.%I VALUES (NEW.*) %s; ' , v_control , v_prev_partition_id , v_control , v_prev_partition_id + v_partition_interval , v_parent_schema , v_prev_partition_name , v_upsert ); END IF; END IF; SELECT count(*) INTO v_count FROM pg_catalog.pg_tables WHERE schemaname = v_parent_schema::name AND tablename = v_next_partition_name::name; IF v_count > 0 THEN v_trig_func := v_trig_func ||format(' ELSIF NEW.%I >= %s AND NEW.%I < %s THEN INSERT INTO %I.%I VALUES (NEW.*) %s;' , v_control , v_next_partition_id , v_control , v_final_partition_id , v_parent_schema , v_next_partition_name , v_upsert ); END IF; END LOOP; v_trig_func := v_trig_func ||format(' ELSE v_current_partition_id := NEW.%I - (NEW.%I %% %s); v_current_partition_name := @extschema@.check_name_length(%L, v_current_partition_id::text, TRUE); SELECT count(*) INTO v_count FROM pg_catalog.pg_tables WHERE schemaname = %L::name AND tablename = v_current_partition_name::name; IF v_count > 0 THEN EXECUTE format(''INSERT INTO %%I.%%I VALUES($1.*) %s'', %L, v_current_partition_name) USING NEW; ELSE RETURN NEW; END IF; END IF;' , v_control , v_control , v_partition_interval , v_parent_tablename , v_parent_schema , v_upsert , v_parent_schema ); v_trig_func := v_trig_func ||' END IF;'; IF v_trigger_return_null IS TRUE THEN v_trig_func := v_trig_func ||' RETURN NULL;'; ELSE v_trig_func := v_trig_func ||' RETURN NEW;'; END IF; IF v_trigger_exception_handling THEN v_trig_func := v_trig_func ||' EXCEPTION WHEN OTHERS THEN RAISE WARNING ''pg_partman insert into child table failed, row inserted into parent (%.%). ERROR: %'', TG_TABLE_SCHEMA, TG_TABLE_NAME, COALESCE(SQLERRM, ''unknown''); RETURN NEW;'; END IF; v_trig_func := v_trig_func ||' END $t$;'; EXECUTE v_trig_func; IF v_jobmon_schema IS NOT NULL THEN PERFORM update_step(v_step_id, 'OK', format('Added function for current id interval: %s to %s', v_current_partition_id, v_final_partition_id-1)); END IF; IF v_jobmon_schema IS NOT NULL THEN PERFORM close_job(v_job_id); END IF; EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false'); EXCEPTION WHEN OTHERS THEN GET STACKED DIAGNOSTICS ex_message = MESSAGE_TEXT, ex_context = PG_EXCEPTION_CONTEXT, ex_detail = PG_EXCEPTION_DETAIL, ex_hint = PG_EXCEPTION_HINT; IF v_jobmon_schema IS NOT NULL THEN IF v_job_id IS NULL THEN EXECUTE format('SELECT %I.add_job(''PARTMAN CREATE FUNCTION: %s'')', v_jobmon_schema, p_parent_table) INTO v_job_id; EXECUTE format('SELECT %I.add_step(%s, ''Partition function maintenance for table %s failed'')', v_jobmon_schema, v_job_id, p_parent_table) INTO v_step_id; ELSIF v_step_id IS NULL THEN EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before first step logged'')', v_jobmon_schema, v_job_id) INTO v_step_id; END IF; EXECUTE format('SELECT %I.update_step(%s, ''CRITICAL'', %L)', v_jobmon_schema, v_step_id, 'ERROR: '||coalesce(SQLERRM,'unknown')); EXECUTE format('SELECT %I.fail_job(%s)', v_jobmon_schema, v_job_id); END IF; RAISE EXCEPTION '% CONTEXT: % DETAIL: % HINT: %', ex_message, ex_context, ex_detail, ex_hint; END $$; CREATE OR REPLACE FUNCTION show_partitions (p_parent_table text, p_order text DEFAULT 'ASC') RETURNS TABLE (partition_schemaname text, partition_tablename text) LANGUAGE plpgsql STABLE SECURITY DEFINER AS $$ DECLARE v_control text; v_control_type text; v_datetime_string text; v_epoch text; v_new_search_path text := '@extschema@,pg_temp'; v_old_search_path text; v_parent_schema text; v_parent_tablename text; v_partition_interval text; v_type text; BEGIN /* * Function to list all child partitions in a set in logical order. */ SELECT current_setting('search_path') INTO v_old_search_path; EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_new_search_path, 'false'); IF upper(p_order) NOT IN ('ASC', 'DESC') THEN EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false'); RAISE EXCEPTION 'p_order paramter must be one of the following values: ASC, DESC'; END IF; SELECT partition_type , partition_interval , datetime_string , control , epoch INTO v_type , v_partition_interval , v_datetime_string , v_control , v_epoch FROM @extschema@.part_config WHERE parent_table = p_parent_table; SELECT n.nspname, c.relname INTO v_parent_schema, v_parent_tablename FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE n.nspname = split_part(p_parent_table, '.', 1)::name AND c.relname = split_part(p_parent_table, '.', 2)::name; SELECT general_type INTO v_control_type FROM @extschema@.check_control_type(v_parent_schema, v_parent_tablename, v_control); IF v_control_type = 'time' OR (v_control_type = 'id' AND v_epoch <> 'none') THEN IF v_partition_interval::interval <> '3 months' OR (v_partition_interval::interval = '3 months' AND v_type = 'time-custom') THEN RETURN QUERY EXECUTE format('SELECT n.nspname::text AS partition_schemaname, c.relname::text AS partition_name FROM pg_catalog.pg_inherits h JOIN pg_catalog.pg_class c ON c.oid = h.inhrelid JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE h.inhparent = ''%I.%I''::regclass ORDER BY to_timestamp(substring(c.relname from ((length(c.relname) - position(''p_'' in reverse(c.relname))) + 2) ), %L) %s' , v_parent_schema , v_parent_tablename , v_datetime_string , p_order); ELSE -- For quarterly, to_timestamp() doesn't recognize "Q" in datetime string. -- First order by just the year, then order by the quarter number (should be last character in table name) RETURN QUERY EXECUTE format('SELECT n.nspname::text AS partition_schemaname, c.relname::text AS partition_name FROM pg_catalog.pg_inherits h JOIN pg_catalog.pg_class c ON c.oid = h.inhrelid JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE h.inhparent = ''%I.%I''::regclass ORDER BY to_timestamp(substring(c.relname from ((length(c.relname) - position(''p_'' in reverse(c.relname))) + 2) for 4), ''YYYY'') %s , substring(reverse(c.relname) from 1 for 1) %s' , v_parent_schema , v_parent_tablename , p_order , p_order); END IF; ELSIF v_control_type = 'id' THEN RETURN QUERY EXECUTE format('SELECT n.nspname::text AS partition_schemaname, c.relname::text AS partition_name FROM pg_catalog.pg_inherits h JOIN pg_catalog.pg_class c ON c.oid = h.inhrelid JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE h.inhparent = ''%I.%I''::regclass ORDER BY substring(c.relname from ((length(c.relname) - position(''p_'' in reverse(c.relname))) + 2) )::bigint %s' , v_parent_schema , v_parent_tablename , p_order); END IF; EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false'); END $$;