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

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.

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!