Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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!



-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux