Primary Key Increment Doesn't Seem Correct Under Table Partition

From: Yan Cheng Cheok <yccheok(at)yahoo(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Primary Key Increment Doesn't Seem Correct Under Table Partition
Date: 2010-01-26 10:00:58
Message-ID: 26007.36399.qm@web65701.mail.ac4.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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, 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Yan Cheng Cheok 2010-01-26 10:08:05 Correct Concept On Table Partition
Previous Message yccheok 2010-01-26 09:29:25 Re: Create Trigger Function For Table Partition.