Re: Creating partitions automatically at least on HASH?

Hello Robert & Robert,

> - 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 ];

I have given a small go at the parser part of that.

There are 3 types of partitions with 3 dedicated syntax structures to 
handle their associated parameters (WITH …, FROM … TO …, IN …). ISTM that 
it is a "looks good from far away" idea, but when trying to extend that it 
is starting to be a pain. If a 4th partition type is added, should it be 
yet another syntax? So I'm looking for an generic and extensible syntax 
that could accomodate all cases for automatic creation of partitions.

Second problem, adding a "CREATE" after "PARTITION BY … (…)" create 
shift-reduce conflicts with potential other CREATE TABLE option 
specification syntax. Not sure which one, but anyway. So the current 
generic syntax I'm considering is using "DO" as a trigger to start the 
optional automatic partition creation stuff:

   CREATE TABLE Stuff (...)
     PARTITION BY [HASH | RANGE | LIST] (…)
       DO NONE -- this is the default
       DO [IMMEDIATE|DEFERRED] USING (…)

Where the USING part would be generic keword value pairs, eg:

For HASH: (MODULUS 8) and/or (NPARTS 10)

For RANGE: (START '1970-01-01', STOP '2020-01-01', INCREMENT '1 year')
     and/or (START 1970, STOP 2020, NPARTS 50)

And possibly for LIST: (IN (…), IN (…), …), or possibly some other 
keyword.

The "DEFERRED" could be used as an open syntax for dynamic partitioning, 
if later someone would feel like doing it.

ISTM that "USING" is better than "WITH" because WITH is already used 
specifically for HASH and other optional stuff in CREATE TABLE.

The text constant would be interpreted depending on the partitioning 
expression/column type.

Any opinion about the overall approach?

-- 
Fabien.