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

[Bob Ippolito]

On Sep 14, 2005, at 1:34 PM, Josh Berkus 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!

Thanks for taking the time to track this down, it would've taken me a  
lot longer to come up with an explanation and/or workaround!

While it is unfortunate that the type inference needs a little help  
in this case, the workaround is quite reasonable and isn't going to  
be a problem at all for us.  I can confirm that rewriting the CHECK  
constraints to include explicit type information does indeed allow CE  
to take place.

-bob