Re: Partitioned tables constraint_exclusion

Jim Nasby wrote:
> 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.
>

I'm pretty sure it could easily be broken.
But to make it easier for me, I know that the reporting system connects,
runs the query, and disconnects.

So I'm so far safe using my current system.

If the system had persistent connections and changed timezones a lot, it
might however cause problems.

Its been the only way that I could get it to be smart enough to not use
the tables outside its range.

With the tables growing 2+ million rows a day, approaching 1 billion
rows, its helps performance a lot.

This works at least until the ongoing discussion of partitioned tables
hopefully improves things in this area.

> On Mar 26, 2007, at 3:48 PM, Weslee Bilodeau wrote:
>> 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)
>
>
>

Weslee