Re: Partitioned table question

On Thu, Sep 24, 2009 at 12:02 PM, Reid Thompson <reid(dot)thompson(at)ateb(dot)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.