From: | Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> |
---|---|
To: | Pg Hackers <pgsql-hackers(at)postgresql(dot)org> |
Cc: | olaf(dot)gw(at)googlemail(dot)com |
Subject: | multi-column range partition constraint |
Date: | 2017-05-02 06:51:21 |
Message-ID: | 1dfff343-c74d-042f-fee1-547253be0a40@lab.ntt.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.
Attachment | Content-Type | Size |
---|---|---|
0001-Emit-correct-range-partition-constraint-expression.patch | text/x-diff | 34.3 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Petr Jelinek | 2017-05-02 06:55:53 | Re: snapbuild woes |
Previous Message | Craig Ringer | 2017-05-02 06:24:49 | Re: Potential hot-standby bug around xacts committed but in xl_running_xacts |