Re: [HACKERS] multi-column range partition constraint - Mailing list pgsql-hackers

Per an off-list report from Olaf Gawenda (thanks Olaf), it seems that the
range partition's constraint is sometimes incorrect, at least in the case
of multi-column range partitioning.  See below:

create table p (a int, b int) partition by range (a, b);
create table p1 partition of p for values from (1, 1) to (10 ,10);
create table p2 partition of p for values from (11, 1) to (20, 10);

Perhaps unusual, but it's still a valid definition.  Tuple-routing puts
rows where they belong correctly.

-- ok
insert into p values (10, 9);
select tableoid::regclass, * from p;
 tableoid | a  | b
----------+----+---
 p1       | 10 | 9
(1 row)

-- but see this
select tableoid::regclass, * from p where a = 10;
 tableoid | a | b
----------+---+---
(0 rows)

explain select tableoid::regclass, * from p where a = 10;
                QUERY PLAN
-------------------------------------------
 Result  (cost=0.00..0.00 rows=0 width=12)
   One-Time Filter: false
(2 rows)

-- or this
insert into p1 values (10, 9);
ERROR:  new row for relation "p1" violates partition constraint
DETAIL:  Failing row contains (10, 9).

This is because of the constraint being generated is not correct in this
case.  p1's constraint is currently:

  a >= 1 and a < 10

where it should really be the following:

  (a > 1  OR (a = 1  AND b >= 1))
    AND
  (a < 10 OR (a = 10 AND b < 10))

Attached patch rewrites get_qual_for_range() for the same, along with some
code rearrangement for reuse.  I also added some new tests to insert.sql
and inherit.sql, but wondered (maybe, too late now) whether there should
really be a declarative_partition.sql for these, moving in some of the old
tests too.

Adding to the open items list.

Thanks,
Amit

PS: due to vacation, I won't be able to reply promptly until Monday 05/08.