Partitioning and sub-partitioning problems

From: "Milen Kulev" <makulev(at)gmx(dot)net>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Partitioning and sub-partitioning problems
Date: 2006-06-15 19:31:21
Message-ID: 01ba01c690b2$47fd5140$0a00a8c0@trivadis.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2006-06-15 19:43:13 Re: SOLVED: Re: UTF-8 and stripping accents
Previous Message Christopher Murtagh 2006-06-15 19:22:09 SOLVED: Re: UTF-8 and stripping accents