Partitioning and sub-partitioning problems - Mailing list pgsql-general

Hi Listers,
I am  playing a little bit more with partioning (constraint exclusion) in tha last days.
I have  tried with one-level partioning (one parent table + 2 inherited tables).
Worls like a charm.
I want to go further, I and wanted to test  sub-partiotioning (grandchild tables of the master table). And here I have
got problems. Let me explain the detals with my code:


-- master table
-- table defintion
CREATE TABLE part (
    id1    int not null,
    id2    int not null,
    id3    int not null,
    filler varchar(200)
    );


-- partitioning level1 is on id1 column
-- partitioning level2 is on id2 column

-- Partitions level 1
create table part_id1_0_10  ( CHECK ( id1>= 1 and  id1<=10)  ) INHERITS  (part);
create table part_id1_11_20 ( CHECK ( id1>=11 and  id1<=20)  ) INHERITS  (part);

-- Partitions level 2
-- subpartitions for parent partition1
create table part_id1_0_10__id2_0_10  ( CHECK ( id2>=  0 and  id2<=10) ) INHERITS(part_id1_0_10);
create table part_id1_0_10__id2_11_20 ( CHECK ( id2>= 11 and  id2<=20) ) INHERITS(part_id1_0_10);

-- subpartitions for parent partition2
create table part_id1_11_20__id2_0_10  ( CHECK ( id2>=  0 and  id2<=10) ) INHERITS(part_id1_11_20);
create table part_id1_11_20__id2_11_20 ( CHECK ( id2>= 11 and  id2<=20) ) INHERITS(part_id1_11_20);

I have INSERT  rule for each table (either on level1 or level2 ), for example
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.id3, NEW.filler);

...
...
...

My problems starts to appear when I try to populate the partitions with data:
1) I have generated an CSV falr file with data. The ranges generated for id1 are
0-20 and for id2 are 0-20 (to keep the test case simple;) ).
I can NOT populate the partitioned table "part" with COPY command because RULEs are not invoked (URL :
http://www.postgresql.org/docs/8.1/interactive/sql-copy.html). Partitioning the data
In CSV file is not an option for me.

Question:
~~~~~~~~~
If I use TRIGGERS instead of RULEs what will be the performance penalty ? I Prefer using RULEs, since they are doing a
better job for me in this case (RULEs are ingerited too from subpartitions)

2) I have creates a TEMPORARY  Table and loaded the data in it.
postgres=# \timing
Timing is on.
postgres=# copy  tmp_tbl from  '/tmp/OUT3'  DELIMITER as ',' ;
COPY
Time: 7131.689 ms
postgres=# copy part from  '/tmp/OUT3'  DELIMITER as ',' ;
COPY
Time: 26649.487 ms

But when I try to load the table "part" I still get an error message, and don't know why
postgres=# insert into  part select * from  tmp_tbl;
ERROR:  new row for relation "part_id1_0_10__id2_0_10" violates check constraint "part_id1_0_10_id1_check"

postgres=# \d+ "part_id1_0_10__id2_0_10"
          Table "public.part_id1_0_10__id2_0_10"
 Column |          Type          | Modifiers | Description
--------+------------------------+-----------+-------------
 id1    | integer                | not null  |
 id2    | integer                | not null  |
 id3    | integer                | not null  |
 filler | character varying(200) |           |
Check constraints:
    "part_id1_0_10__id2_0_10_id2_check" CHECK (id2 >= 0 AND id2 <= 10)
    "part_id1_0_10_id1_check" CHECK (id1 >= 1 AND id1 <= 10)
Inherits: part_id1_0_10
Has OIDs: no

part_id1_0_10_id1_check is inherited check constraint


Questions:
~~~~~~~~~~~
How can I trace which record is causing the problem (possibly without PGSQL!) ?
Why TEMP table is almost 4 times faster than the normal table (it is good, I am just curious;))



Best regards. MILEN