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

On 28 Jan 2010, at 2:10, Yan Cheng Cheok wrote:

>>> EXECUTE 'CREATE TABLE ' ||
>> quote_ident(measurement_table_name) || '
>>> (
>>> CONSTRAINT
>> pk_measurement_id_' || measurement_table_index || ' PRIMARY
>> KEY (measurement_id),
>>> CONSTRAINT
>> fk_unit_id_' || measurement_table_index || ' FOREIGN KEY
>> (fk_unit_id)
>>>
>> REFERENCES unit (unit_id) MATCH SIMPLE
>>> ON
>> UPDATE NO ACTION ON DELETE CASCADE
>>
>>> ) INHERITS
>> (measurement);';
>>> EXECUTE 'CREATE INDEX ' ||
>> quote_ident(measurement_table_name) || '_measurement_id ON '
>> || quote_ident(measurement_table_name) ||
>> '(measurement_id);';
>>
>>
>> I think you should actually add the constraints back in
>> there, not just create an index.
>>
>
> Thanks. The example I seen here doesn't use "ALERT TABLE"
>
> http://www.if-not-true-then-false.com/2009/11/howto-create-postgresql-table-partitioning-part-1/
>
> But I assume both shall doing the same thing.

Sorry, my mistake, must not have had enough coffee yesterday. You _are_ in fact re-defining the primary and foreign keys on your child tables, as you should.

Your index threw me off though, as you're adding a second index to the primary key instead of one on the foreign key - and the latter is the one you need. As I wrote before, defining a primary key constraint implicitly creates an index on those columns the primary key is on, so you just created a duplicate index there.

From the page you link to I see how you got the idea that you needed an index - and in your case you probably do, just on a different column.
They have a good reason to add an index on their 'day' column - they're partitioning on a date-range on that column and it doesn't have any indexes on it that are usable to query just 'day'[*]. For them it's not their primary key.

I think their 'advertiser_id' is in fact a foreign key to another table, but they haven't specified it like that for some reason. I think they should; it's an integer column without a sequence on it and with a not null constraint, it has no meaning by itself so it's clearly referencing some row in another table.

*) Indexes on multiple columns can not be used on columns deeper in the index if the query doesn't also query for the higher-up columns. An index on (advertiser_id, day) can not efficiently be used without an advertiser_id to query for days.
Advertiser_id is probably a foreign key to another table, so it's not unique by itself and they added the day column to the primary key to make it unique - it's some kind of summary table with a resolution of one day per advertiser, so those together are unique.

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,4b614e3f10601193912706!