Auto-partitioning?

From: Steve Wampler <swampler(at)noao(dot)edu>
To: Postgres-General <pgsql-general(at)postgresql(dot)org>
Subject: Auto-partitioning?
Date: 2007-08-20 18:53:21
Message-ID: 46C9E321.7030208@noao.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


As I understand partitioning, you can automatic "locate the partition into which a
row should be added" by adding rules such as (from the documentation):

----------------------------------------------------------
CREATE RULE measurement_insert_y2004m02 AS
ON INSERT TO measurement WHERE
( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' )
DO INSTEAD
INSERT INTO measurement_y2004m02 VALUES ( NEW.city_id,
NEW.logdate,
NEW.peaktemp,
NEW.unitsales );
...
CREATE RULE measurement_insert_y2005m12 AS
ON INSERT TO measurement WHERE
( logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01' )
DO INSTEAD
INSERT INTO measurement_y2005m12 VALUES ( NEW.city_id,
NEW.logdate,
NEW.peaktemp,
NEW.unitsales );
--------------------------------------------------------

Is it possible to add a default rule that gets invoked if all the
above rules fail to match? Specifically, I'm wondering if it's
possible to add a default rule that would create a new partition
(with indices, etc.) and would add a new rule for this partition
to match the similar ones above (and, of course, then move the
INSERT into the new partition).

In our case, the partitioning would be simple list partitioning on
the values of an 'experiment id', so I'm not concerned about trying
to create a range-based WHERE clause in the new rule.

Thanks!
Steve
--
Steve Wampler -- swampler(at)noao(dot)edu
The gods that smiled on your birth are now laughing out loud.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Martijn van Oosterhout 2007-08-20 19:07:18 Re: PGError: input out of range
Previous Message Michael Glaesemann 2007-08-20 18:34:01 Re: Stripping apostrophes from data