Re: Syntax for partitioning

Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:

> On tor, 2009-10-29 at 11:15 +0900, Itagaki Takahiro wrote:
> > Range partitioning:
> > CREATE TABLE table_name ( columns )
> > PARTITION BY RANGE ( a_expr )
> > (
> > PARTITION name VALUES LESS THAN [(] const [)],
> > PARTITION name VALUES LESS THAN [(] MAXVALUE [)] -- overflow partition
> > );
>
> Maybe this needs to mention the actual operator name instead of LESS
> THAN, in case the operator is not named < or the user wants to use a
> different one.

How about to use "sortby" or "index_elem" here?

PARTITION BY RANGE '(' sortby-or-index_elem ')' '(' RangePartitions ')'

sortby:
a_expr USING qual_all_Op opt_nulls_order
| a_expr opt_asc_desc opt_nulls_order

index_elem:
ColId opt_class opt_asc_desc opt_nulls_order
| func_expr opt_class opt_asc_desc opt_nulls_order
| '(' a_expr ')' opt_class opt_asc_desc opt_nulls_order

We should allow only btree operator class here because we need to
extract GREATER-THAN-OR-EQUAL operator from LESS THAN. In addition,
we will be able to optimize parition search in the future if we
restrict a range partition key should be comparable scalar value.

Multidimensional partitioning will be implemented with another
approach, something like "PARTITION BY GIST", because it would
require different oprimization from range partitioning.
BTW, "PARTITION BY <pg_am.amname>" crossed my mind here,
but it is not well-investigated yet.

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center