Re: New partitioning WAS: Check constraints on partition parents only?

On Tue, Jul 26, 2011 at 7:58 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> Jim,
>
>> That's why I'd be opposed to any partitioning scheme that removed the ability to have different fields in different children. We've found that ability to be very useful. Likewise, I think we need to have intelligent plans involving a parent table that's either completely empty or mostly empty.
>
> Well, I don't think that anyone is proposing making constraint exclusion
> go away.  However, we also need a new version of partitioning which
> happens "below" the table level.  I don't agree that the new
> partitioning needs -- at least at the start -- the level of flexibility
> which CE gives the user.  In order to get simplicity, we have to
> sacrifice flexibility.

Agreed.

> In fact, I'd suggest extreme simplicity for the first version of this,
> with just key partitioning.  That is:
>
> CREATE TABLE <table_name> (
>        ... cols ... )
> PARTITION ON <key_expression>
> [ AUTOMATIC CREATE ];

I think that the automatic create feature is just about impossible to
implement reliably, at least not without autonomous transactions.
There are big problems here in the case of concurrent activity.

What Itagaki Takahiro proposed a year ago was basically something
where you would say, OK, I want to partition on this column (or maybe
expression). And then you say:

If the value is less than v1, put it in a partition called p1.
If the value is less than v2, put it in a position called p2.
<repeat ad nauseum, and then, optionally:>
If the value is not less than any of the above, put it in a partition
called poverflow.

I like that design, not least but also not only because it's similar
to what one of our competitors does.

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