On Tue, May 2, 2017 at 2:47 PM, Amit Langote <langote_amit...@lab.ntt.co.jp> wrote:
> Hi Beena, > > On 2017/05/02 17:47, Beena Emerson wrote: > > Hello Amit, > > > > On Tue, May 2, 2017 at 12:21 PM, Amit Langote < > langote_amit...@lab.ntt.co.jp > >> wrote: > > > >> 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)) > >> > > > > > > IIUC, when we say range 1 to 10 we allow values from 1 to 9. Here we are > > allowing a=10 be stored in p1 Is it okay? > > Yes it is. Consider that the multi-column range partitioning uses > tuple-comparison logic to determine if a row's partition key is >= > lower_bound and < upper_bound tuple. > > In this case, p1's lower bound is a "tuple" (1, 1) and (10, 10) its upper > bound. Consider an input row with (2, -1) as its partition key. > Tuple-comparison logic puts this row into p1, because: > > select (1, 1) <= (2, -1) and (2, -1) < (10, 10); > ?column? > ---------- > t > (1 row) > > When performing tuple-comparison with the lower bound, since 2 > 1, the > tuple comparison is done and the whole tuple is concluded to be > (1, 1); > no attention is paid to the second column (or to the fact that -1 not >= > 1). > Now consider an input row with (10, 9) as its partition key, which too > fits in p1, because: > > select (1, 1) <= (10, 9) and (10, 9) < (10, 10); > ?column? > ---------- > t > (1 row) > > In this case, since 10 = 10, tuple-comparison considers the next column to > determine the result. In this case, since the second column 9 < 10, the > whole tuple is concluded to be < (10, 10). > > However, neither of (1, 0), (10, 10), or (10, 11) is admissible into p1 by > the above comparison logic: > > select (1, 1) <= (1, 0) and (1, 0) < (10, 10); > ?column? > ---------- > f > (1 row) > > select (1, 1) <= (10, 10) and (10, 10) < (10, 10); > ?column? > ---------- > f > (1 row) > > select (1, 1) <= (10, 11) and (10, 11) < (10, 10); > ?column? > ---------- > f > (1 row) > > I havent been following these partition mails much. Sorry if I am missing > > something obvious. > > No problem. > I have recently started looking at partition. I was wondering why 2nd column is ignored and the exceptions. For following partitions: 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); IIUC, we can store values a = 1 to 9 , 11 to 19 and any value in b. But can store 10 and 20 only when b <=9. This still seems a bit confusing to me but thank you for your explanation. These are just rules u have to abide by I guess! > > >> 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. > >> > > I got the following warning on compiling: > > partition.c: In function ‘make_partition_op_expr’: > > partition.c:1267:2: warning: ‘result’ may be used uninitialized in this > > function [-Wmaybe-uninitialized] > > return result; > > Oops, fixed. Updated patch attached. > > Thank you, -- Beena Emerson EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
