Skip site navigation (1) Skip section navigation (2)

Re: Syntax for partitioning

From: Itagaki Takahiro <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp>
To: Peter Eisentraut <peter_e(at)gmx(dot)net>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Syntax for partitioning
Date: 2009-10-30 03:03:10
Message-ID: 20091030120309.F608.52131E4D@oss.ntt.co.jp (view raw or flat)
Thread:
Lists: pgsql-hackers
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



In response to

pgsql-hackers by date

Next:From: Greg StarkDate: 2009-10-30 04:20:23
Subject: Re: Syntax for partitioning
Previous:From: Robert HaasDate: 2009-10-30 02:14:13
Subject: Re: Syntax for partitioning

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group