Re: Automating PostgreSql table partition using triggers

On Thu, Jan 27, 2011 at 10:50 AM, Amitabh Kant <amitabhkant@gmail.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.