Re: On partitioning - Mailing list pgsql-hackers

On Tue, Dec 9, 2014 at 12:59 PM, Amit Kapila <amit.kapila16@gmail.com> wrote:
> On Tue, Dec 9, 2014 at 8:08 AM, Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
> wrote:
>> > From: Robert Haas [mailto:robertmhaas@gmail.com]
>> > I don't understand.  If you want to range partition on columns (a, b),
>> > you say that, say, tuples with (a, b) values less than (100, 200) go
>> > here and the rest go elsewhere.  For list partitioning, you say that,
>> > say, tuples with (a, b) values of EXACTLY (100, 200) go here and the
>> > rest go elsewhere.  I'm not sure how useful that is but it's not
>> > illogical.
>> >
>>
>> In case of list partitioning, 100 and 200 would respectively be one of the
>> values in lists of allowed values for a and b. I thought his concern is
>> whether this "list of values for each column in partkey" is as convenient to
>> store and manipulate as range partvalues.
>>
>
> Yeah and also how would user specify the values, as an example
> assume that table is partitioned on monthly_salary, so partition
> definition would look:
>
> PARTITION BY LIST(monthly_salary)
> (
> PARTITION salary_less_than_thousand VALUES(300, 900),
> PARTITION salary_less_than_two_thousand VALUES (500,1000,1500),
> ...
> )
>
> Now if user wants to define multi-column Partition based on
> monthly_salary and annual_salary, how do we want him to
> specify the values.  Basically how to distinguish which values
> belong to first column key and which one's belong to second
> column key.
>

Amit, in one of my earlier replies to your question of why we may not want to implement multi-column list partitioning
(lackof user interest in the feature or possible complexity of the code), I tried to explain how that may work if we do
chooseto go that way. Basically, something we may call PartitionColumnValue should be such that above issue can be
suitablysorted out. 

For example, a partition defining/bounding value would be a pg_node_tree representation of List of one of the (say)
followingparse nodes as appropriate -  

typedef struct PartitionColumnValue
{   NodeTag    type,   Oid        *partitionid,   char        *partcolname,   char        partkind,   Node
*partrangelower,  Node        *partrangeupper,   List        *partlistvalues 
};

OR separately,

typedef struct RangePartitionColumnValue
{   NodeTag    type,   Oid        *partitionid,   char        *partcolname,   Node        *partrangelower,   Node
*partrangeupper 
};

&

typedef struct ListPartitionColumnValue
{   NodeTag    type,   Oid        *partitionid,   char        *partcolname,   List        *partlistvalues
};

Where a partition definition would look like

typedef struct PartitionDef
{   NodeTag    type,   RangeVar    partition,   RangeVar    parentrel,   char        *kind,   Node        *values,
List       *options,   char        *tablespacename 
};

PartitionDef.values is an (ordered) List of PartitionColumnValue each of which corresponds to one column in the
partitionkey in that order. 

We should be able to devise a way to load the pg_node_tree representation of  PartitionDef.values (on-disk
pg_partition_def.partvalues)into relcache using a "suitable data structure" so that it becomes readily usable in
varietyof contexts that we are interested in using this information.  

Regards,
Amit