Re: Partitioned tables constraint_exclusion

See Simon's reply... timestamptz math is *not* IMMUTABLE, because  
sessions are free to change their timezone at any time. I bet you can  
get some invalid results using that function with a clever test case.

On Mar 26, 2007, at 3:48 PM, Weslee Bilodeau wrote:

> Weslee Bilodeau wrote:
>> Mainly its because the value comes from a reporting system that has
>> minimal brains, it passes values it gets from the user directly  
>> into a
>> query.
>>
>> IE, they enter '1 month', which I use to populate the interval value,
>> "ts > ( NOW() - $VALUE )"
>>
>> But, in the example I did a "timestamp - interval", the exact  
>> date, not
>> NOW() - Still didn't work.
>>
>> I'm guessing anything that has to think, math, etc is not valid for
>> constrain_exclusion?
>>
>> Its not in the docs anywhere, so trying to isolate what can and  
>> can't be
>> done.
>
> This works -
>
> CREATE FUNCTION now_interval( interval ) RETURNS timestamptz IMMUTABLE
> STRICT AS $$ SELECT NOW() - $1 $$ LANGUAGE SQL ;
>
> SELECT count(*) FROM master WHERE var_ts > now_interval( '1 month' );
>
> This doesn't work -
>
> SELECT count(*) FROM master WHERE var_ts > ( NOW() - '1  
> month'::interval );
>
>
> This works for me, as the reporting system I know doesn't change
> timezones, and function cache doesn't last longer then the current  
> select?
>
>
> But, its basically the exact same logic in both cases?
>
> Weslee
>
>
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that  
> your
>        message can get through to the mailing list cleanly
>

--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)