partitioning question -- how to guarantee uniqueness across partitions

[Whit Armstrong <armstrong.whit@xxxxxxxxx>]

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;

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