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.
