Thread: managing table partitions automatically

The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer.

Help make the earth a greener place. If at all possible resist printing this email and join us in saving paper.

On Thu, Nov 13, 2008 at 10:33:11AM -0600, Ertel, Steve wrote:
> 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

1) Please avoid top-posting
2) Use triggers instead of rules wherever possible
3) From the docs: "If more than one trigger is defined for the same
event on the same relation, the triggers will be fired in alphabetical
order by trigger name. In the case of before triggers, the
possibly-modified row returned by each trigger becomes the input to the
next trigger. If any before trigger returns NULL, the operation is
abandoned for that row and subsequent triggers are not fired."

- Josh / eggyknap

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

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

> 2) Use triggers instead of rules wherever possible
> 3) From the docs: "If more than one trigger is defined for the same
> event on the same relation, the triggers will be fired in alphabetical
> order by trigger name. In the case of before triggers, the
> possibly-modified row returned by each trigger becomes the input to the
> next trigger. If any before trigger returns NULL, the operation is
> abandoned for that row and subsequent triggers are not fired."

> - Josh / eggyknap

Steve,

What you are doing can be implemented with rules or triggers.  I wouldn't agree with Josh that in all cases a trigger is better than a rule.  It depends on your pattern of inserts and if you are optimizing for speed or manageability or what.

Either way, you will want to put check constraints in each of your tables and have constraint_exclusion = on for your db so you can take advantage of constraint exclusion. And even ask the very fundamental question of if you really have enough data that it makes the most sense to partition right now.

Anyrate Robert Treat's slide is a good primer of how to use both approaches and the pros and cons of each.

http://www.slideshare.net/xzilla/postgresql-partitioning-pgcon-2007-presentation

Just my 2 cents,
Regina

The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer.

Help make the earth a greener place. If at all possible resist printing this email and join us in saving paper.