Обсуждение: Partitioned table question
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? Thanks, reid
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.