Make parent partition id and sub partition id agnostic to each other

[mayank-pant]

database=> CREATE TABLE public.xyz (
    id int8 NOT NULL,
    sub_id int8 NOT NULL,
    PRIMARY KEY (id, sub_id)
) PARTITION BY RANGE (id);

SELECT partman.create_parent(
    p_parent_table := 'public.xyz',
    p_control := 'id',
    p_type := 'native',
    p_template_table := NULL,
    p_interval := '1000',
    p_premake := 5
);

SELECT partman.create_sub_parent(
    p_top_parent := 'public.xyz',
    p_native_check := 'yes',
    p_control := 'sub_id',
    p_type := 'native',
    p_interval := '1',
    p_premake := 5,
    p_start_partition := '1'
);
ERROR:  relation "xyz" already exists
 create_parent 
---------------
 t
(1 row)

WARNING:  Child table public.xyz_p0 is not natively partitioned. Dropping and recreating with native partitioning
WARNING:  Child table public.xyz_p1000 is not natively partitioned. Dropping and recreating with native partitioning
WARNING:  Child table public.xyz_p2000 is not natively partitioned. Dropping and recreating with native partitioning
WARNING:  Child table public.xyz_p3000 is not natively partitioned. Dropping and recreating with native partitioning
WARNING:  Child table public.xyz_p4000 is not natively partitioned. Dropping and recreating with native partitioning
WARNING:  Child table public.xyz_p5000 is not natively partitioned. Dropping and recreating with native partitioning
 create_sub_parent 
-------------------
 t
(1 row)

database=> SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables 
WHERE tablename LIKE '%xyz%' 
ORDER BY tablename;
 schemaname |      tablename      |    size    
------------+---------------------+------------
 partman    | template_public_xyz | 0 bytes
 public     | xyz                 | 0 bytes
 public     | xyz_default         | 8192 bytes
 public     | xyz_p0              | 0 bytes
 public     | xyz_p0_default      | 8192 bytes
 public     | xyz_p0_p1           | 8192 bytes
 public     | xyz_p0_p2           | 8192 bytes
 public     | xyz_p0_p3           | 8192 bytes
 public     | xyz_p0_p4           | 8192 bytes
 public     | xyz_p0_p5           | 8192 bytes
 public     | xyz_p0_p6           | 8192 bytes
 public     | xyz_p1000           | 0 bytes
 public     | xyz_p1000_default   | 8192 bytes
 public     | xyz_p1000_p1000     | 8192 bytes
 public     | xyz_p2000           | 0 bytes
 public     | xyz_p2000_default   | 8192 bytes
 public     | xyz_p2000_p2000     | 8192 bytes
 public     | xyz_p3000           | 0 bytes
 public     | xyz_p3000_default   | 8192 bytes
 public     | xyz_p3000_p3000     | 8192 bytes
 public     | xyz_p4000           | 0 bytes
 public     | xyz_p4000_default   | 8192 bytes
 public     | xyz_p4000_p4000     | 8192 bytes
 public     | xyz_p5000           | 0 bytes
 public     | xyz_p5000_default   | 8192 bytes
 public     | xyz_p5000_p5000     | 8192 bytes
(26 `rows)`

Only subpartitions of the p0 partition have been created, and some incorrect subpartitions, such as p1000_p1000, have been generated for other partitions. It appears there is a dependency between the parent partition id and the subpartition id. Is it possible to make these ids independent of each other at the value level?

[keithf4]

Hmm... since your sub-partitioning on a different column, I thought that it should have been doing that already. I'd have to investigate why it's not. Apologies that my time on partman is a bit limited right now, so it may be a while before I can investigate. I'm happy to look at a PR if you're able to find a solution, though!

[mayank-pant]

Hey, first of all appreciate you building this tool. Also sorry for the late reponse. I reviewed the code to scope out and evaluate the problem. From my understanding, the change will be in the create_sub_parent, create_partiton_id and create_parent.

In create_sub_parent, the code block would be skipped when the v_control_parent_type and v_control_sub_type are different columns. The reason is that since the columns are different, the interval and values will be independent of the parent column.

pg_partman/sql/functions/create_sub_parent.sql

Lines 127 to 148 in 29f0305

	-- If both parent and sub-parent are the same partition type (time/id), ensure intereval of sub-parent is less than parent
	IF (v_control_parent_type IN ('time', 'text', 'uuid') 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
	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;
	IF v_child_interval >= v_parent_interval::interval THEN
	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)
	OR (p_date_trunc_interval = 'week') THEN
	RAISE EXCEPTION 'Due to conflicting data boundaries between weeks and any larger interval of time, pg_partman cannot support a sub-partition interval of weekly time periods';
	END IF;
	ELSIF v_control_parent_type = 'id' AND v_control_sub_type = 'id' AND v_parent_epoch = 'none' AND p_epoch = 'none' THEN
	IF p_interval::bigint >= v_parent_interval::bigint THEN
	RAISE EXCEPTION 'Sub-partition interval cannot be greater than or equal to the given parent interval';
	END IF;
	END IF;

In create_partition_id, this code block will also be skipped when the columns are different. However, there is one catch I’ve mentioned in the footnotes, which I’m not sure is currently handled or if it should fall under the scope of this issue.

pg_partman/sql/functions/create_partition_id.sql

Lines 107 to 112 in 29f0305

	-- 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;

In create_parent, from my initial inspection, I don’t see any changes required. This function mainly validates parameters, creates the template and default table, and then supplies create_partition_id with values for which it expects partitions to be created. Moreover, I found the below function to be redundant. The code block is responsible for creating the xyz_p1000_p1000 partition, and i don’t recall any valid use of this function. If you can provide an example, it would help. I think it would be better to raise an exception or warning with a why a child partition could not be created. for example, it silently created some unexpected partition in my case.

pg_partman/sql/functions/create_parent.sql

Lines 555 to 588 in 29f0305

	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);

Regarding the excerpt below, I’m not sure whether it should be part of this issue (if it’s not already resolved). Consider the following scenario:

• A table abc with columns a, b, c.

• It originally has 2-level partitioning: a (range 1 to 100) -> b (list 1 to 10)

• Now, I want to add a 3rd-level partition on a with range 1 to 200. The final partition structure would be:
  a (range 1 to 100) -> b (list 1 to 10) -> a (range 1 to 200)

The new partition interval is obviously invalid. Here, the partition limit should be checked against the grandparent node, or any ancestor table. If this hasn’t already been solved, let me know whether this should be part of this issue or if a separate issue should be opened.

I haven’t inspected run_maintenance yet, but I think it mainly concerns validating table limits, which ties back to these three functions. Please let me know if I’m underestimating the scope of the issue and whether other functions might also need changes.