Re: On partitioning

On 12/12/2014 05:43 AM, Amit Langote wrote:
> [snip]
> In case of what we would have called a 'LIST' partition, this could look like
>
> ... FOR VALUES (val1, val2, val3, ...)
>
> Assuming we only support partition key to contain only one column in such a case.

Hmmm….

[...] PARTITION BY LIST(col1 [, col2, ...])

just like we do for indexes would do.

and CREATE PARTITION child_name OF parent_name
FOR [VALUES] (val1a,val2a), (val1b,val2b), (val1c,val2c)
[IN tblspc_name]

just like we do for multi-valued inserts.

> In case of what we would have called a 'RANGE' partition, this could look like
>
> ... FOR VALUES (val1min, val2min, ...) TO (val1max, val2max, ...)
>
> How about BETWEEN ... AND ... ?

Unless I'm missing something obvious, we already have range types for
this, don't we?

... PARTITION BY RANGE (col)

CREATE PARTITION child_name OF parent_name
FOR [VALUES] '[val1min,val1max)', '[val2min,val2max)',
'[val3min,val3max)'
[IN tblspc_name]

and I guess this should simplify a fully flexible implementation (if you
can construct a RangeType for it, you can use that for partitioning).
This would substitute the ugly (IMHO) "VALUES LESS THAN" syntax with a
more flexible one
(even though it might end up being converted into "less than"
boundaries internally for implementation/optimization purposes)

In both cases we would need to allow for overflows / default partition
different from the parent table.

Plus some ALTER PARTITION part_name TABLESPACE=tblspc_name

The main problem being that we are assuming named partitions here, which
might not be that practical at all.

> [snip]
>> I would include the noise keyword VALUES just for readability if
>> anything.

+1

FWIW, deviating from already "standard" syntax (Oracle-like --as
implemented by PPAS for example-- or DB2-like) is quite
counter-productive unless we have very good reasons for it... which
doesn't mean that we have to do it exactly like they do (specially if we
would like to go the incremental implementation route).

Amit: mind if I add the DB2 syntax for partitioning to the wiki, too?

This might as well help with deciding the final form of
partitioning (and define the first implementation boundaries, too)

Thanks,

/ J.L.