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

[Josh Berkus]

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!

-- 
Josh Berkus                GreenPlum Inc
Community Liason       www.greenplum.com
415-752-2500       jberkus at greenplum.com