Thread: Partitioning...
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
"Milen Kulev" <makulev@gmx.net> writes:
> 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);
> ERROR: new row for relation "part_id1_0_10" violates check constraint "part_id1_0_10_id1_check"
Don't use random() in your test case.
regards, tom lane
Thanks for the prompt reply Tom,
What is wrong with random() ?
The following snipped is working ...
insert into part(id1, id2, filler)
select
11 + round( (random()*9)::bigint,0) as id1, --- 11-20 range for id1 , as of definition
round( (random()*20)::bigint,0) as id2,
'TTTTTTTTTTTESTTTTTZZZZZZZZZZZZZZZZZZZ'
from generate_series(0,100000);
Regards. Milen
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Tuesday, June 13, 2006 12:18 AM
To: Milen Kulev
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Partitioning...
"Milen Kulev" <makulev@gmx.net> writes:
> 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);
> ERROR: new row for relation "part_id1_0_10" violates check
> constraint "part_id1_0_10_id1_check"
Don't use random() in your test case.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
BTW , I can not see the planner to prune/isolate the right table/partion ?
(constraint_exclusion=on, Version= 8.1.3 on RHEL4)
postgres=# explain analyze select count(id1) from part_all where id1 =12 ;
QUERY PLAN
---------------------------------------------------------------------------------------------------
Aggregate (cost=5951.74..5951.75 rows=1 width=4) (actual time=111.687..111.688 rows=1 loops=1)
-> Append (cost=164.60..5463.19 rows=39084 width=49) (actual time=4.764..91.488 rows=13203 loops=1)
-> Bitmap Heap Scan on part_id1_0_10 (cost=164.60..3577.60 rows=25600 width=49) (actual time=0.123..0.123
rows=0 loops=1)
Recheck Cond: (id1 = 12)
-> Bitmap Index Scan on idx_part_id1_0_10 (cost=0.00..164.60 rows=25600 width=0) (actual
time=0.119..0.119 rows=0 loops=1)
Index Cond: (id1 = 12)
-> Bitmap Heap Scan on part_id1_11_20 (cost=88.19..1494.74 rows=13484 width=49) (actual time=4.635..55.140
rows=13203 loops=1)
Recheck Cond: (id1 = 12)
-> Bitmap Index Scan on idx_part_id1_11_20 (cost=0.00..88.19 rows=13484 width=0) (actual
time=4.067..4.067 rows=13205 loops=1)
Index Cond: (id1 = 12)
Total runtime: 111.812 ms
(11 rows)
Time: 114.779 ms
Regards. Milen
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Tuesday, June 13, 2006 12:18 AM
To: Milen Kulev
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Partitioning...
"Milen Kulev" <makulev@gmx.net> writes:
> 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);
> ERROR: new row for relation "part_id1_0_10" violates check
> constraint "part_id1_0_10_id1_check"
Don't use random() in your test case.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
"Milen Kulev" <makulev@gmx.net> writes:
> What is wrong with random() ?
Not guaranteed to be stable across the multiple evaluations that the
rule will perform ... remember a rule is a macro and has the usual
multiple-evaluation gotchas in the face of volatile arguments.
regards, tom lane
Aha !
Obviosly that is the reason for working sometimes properly ans sometimes not ...
Thanks !
Regards. Milen
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Tuesday, June 13, 2006 12:58 AM
To: Milen Kulev
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Partitioning...
"Milen Kulev" <makulev@gmx.net> writes:
> What is wrong with random() ?
Not guaranteed to be stable across the multiple evaluations that the rule will perform ... remember a rule is a macro
and has the usual multiple-evaluation gotchas in the face of volatile arguments.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
On Mon, Jun 12, 2006 at 06:58:06PM -0400, Tom Lane wrote:
> "Milen Kulev" <makulev@gmx.net> writes:
> > What is wrong with random() ?
>
> Not guaranteed to be stable across the multiple evaluations that the
> rule will perform ... remember a rule is a macro and has the usual
> multiple-evaluation gotchas in the face of volatile arguments.
I believe a safe alternative would be...
INSERT INTO ... SELECT * FROM
(SELECT random()*20 FROM ...)
;
You might need to add an ORDER BY to the subquery to ensure PostgreSQL
doesn't pull it into the main query.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Aha !!!
Thanks Jim, the "trick" worked for me !
Regards. Milen
-----Original Message-----
From: Jim C. Nasby [mailto:jnasby@pervasive.com]
Sent: Wednesday, June 14, 2006 1:09 AM
To: Tom Lane
Cc: Milen Kulev; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Partitioning...
On Mon, Jun 12, 2006 at 06:58:06PM -0400, Tom Lane wrote:
> "Milen Kulev" <makulev@gmx.net> writes:
> > What is wrong with random() ?
>
> Not guaranteed to be stable across the multiple evaluations that the
> rule will perform ... remember a rule is a macro and has the usual
> multiple-evaluation gotchas in the face of volatile arguments.
I believe a safe alternative would be...
INSERT INTO ... SELECT * FROM
(SELECT random()*20 FROM ...)
;
You might need to add an ORDER BY to the subquery to ensure PostgreSQL doesn't pull it into the main query.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
"Milen Kulev" <makulev@gmx.net> writes:
> Thanks Jim, the "trick" worked for me !
>> You might need to add an ORDER BY to the subquery to ensure
>> PostgreSQL doesn't pull it into the main query.
Actually, the usual trick is "OFFSET 0", which works just as well as
an optimization fence and doesn't force any significant extra work.
Of course, if you *need* an ORDER BY then that's what to use, but
it's overkill if you just want to prevent flattening the subquery.
regards, tom lane
Thanks Tom,
OFFSET is even better solution for me .
Regards. Milen
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, June 14, 2006 3:57 PM
To: Milen Kulev
Cc: 'Jim C. Nasby'; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Partitioning...
"Milen Kulev" <makulev@gmx.net> writes:
> Thanks Jim, the "trick" worked for me !
>> You might need to add an ORDER BY to the subquery to ensure
>> PostgreSQL doesn't pull it into the main query.
Actually, the usual trick is "OFFSET 0", which works just as well as an optimization fence and doesn't force any
significant extra work. Of course, if you *need* an ORDER BY then that's what to use, but it's overkill if you just
want
to prevent flattening the subquery.
regards, tom lane