Search Postgresql Archives

Partitioning...

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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 



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux