Thread: Partitioning...

Partitioning...

From
"Milen Kulev"
Date:
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


Re: Partitioning...

From
Tom Lane
Date:
"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

Re: Partitioning...

From
"Milen Kulev"
Date:
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


Re: Partitioning...

From
"Milen Kulev"
Date:
 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


Re: Partitioning...

From
Tom Lane
Date:
"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

Re: Partitioning...

From
"Milen Kulev"
Date:
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


Re: Partitioning...

From
"Jim C. Nasby"
Date:
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

Re: Partitioning...

From
"Milen Kulev"
Date:
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


Re: Partitioning...

From
Tom Lane
Date:
"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

Re: Partitioning... -> solved

From
"Milen Kulev"
Date:
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