Re: [POC] hash partitioning

On 01.03.2017 05:14, Amit Langote wrote:
> Nagata-san,
>
>> 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's a good idea but in this case we can't create hash-partition that is
also partitioned table, and as a consequence we are unable to create
subpartitions. My understanding is that the table can be partitioned
only using CREATE TABLE statement, not ALTER TABLE. For this reason the
new created partitions are only regular tables.

We can achieve desired result through creating a separate partitioned
table and making the DETACH/ATTACH manipulation, though. But IMO it's
not flexible case.

It would be a good thing if a regular table could be partitioned through
separate command. Then your idea would not be restrictive.

--
Maksim Milyutin
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company