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.