Re: Declarative partitioning - Mailing list pgsql-hackers

On 2015-08-19 AM 02:59, Corey Huinker wrote:
> 
> Quick thoughts borne of years of slugging it out with partitions on Oracle:
> 
> - Finally!!!!!!!!!!!
> 
> - Your range partitioning will need to express exclusive/inclusive bounds,
> or go to the Oracle model where every partition is a cascading "values less
> than" test context dependent on the partitions defined before it. I would
> suggest that leveraging existing range types (or allowing the user to
> specify a range type, like for a specific collation of a text range) would
> allow for the most flexible and postgres-ish range definition. You seem to
> do this with the "[USING] opclass_name" bit, but don't follow through on
> the START(...) and END(...).  Something like FOR VALUES <@
> '[''2014-01-01'',''2015-01-01)'::daterange would cover most needs
> succinctly, though I admit the syntax for complex ranges could be
> cumbersome, though something like FOR VALUES <@
> '[(''a'',1),(''b'',1))'::letter_then_number_range is still readable.
> 

It seems the way of specifying per-partition definition/constraint,
especially for range partitioning, would have a number of interesting
alternatives.

By the way, the [USING opclass_name] bit is just a way of telling that a
particular key column has user-defined notion of "ordering" in case of
range partitioning and "equality" for list partitioning. The opclass would
eventually determine which WHERE clauses (looking at operators, operand
types) are candidates to help prune partitions. If we use the range_op
range_literal::range_type notation to describe partition constraint for
each partition, it might not offer much beyond the readability. We are not
really going to detect range operators being applied in WHERE conditions
to trigger partition pruning, for example. Although I may be missing
something...

> - No partitioning scheme survives first contact with reality. So you will
> need a facility for splitting and joining existing partitions. For
> splitting partitions, it's sufficient to require that the new partition
> share either a upper/lower bound (with the same inclusivity/exclusivity) of
> an existing partition, thus uniquely identifying the partition to be split,
> and require that the other bound be within the range of the partition to be
> split. Similarly, it's fair to require that the partitions to be joined be
> adjacent in range. In both cases, range operators make these tests simple.
> 

SPLIT/MERGE can be done in later patches/release, I think.

> - Your features 4 and 5 are implemented in Oracle with SWAP PARTITION,
> which is really neat for doing ETLs and index rebuilds offline in a copy
> table, and then swapping the data segment of that table with the partition
> specified. Which could be considered cheating because none of the partition
> metadata changed, just the pointers to the segments. We already do this
> with adding removing INHERIT. I'm not saying they can't be separate
> functionality, but keeping an atomic SWAP operation would be grand.
> 

I think we can manage to find ways to make the proposed ATTACH/DETACH as
useful and convenient. Thanks for reminding of SWAP PARTITION.

Regards,
Amit