Re: On partitioning

On Thu, Dec 11, 2014 at 12:00 AM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> Yeah either this way or what Josh has suggested upthread, the main
> point was that if at all we want to support multi-column list partitioning
> then we need to have slightly different syntax, however I feel that we
> can leave multi-column list partitioning for first version.

Yeah, possibly.

I think we could stand to have a lot more discussion about the syntax
here. So far the idea seems to be to copy what Oracle has, but it's
not clear if we're going to have exactly what Oracle has or something
subtly different. I personally don't find the Oracle syntax very
PostgreSQL-ish. Stuff like "VALUES LESS THAN 500" doesn't sit
especially well with me - less than according to which opclass? Are
we going to insist that partitioning must use the default btree
opclass so that we can use that syntax? That seems kind of lame.

There are lots of interesting things we could do here, e.g.:

CREATE TABLE parent_name PARTITION ON (column [ USING opclass ] [, ... ]);
CREATE TABLE child_name PARTITION OF parent_name
FOR { (value, ...) [ TO (value, ...) ] } [, ...];

So instead of making a hard distinction between range and list
partitioning, you can say:

CREATE TABLE child_name PARTITION OF parent_name FOR (3), (5), (7);
CREATE TABLE child2_name PARTITION OF parent_name FOR (8) TO (12);
CREATE TABLE child2_name PARTITION OF parent_name FOR (20) TO (30),
(120) TO (130);

Now that might be a crappy idea for various reasons, but the point is
there are a lot of details to be hammered out with the syntax, and
there are several ways we can go wrong. If we choose an
overly-limiting syntax, we're needlessly restricting what can be done.
If we choose an overly-permissive syntax, we'll restrict the
optimization opportunities.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company