Primary Key Increment Doesn't Seem Correct Under Table Partition

Поиск
Список
Период
Сортировка
От Yan Cheng Cheok
Тема Primary Key Increment Doesn't Seem Correct Under Table Partition
Дата
Msg-id 26007.36399.qm@web65701.mail.ac4.yahoo.com
обсуждение исходный текст
Ответы Re: Primary Key Increment Doesn't Seem Correct Under Table Partition  (Alban Hertroys <dalroi@solfertje.student.utwente.nl>)
Список 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 по дате отправления:

Предыдущее
От: yccheok
Дата:
Сообщение: Re: Create Trigger Function For Table Partition.
Следующее
От: Yan Cheng Cheok
Дата:
Сообщение: Correct Concept On Table Partition