Thanks and Regards Yan Cheng CHEOK --- On Thu, 1/28/10, Alban Hertroys <dalroi@xxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote: > From: Alban Hertroys <dalroi@xxxxxxxxxxxxxxxxxxxxxxxxxxxx> > Subject: Re: Primary Key Increment Doesn't Seem Correct Under Table Partition > To: "Yan Cheng Cheok" <yccheok@xxxxxxxxx> > Cc: pgsql-general@xxxxxxxxxxxxxx > Date: Thursday, January 28, 2010, 2:50 AM > On 27 Jan 2010, at 4:22, Yan Cheng > Cheok wrote: > > > Hello all, > > > > I solve my problem using the following. It seems that > when inherit from parent table, the parent table's > constraint is not being carried over to child table. > > > > CREATE OR REPLACE FUNCTION > measurement_insert_trigger() > > RETURNS TRIGGER AS > > $BODY$DECLARE > > measurement_table_index bigint; > > measurement_table_name text; > > BEGIN > > -- 20 is just an example here right now. > The true value will be 100,000,000 > > measurement_table_index = > NEW.measurement_id % 20; > > measurement_table_name = 'measurement_' > || measurement_table_index; > > > > -- Since measurement_id for parent table > is already a bigserial > > -- Do I still need to create index for > child's measurement_id? > > > > IF NOT EXISTS(SELECT * FROM > information_schema.tables WHERE table_name = > measurement_table_name) THEN > > EXECUTE 'CREATE TABLE ' || > quote_ident(measurement_table_name) || ' > > ( > > CONSTRAINT > pk_measurement_id_' || measurement_table_index || ' PRIMARY > KEY (measurement_id), > > CONSTRAINT > fk_unit_id_' || measurement_table_index || ' FOREIGN KEY > (fk_unit_id) > > > REFERENCES unit (unit_id) MATCH SIMPLE > > ON > UPDATE NO ACTION ON DELETE CASCADE > > > ) INHERITS > (measurement);'; > > EXECUTE 'CREATE INDEX ' || > quote_ident(measurement_table_name) || '_measurement_id ON ' > || quote_ident(measurement_table_name) || > '(measurement_id);'; > > > I think you should actually add the constraints back in > there, not just create an index. > Thanks. The example I seen here doesn't use "ALERT TABLE" http://www.if-not-true-then-false.com/2009/11/howto-create-postgresql-table-partitioning-part-1/ But I assume both shall doing the same thing. > EXECUTE 'ALTER TABLE ' || ... || > ' ADD PRIMARY KEY (measurement_id),' || > ' ADD FOREIGN KEY (fk_unit_id) REFERENCES unit (unit_id) > MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE;'; > > One thing to note though is that this primary key is not > guaranteed to be unique across different partitions or in > the parent table (as the rows aren't actually IN the parent > table). > > > END IF; > > > > EXECUTE 'INSERT INTO ' || > quote_ident(measurement_table_name) || '(measurement_id, > fk_unit_id, v) VALUES (' || NEW.measurement_id || ',' || > NEW.fk_unit_id || ',' || quote_literal(NEW.v) || ')'; > > RETURN NULL; > > END;$BODY$ > > LANGUAGE plpgsql; > > Alban Hertroys > > -- > Screwing up is the best way to attach something to the > ceiling. > > > !DSPAM:737,4b608af610606065868549! > > > > -- > Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
