Re: Primary Key Increment Doesn't Seem Correct Under Table Partition

Thanks and Regards
Yan Cheng CHEOK

--- On Thu, 1/28/10, Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl> wrote:

> From: Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl>
> Subject: Re: [GENERAL] Primary Key Increment Doesn't Seem Correct Under Table Partition
> To: "Yan Cheng Cheok" <yccheok(at)yahoo(dot)com>
> Cc: pgsql-general(at)postgresql(dot)org
> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>