Re: [HACKERS] [POC] hash partitioning - Mailing list pgsql-hackers

Hi Ammit,

On Wed, 1 Mar 2017 11:14:15 +0900
Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> wrote:

> Nagata-san,
> 
> On 2017/02/28 23:33, Yugo Nagata wrote:
> > Hi all,
> > 
> > Now we have a declarative partitioning, but hash partitioning is not
> > implemented yet. Attached is a POC patch to add the hash partitioning
> > feature. I know we will need more discussions about the syntax and other
> > specifications before going ahead the project, but I think this runnable
> > code might help to discuss what and how we implement this.
> 
> Great!

Thank you!

> 
> > * Description
> > 
> > In this patch, the hash partitioning implementation is basically based
> > on the list partitioning mechanism. However, partition bounds cannot be
> > specified explicitly, but this is used internally as hash partition
> > index, which is calculated when a partition is created or attached.
> > 
> > The tentative syntax to create a partitioned table is as bellow;
> > 
> >  CREATE TABLE h (i int) PARTITION BY HASH(i) PARTITIONS 3 USING hashint4;
> > 
> > The number of partitions is specified by PARTITIONS, which is currently
> > constant and cannot be changed, but I think this is needed to be changed in
> > some manner. A hash function is specified by USING. Maybe, specifying hash
> > function may be ommitted, and in this case, a default hash function
> > corresponding to key type will be used.
> > 
> > A partition table can be create as bellow;
> > 
> >  CREATE TABLE h1 PARTITION OF h;
> >  CREATE TABLE h2 PARTITION OF h;
> >  CREATE TABLE h3 PARTITION OF h;
> > 
> > FOR VALUES clause cannot be used, and the partition bound is
> > calclulated automatically as partition index of single integer value.
> > 
> > When trying create partitions more than the number specified
> > by PARTITIONS, it gets an error.
> > 
> > postgres=# create table h4 partition of h;
> > ERROR:  cannot create hash partition more than 3 for h
> 
> Instead of having to create each partition individually, wouldn't it be
> better if the following command
> 
> CREATE TABLE h (i int) PARTITION BY HASH (i) PARTITIONS 3;
> 
> created the partitions *automatically*?
> 
> It makes sense to provide a way to create individual list and range
> partitions separately, because users can specify custom bounds for each.
> We don't need that for hash partitions, so why make users run separate
> commands (without the FOR VALUES clause) anyway?  We may perhaps need to
> offer a way to optionally specify a user-defined name for each partition
> in the same command, along with tablespace, storage options, etc.  By
> default, the names would be generated internally and the user can ALTER
> individual partitions after the fact to specify tablespace, etc.

I though that creating each partition individually is needed because some
user will want to specify a tablespce to each partition. However, as you
say, that isn't need for many cases because use can move a partition
to other tablespaces afterward by ALTER.

Thanks,
Yugo Nagata

> 
> Thanks,
> Amit
> 
> 
> 
> 
> -- 
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers


-- 
Yugo Nagata <nagata@sraoss.co.jp>