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.