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.
