Re: Declarative partitioning grammar

 

<..>

FWIW, I had done some very initial work on declarative partitioning (no where as exhaustive as this proposal) and submitted a wip patch here:

 

 

Kindly take a look at the patch, to see if would be useful to you folks in any way.

<..>

 

Range
-----

Range has the most expressive grammar. I'll introduce it in steps:

...    PARTITION BY RANGE (b)
(
PARTITION aa start (date '2007-01-01') end (date '2008-01-01'),
PARTITION bb start (date '2008-01-01') end (date '2009-01-01')
);

It is common that these partitions follow a pattern, such as following
every week, month or year. So, we support the following specification:

...   PARTITION BY RANGE(order_date)
     (
           START (date '2005-12-01') end (date '2007-12-01')
                  EVERY(interval '2 months')
     );

<..>

It will be interesting to see how this start,end, interval usage accomodates data types other than dates. I hope, this specification is not influenced overlty just by dates-like partitions.

<..>

 

ADD
---

For range and list partitioning, it's important to be able to add
partitions for data not covered by the existing specification. So, we
propose:

...  ADD PARTITION q1_2008 end (date '2008-04-01')

<..>

What about data that does not match any existing partition specification? It might make sense to have a dummy partition which handles all these cases.

<..>

 

DROP
----

For list and range partitions, drop a specified partition from the set
of partitions.

... DROP PARTITION minny;

This drops a named partition. Often, it will be difficult for users to
know partition names, and they might be unnamed. So, we allow this
syntax:

... DROP PARTITION FOR(date '2007-01-01');

for range partitions; and:

... DROP PARTITION FOR(VALUES('CA'));

for list partitions.

We've also discussed something like:

... DROP PARTITION FOR(POSITION(1));

so that users can easily drop a specific partition in an array of range
partitions. It seems to me, though, that the use case is generally to
drop the oldest partition so perhaps we should have a more explicit
syntax. Thoughts?

<..>

Surely, the partitions will get (default, parent inferred) names when they get created? Do we expect the users to remember FOR() specifications like the ones mentioned above? It might make sense to have a "\d in psql" e.g to present a parent with all its named partitions alongwith the partition clauses to facilitate drop partition using partition names. 

<..>

EXCHANGE
--------

This sub-clause allows us to make a table a partition in a set of
partitions or take a partition out of a set but keep it as a table. IBM
uses ATTACH and DETACH, which is explicit but Oracle uses EXCHANGE. I'll
explain the latter:

... EXCHANGE <partition identifier> WITH TABLE <table name>

partition identifier is one of PARTITION <name> or PARTITION FOR(...).
The partition in the partition set 'becomes' the table <table name> and
vice-versa. Essentially, we'd swap the relfilenodes. This means that we
have to first ADD PARTITION then swap the table and the partition.
Thoughts?

<..>

Surely this wont be instantaneous?

<..>