Re: Creating partitions automatically at least on HASH?

Hello Robert and Robert,

>>
>>    CREATE TABLE foo(...) PARTITION BY HASH AUTOMATIC (MODULUS 10);
>>    -- or some other syntax
>>
>> This would be a relief on the longer path of dynamically creating
>> partitions, but with lower costs than a dynamic approach.
>
> Yeah, I think something like this would be reasonable, but I think
> that the best syntax is not really clear.  We might want to look at
> how other systems handle this.

> I don't much like AUTOMATIC.  It doesn't read like SQL's usual
> pseudo-English.

My English is kind-of broken. The intention is to differentiate the 3
cases with some syntax to say very clearly whether:

  - no partitions are created immediately (current case)
    but will have to be created manually later

  - static partitions are created automatically, based on provided
    parameters

  - dynamic partitions will be created later, when needed, based
    on provided parameters again.

Even if all that is not implemented immediately.

> We need something that will let you specify just a modulus for hash 
> partitions, a start, end, and interval for range partitions, and a list 
> of bounds for list partitions.  If we're willing to create a new 
> keyword, we could make PARTITIONS a keyword. Then:
>
> PARTITION BY HASH (whatever) PARTITIONS 8

I think that it should reuse already existing keywords, i.e. MODULUS 
should appear somewhere.

Maybe:

   ... PARTITION BY HASH (whatever)
       [ CREATE [IMMEDIATE | DEFERRED] PARTITIONS (MODULUS 8) |
         NOCREATE or maybe NO CREATE ];

This way the 3 cases are syntactically covered. Then they just need to be 
implemented:-) The IMMEDIATE case for HASH is pretty straightforward.

> PARTITION BY RANGE (whatever) PARTITIONS FROM 'some value' TO 'some
> later value' ADD 'some delta'

Robert Eckhardt "greenplum" syntax for ranges looks okay as well, and 
cover some corner cases (default, included/excluded bound...).

> PARTITION BY LIST (whatever) PARTITIONS ('bound', 'other bound',
> ('multiple', 'bounds', 'same', 'partition'))

Possibly.

> That looks fairly clean.  The method used to generate the names of the
> backing tables would need some thought.

Pg has a history of doing simple things, eg $ stuff on constraints, _pk 
for primary keys... I would not look too far.

>> The ALTER thing would be a little pain.
>
> Why would we need to do anything about ALTER?  I'd view this as a
> convenience way to set up a bunch of initial partitions, nothing more.

I'm naïve: I'd like that the user could change their mind about a given 
parameter and change it with ALTER:-)

-- 
Fabien.