Re: (possible) bug with constraint exclusion

On Jan 12, 2008 1:26 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Rajesh Kumar Mallah" <mallah(dot)rajesh(at)gmail(dot)com> writes:
> > looks like constraint exclusion is being too aggressive in excluding null values
>
> Hmm, you're right. Looks like I broke it here:
> http://archives.postgresql.org/pgsql-committers/2007-05/msg00187.php
>
> > although its well known that check constraints apply on not null values only.
>
> No, that is not a correct statement either --- it's exactly that type of
> sloppy thinking that got me into trouble with this patch :-(
>
> The problem is that predicate_refuted_by_simple_clause() is failing to
> distinguish whether "refutes" means "proves false" or "proves not true".
> For constraint exclusion we have to use the stricter "proves false"
> interpretation, and in that scenario a clause "foo IS NULL" fails to
> refute a check constraint "foo > 0", because the latter will produce
> NULL which isn't false and therefore doesn't cause the check constraint
> to fail.
>
> The motivation for that patch was to support IS NULL as one partition
> of a partitioned table. Thinking about it I see that if the other
> partitions have check constraints like "foo > 0" then the partitioning
> is actually incorrect, because the other check constraints are failing
> to exclude NULLs. The right way to set up such a partitioned table is
> to include "foo IS NOT NULL" as part of the check constraint, or as
> a special-purpose NOT NULL flag, except in the IS NULL partition.
> The current constraint exclusion logic fails to notice attnotnull,
> though. So the correct fix seems to be:

Dear Tom,
Thanks for the elaborate explanation on your part,
owing to my limitations I could not understand all the parts of it.
Am I correct in understanding that the current behavior is inappropriate
and shall be corrected at some point of time in future versions ?
thanks once again to all the developers for making PostgreSQL.

regds
mallah.

>
> * Fix predicate_refuted_by_simple_clause to not suppose that a strict
> operator is proved FALSE by an IS NULL clause.
>
> * Fix relation_excluded_by_constraints to add "foo IS NOT NULL" clauses
> to the constraint list for attnotnull columns (perhaps this should be
> pushed into get_relation_constraints?). This buys back the loss of
> exclusion from the other change, so long as the partitioning is done
> correctly.
>
> regards, tom lane
>