On Thu, Sep 24, 2009 at 12:02 PM, Reid Thompson <reid.thompson@xxxxxxxx> 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
