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@gmail.com]
Sent: Thursday, November 13, 2008 11:22 AM
To: Ertel, Steve
Cc: pgsql-novice@postgresql.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@postgresql.org
> [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Ertel,
Steve
> Sent: Wednesday, November 12, 2008 12:48 PM
> To: pgsql-novice@postgresql.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