Re: Declarative partitioning grammar

Hi Jeff,

Jeff Cohen wrote:
> If you don't define a "default" partition to handle outliers, the
> insert should fail with an error.

IMO, you should always have a "default" partition, then, so as not to
violate the constraints (by rejecting tuples which are correct according
to the constraints).

> With the generic approach, you start with a single table, and start by
> splitting it into two six-month partitions:
>
> ALTER TABLE sales
> SPLIT where sales_date > date '2007-06-01'
> INTO
> (
> PARTITION first_half
> PARTITION second_half
> );
>
> We could implement this approach using check constraints and table
> inheritance: the partition second_half is a child table where sales_date
> > date '2007-06-01', and the partition first_half has the complementary
> constraint NOT(sales_date > date '2007-06-01').
>
> Next, you split each partition:
>
> ALTER TABLE sales
> SPLIT PARTITION first_half where sales_date > date '2007-03-01'
> INTO
> (
> PARTITION first_quarter
> PARTITION second_quarter
> );
>
> So now the child table for first_half itself has two children. As you
> continue this process you construct a binary tree of table inheritance
> using 12 ALTER statements.

<nitpicking>There are just 11 splits between 12 months, otherwise
correct, yes.</nitpicking>

> In the "long" grammar you can create and partition the table in one
> statement:
>
> CREATE TABLE sales
> ...
> PARTITION BY sales_date
> (
> start (date '2007-01-01') end (date '2008-01-01')
> every (interval '1 month')
> );

To be fair, you should add the 12 partition names here as well.

I can certainly see merit in letting the database system handle the
binary tree.

> Thanks for your feedback. Partitioning the table using series of splits
> is a clever solution for situations where the partitioning operation
> cannot be described using simple equality (like list,hash) or ordered
> comparison (range). But for many common business cases, the "long"
> grammar is easier to specify.

Easier to specify initially, maybe, yes. But how about managing it
afterwards? Having seen all the different options for merging,
splitting, exchanging, coalescing and adding, all of them with small
little differences for hash, range and list partitioning - let alone
sub-partitioning - with all of that, the proposed grammar doesn't look
particularly easy to me.

Let's at least drop the differences for list, hash and range
partitioning, those are pretty unneeded, IMO.

Regards

Markus