Re: Auto creation of Partitions

From: Jim Nasby <decibel(at)decibel(dot)org>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: "NikhilS" <nikkhils(at)gmail(dot)com>, "Peter Eisentraut" <peter_e(at)gmx(dot)net>, <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Auto creation of Partitions
Date: 2007-03-08 04:27:03
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers pgsql-patches

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 jim(at)nasby(dot)net
EnterpriseDB 512.569.9461 (cell)

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Greg Smith 2007-03-08 04:33:46 Re: Log levels for checkpoint/bgwriter monitoring
Previous Message Jim Nasby 2007-03-08 04:17:38 Re: Auto creation of Partitions

Browse pgsql-patches by date

  From Date Subject
Next Message Pavan Deolasee 2007-03-08 07:54:45 HOT WIP Patch - Version 4.1
Previous Message Jim Nasby 2007-03-08 04:17:38 Re: Auto creation of Partitions