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
keyson 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 -
andthe latter is the one you need. As I wrote before, defining a primary key constraint implicitly creates an index on
thosecolumns 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
ona 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
itdoesn'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
somereason. I think they should; it's an integer column without a sequence on it and with a not null constraint, it has
nomeaning 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-upcolumns. 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
theprimary key to make it unique - it's some kind of summary table with a resolution of one day per advertiser, so
thosetogether 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!