Re: Auto creation of Partitions

> > The hard part there is checking that the partition constraints are 
> > distinct. If the partition constraints are added one at a time, you 
> > can use the predicate testing logic to compare the to-be-added 
> > partition's constraint against each of the already added
constraints. 
> > That becomes an O(N) problem.

Yes, we could preevaluate that check (per index) in the DDL phase and
keep the info in a flag. 
Also interesting info is if there is an order the partitions can be read
in to satisfy a particular order by.

> > What is really needed is a data structure that allows range
partitions 
> > to be accessed more efficiently. This could make adding partitions
and 
> > deciding in which partition a specific value goes an O(logN) 
> > operation.

I do not really see a problem with O(N) since typical N currently range
from 10 to 200.
N = 1000 is already good for a >= 10 TB table.
If a 10 GB partition were too large we should imho invest more in the
advanced indexing methods that are currently beeing developed.

> Directing data to child tables with triggers pretty much 
> necessitates having some way to codify what partition a 
> particular row belongs in.  
> IE: for partitioning by month, you'll see things like naming 
> the partition tables "parent_table_name_$YEAR_$MONTH", so the 
> 'partitioning function' takes a date or timestamp and then 
> returns what partition it belongs to. Perhaps there is some 
> way to use that mapping to drive the selection of what 
> partitions could contain a given value?

You put it in the first partition that has matching constraints.

> One possibility would be to require 3 functions for a partitioned
> table: one accepts the partitioning key and tells you what 
> partition it's in, one that tells you what the minimum 
> partitioning key for a partition would be, and one that tells 
> you what the maximum would be.  
> If the user supplied those 3 functions, I think it would be 
> possibly to automatically generate code for the triggers and 
> check constraints. The min/max partition key functions might 
> allow you to more efficiently do partition elimination, too.

I can see this as a good optional addition, but it can only be optional
else it would pretty much limit the methods that can be used for
partitioning. e.g. hash, modulo do not have a min,max per partition.

Andreas