create_parent: lift the restriction to p_interval <integer> >= 10 for ID based columns

[tchernobog]

In our company, we have measurement tables linked to a specific build job. Typically we get > 10e7 records per build in the same table, which we would like to partition.

Data is often perused per build in our scenario, so it makes sense to create one partition per value of the build foreign key ID value.

Please lift the restriction of having the p_interval parameter to be >= 10 (one order of magnitude bigger for us), allowing it to go down to just 1.

[keithf4]

pg_partman is based around the partitioning pattern of a range, not individual values. The upper and lower values for each child table must be different. I ran into issues even with integer ranges within 2 or 3 values of each other just due to the nature of the way range boundaries work, which is why I ended up at a minimum supported value of 10. Currently even the native partitioning method in PG10+ does not allow single value boundaries for its RANGE type for similar reasons.

# create table measurement_p1 partition of measurement for values from (1) to (1);
ERROR:  empty range bound specified for partition "measurement_p1"
DETAIL:  Specified lower bound (1) is greater than or equal to upper bound (1).
Time: 1.564 ms

So, in order to do single value partitioning like this, the native LIST partitioning method would need to be used. I do not currently have that partitioning method built into pg_partman, so it's not simply removing a restriction to allow this.

https://www.postgresql.org/docs/11/static/ddl-partitioning.html

About all I really could do at this point would be to allow the partition interval to be as low as 2, and only for native partitioning. I could see about single value child tables in the future, but I currently do not have it on my development roadmap. I would be happy to review any patches that could add this functionality.

[keithf4]

So went back and looked at why I was having issues with this before. It wasn't so much the constraints themselves on the child tables, it was the maintenance runs that I was having difficulty getting to work with an interval of either 1 or 2 (#39).

I think with native LIST partitioning, I could likely get an interval of 1 working now. But it would require quite a bit of work and testing to ensure everything is working properly. I do have it on my list of things to look into in the future, but I don't have an ETA on when that would be. Again, I'd appreciate any patches to try and add the feature in the mean time.

[samtech09]

Hi, as of 2022, does it support LIST partitions as we do with PostgreSQL ?

[keithf4]

I haven't had the time to address this issue yet. I have a large refactor planned for later this year or the first part of next year that I can hopefully try and address this.

#360

[keithf4]

Will lift this restriction once trigger-based partitioning has been removed in 5.0. Not sure if LIST partitioning support will be added yet at that time.

[keithf4]

Note that the >= 10 restriction has been lifted as of 5.0.0, however I don't believe a range of 1 works right now. So the originally requested feature here will still have to wait until LIST partitioning is supported. Will try to get it in the next release, but cannot guarantee that at this time.

[keithf4]

For anyone that was following this issue, I have a beta PR up that includes LIST partitioning for single integer values

#615

If you're able to test, any feedback would be greatly appreciated.

[keithf4]

Version 5.1 has been released. The range limitation has been removed so you can go down to a range of 2. For a single value range, LIST partitioning is now supported.