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