Primary Key Increment Doesn't Seem Correct Under Table Partition - Mailing list pgsql-general

From Yan Cheng Cheok
Subject Primary Key Increment Doesn't Seem Correct Under Table Partition
Date
Msg-id 26007.36399.qm@web65701.mail.ac4.yahoo.com
Whole thread Raw
Responses Re: Primary Key Increment Doesn't Seem Correct Under Table Partition  (Alban Hertroys <dalroi@solfertje.student.utwente.nl>)
List pgsql-general
Currently, I have a table which I implement table (measurement) partition policy.

http://www.postgresql.org/docs/current/static/ddl-partitioning.html

Whenever an item being inserted into measurement table, modulo will be perform on measurement table primary key. Then,
byusing the result of modulo, dynamic table name will be generated. and that particular row will be assigned into
measurement'schild table. 

Some portion of code is as follow :

--------------------
-- measurement table
--------------------

        CREATE TABLE measurement
        (
          measurement_id bigserial NOT NULL,
          fk_unit_id bigint NOT NULL,
          v text NOT NULL,
          CONSTRAINT pk_measurement_id PRIMARY KEY (measurement_id),
          CONSTRAINT fk_unit_id FOREIGN KEY (fk_unit_id)
              REFERENCES unit (unit_id) MATCH SIMPLE
              ON UPDATE NO ACTION ON DELETE CASCADE
        );

--------------------
-- measurement table trigger function
--------------------

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) || '
        (
        ) INHERITS (measurement);';
        EXECUTE 'CREATE INDEX ' || quote_ident(measurement_table_name) || '_measurement_id ON ' ||
quote_ident(measurement_table_name)|| '(measurement_id);';       
    END IF;

    EXECUTE 'INSERT INTO ' || quote_ident(measurement_table_name) || '(fk_unit_id, v) VALUES (' || NEW.fk_unit_id ||
','|| quote_literal(NEW.v) || ')'; 
    RETURN NULL;
END;$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER insert_measurement_trigger
    BEFORE INSERT ON measurement
    FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();


However, whenever I insert row into measurement table, I realize its primary key value is going from 2, 4, 6, 8, 10...

May I know how can I prevent this?

The complete code is at

http://sites.google.com/site/yanchengcheok/Home/table-partition.sql?attredirects=0&d=1

(1) create a database named sandbox.

(2) execute script in table-partition.sql

(3) SELECT * FROM create_lot();

(4) View on measurement table.

Also, is it necessary to create index for measurement_id found in measurement's child table? I am concern on the read
speed.

Thanks and Regards
Yan Cheng CHEOK





pgsql-general by date:

Previous
From: yccheok
Date:
Subject: Re: Create Trigger Function For Table Partition.
Next
From: Yan Cheng Cheok
Date:
Subject: Correct Concept On Table Partition