On Mar 7, 2007, at 3:26 AM, Simon Riggs wrote:
If you know that the constraints on each of the tables is distinct,
then
building a UNIQUE index on each of the partitions is sufficient to
prove
that all rows in the combined partitioned table are distinct also.
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.
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.
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?
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.
--
Jim Nasby [EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
