[Bizgres-general] constraint exclusion (table partitioning) not working?

[Luke Lonergan]

Bob, Josh,

Thanks for finding this!

- Luke


On 9/14/05 10:34 AM, "Josh Berkus" <jberkus at greenplum.com> wrote:

> Bob,
> 
>> If you make the a column bigserial (or bigint), and it'll be busted.  
>> Surprisingly, numeric seems to work.
> 
> Thanks for finding this.  It's a bit of a corner case but a lot of users
> are going to run across it.
> 
> What's happening is that, if your constraint is declared without data
> types, default type coercion takes over, i.e.
> 
> check a.a between 1 and 3
> 
> becomes effectively
> 
> check a.a between 1::INT and 3::INT
> 
> However, when you run the query, the correct type conversion happens:
> 
> where a.a between 5 and 6
> 
> becomese effectively
> 
> where a.a between 5::BIGINT and 6::BIGINT
> 
> which isn't comparable to 5::INT and thus CE does not happen.
> 
> The temporary work-around is to make sure to declare your constraints with
> data types:
> 
> CHECK a.a BETWEEN 1::BIGINT and 3::BIGINT
> 
> and then CE will happen.
> 
> Unfortunately, this means that this is actually a bug in PostgreSQL's type
> coercion inside constraints, which makes it harder to fix.   We'll see if
> it's possible to get something in before 8.1!