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? I havent been following these partition mails much. Sorry if I am missing something obvious. > > 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. > > 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; -- Beena Emerson EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
