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
