partitioning question -- how to guarantee uniqueness across partitions

From: Whit Armstrong <armstrong(dot)whit(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: partitioning question -- how to guarantee uniqueness across partitions
Date: 2009-06-28 16:28:21
Message-ID: 8ec76080906280928k2aaf90c7yf3025b2db2ef7805@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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;

Responses

Browse pgsql-general by date

  From Date Subject
Next Message mobiledreamers 2009-06-28 16:51:41 Re: horizontal sharding
Previous Message APseudoUtopia 2009-06-28 16:22:32 Re: Switching from MySQL: ON DUPLICATE KEY UPDATE, plpgsql function