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

On 27 Jan 2010, at 2:00, Yan Cheng Cheok wrote:
>>> 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?
>>
>> Apparently nextval on that sequence gets called multiple
>> times in your queries.
>>
>
> Do you have any idea why it happens so?

It's something you're doing in your queries that causes this. You haven't provided any info about that, so I can only guess.
The problem could for example be that you're selecting nextval() of the sequence and then don't fill in the value for that column in a subsequent INSERT, causing the default to call nextval() again.

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

> But...
>
> measurement table does contains
>
> CONSTRAINT pk_measurement_id PRIMARY KEY (measurement_id),
>
> when measurement_0 child table inherits from measurement table, isn't it will inherit PRIMARY KEY (measurement_id)?
>
> Do I still have to create index for measurement_0.measurement_id ?

Ah I see, you were asking about table inheritance. To quote the documentation at http://www.postgresql.org/docs/8.4/interactive/ddl-inherit.html:

"All check constraints and not-null constraints on a parent table are automatically inherited by its children. Other types of constraints (unique, primary key, and foreign key constraints) are not inherited."

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4b60897b10606504295220!