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

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
parenttable 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.

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 ];

... where <key_expression> can be any immutable expression on one or
more columns of some_table.  This actually covers range partitioning as
well, provided that the ranges can be expressed as the results of an
expression (e.g. EXTRACT ('month' FROM date_processed ) ).

For the optional AUTOMATIC CREATE phrase, new values for key_expression
would result in the automatic creation of new partitions when they
appear (this has some potential deadlocking issues, so it's not ideal
for a lot of applications).  Otherwise, you'd create partitions manually:

CREATE PARTITION ON <table_name> KEY <key_value>;
DROP PARTITION ON <table_name> KEY <key_value>;

... where <key_value> is some valid value which could result from
<key_expression>.

Yes, this is a very narrow and simplistic partitioning spec.  However,
it would cover 80% of the use cases I see in the field or on IRC, while
being 80% simpler than CE.  And CE would still be there for those who
need it.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com