Hi,
While reviewing one of the 'Table partitioning' related patches, I found that Postgres does not support multiple column based LIST partitioning. Based on this understanding, I have started working on this feature. I also feel that 'Multi-Column List Partitioning' can be benefited to the Postgres users in future.
I am attaching the WIP patch for this feature here. It supports 'Multi-Column List Partitioning', however some tasks are still pending. I would like to know your thoughts about this, So that I can continue the work with improvising the current patch.
Following things are handled in the patch.
1. Syntax
CREATE TABLE table_name (attrs) PARTITION BY LIST(list_of_columns);
Earlier there was no provision to mention multiple columns as part of the 'list_of_columns' clause. Now we can mention the list of columns separated by comma.
CREATE TABLE table_name_p1 PARTITION OF table_name FOR VALUES IN list_of_values.
Whereas list_of_columns can be
a. (value [,...])
b. (value [,...]) [,...]
I would like to list a few examples here for better understanding.
Ex-1:
CREATE TABLE t1(a int) PARTITION BY LIST(a);
CREATE TABLE t1_1 PARTITION OF t1 FOR VALUES IN (1, 2, 10, 5, 7);
Ex-2:
CREATE TABLE t2(a int, b int) PARTITION BY LIST(a,b);
CREATE TABLE t2_1 PARTITION OF t2 FOR VALUES IN (1, 2), (1, 5), (2, 2),(2, 10);
Please share if any changes are required in the above syntax.
2. Modified transformation logic to support above syntax.
3. Modified the data structures to store the information caused by above syntax. Also modified the searching logic to route the tuple to the appropriate partition.
4. Done a few basic testing and verified CREATE TABLE, INSERT INTO and SELECT are working fine.
Following items are pending and I am working on it.
1. Handling of 'NULL' values.
2. Support multi column case in partition pruning.
3. Add test cases to the regression test suite.
Please share your thoughts.
Thanks & Regards,
Nitin Jadhav