Re: managing table partitions automatically

Steve,
I usually use an SQL script that generates SQL to dynamically create
named tables and other DDL like stuff - something like described in the
2 below articles. In your case you'd probably want to use
generate_series() to generate table names based on year and month.

http://www.postgresonline.com/journal/index.php?/archives/30-DML-to-gene
rate-DDL-and-DCL--Making-structural-and-Permission-changes-to-multiple-t
ables.html

http://www.bostongis.com/blog/index.php?/archives/33-More-generate_serie
s-tricks.html

(look at the below section of the above to see how to use
generate_series to generate date parts)

Also have an example of dates and generate_series in the cheat sheet
http://www.postgresonline.com/journal/index.php?/archives/17-PostgreSQL-
8.3-Cheat-Sheet-Overview.html


Hope that helps,
Regina

________________________________

From: pgsql-novice-owner(at)postgresql(dot)org
[mailto:pgsql-novice-owner(at)postgresql(dot)org] On Behalf Of Ertel, Steve
Sent: Thursday, November 13, 2008 9:42 AM
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] managing table partitions automatically

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)
<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

-----------------------------------------
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.