What happens if the partitions overlap? - Mailing list pgsql-novice

I have a table that is indexed on two columns one is eximm message-id
(eseentially a base-62 timestamp that sorts usefully in locale
C but not in the locale the database uses so I have coerced it
into timestamptz) and the other an ordinary serial

As both the timestamp and the serial are generated at around the same
time the both follow the same approximate order.

I have chosen to partition on the timestamp as I feel it's easier to do
that in a cron job than it is to partition on the serial column.

many of the queries I want to run are selecting single records using the
the serial column.

can constraint exclusion still be used if the range ovelaps a little?

eg (using a int instad of a timestamp)

 create table foo (a int, b serial);
 create table foo_1(
   check( a => 10000  and a < 20000 and b>9811 and b < 20121)
   ) inherits (foo);
 create table foo_2(
   check( a => 20000  and a < 30000 and b>18702 and b < 30171)
   ) inherits (foo);
 create table foo_3(
   check( a => 30000  and a < 40000 and b>29212 )
   ) inherits (foo);
...

I am immagining that I could create the 'b' parts of the constraint an
hour or so after the cross over of a into the the new partition, when
I can be fairly sure that there are no low values left to be inserted
into the high-a partition, or high values of b left to go into the the
low a partition. I'd do this by inspection:

 select max(b) from foo_1

 etc.

before then I'd leave the upper end on the low partition unbounded and
use a value picked from an hour previous for the low bound on the high
partition.


Anyway. it seems to work with these overlaps:


 SET constraint_exclusion = on;

 explain select * from foo where b=20000;
                              QUERY PLAN

-----------------------------------------------------------------------
 Result  (cost=0.00..110.25 rows=33 width=8)
   ->  Append  (cost=0.00..110.25 rows=33 width=8)
        ->  Seq Scan on foo  (cost=0.00..36.75 rows=11 width=8)
               Filter: (b = 20000)
        ->  Seq Scan on foo_1 foo  (cost=0.00..36.75 rows=11 width=8)
               Filter: (b = 20000)
        ->  Seq Scan on foo_2 foo  (cost=0.00..36.75 rows=11 width=8)
               Filter: (b = 20000)

It seems to have worked here, but there is a warning against it on this page:

http://www.postgresql.org/docs/8.3/interactive/ddl-partitioning.html#DDL-PARTITIONING-CONSTRAINT-EXCLUSION

> 3: We must provide non-overlapping table constraints. Rather than just
> creating the partition tables as above,

and

>  Ensure that the constraints guarantee that there is no overlap
> between the key values permitted in different partitions. A common
> mistake is to set up range constraints like this:
>
> CHECK ( outletID BETWEEN 100 AND 200 )
> CHECK ( outletID BETWEEN 200 AND 300 )
>
> This is wrong since it is not clear which partition the key value 200
> belongs in.

Is overlap actually bad (leading to corruption or other serious
failure), or just a little inefficient, meaning that in rare cases two tables
need to be checked instead of just one?