partitioning question -- how to guarantee uniqueness across partitions - Mailing list pgsql-general

From Whit Armstrong
Subject partitioning question -- how to guarantee uniqueness across partitions
Date
Msg-id 8ec76080906280928k2aaf90c7yf3025b2db2ef7805@mail.gmail.com
Whole thread Raw
Responses Re: partitioning question -- how to guarantee uniqueness across partitions
List pgsql-general
I have a simple example copied from the 8.3 manual on partitioning
(http://www.postgresql.org/docs/8.3/interactive/ddl-partitioning.html).

My question is, if you create a serial type in the parent table which
is meant to be the primary key across all the partitions, how does one
guarantee uniqueness of that key?

This example is contrived, but it shows that there is no constraint to
prevent insertion of duplicate 'id' values into separate partition
tables. (after running the example there should be two id=100 rows in
the table). This makes sense because each partition only guarantees
uniqueness for it's own rows, and the primary key constraint on the
parent table never comes into play.  Do I need to add a test in the
trigger function to prevent insertion of duplicate 'id' values?

I realize that if I used a partitioning function that was based on
ranges of the primary key, then I would be guaranteed unique ranges of
id for each table, but most partitioning examples I've seen usually
test for date ranges which is similar to this example.

Any practical thoughts on this issue would be appreciated.

Thanks,
Whit


begin;

CREATE TABLE part_main (
id serial primary key,
data_value integer not null
);

CREATE TABLE part_10 (CHECK (data_value <= 10)) inherits (part_main);
ALTER TABLE part_10 ADD PRIMARY KEY (id);

CREATE TABLE part_20 (CHECK (data_value > 10 and data_value <= 20 ))
inherits (part_main);
ALTER TABLE part_20 ADD PRIMARY KEY (id);

CREATE OR REPLACE FUNCTION part_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF ( NEW.data_value <= 10) THEN INSERT INTO part_10 VALUES (NEW.*);
    ELSEIF ( NEW.data_value > 10 and NEW.data_value <= 20) THEN INSERT
INTO part_20 VALUES (NEW.*);
    ELSE RAISE EXCEPTION 'value out of range.  Fix the
part_insert_trigger() function!';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER insert_part_trigger
    BEFORE INSERT ON part_main
    FOR EACH ROW EXECUTE PROCEDURE part_insert_trigger();

insert into part_main (id,data_value)
values
(100,10),
(100,20);

commit;

pgsql-general by date:

Previous
From: APseudoUtopia
Date:
Subject: Re: Switching from MySQL: ON DUPLICATE KEY UPDATE, plpgsql function
Next
From: mobiledreamers@gmail.com
Date:
Subject: Re: horizontal sharding