From: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: (possible) bug with constraint exclusion
Date: Sat, 12 Jan 2008 10:30:43 +0530
Message-ID: <a97c77030801112100w1c4c4fadwb5eaeab52ad4fe7e@mail.gmail.com> (raw)
In-Reply-To: <23924.1200081368@sss.pgh.pa.us>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mallah.rajesh@gmail.com, tgl@sss.pgh.pa.us
Subject: Re: (possible) bug with constraint exclusion
In-Reply-To: <a97c77030801112100w1c4c4fadwb5eaeab52ad4fe7e@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox