X-Original-To: pgsql-general-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 29DB09FA68D for ; Mon, 12 Jun 2006 19:03:03 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 33699-06 for ; Mon, 12 Jun 2006 19:02:51 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from mail.gmx.net (mail.gmx.de [213.165.64.20]) by postgresql.org (Postfix) with SMTP id A32349FA682 for ; Mon, 12 Jun 2006 19:02:52 -0300 (ADT) Received: (qmail invoked by alias); 12 Jun 2006 22:02:48 -0000 Received: from dslb-084-057-013-058.pools.arcor-ip.net (EHLO ltmku) [84.57.13.58] by mail.gmx.net (mp035) with SMTP; 13 Jun 2006 00:02:48 +0200 X-Authenticated: #23352258 From: "Milen Kulev" To: Subject: Partitioning... Date: Tue, 13 Jun 2006 00:02:42 +0200 Message-ID: <000001c68e6b$f0ecbf50$0a00a8c0@trivadis.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.6626 Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 X-Y-GMX-Trusted: 0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/470 X-Sequence-Number: 96842 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