CREATE FUNCTION @extschema@.create_partition_id( p_parent_table text , p_partition_ids bigint[] , p_start_partition text DEFAULT NULL ) RETURNS boolean LANGUAGE plpgsql AS $$ DECLARE ex_context text; ex_detail text; ex_hint text; ex_message text; v_control text; v_control_type text; v_exists text; v_id bigint; v_inherit_privileges boolean; v_job_id bigint; v_jobmon boolean; v_jobmon_schema text; v_new_search_path text; v_old_search_path text; v_parent_oid oid; v_parent_schema text; v_parent_tablename text; v_parent_tablespace name; v_partition_interval bigint; v_partition_created boolean := false; v_partition_name text; v_partition_type text; v_row record; v_sql text; v_step_id bigint; v_sub_control text; v_sub_partition_type text; v_sub_id_max bigint; v_sub_id_min bigint; v_template_table text; BEGIN /* * Function to create id partitions */ SELECT control , partition_interval::bigint -- this shared field also used in partition_time as interval , partition_type , jobmon , template_table , inherit_privileges INTO v_control , v_partition_interval , v_partition_type , v_jobmon , v_template_table , v_inherit_privileges FROM @extschema@.part_config WHERE parent_table = p_parent_table; IF NOT FOUND THEN RAISE EXCEPTION 'ERROR: no config found for %', p_parent_table; END IF; SELECT n.nspname , c.relname , c.oid , t.spcname INTO v_parent_schema , v_parent_tablename , v_parent_oid , v_parent_tablespace FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid LEFT OUTER JOIN pg_catalog.pg_tablespace t ON c.reltablespace = t.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 <> 'id' THEN RAISE EXCEPTION 'ERROR: Given parent table is not set up for id/serial partitioning'; END IF; SELECT current_setting('search_path') INTO v_old_search_path; IF length(v_old_search_path) > 0 THEN v_new_search_path := '@extschema@,pg_temp,'||v_old_search_path; ELSE v_new_search_path := '@extschema@,pg_temp'; END IF; 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 := format('%s,%s',v_jobmon_schema, v_new_search_path); END IF; END IF; EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_new_search_path, 'false'); -- 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 SELECT sub_min::bigint, sub_max::bigint INTO v_sub_id_min, v_sub_id_max FROM @extschema@.check_subpartition_limits(p_parent_table, 'id'); IF v_jobmon_schema IS NOT NULL THEN v_job_id := add_job(format('PARTMAN CREATE TABLE: %s', p_parent_table)); END IF; FOREACH v_id IN ARRAY p_partition_ids LOOP -- Do not create the child table if it's outside the bounds of the top parent. IF v_sub_id_min IS NOT NULL THEN IF v_id < v_sub_id_min OR v_id >= v_sub_id_max THEN CONTINUE; END IF; END IF; v_partition_name := @extschema@.check_name_length(v_parent_tablename, v_id::text, TRUE); -- If child table already exists, skip creation -- Have to check pg_class because if subpartitioned, table will not be in pg_tables SELECT c.relname INTO v_exists FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE n.nspname = v_parent_schema::name AND c.relname = v_partition_name::name; IF v_exists IS NOT NULL THEN CONTINUE; END IF; IF v_jobmon_schema IS NOT NULL THEN 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); END IF; -- Same INCLUDING list is used in create_parent() v_sql := format('CREATE TABLE %I.%I (LIKE %I.%I INCLUDING COMMENTS INCLUDING COMPRESSION INCLUDING CONSTRAINTS INCLUDING DEFAULTS INCLUDING GENERATED INCLUDING STATISTICS INCLUDING STORAGE) ' , v_parent_schema , v_partition_name , v_parent_schema , v_parent_tablename); IF v_parent_tablespace IS NOT NULL THEN v_sql := format('%s TABLESPACE %I ', v_sql, v_parent_tablespace); END IF; SELECT sub_partition_type, sub_control INTO v_sub_partition_type, v_sub_control FROM @extschema@.part_config_sub WHERE sub_parent = p_parent_table; IF v_sub_partition_type = 'range' THEN v_sql := format('%s PARTITION BY RANGE (%I) ', v_sql, v_sub_control); ELSIF v_sub_partition_type = 'list' THEN v_sql := format('%s PARTITION BY LIST (%I) ', v_sql, v_sub_control); END IF; RAISE DEBUG 'create_partition_id v_sql: %', v_sql; EXECUTE v_sql; IF v_template_table IS NOT NULL THEN PERFORM @extschema@.inherit_template_properties(p_parent_table, v_parent_schema, v_partition_name); END IF; IF v_partition_type = 'range' THEN EXECUTE format('ALTER TABLE %I.%I ATTACH PARTITION %I.%I FOR VALUES FROM (%L) TO (%L)' , v_parent_schema , v_parent_tablename , v_parent_schema , v_partition_name , v_id , v_id + v_partition_interval); ELSIF v_partition_type = 'list' THEN EXECUTE format('ALTER TABLE %I.%I ATTACH PARTITION %I.%I FOR VALUES IN (%L)' , v_parent_schema , v_parent_tablename , v_parent_schema , v_partition_name , v_id); ELSE RAISE EXCEPTION 'create_partition_id: Unexpected partition type (%) encountered in part_config table for parent table %', v_partition_type, p_parent_table; END IF; -- NOTE: Privileges not automatically inherited. Only do so if config flag is set IF v_inherit_privileges = TRUE THEN PERFORM @extschema@.apply_privileges(v_parent_schema, v_parent_tablename, v_parent_schema, v_partition_name, v_job_id); END IF; IF v_jobmon_schema IS NOT NULL THEN PERFORM update_step(v_step_id, 'OK', 'Done'); END IF; -- Will only loop once and only if sub_partitioning is actually configured -- This seemed easier than assigning a bunch of variables then doing an IF condition -- This column list must be kept consistent between: -- create_parent, check_subpart_sameconfig, create_partition_id, create_partition_time, dump_partitioned_table_definition, and table definition FOR v_row IN SELECT sub_parent , sub_control , sub_time_encoder , sub_time_decoder , sub_partition_interval , sub_partition_type , sub_premake , sub_automatic_maintenance , sub_template_table , sub_retention , sub_retention_schema , sub_retention_keep_index , sub_retention_keep_table , sub_epoch , sub_constraint_cols , sub_optimize_constraint , sub_infinite_time_partitions , sub_jobmon , sub_inherit_privileges , sub_constraint_valid , sub_date_trunc_interval , sub_ignore_default_data , sub_default_table , sub_maintenance_order , sub_retention_keep_publication , sub_control_not_null FROM @extschema@.part_config_sub WHERE sub_parent = p_parent_table LOOP IF v_jobmon_schema IS NOT NULL THEN v_step_id := add_step(v_job_id, 'Subpartitioning '||v_partition_name); END IF; v_sql := format('SELECT @extschema@.create_parent( p_parent_table := %L , p_control := %L , p_time_encoder := %L , p_time_decoder := %L , p_type := %L , p_interval := %L , p_default_table := %L , p_constraint_cols := %L , p_premake := %L , p_automatic_maintenance := %L , p_epoch := %L , p_template_table := %L , p_jobmon := %L , p_start_partition := %L , p_date_trunc_interval := %L , p_control_not_null := %L )' , v_parent_schema||'.'||v_partition_name , v_row.sub_control , v_row.sub_time_encoder , v_row.sub_time_decoder , v_row.sub_partition_type , v_row.sub_partition_interval , v_row.sub_default_table , v_row.sub_constraint_cols , v_row.sub_premake , v_row.sub_automatic_maintenance , v_row.sub_epoch , v_row.sub_template_table , v_row.sub_jobmon , p_start_partition , v_row.sub_date_trunc_interval , v_row.sub_control_not_null); RAISE DEBUG 'create_partition_id (create_parent loop): %', v_sql; EXECUTE v_sql; UPDATE @extschema@.part_config SET retention_schema = v_row.sub_retention_schema , retention_keep_table = v_row.sub_retention_keep_table , optimize_constraint = v_row.sub_optimize_constraint , infinite_time_partitions = v_row.sub_infinite_time_partitions , inherit_privileges = v_row.sub_inherit_privileges , constraint_valid = v_row.sub_constraint_valid , ignore_default_data = v_row.sub_ignore_default_data , maintenance_order = v_row.sub_maintenance_order , retention_keep_publication = v_row.sub_retention_keep_publication WHERE parent_table = v_parent_schema||'.'||v_partition_name; IF v_jobmon_schema IS NOT NULL THEN PERFORM update_step(v_step_id, 'OK', 'Done'); END IF; END LOOP; -- end sub partitioning LOOP -- NOTE: Replication identity not automatically inherited as of PG16 (revisit in future versions) PERFORM @extschema@.inherit_replica_identity(v_parent_schema, v_parent_tablename, v_partition_name); -- Manage additional constraints if set PERFORM @extschema@.apply_constraints(p_parent_table, p_job_id := v_job_id); v_partition_created := true; END LOOP; IF v_jobmon_schema IS NOT NULL THEN IF v_partition_created = false THEN v_step_id := add_step(v_job_id, format('No partitions created for partition set: %s', p_parent_table)); PERFORM update_step(v_step_id, 'OK', 'Done'); END IF; PERFORM close_job(v_job_id); END IF; EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false'); RETURN v_partition_created; 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 TABLE: %s'')', v_jobmon_schema, p_parent_table) INTO v_job_id; 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; 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 $$;