Skip site navigation (1) Skip section navigation (2)

Re: managing table partitions automatically

From: "Ertel, Steve" <Steve(dot)Ertel(at)infimatic(dot)com>
To: "Joshua Tolley" <eggyknap(at)gmail(dot)com>
Cc: <pgsql-novice(at)postgresql(dot)org>
Subject: Re: managing table partitions automatically
Date: 2008-11-13 16:33:11
Message-ID: 863ECFDF04C8804ABDF3559D87F157C601472CB5@MAGPTCPEXC02.na.mag-ias.net (view raw or flat)
Thread:
Lists: pgsql-novice
I realize that I could do this from a background process.  The problem
is that a user may create a record where the start_time is further in
the future or the past than I have tables to hold it.  The application
that I am working on contains schedule functionality that allows people
to schedule events for an unlimited time into the future.

I could create a TRIGGER or RULE that would be executed for each insert
that checks for a tables existence, but how do I control the order that
the rules or triggers are executed in?

Steve
 

-----Original Message-----
From: Joshua Tolley [mailto:eggyknap(at)gmail(dot)com] 
Sent: Thursday, November 13, 2008 11:22 AM
To: Ertel, Steve
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] managing table partitions automatically

On Thu, Nov 13, 2008 at 08:42:28AM -0600, Ertel, Steve wrote:
>    *
>    Still haven't heard from anyone.
>     
>    I could really use your help.
>     
>    Do you have any suggestions?
>     
>     
>    Thanks,
>    Steve
> 
>      
> ----------------------------------------------------------------------
> 
>    From: pgsql-novice-owner(at)postgresql(dot)org
>    [mailto:pgsql-novice-owner(at)postgresql(dot)org] On Behalf Of Ertel,
Steve
>    Sent: Wednesday, November 12, 2008 12:48 PM
>    To: pgsql-novice(at)postgresql(dot)org
>    Subject: [NOVICE] managing table partitions automatically
>    Hello All,
>     
>    I am beginning to partition some database tables and need some help
>    thinking things through.  After evaluating my data, it appears that
the
>    best partition would be based on year and month.  I was planning to
create
>    my tables in the format of tablename_yyyy_mm. 
>     
>    I planned to create a parent table and child tables (similar to the
>    example at
>    http://www.postgresql.org/docs/8.1/static/ddl-partitioning.html).
I would
>    create the child tables for the range of my data and maybe even a
few
>    months in advance.  I have all of the insert, update, and delete
rules
>    worked out to manage the data.  The data will be routed to tables
based on
>    a date that is passed to the table (e.g. start_time).  The date can
be in
>    the future or in the past.
>     
>    The problem is that the databases are running on remote servers and
I will
>    not have access to them.  I need to find a way to automatically
create the
>    child tables, indexes, and rules.  Any suggestions would be
helpful.  I am
>    stuck.
>     
>    Thanks,
>    Steve
>     

Your best bet is a really well-tested script to create the new
partitions periodically. PostgreSQL won't do it for you.

- Josh / eggyknap

In response to

Responses

pgsql-novice by date

Next:From: Joshua TolleyDate: 2008-11-13 17:20:54
Subject: Re: managing table partitions automatically
Previous:From: Joshua TolleyDate: 2008-11-13 16:21:49
Subject: Re: managing table partitions automatically

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group