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