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(at)postgresql(dot)org so that
> your
> message can get through to the mailing list cleanly
>

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