From: | "Milen Kulev" <makulev(at)gmx(dot)net> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Partitioning... |
Date: | 2006-06-12 22:02:42 |
Message-ID: | 000001c68e6b$f0ecbf50$0a00a8c0@trivadis.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi listers,
I am trying to learn PG partioning (constaraint exclustion).
I have created pretty simple table (all the code is below), but when I try to populate
The table with data, the RULE system is not working as expected (e.g. as I have expected).
The code:
-------------------------------------------
CREATE TABLE part (
id1 int not null,
id2 int not null,
filler varchar(200)
);
create table part_id1_0_10 ( CHECK ( id1>= 0 and id1<=10) ) INHERITS (part);
create table part_id1_11_20 ( CHECK ( id1>=11 and id1<=20) ) INHERITS (part);
CREATE INDEX idx_part_id1_0_10 ON part_id1_0_10(id1);
CREATE INDEX idx_part_id1_11_20 ON part_id1_11_20(id1);
CREATE RULE part_id1_0_10_insert AS ON INSERT TO part
WHERE ( id1>= 0 and id1<=10 )
DO INSTEAD INSERT INTO part_id1_0_10 VALUES ( NEW.id1, NEW.id2, NEW.filler);
CREATE RULE part_id1_11_20_insert AS ON INSERT TO part
WHERE ( id1>=11 and id1<=20 )
DO INSTEAD INSERT INTO part_id1_11_20 VALUES ( NEW.id1, NEW.id2, NEW.filler);
analyze part_id1_0_10 ;
analyze part_id1_11_20 ;
CREATE VIEW part_all AS
select * from part_id1_0_10
UNION ALL
select * from part_id1_11_20
;
postgres=# \d+ part
Table "public.part"
Column | Type | Modifiers | Description
--------+------------------------+-----------+-------------
id1 | integer | not null |
id2 | integer | not null |
filler | character varying(200) | |
Rules:
part_id1_0_10_insert AS
ON INSERT TO part
WHERE new.id1 >= 0 AND new.id1 <= 10 DO INSTEAD INSERT INTO part_id1_0_10 (id1, id2, filler)
VALUES (new.id1, new.id2, new.filler)
part_id1_11_20_insert AS
ON INSERT TO part
WHERE new.id1 >= 11 AND new.id1 <= 20 DO INSTEAD INSERT INTO part_id1_11_20 (id1, id2, filler)
VALUES (new.id1, new.id2, new.filler)
Has OIDs: no
-------------------------------------------
When I try :
insert into part(id1, id2, filler)
select
round( (random()*10)::bigint,0) as id1,
round( (random()*20)::bigint,0) as id2,
'TTTTTTTTTTTESTTTTTZZZZZZZZZZZZZZZZZZZ'
from generate_series(0,100000);
All the data is redirected to part_id1_0_10 (as expected).
But When I issue:
insert into part(id1, id2, filler)
select
round( (random()*20)::bigint,0) as id1, <---!!! Note that both partitions should be populated!
round( (random()*20)::bigint,0) as id2,
'TTTTTTTTTTTESTTTTTZZZZZZZZZZZZZZZZZZZ'
from generate_series(0,100000);
I am getting :
ERROR: new row for relation "part_id1_0_10" violates check constraint "part_id1_0_10_id1_check"
How to fix the problem ? I thought that the rules were enough to redirect to records to
The right partions. Should I use triggers instead. The documentation is saying that can use either
Rules OR triggers:
http://www.enterprisedb.com/documentation/ddl-partitioning.html
Point 5 in 4.10.2. Implementing Partitioning.
Any suggestions ?
Many thanks in advance
Milen
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2006-06-12 22:17:46 | Re: Partitioning... |
Previous Message | jdwatson1 | 2006-06-12 21:44:34 | BLOB & Searching |