Skip site navigation (1) Skip section navigation (2)

Re: (possible) bug with constraint exclusion

From: "Rajesh Kumar Mallah" <mallah(dot)rajesh(at)gmail(dot)com>
To: "postgres sql" <pgsql-sql(at)postgresql(dot)org>
Subject: Re: (possible) bug with constraint exclusion
Date: 2008-01-11 07:20:13
Message-ID: a97c77030801102320g63f4fa08j163b67494dfe188@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-sql
Update the phenomenon does not exists in 8.2.0 but  exists in 8.2.5.


On Jan 11, 2008 12:28 PM, Rajesh Kumar Mallah <mallah(dot)rajesh(at)gmail(dot)com> wrote:
> Hi ,
>
> looks like constraint exclusion is being too aggressive in excluding null values
> although its well known that check constraints apply on not null values only.
> Hope the minimal test session below explains the problem we facing.
> BTW: we are very impressed with the performance gains we achieved  by
> partitioning a table recently.
>
>
>
> tradein_clients=> SELECT version();
>                                               version
> ---------------------------------------------------------------------------------------------------
>  PostgreSQL 8.2.6 on i686-pc-linux-gnu, compiled by GCC gcc (GCC)
> 3.4.6 20060404 (Red Hat 3.4.6-9)
> (1 row)
>
> tradein_clients=> \pset null NULL
>
>
>  tradein_clients=> \d x
>         Table "temp.x"
>  Column |  Type   | Modifiers
> --------+---------+-----------
>  id     | integer |
> Check constraints:
>     "x_id_check" CHECK (id > 0)
>
> tradein_clients=> SELECT * from x;
>   id
> ------
>     1
>     2
>  NULL
> (3 rows)
>
> tradein_clients=> explain SELECT * from  x  where id is null;
>                 QUERY PLAN
> ------------------------------------------
>  Result  (cost=0.00..0.01 rows=1 width=0)
>    One-Time Filter: false
> (2 rows)
>
> tradein_clients=> SELECT * from  x  where id is null;
>  id
> ----
> (0 rows)
> tradein_clients=> SET constraint_exclusion to off;
> SET
> tradein_clients=> SELECT * from  x  where id is null;
>   id
> ------
>  NULL
> (1 row)
>
> tradein_clients=>
>
> Regds
> mallah.
>

In response to

pgsql-bugs by date

Next:From: Jan Arne HansenDate: 2008-01-11 14:30:20
Subject: BUG #3868: Unattended install fails
Previous:From: Rajesh Kumar MallahDate: 2008-01-11 06:58:09
Subject: (possible) bug with constraint exclusion

pgsql-sql by date

Next:From: Richard HuxtonDate: 2008-01-11 08:24:35
Subject: Re: trigger for TRUNCATE?
Previous:From: Rajesh Kumar MallahDate: 2008-01-11 06:58:09
Subject: (possible) bug with constraint exclusion

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group