Question - Why should sub-partition interval be smaller than parent interval

[fobbing]

I have a requirement to create ID partition and sub-partitions.

If I partition the top level using list partitioning with an interval of 1 (each partition will represent a customer), I am unable to create a sub-partition on a different column which also has interval of 1.

The error I receive is:
Sub-partition interval cannot be greater than or equal to the given parent interval

I'd like to understand the reasoning behind this, are there any foot guns I'm not seeing?

[keithf4]

Good point. As long as the columns are different, should be able to have different intervals. I'll have to see about fixing that.

This has generally been considered that the subpartition would be on the same column as the parent. In that case it wouldn't make any sense.

[fobbing]

Good point. As long as the columns are different, should be able to have different intervals. I'll have to see about fixing that.

This has generally been considered that the subpartition would be on the same column as the parent. In that case it wouldn't make any sense.

Aah, thank you. I hadn't considered sub-partitioning on the same column as the parent.
The limitation makes perfect sense in that context.

I assume this would be a matter of adding an additional check in the conditionals in create_sub_parent() to make sure the control columns are the same before enforcing the limitation?

[keithf4]

That was my intention to try

[keithf4]

I already check if they're the same data type here.

https://github.com/pgpartman/pg_partman/blob/master/sql/functions/create_sub_parent.sql#L117

Would be another check to ensure they're the same column

[fobbing]

On further inspection I see that the current rule is enforced in a few functions, not only create_sub_parent().

I'd hoped it was limited to create_sub_parent(), I'm not so confident about hacking in a lot of places.

[keithf4]

I'll definitely work on getting this fixed, but I don't think it will make it into the next release at this point.