About Uniform Multi-level Partitioned Tables
- The partitioned table structure is uniform. Each partition node at the same level must have the same hierarchical structure.
- The partition key constraints must be consistent and uniform. At each subpartition level, the sets of constraints on the child tables created for each branch must match.
- The pg_partitions system view contains information on the structure of a partitioned table.
- The pg_constraint system catalog table contains information on table constraints.
- The psql meta command \d+ tablename displays the table constraints for child leaf tables of a partitioned table.
Example
This CREATE TABLE command creates a uniform partitioned table.
CREATE TABLE mlp (id int, year int, month int, day int, region text) DISTRIBUTED BY (id) PARTITION BY RANGE ( year) SUBPARTITION BY LIST (region) SUBPARTITION TEMPLATE ( SUBPARTITION usa VALUES ( 'usa'), SUBPARTITION europe VALUES ( 'europe'), SUBPARTITION asia VALUES ( 'asia')) ( START ( 2000) END ( 2010) EVERY ( 5));
mlp_1_prt_11 mlp_1_prt_11_2_prt_usa mlp_1_prt_11_2_prt_europe mlp_1_prt_11_2_prt_asia mlp_1_prt_21 mlp_1_prt_21_2_prt_usa mlp_1_prt_21_2_prt_europe mlp_1_prt_21_2_prt_asia
The hierarchy of the table is uniform, each partition contains a set of three child tables (subpartitions). The constraints for the region subpartitions are uniform, the set of constraints on the child tables for the branch table mlp_1_prt_11 are the same as the constraints on the child tables for the branch table mlp_1_prt_21.
WITH tbl AS (SELECT oid, partitionlevel AS level, partitiontablename AS part FROM pg_partitions, pg_class WHERE tablename = 'mlp' AND partitiontablename=relname AND partitionlevel=1 ) SELECT tbl.part, consrc FROM tbl, pg_constraint WHERE tbl.oid = conrelid ORDER BY consrc;
The consrc column displays constraints on the subpartitions. The set of region constraints for the subpartitions in mlp_1_prt_1 match the constraints for the subpartitions in mlp_1_prt_2. The constraints for year are inherited from the parent branch tables.
part | consrc --------------------------+------------------------------------ mlp_1_prt_2_2_prt_asia | (region = 'asia'::text) mlp_1_prt_1_2_prt_asia | (region = 'asia'::text) mlp_1_prt_2_2_prt_europe | (region = 'europe'::text) mlp_1_prt_1_2_prt_europe | (region = 'europe'::text) mlp_1_prt_1_2_prt_usa | (region = 'usa'::text) mlp_1_prt_2_2_prt_usa | (region = 'usa'::text) mlp_1_prt_1_2_prt_asia | ((year >= 2000) AND (year < 2005)) mlp_1_prt_1_2_prt_usa | ((year >= 2000) AND (year < 2005)) mlp_1_prt_1_2_prt_europe | ((year >= 2000) AND (year < 2005)) mlp_1_prt_2_2_prt_usa | ((year >= 2005) AND (year < 2010)) mlp_1_prt_2_2_prt_asia | ((year >= 2005) AND (year < 2010)) mlp_1_prt_2_2_prt_europe | ((year >= 2005) AND (year < 2010)) (12 rows)
ALTER TABLE mlp ADD DEFAULT PARTITION def
The partitioned table remains a uniform partitioned table. The branch created for default partition contains three child tables and the set of constraints on the child tables match the existing sets of child table constraints.
ALTER TABLE mlp ALTER PARTITION FOR (RANK(2)) DROP PARTITION asia ; ALTER TABLE mlp ALTER PARTITION FOR (RANK(2)) ADD PARTITION canada VALUES ('canada');
Also, if you add a partition canada under mlp_1_prt_21, the partitioning hierarchy is not uniform.
However, if you add the subpartition canada to both mlp_1_prt_21 and mlp_1_prt_11 the of the original partitioned table, it remains a uniform partitioned table.