Partitioned table question

On Thu, Sep 24, 2009 at 12:02 PM, Reid Thompson <reid.thompson@ateb.com> wrote:

Assuming the examples on
http://www.postgresql.org/docs/8.3/static/ddl-partitioning.html

where measurement has children as noted....

       CREATE TABLE measurement (
           city_id         int not null,
           logdate         date not null,
           peaktemp        int,
           unitsales       int
       );

       CREATE TABLE measurement_y2006m02 ( ) INHERITS (measurement);
       CREATE TABLE measurement_y2006m03 ( ) INHERITS (measurement);
       ...
       CREATE TABLE measurement_y2007m11 ( ) INHERITS (measurement);
       CREATE TABLE measurement_y2007m12 ( ) INHERITS (measurement);
       CREATE TABLE measurement_y2008m01 ( ) INHERITS (measurement);

and insertion is governed ala the trigger example

       CREATE TRIGGER insert_measurement_trigger
           BEFORE INSERT ON measurement
           FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();

       CREATE OR REPLACE FUNCTION measurement_insert_trigger()
       RETURNS TRIGGER AS $$
       BEGIN
           IF ( NEW.logdate >= DATE '2006-02-01' AND NEW.logdate < DATE '2006-03-01' ) THEN
               INSERT INTO measurement_y2006m02 VALUES (NEW.*);
           ELSIF ( NEW.logdate >= DATE '2006-03-01' AND NEW.logdate < DATE '2006-04-01' ) THEN
               INSERT INTO measurement_y2006m03 VALUES (NEW.*);
           ...
           ELSIF ( NEW.logdate >= DATE '2008-01-01' AND NEW.logdate < DATE '2008-02-01' ) THEN
               INSERT INTO measurement_y2008m01 VALUES (NEW.*);
           ELSE
               RAISE EXCEPTION 'Date out of range.  Fix the measurement_insert_trigger() function!';
           END IF;
           RETURN NULL;
       END;
       $$
       LANGUAGE plpgsql;

If I know that no incoming data will be going into measurement_y2007m11
and I NO INHERIT measurement_y2007m11 from measurement, do I have to
immediately update the function measurement_insert_trigger() to remove
references to measurement_y2007m11, or will the function continue to
work fine and I can update it when convenient?

You can update it whenever it's convenient. I have a similar set and
I drop and recreate the insert trigger every night to handle inserts
for all past partitions and into the future 30 days. So if it fails
for a night or two no great loss.

You can test inserting with a large trigger and all the partitions and
on one that only hits maybe a few days in the past and a few days in
the future to see if it's faster on your machine. On mine there's no
big difference up to a few hundred tables at lease.

So we know have data in ~30 partitioned tables.
Our requirements now necessitate adding some columns to all these tables
( done ) which will get populated via batch sql for the older tables and
by normal processing as we move forward.

The batch update is going to result in dead tuples in the older tables.
What would be the recommended way to recover this dead space?
Vacuum full children tables + reindex children tables? or....

Thanks,
reid

On Wednesday 14 October 2009, Reid Thompson <reid.thompson@ateb.com> wrote:

So we know have data in ~30 partitioned tables.
Our requirements now necessitate adding some columns to all these tables
( done ) which will get populated via batch sql for the older tables and
by normal processing as we move forward.

The batch update is going to result in dead tuples in the older tables.
What would be the recommended way to recover this dead space?
Vacuum full children tables + reindex children tables? or....

cluster's faster.