DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=nJfM30knoEvx+UF+OfjJPQ+V+s+cN7eq9m8N/UPqpwLy7VX+QBa7kKrj7hZzxCGAb52xDLYe0F6AK6DdSA3cSOPPn8fb8TgbNKoOsNYzHnV3HjmTvRFGA97eDP1oIb6/Dd1EfgZPBf+2kvG4S3EYyP8HUMzzE3P8kZLtWUIQJn0=
Message-ID: <a97c77030801112100w1c4c4fadwb5eaeab52ad4fe7e@mail.gmail.com>
Date: Sat, 12 Jan 2008 10:30:43 +0530
From: "Rajesh Kumar Mallah" <mallah.rajesh@gmail.com>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Subject: Re: (possible) bug with constraint exclusion
Cc: pgsql-sql@postgresql.org
In-Reply-To: <23924.1200081368@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <a97c77030801102258y3d501045u6b31afe0541997db@mail.gmail.com>
	<23924.1200081368@sss.pgh.pa.us>

On Jan 12, 2008 1:26 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Rajesh Kumar Mallah" <mallah.rajesh@gmail.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
>