Re: default range partition and constraint exclusion

On 2017/11/22 6:31, Robert Haas wrote:
> On Tue, Nov 21, 2017 at 4:36 AM, Amit Langote
> <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>>>> The attached will make the constraint to look like:
>>>
>>> Uh, if the constraint exclusion logic we're using is drawing false
>>> conclusions, we need to fix it so it doesn't, not change the
>>> constraint so that the wrong logic gives the right answer.
>>
>> I did actually consider it, but ended up concluding that constraint
>> exclusion is doing all it can using the provided list partition constraint
>> clauses.
>>
>> If all predicate_refuted_by() receives is the expression tree (AND/OR)
>> with individual nodes being strict clauses involving partition keys (and
>> nothing about the nullness of the keys), the downstream code is just
>> playing by the rules as explained in the header comment of
>> predicate_refuted_by_recurse() in concluding that query's restriction
>> clause a = 2 refutes it.
>
> Oh, wait a minute. Actually, I think predicate_refuted_by() is doing
> the right thing here. Isn't the problem that mc2p2 shouldn't be
> accepting a (2, null) tuple at all?

It doesn't. But, for a query, it does contain (2, <unknown>) tuples,
where <unknown> would always be non-null. So, it should be scanned in the
plan for the query that has only a = 2 as restriction and no restriction
on b. That seems to work.

\d+ mc2p2
...
Partition of: mc2p FOR VALUES FROM (1, 1) TO (MAXVALUE, MAXVALUE)
Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND ((a > 1) OR
((a = 1) AND (b >= 1))))

insert into mc2p2 values (2, null);
ERROR: new row for relation "mc2p2" violates partition constraint
DETAIL: Failing row contains (2, null).

explain select * from mc2p where a = 2;
QUERY PLAN
-------------------------------------------------------------
Append (cost=0.00..38.25 rows=11 width=8)
-> Seq Scan on mc2p2 (cost=0.00..38.25 rows=11 width=8)
Filter: (a = 2)
(3 rows)

My complaint is about m2p_default not being included in the plan for a
query with only a = 2 restriction. The aforementioned <unknown> includes
null and it's only m2p_default that has such tuples, so it should be in
the plan for queries that don't explicitly prevent null values for b by
including b is not null in the query.

With the patch:

explain (costs off) select * from mc2p where a = 2;
QUERY PLAN
-------------------------------------------------------------------
Append
-> Seq Scan on mc2p_default
Filter: ((b IS NULL) AND (a = 2))
(3 rows)

explain (costs off) select * from mc2p where a = 2 and b is null;
QUERY PLAN
-------------------------------------------------------------------
Append
-> Seq Scan on mc2p_default
Filter: ((b IS NULL) AND (a = 2))
(3 rows)

explain (costs off) select * from mc2p where a = 2 and b is not null;
QUERY PLAN
-------------------------------------------------------------
Append
-> Seq Scan on mc2p2
Filter: ((b IS NOT NULL) AND (a = 2))
(3 rows)

Thanks,
Amit