Re: Automating PostgreSql table partition using triggers

On Fri, Jan 28, 2011 at 2:57 AM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>wrote:

> On Thu, Jan 27, 2011 at 10:50 AM, Amitabh Kant <amitabhkant(at)gmail(dot)com>
> wrote:
> > Hi
> >
> > I am trying to write a function which is being called from a trigger used
> > for partitioning a large table. The partitioning is to happen based on an
> > integer field (testing_id). A simplified structure of what I am trying to
> do
> > is written below.
>
> My advice is to move the table creation from a trigger to a nightly
> cron job. I have one that runs each night that checks for the
> existence of the needed tables and creates new ones for up to a month
> in advance and emails me the results. that way if it starts failing
> I've got 30 days to get things fixed. Then another script runs that
> creates my new triggers to work with the new partitions.
>
> Too many race conditions and performance issues with using dynamic DDL
> to create partitions.
>

My partitions are based on a foreign key. I would be creating child tables
when a new entry is made in the parent table.

With regards

Amitabh