Thread: Primary Key Increment Doesn't Seem Correct Under Table Partition
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, byusing the result of modulo, dynamic table name will be generated. and that particular row will be assigned into measurement'schild 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
On 26 Jan 2010, at 11: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. > Also, is it necessary to create index for measurement_id found in measurement's child table? I am concern on the read speed. Serials don't create indexes, only a sequence. Primary keys create indexes (to guarantee uniqueness) - foreign keys do not. So yes, you want an index on that column in the child table. Alban Hertroys -- Screwing up is the best way to attach something to the ceiling. !DSPAM:737,4b5ec59910605107914066!
Thanks and Regards Yan Cheng CHEOK --- On Tue, 1/26/10, Alban Hertroys <dalroi@solfertje.student.utwente.nl> wrote: > From: Alban Hertroys <dalroi@solfertje.student.utwente.nl> > Subject: Re: [GENERAL] Primary Key Increment Doesn't Seem Correct Under Table Partition > To: "Yan Cheng Cheok" <yccheok@yahoo.com> > Cc: pgsql-general@postgresql.org > Date: Tuesday, January 26, 2010, 6:36 PM > On 26 Jan 2010, at 11: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? > > Also, is it necessary to create index for > measurement_id found in measurement's child table? I am > concern on the read speed. > > > Serials don't create indexes, only a sequence. Primary keys > create indexes (to guarantee uniqueness) - foreign keys do > not. > So yes, you want an index on that column in the child > table. > 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 ? > Alban Hertroys > > -- > Screwing up is the best way to attach something to the > ceiling. > > > !DSPAM:737,4b5ec59910605107914066! > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Hello all,
I solve my problem using the following. It seems that when inherit from parent table, the parent table's constraint is
notbeing carried over to child table.
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) || '
(
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);';
END IF;
EXECUTE 'INSERT INTO ' || quote_ident(measurement_table_name) || '(measurement_id, fk_unit_id, v) VALUES (' ||
NEW.measurement_id|| ',' || NEW.fk_unit_id || ',' || quote_literal(NEW.v) || ')';
RETURN NULL;
END;$BODY$
LANGUAGE plpgsql;
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 thatcolumn 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 typesof 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!
On 27 Jan 2010, at 4:22, Yan Cheng Cheok wrote:
> Hello all,
>
> I solve my problem using the following. It seems that when inherit from parent table, the parent table's constraint
isnot being carried over to child table.
>
> 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) || '
> (
> 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.
EXECUTE 'ALTER TABLE ' || ... ||
' ADD PRIMARY KEY (measurement_id),' ||
' ADD FOREIGN KEY (fk_unit_id) REFERENCES unit (unit_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE;';
One thing to note though is that this primary key is not guaranteed to be unique across different partitions or in the
parenttable (as the rows aren't actually IN the parent table).
> END IF;
>
> EXECUTE 'INSERT INTO ' || quote_ident(measurement_table_name) || '(measurement_id, fk_unit_id, v) VALUES (' ||
NEW.measurement_id|| ',' || NEW.fk_unit_id || ',' || quote_literal(NEW.v) || ')';
> RETURN NULL;
> END;$BODY$
> LANGUAGE plpgsql;
Alban Hertroys
--
Screwing up is the best way to attach something to the ceiling.
!DSPAM:737,4b608af610606065868549!
Thanks and Regards Yan Cheng CHEOK --- On Thu, 1/28/10, Alban Hertroys <dalroi@solfertje.student.utwente.nl> wrote: > From: Alban Hertroys <dalroi@solfertje.student.utwente.nl> > Subject: Re: [GENERAL] Primary Key Increment Doesn't Seem Correct Under Table Partition > To: "Yan Cheng Cheok" <yccheok@yahoo.com> > Cc: pgsql-general@postgresql.org > Date: Thursday, January 28, 2010, 2:50 AM > On 27 Jan 2010, at 4:22, Yan Cheng > Cheok wrote: > > > Hello all, > > > > I solve my problem using the following. It seems that > when inherit from parent table, the parent table's > constraint is not being carried over to child table. > > > > 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) || ' > > ( > > 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. > EXECUTE 'ALTER TABLE ' || ... || > ' ADD PRIMARY KEY (measurement_id),' || > ' ADD FOREIGN KEY (fk_unit_id) REFERENCES unit (unit_id) > MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE;'; > > One thing to note though is that this primary key is not > guaranteed to be unique across different partitions or in > the parent table (as the rows aren't actually IN the parent > table). > > > END IF; > > > > EXECUTE 'INSERT INTO ' || > quote_ident(measurement_table_name) || '(measurement_id, > fk_unit_id, v) VALUES (' || NEW.measurement_id || ',' || > NEW.fk_unit_id || ',' || quote_literal(NEW.v) || ')'; > > RETURN NULL; > > END;$BODY$ > > LANGUAGE plpgsql; > > Alban Hertroys > > -- > Screwing up is the best way to attach something to the > ceiling. > > > !DSPAM:737,4b608af610606065868549! > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
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!
Sorry. I didn't get all your points. "defining a primary key constraint implicitly creates an index" - Yup. I agree on this. But what is the purpose, for authorto explicitly define index for day? CREATE INDEX impressions_by_day_y2012m3ms2_day ON impressions_by_day_y2012m3ms2 (day); Isn't the primary constraint will implicitly create an index for day already? PRIMARY KEY (advertiser_id, day), Thanks. > > > 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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
On 29 Jan 2010, at 1:56, Yan Cheng Cheok wrote: > Isn't the primary constraint will implicitly create an index for day already? > > PRIMARY KEY (advertiser_id, day), Yes, but it's not a very efficient index to look for values of day if you don't provide a value for advertiser_id as well.See http://www.postgresql.org/docs/8.4/interactive/indexes-multicolumn.html 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,4b62d2e810751877882363!