Primary Key Increment Doesn't Seem Correct Under Table Partition

[Yan Cheng Cheok <yccheok@xxxxxxxxx>]

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, by using the result of modulo, dynamic table name will be generated. and that particular row will be assigned into measurement's child 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


      


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general