From: | Beena Emerson <memissemerson(at)gmail(dot)com> |
---|---|
To: | Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> |
Cc: | Pg Hackers <pgsql-hackers(at)postgresql(dot)org>, olaf(dot)gw(at)googlemail(dot)com |
Subject: | Re: multi-column range partition constraint |
Date: | 2017-05-02 08:47:36 |
Message-ID: | CAOG9ApH4NQ54OCqV5TbLBq4iROPvwN6VUM85FuRM47k1+zgeEw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hello Amit,
On Tue, May 2, 2017 at 12:21 PM, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Dmitriy Sarafannikov | 2017-05-02 08:59:30 | Re: [PROPOSAL] Use SnapshotAny in get_actual_variable_range |
Previous Message | Michael Paquier | 2017-05-02 07:43:37 | Re: logical replication and PANIC during shutdown checkpoint in publisher |