Another way for PostgreSQL partitioning

[BDiE8VNy]

This is yet another way for partitioning PostgreSQL relations.
The code is a result of playing around with PL/pgSQL and is inspired by the the solution provided in the Wiki.

Usage:

SELECT create_zbx_partitions();       -- Create partitions for next month
SELECT create_zbx_partitions(2);      -- Create partitions for the next-but-one month
SELECT create_zbx_partitions(-10,12); -- Create partitions beginning from 10 month ago to next-but-one month

SELECT list_zbx_partitions();       -- List  partition for current month
SELECT list_zbx_partitions(2);      -- List partitions for the next-but-one month
SELECT list_zbx_partitions(-10,12); -- List partitions beginning from 10 month ago to next-but-one month

SELECT drop_zbx_partitions();       -- Drop partitions for last month
SELECT drop_zbx_partitions(2);      -- Drop partitions for the next-but-one month
SELECT drop_zbx_partitions(-10,12); -- Drop partitions beginning from 10 month ago to next-but-one month

[Colttt]

and what is the difference between yours and THIS odr THIS

[BDiE8VNy]

roughly:
- insert fails if partition does not exist
- partition interval is defined to a fixed range - like monthly
- no condition evaluation on inserts
- no additional select on inserts

[alledm]

Hi,

the partition creation would be scheduled right?

Why would you want the insert to fail if the table is not there?

Also, do you have daily partitions as well?

Regards
Alessandro

[BDiE8VNy]

Yes, the partition maintenance is scheduled via pgAgent.

It's a smart idea to implement automated partition/table creation within the insert trigger.
However, the process for partition maintenance has to be reliable. If set up and monitored correctly there can't be the case of a missing partition/table.
I don't like to use triggers but in PostgreSQL (except using EnterpriseDB) there is no other way to achieve something similar to partitions. Therefore I personally want to have as less and simple code within the triggers as possible and let the maintenance be done by a decoupled process.

One can partition in any kind of period. Just change the code accordantly.