CREATE 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_template_table text; 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, template_table INTO v_parent_type, v_parent_interval, v_control, v_run_maint, v_parent_epoch, v_template_table 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 -- All sub-partition parents must use the same template table for native partitioning, so ensure the one from the given parent is obtained and used. 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 , sub_template_table) 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 , v_template_table); 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_template_table := %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 , v_template_table , 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 $$;